Oracle DB 26ai AI/Vector - #8

2026. 10. 26. 09:05

SQL Firewall

Capture(학습) - Oracle DB에서 실행 되는 SQL 관찰해서 기록
Generate(허용목록) - Oracle DB에서 실행 허용 가능 SQL 목록 작성
Enforce(강제) - Oracle DB에서 허용목록에 없는 SQL실행 차단, 경고 기록

SQL Firewall Test

[oracle@rhel10nm ~]$ --##SQL Firewall 활성화
[oracle@rhel10nm ~]$ id
uid=5001(oracle) gid=500(dba) groups=500(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@rhel10nm ~]$ env | grep SID
ORACLE_SID=CDB1
[oracle@rhel10nm ~]$ sqlplus "/as sysdba"
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show user
USER is "SYS"
SQL>
SQL> ALTER SESSION SET CONTAINER = PDB1;
Session altered.
SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> show user
USER is "SYS"
SQL>
SQL> EXEC DBMS_SQL_FIREWALL.ENABLE;
PL/SQL procedure successfully completed.
SQL> --결과 확인
SQL> COLUMN parameter FORMAT A25
SQL> COLUMN value FORMAT A15
SQL> SELECT parameter, value FROM V$OPTION WHERE parameter = 'SQL Firewall';
PARAMETER                 VALUE
------------------------- ---------------
SQL Firewall              TRUE
SQL>
SQL>--VECLAB 대상 Capture(학습 모드) 시작
SQL> BEGIN
  2    DBMS_SQL_FIREWALL.CREATE_CAPTURE(
  3      username       => 'VECLAB',
  4      top_level_only => TRUE,
  5      start_capture  => TRUE
  6    );
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> --결과 확인
SQL> COLUMN username FORMAT A15
SQL> SELECT username, status, top_level_only FROM DBA_SQL_FIREWALL_CAPTURES WHERE username = 'VECLAB';

USERNAME        STATUS   TOP_LEVEL_ONLY
--------------- -------- --------------
VECLAB          ENABLED  Y

SQL>
SQL> --평소 업무 흉내내기 — 정상 SQL 녹화 (VECLAB으로 접속)
SQL> CONNECT veclab/VecLab_2026#@//localhost:1521/pdb1
Connected.
SQL> show user
USER is "VECLAB"
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL>
SQL> SELECT doc_id, COUNT(*) FROM lab_doc_chunks GROUP BY doc_id;
    DOC_ID   COUNT(*)
---------- ----------
         1          5
         2          3
         3          3
         4          3
         5          3

SQL> SELECT doc_id, chunk_id, chunk_text FROM lab_doc_chunks WHERE doc_id = 1;
    DOC_ID   CHUNK_ID
---------- ----------
CHUNK_TEXT
--------------------------------------------------------------------------------
         1          1
Oracle Data Guard is Oracle's primary high availability and disaster recovery so
lution. It maintains one or more standby databases that are transactionally cons
istent
         1          2
or more standby databases that are transactionally consistent
copies of a production database. When the production database becomes unavailabl
e, Data Guard can switch any standby to the production
    DOC_ID   CHUNK_ID
---------- ----------
CHUNK_TEXT
--------------------------------------------------------------------------------
         1          3
Guard can switch any standby to the production role, minimizing downtime. Active
         1          4
the production role, minimizing downtime. Active
Data Guard extends this capability by allowing the standby database to be opened
 for read-only queries and reporting while apply
    DOC_ID   CHUNK_ID
---------- ----------
CHUNK_TEXT
--------------------------------------------------------------------------------
         1          5
read-only queries and reporting while apply continues in real time.'
SQL> SELECT doc_title FROM lab_raw_docs WHERE doc_id = 3;
DOC_TITLE
--------------------------------------------------------------------------------
RMAN Guide
SQL>
SQL> exit
[oracle@rhel10nm ~]$
[oracle@rhel10nm ~]$ --##Capture 중지 + 녹화 내역 확인 (SYS)
[oracle@rhel10nm ~]$ id
uid=5001(oracle) gid=500(dba) groups=500(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@rhel10nm ~]$ env | grep SID
ORACLE_SID=CDB1
[oracle@rhel10nm ~]$
[oracle@rhel10nm ~]$ sqlplus "/as sysdba"
SQL> ALTER SESSION SET CONTAINER = PDB1;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> show user
USER is "SYS"
SQL> --Capture 중지
SQL> EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE('VECLAB');
PL/SQL procedure successfully completed.
SQL>
SQL> --갭처 내역 확인
SQL> SET LINESIZE 200 
SQL> COLUMN sql_text FORMAT A90
SQL> COLUMN current_user FORMAT A12
SQL>
SQL> SELECT sql_text, current_user, top_level FROM DBA_SQL_FIREWALL_CAPTURE_LOGS
  2  WHERE username = 'VECLAB' ORDER BY sql_text;
SQL_TEXT                                                                                   CURRENT_USER TOP_LEVEL
------------------------------------------------------------------------------------------ ------------ ---------
SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL  VECLAB       Y
SELECT DOC_ID,CHUNK_ID,CHUNK_TEXT FROM LAB_DOC_CHUNKS WHERE DOC_ID=:"SYS_B_0"              VECLAB       Y
SELECT DOC_ID,COUNT (*) FROM LAB_DOC_CHUNKS GROUP BY DOC_ID                                VECLAB       Y
SELECT DOC_TITLE FROM LAB_RAW_DOCS WHERE DOC_ID=:"SYS_B_0"                                 VECLAB       Y
SELECT SYS_CONTEXT (:"SYS_B_0",:"SYS_B_1") NAME_COL_PLUS_PDB_CONTAINER FROM SYS.DUAL       VECLAB       Y
SQL> --실행한 3개 쿼리가 전부 정확히 캡처됐고, 리터럴 값이 바인드 변수로 정규화 됨
SQL> --추가로 잡힌 2개는 SQLPlus가 접속할 때 자동으로 실행하는 내부 쿼리
SQL> --top_level_only=TRUE로 설정했는데도 잡힌 이유는, Oracle DB 내부 재귀 SQL이 아니라 SQL*LPlus 클라이언트가 사용자 세션에서 직접 날린 쿼리이기 때문
SQL> --허용목록 생성 및 강제 적용
SQL> BEGIN
  2    DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST(username => 'VECLAB');
  3  END;
  4  /
PL/SQL procedure successfully completed.
SQL>
SQL> --결과 확인 - 어떤 SQL이 허용목록에 들어갔는지 먼저 확인
SQL> COLUMN sql_text FORMAT A90
SQL> SELECT sql_text FROM DBA_SQL_FIREWALL_ALLOWED_SQL WHERE username = 'VECLAB' ORDER BY sql_text;
SQL_TEXT
------------------------------------------------------------------------------------------
SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL
SELECT DOC_ID,CHUNK_ID,CHUNK_TEXT FROM LAB_DOC_CHUNKS WHERE DOC_ID=:"SYS_B_0"
SELECT DOC_ID,COUNT (*) FROM LAB_DOC_CHUNKS GROUP BY DOC_ID
SELECT DOC_TITLE FROM LAB_RAW_DOCS WHERE DOC_ID=:"SYS_B_0"
SELECT SYS_CONTEXT (:"SYS_B_0",:"SYS_B_1") NAME_COL_PLUS_PDB_CONTAINER FROM SYS.DUAL
SQL>
SQL>
SQL> BEGIN
  2    DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST(
  3      username => 'VECLAB',
  4      enforce  => DBMS_SQL_FIREWALL.ENFORCE_ALL,
  5      block    => TRUE
  6    );
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> --결과 확인
SQL> SELECT username, status, enforce, block FROM DBA_SQL_FIREWALL_ALLOW_LISTS
  2  WHERE username = 'VECLAB';
USERNAME                                                                                                                         STATUS   ENFORCE         BLOCK
-------------------------------------------------------------------------------------------------------------------------------- -------- --------------- --------------
VECLAB                                                                                                                           ENABLED  ENFORCE_ALL     Y
SQL>
SQL> --등록정보 확인
SQL> COLUMN ip_address FORMAT A20
SQL> COLUMN os_program FORMAT A30
SQL> COLUMN os_user FORMAT A20
SQL>
SQL> --IP Address 기준
SQL> SELECT ip_address FROM DBA_SQL_FIREWALL_ALLOWED_IP_ADDR WHERE username = 'VECLAB';
IP_ADDRESS
--------------------
127.0.0.1
SQL>
SQL> --OS Program 기준
SQL> SELECT os_program FROM DBA_SQL_FIREWALL_ALLOWED_OS_PROG WHERE username = 'VECLAB';
OS_PROGRAM
------------------------------
sqlplus@rhel10nm (TNS V1-V3)
SQL>
SQL> --OS User 기준
SQL> SELECT os_user    FROM DBA_SQL_FIREWALL_ALLOWED_OS_USER WHERE username = 'VECLAB';
OS_USER
--------------------
oracle
SQL>
SQL> exit
[oracle@rhel10nm ~]$

SQL Firewall 차단 테스트

[oracle@rhel10nm ~]$
[oracle@rhel10nm ~]$ id
uid=5001(oracle) gid=500(dba) groups=500(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@rhel10nm ~]$ env | grep SID
ORACLE_SID=CDB1
[oracle@rhel10nm ~]$
[oracle@rhel10nm ~]$ sqlplus "/as sysdba"
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show user
USER is "SYS"
SQL>
SQL> CONNECT veclab/VecLab_2026#@//localhost:1521/pdb1
Connected.
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> show user
USER is "VECLAB"
SQL>
SQL> --1.학습된 SQL, 값만 다르게 -> 통과
SQL> SELECT doc_title FROM lab_raw_docs WHERE doc_id = 5;
DOC_TITLE
--------------------------------------------------------------------------------
GoldenGate Guide
SQL>
SQL> --2.학습 안 된 SQL (컬럼 구성이 다름) -> 차단
SQL> SELECT doc_id, doc_title FROM lab_raw_docs;
SELECT doc_id, doc_title FROM lab_raw_docs
                              *
ERROR at line 1:
ORA-47605: SQL Firewall violation
Help: https://docs.oracle.com/error-help/db/ora-47605/
SQL>
SQL> --3.SQL Injection 전형 패턴 -> 차단
SQL> SELECT doc_title FROM lab_raw_docs WHERE doc_id = 1 OR 1=1;
SELECT doc_title FROM lab_raw_docs WHERE doc_id = 1 OR 1=1
                      *
ERROR at line 1:
ORA-47605: SQL Firewall violation
Help: https://docs.oracle.com/error-help/db/ora-47605/
SQL>
SQL> exit
[oracle@rhel10nm ~]$ --##위반 Log 확인(PDB1/SYS)
[oracle@rhel10nm ~]$ sqlplus "/as sysdba"
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show user
USER is "SYS"
SQL>
SQL> ALTER SESSION SET CONTAINER = PDB1;
Session altered.
SQL> show user
USER is "SYS"
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL>
SQL> SET LINESIZE 200
SQL> COLUMN sql_text FORMAT A60
SQL> COLUMN cause FORMAT A25
SQL> COLUMN firewall_action FORMAT A12
SQL>
SQL> COLUMN ip_address FORMAT A15
SQL>
SQL> SELECT sql_text, cause, firewall_action, ip_address, occurred_at FROM DBA_SQL_FIREWALL_VIOLATIONS
  2  WHERE username = 'VECLAB' ORDER BY occurred_at;
SQL_TEXT                                                     CAUSE                     FIREWALL_ACT IP_ADDRESS      OCCURRED_AT
------------------------------------------------------------ ------------------------- ------------ --------------- ---------------------------------------------------------------------------
SELECT DOC_ID,DOC_TITLE FROM LAB_RAW_DOCS                    SQL violation             Blocked      127.0.0.1       04-SEP-26 03.11.42.037019 PM +09:00
SELECT DOC_TITLE FROM LAB_RAW_DOCS WHERE DOC_ID=:"SYS_B_0" O SQL violation             Blocked      127.0.0.1       04-SEP-26 03.11.58.417037 PM +09:00
R :"SYS_B_1"=:"SYS_B_2"
SQL>
SQL> --두 번째 로그에서 OR 1=1이 OR :"SYS_B_1"=:"SYS_B_2"로 정규화되어 기록
SQL> --공격자가 OR 1=1, OR 2=2, OR 'a'='a' 등 값을 바꿔가며 시도해도 전부 같은 패턴으로 잡힌다는 뜻
SQL> --SQL Firewall이 단순 문자열 매칭이 아니라 구조 기반으로 방어
SQL> 
SQL> --정당한 SQL을 허용목록에 추가
SQL> --sql_signature 파라미터는 SQL 텍스트가 아니라 해시값을 사용 해야 함. 
SQL> --위반 로그 뷰에서 실제 시그니처 값을 확인 해야 함
SQL> COLUMN sql_signature FORMAT A70
SQL> COLUMN sql_text FORMAT A60
SQL> SELECT sql_signature, sql_text
  2  FROM DBA_SQL_FIREWALL_VIOLATIONS
  3  WHERE username = 'VECLAB'
  4  ORDER BY occurred_at;
SQL_SIGNATURE                                                          SQL_TEXT
---------------------------------------------------------------------- ------------------------------------------------------------
D12F75414A5905506A60BC1342C55E39C70EDA1450B58B9884C3609A94C15851       SELECT DOC_ID,DOC_TITLE FROM LAB_RAW_DOCS
A103003D5DB7B20FC77121DE404ED15ECB8C6163D667DB7392368F2C8D409170       SELECT DOC_TITLE FROM LAB_RAW_DOCS WHERE DOC_ID=:"SYS_B_0" O
                                                                       R :"SYS_B_1"=:"SYS_B_2"
SQL>
SQL> --정당한 SQL을 허용목록에 추가
SQL> BEGIN
  2    DBMS_SQL_FIREWALL.APPEND_ALLOW_LIST_SINGLE_SQL(
  3      username      => 'VECLAB',
  4      sql_signature => 'D12F75414A5905506A60BC1342C55E39C70EDA1450B58B9884C3609A94C15851',
  5      current_user  => 'VECLAB',
  6      top_level     => 'Y',
  7      source        => DBMS_SQL_FIREWALL.VIOLATION_LOG
  8    );
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL> --결과 확인 허용목록 6건
SQL> COLUMN sql_text FORMAT A90
SQL> SELECT sql_text FROM DBA_SQL_FIREWALL_ALLOWED_SQL WHERE username = 'VECLAB' ORDER BY sql_text;
SQL_TEXT
------------------------------------------------------------------------------------------
SELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL
SELECT DOC_ID,CHUNK_ID,CHUNK_TEXT FROM LAB_DOC_CHUNKS WHERE DOC_ID=:"SYS_B_0"
SELECT DOC_ID,COUNT (*) FROM LAB_DOC_CHUNKS GROUP BY DOC_ID
SELECT DOC_ID,DOC_TITLE FROM LAB_RAW_DOCS
SELECT DOC_TITLE FROM LAB_RAW_DOCS WHERE DOC_ID=:"SYS_B_0"
SELECT SYS_CONTEXT (:"SYS_B_0",:"SYS_B_1") NAME_COL_PLUS_PDB_CONTAINER FROM SYS.DUAL
6 rows selected.
SQL>
SQL> --결과 확인 재시작 없이 즉시 반영
SQL> CONNECT veclab/VecLab_2026#@//localhost:1521/pdb1
Connected.
SQL> show user
USER is "VECLAB"
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL>
SQL> --이전에는 차단됐던 쿼리 -> 이번에는 통과
SQL> SELECT doc_id, doc_title FROM lab_raw_docs;
    DOC_ID DOC_TITLE
---------- ----------------------------------------------------------------------------------------------------
         1 Oracle DB Guide
         2 RAC Guide
         3 RMAN Guide
         4 ASM Guide
         5 GoldenGate Guide

SQL>
SQL> --SQL Injection 패턴은 여전히 차단
SQL> SELECT doc_title FROM lab_raw_docs WHERE doc_id = 1 OR 1=1;
SELECT doc_title FROM lab_raw_docs WHERE doc_id = 1 OR 1=1
*
ERROR at line 1:
ORA-47605: SQL Firewall violation
Help: https://docs.oracle.com/error-help/db/ora-47605/
SQL>
SQL>
SQL> exit
[oracle@rhel10nm ~]$

벡터 인덱스 진단 모니터링

벡터 인덱스, 특히 HNSW는 그래프 구조 전체를 메모리에 상주해야 성능 나옴, 일반 인덱스에는 없던 새로운 확인 필요 함

이 인덱스가 지금 진짜로 메모리에 올라가 있나?
메모리 예산이 부족해서 인덱스가 못 올라가고 있는 건 아닌가?
여러 PDB가 같은 메모리 풀을 나눠 쓰는데, 누가 얼마나 쓰고 있나?

벡터 인덱스를 확인 하기 위해서는 3가지 범위를 확인 해야 함

메모리 계층 - V$VECTOR_MEMORY_POOL
인덱스 계층 - DBA_INDEXES / V$VECTOR_INDEX
정책 계층 - SQL Firewall 상태

[oracle@rhel10nm ~]$
[oracle@rhel10nm ~]$ sqlplus "/as sysdba"
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> ALTER SESSION SET CONTAINER = PDB1;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> show user
USER is "SYS"
SQL>
SQL> --메모리 계층 확인
SQL> SET LINESIZE 200
SQL> SELECT con_id, pool,
  2         ROUND(alloc_bytes/1024/1024, 2) AS alloc_mb,
  3         ROUND(used_bytes/1024/1024, 2)  AS used_mb,
  4         populate_status
  5  FROM v$vector_memory_pool
  6  ORDER BY con_id, pool;
    CON_ID POOL                         ALLOC_MB    USED_MB POPULATE_STATUS
---------- -------------------------- ---------- ---------- --------------------------
         3 1MB POOL                          224          3 DONE
         3 64KB POOL                          24        .38 DONE
SQL> --할당량 224+24 = 248MB : #1에서 설정한 256MB와 일치 (차이는 내부 오버헤드)
SQL> --사용량 3MB : #4때(1MB)보다 오히려 늘었음, 3MB가 잡힌 건 #5에서 만든 Hybrid Vector Index가 내부적으로 HNSW를 사용, Index 계층에서 확인 가능
SQL>
SQL> --인덱스 계층 확인
SQL> COLUMN owner FORMAT A12
SQL> COLUMN index_name FORMAT A25
SQL> COLUMN table_name FORMAT A20
SQL> SELECT owner, index_name, table_name, status
  2  FROM dba_indexes
  3  WHERE index_type = 'VECTOR'
  4  ORDER BY owner, index_name;
OWNER        INDEX_NAME                TABLE_NAME                 STATUS
------------ ------------------------- -------------------------  --------
VECLAB       DR$LAB_DOCS_HYBRID_IDX$VI DR$LAB_DOCS_HYBRID_IDX$VR  VALID
VECLAB       LAB_CHUNKS_IVF_IDX        LAB_DOC_CHUNKS       VALID
SQL> --DR$LAB_DOCS_HYBRID_IDX$VI(Oracle이 Hybrid Vector Index를 만들 때 자동으로 생성한 내부 객체) -> #5 Hybrid Index의 내부 벡터 인덱스
SQL> --LAB_CHUNKS_IVF_IDX -> Module 4에서 만든 IVF 인덱스
SQL>
SQL> --정책 계층 확인
SQL> COLUMN username FORMAT A15
SQL> SELECT username, status, enforce, block FROM DBA_SQL_FIREWALL_ALLOW_LISTS;
USERNAME        STATUS   ENFORCE         BLOCK
--------------- -------- --------------- --------------
VECLAB          ENABLED  ENFORCE_ALL     Y
SQL> --ALLOW_LISTS:  VECLAB  ENABLED / ENFORCE_ALL / BLOCK=Y 방화벽 정상 가동 중
SQL>
SQL> SELECT username, status FROM DBA_SQL_FIREWALL_CAPTURES;
USERNAME        STATUS
--------------- --------
VECLAB          DISABLED
SQL> --CAPTURES:     VECLAB  DISABLED 학습 모드는 종료됨(정상)
SQL>
SQL> exit
[oracle@rhel10nm ~]$

Database Sync

Oracle DB 26ai AI/Vector - #8

SQL Firewall

SQL Firewall Test

SQL Firewall 차단 테스트

벡터 인덱스 진단 모니터링

+ Recent posts

티스토리툴바