반응형
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 ~]$
반응형
