안녕하세요, Surmin입니다. 오늘은 FGA(Fine-Grained Auditing)에 대해 소개해 드리고자 합니다.
이 내용이 여러분의 공부에 도움이 되거나 좋은 참고 자료가 되기를 바랍니다 🙂
FGA란
- 데이터 베이스 감사 기능을 사용하면 스키마 내의 테이블에 select 에 관한 감사를 설정 했을 경우 해당 테이블의 조회 관련된 모든 수행기록이 Audit Trail Record의 형태로 저장될 것이다. 이런 경우 원하지 않는 결과까지도 모두 기록되어 Audit Trail의 양은 상당히 많아 질것이고, 특정 컬럼에 대한 조회내역 직접적으로 사용된 select문을 찾기 어려울 것이다.
- FGA은 이러한 문제를 해결하도록 나왔으며, FGA는 특정 데이터를 조회하는 경우에만 감사가 가능하도록 설정할 수 있다. (DQL,DML포함)
특징
더 상세한 레벨의 감사 기능을 제공한다. 선택적인 감사를 위한 조건으로 SQL의 WHERE절을 기반으로 한다.
값에 의한 감사뿐만 아니라 특정 컬럼의 참조 또는 액세스 여부에 대해서도 Auditing이 가능하다.
DBMS_FGA 패키지를 이용하여 활성화/비활성화 한다.
CBO(Cost Based Optimizer)인 경우에 정상적으로 작동한다. (인스턴스 레벨의 CBO 설정 및 테이블에대한 분석(Analyze) 이 되어 있어야 하며 SQL에 힌트가 없어야 한다.)
Oracle Database 9i에서는 SELECT문에서만 가능하며, Oracle Database 10g에서는 Insert, Update, Delete, Merge문에 대해서도 가능하다.
Oracle Database 10g에서는 컬럼에 대한 옵션(audit_column_opts) 지정으로 DBMS_FGA.ALL_COLUMNS / DBMS_FGA.ANY_COLUMNS를 사용할 수 있다.
FGA에서 Audit Trail Record는 SYS.FGA_LOG$에 저장된다.
STATEMENT_TYPE,AUDIT_TRAIL
- SYS 로 트랜잭션 유발 후 확인 하려고 할경우 감사에 안남음 일반 계정으로 해야함
- a계정에서 b계정 소유의 table에 트랜잭션 작업을 해도 감사 기록됨
- commit을 하지 않아도 감사에 기록됨
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string NONE
#ERROR 발생, 같은 정책이 있어서 발생하는것
SQL> begin
DBMS_FGA.ADD_POLICY(object_schema => 'HR',
object_name=>'EMPLOYEES',
policy_name=>'POL_HR_EMPLOYEES',
audit_condition=>'SALARY > 3000',
enable=>true,
statement_types=>'SELECT, INSERT',
audit_trail=>DBMS_FGA.DB_EXTENDED);
end;
/
begin
*
ERROR at line 1:
ORA-28101: policy already exists
ORA-06512: at "SYS.DBMS_FGA", line 20
ORA-06512: at line 2
#삭제하자
BEGIN
DBMS_FGA.DROP_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'POL_HR_EMPLOYEES'
);
END;
/
#다시생성
##ine-Grained Auditing (FGA) 정책을 생성##
begin
DBMS_FGA.ADD_POLICY(object_schema => 'HR',
object_name=>'EMPLOYEES',
policy_name=>'POL_HR_EMPLOYEES',
audit_condition=>'SALARY > 3000',
enable=>true,
statement_types=>'SELECT, INSERT',
audit_trail=>DBMS_FGA.DB_EXTENDED);
end;
/
==
기능 설명:
목적: HR.EMPLOYEES 테이블에 대한 세밀한 감사 설정
조건: SALARY > 3000인 레코드에 대해서만 감사
대상 작업: SELECT, INSERT 문
감사 범위: 확장된 정보까지 모두 기록
실제 효과:
급여가 3000 이상인 직원 정보를 조회하거나 입력할 때
누가, 언제, 어떤 쿼리를 실행했는지 자동으로 기록
==
-- 페이지 크기 및 라인 크기 설정
SET PAGESIZE 50
SET LINESIZE 150
-- 컬럼 폭 조절
COLUMN object_schema FORMAT A15
COLUMN object_name FORMAT A20
COLUMN policy_name FORMAT A20
COLUMN policy_text FORMAT A20
COLUMN policy_column FORMAT A15
COLUMN enabled FORMAT A7
SELECT object_schema, object_name, policy_name, policy_text, policy_column, enabled
FROM dba_audit_policies;
# 특정 컬럼 지정 안함 (모든 컬럼 대상)
OBJECT_SCHEMA OBJECT_NAME POLICY_NAME POLICY_TEXT POLICY_COLUMN ENABLED
--------------- -------------------- -------------------- -------------------- --------------- -------
HR EMPLOYEES POL_HR_EMPLOYEES SALARY > 3000 YES
SELECT timestamp, object_name, scn, sql_text, sql_bind
FROM dba_fga_audit_trail;
no rows selected
#1행 샘플로 넣음
INSERT INTO HR.EMPLOYEES
(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY)
VALUES
(999, 'John', 'Smith', 'JSMITH', SYSDATE, 'IT_PROG', 5000);
select * from hr.EMPLOYEES;
INSERT INTO HR.EMPLOYEES
(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY)
VALUES
(998, 'Jane', 'Brown', 'JBROWN', SYSDATE, 'SA_REP', 2500);
DELETE FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = 999;
#조회
SET PAGESIZE 100
SET LINESIZE 200
COLUMN timestamp FORMAT A20
COLUMN db_user FORMAT A10
COLUMN object_schema FORMAT A12
COLUMN object_name FORMAT A15
COLUMN policy_name FORMAT A20
COLUMN sql_text FORMAT A80 WORD_WRAPPED
SELECT timestamp, object_name, scn, sql_text, sql_bind
FROM dba_fga_audit_trail;
TIMESTAMP DB_USER OBJECT_SCHEM OBJECT_NAME POLICY_NAME
-------------------- ---------- ------------ --------------- --------------------
13-JUN-25 HR HR EMPLOYEES POL_HR_EMPLOYEES
13-JUN-25 HR HR EMPLOYEES POL_HR_EMPLOYEES
13-JUN-25 TESTUSER HR EMPLOYEES POL_HR_EMPLOYEES
13-JUN-25 HR HR EMPLOYEES POL_HR_EMPLOYEES
* Oracle Database 10g 에서는 INSERT, DELETE, UPDATE, MERGE 에 대해서도 가능하다. 또한
audit_trail 파라메터에 DBMS_FGA.DB_EXTENDED로 설정하므로 init parameter 의 audit_trail 파라메터와 무관하
게 FGA 가 설정만으로 수행된 구문과 바인드 정보를 볼 수 있다.
* 위 예제에서 보았듯이 statement_types 가 ‘SELECT’, ‘INSERT’이므로 두가지의 구문형식에 대해서만
Auditing 되며, ‘DELETE’는 Auditing되지 않았다. 그리고 audit_trail 이 DB_EXTENEDED로 설정되어 sql_bind,
와 sql_text에 대해서도 audit trail이 작성되었다.AUDIT_COLUMN_OPTS
- DBMS_FGA.ANY_COLUMNS: 지정된 컬럼중 하나라도 해당되면 로그 기록함
- DBMS_FGA.ANY_ALL_COLUMN:지정된 컬럼이 모두 조회 되는 구문에서만 로그 기록함
- EX) EMPLOYEE_ID, SALARY, COMMISSION_PCT 지정했으면 EMPLOYEE_ID, SALARY, COMMISSION_PCT 가 다 포함 되어야함 하나라도 빠지면, 기록을 안함
SQL> truncate table fga_log$;
#정책생성
BEGIN
DBMS_FGA.ADD_POLICY(
object_schema => 'HR', -- 감사할 테이블의 스키마명
object_name => 'EMPLOYEES', -- 감사할 테이블명, 다른 테이블에서 조회 하였는데 EMPLOYEE_ID 컬럼이 나와도 기록안됨 , 테이블 명이 다르니
policy_name => 'POL_HR_EMPLOYEES1', -- FGA 정책명 (고유한 이름)
audit_column => 'EMPLOYEE_ID, SALARY, COMMISSION_PCT', -- 감사 대상 컬럼들 (쉼표로 구분)
enable => TRUE, -- 정책 활성화 여부 (TRUE=활성화)
audit_column_opts => DBMS_FGA.ANY_COLUMNS -- 컬럼 감사 옵션 (ANY_COLUMNS = 지정된 컬럼 중 어느 하나라도 접근시 감사)
);
END;
/
SQL> select timestamp, object_name, scn, sql_bind, sql_text from dba_fga_audit_trail;
SQL> conn hr/hr
SQL> select EMPLOYEE_ID, SALARY from EMPLOYEES where employee_id =100;
EMPLOYEE_ID SALARY
----------- ----------
100 24000
SQL> select SALARY, COMMISSION_PCT from EMPLOYEES where employee_id =100;
SALARY COMMISSION_PCT
---------- --------------
24000
SQL> select employee_id from EMPLOYEES where SALARY=24000;
EMPLOYEE_ID
-----------
100
SQL> select employee_id from EMPLOYEES where employee_id=100;
EMPLOYEE_ID
-----------
100
#구조 확인 시 위에서 설정한 컬럼들이 없음을 확인
SQL> desc hr.locations;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
LOCATION_ID NOT NULL NUMBER(4)
STREET_ADDRESS VARCHAR2(40)
POSTAL_CODE VARCHAR2(12)
CITY NOT NULL VARCHAR2(30)
STATE_PROVINCE VARCHAR2(25)
COUNTRY_ID CHAR(2)
SQL> select * from locations;
-- 컬럼 폭 넓게 지정
COLUMN timestamp FORMAT A20
COLUMN object_name FORMAT A20
COLUMN scn FORMAT 999999999999
COLUMN sql_bind FORMAT A30
COLUMN sql_text FORMAT A120
-- 출력 행 길이 및 페이지 크기 넉넉하게
SET LINESIZE 200
SET PAGESIZE 50
SET WRAP OFF
SELECT timestamp, object_name, scn, sql_bind, sql_text
FROM dba_fga_audit_trail;
TIMESTAMP OBJECT_NAME SCN SQL_BIND SQL_TEXT
-------------------- -------------------- ------------- ------------------------------ -----------------------------------------------------------------------------------------------------------------
13-JUN-25 EMPLOYEES 1429123 select EMPLOYEE_ID, SALARY from EMPLOYEES where employee_id =100
13-JUN-25 EMPLOYEES 1429123 select EMPLOYEE_ID, SALARY from EMPLOYEES where employee_id =100
13-JUN-25 EMPLOYEES 1429143 select SALARY, COMMISSION_PCT from EMPLOYEES where employee_id =100
13-JUN-25 EMPLOYEES 1429143 select SALARY, COMMISSION_PCT from EMPLOYEES where employee_id =100
13-JUN-25 EMPLOYEES 1429173 select employee_id from EMPLOYEES where SALARY=24000
13-JUN-25 EMPLOYEES 1429173 select employee_id from EMPLOYEES where SALARY=24000
13-JUN-25 EMPLOYEES 1429185 select employee_id from EMPLOYEES where employee_id=100
13-JUN-25 EMPLOYEES 1429185 select employee_id from EMPLOYEES where employee_id=100
8 rows selected.
* audit_column_opts 값이 DBMS_FGA.ANY_COLUMNS 으로 설정했을 경우 audit_column 에 나열된 Column 중에
1 개라도 포함이 되어 있다면 Audit Trail 이 기록된다. 나열된 컬럼들 중에 없는 경우는 기록되지 않는다. - 한번에 여러 Table 지정이 가능 할까?
- ⇒ 불가능 하다 하나씩 지정해야 한다.
- ⇒ table 지정 구문을 생략하면 Error남
- ERROR at line 2: ORA-06550: line 2, column 5: PLS-00306: wrong number or types of arguments in call to ‘ADD_POLICY’ ORA-06550: line 2, column 5: PL/SQL: Statement ignored
BEGIN
DBMS_FGA.ADD_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES,JOB_HISTORY',
policy_name => 'POL_MULTI_TEST1',
audit_column => 'EMPLOYEE_ID',
enable => TRUE
);
END;
/
BEGIN
DBMS_FGA.ADD_POLICY(
object_schema => 'HR',
object_name => '%', -- 모든 테이블 시도
policy_name => 'POL_MULTI_TEST5',
audit_column => 'EMPLOYEE_ID',
enable => TRUE
);
END;
/
ERROR at line 1:
ORA-28104: input value for OBJECT_NAME is not valid
ORA-06512: at "SYS.DBMS_FGA", line 20
ORA-06512: at line 2