Fine Grained Auditing

• 컨텐츠(조건)를 기준으로 데이터 액세스 모니터
• SELECT, INSERT, UPDATE, DELETE, MERGE 감사
• 테이블이나 뷰에 있는 하나 이상의 열에 연결 가능
• 프로시저 실행 가능
• DBMS_FGA 패키지를 사용한다.

• FGA 정책 만들기

BEGIN
    dbms_fga.add_policy(
                        object_schema => 'HR',
                        object_name => 'EMPLOYEES',
                        policy_name => 'EMP_POL', -- 여기까지는 필수값
                        audit_condition => 'department_id = 10' ,-- 이후부터는 옵션적인 부분
                        audit_column => 'salary, commission_pct'  ,
                        audit_column_opts => dbms_fga.all_columns ,
                        statement_types =>'select,insert,update,delete' ,
                        enable => TRUE);
END;

- dbms_fga.all_columns : audit_column 변수에 나열한 컬럼을 같이 볼 경우
- dbms_fga.any_columns(기본값) : audit_column 변수에 나열한 컬럼을 각각으로 볼 경우 또는 같이 볼경우

• FGA 정책 내용 확인

SELECT * FROM dba_audit_policies;

• FGA 정책 삭제하기

BEGIN
    dbms_fga.drop_policy(
                        object_schema => 'HR',
                        object_name => 'EMPLOYEES',
                        policy_name => 'EMP_POL');
END;
/

INSA SESSION

SELECT * 
FROM hr.employees; 

SELECT employee_id, salary
FROM hr.employees
WHERE department_id = 10
AND commission_pct IS NOT NULL;

-- 해당 쿼리문도 감사에 걸리게 된다. EMPLOYEE_ID 200번 사원의 부서번호는 10번이기 때문이다.
SELECT * 
FROM hr.employees
WHERE employee_id = 200;

SELECT * FROM hr.employees WHERE employee_id = :b_id; -- 변수에 200 대입하면 감사에 걸린다.

- department_id = 10 조건절을 넣지 않아도 전체 조회를 해도 10 번 아이디를 볼 수 있기 때문에 감사 테이블에 걸리게 된다.

DBA SESSION

SELECT * FROM dba_fga_audit_trail; -- 딕셔너리 뷰 

SELECT * FROM sys.fga_log$; -- 실제 딕셔너리 테이블

---

PROCEDURE를 활용한 FGA 감사

DBA SESSION

-- 테이블 생성
CREATE TABLE hr.fga_emp_log(
    object_schema varchar2(30),
    object_name varchar2(30),
    policy_name varchar2(30),
    user_name varchar2(30),
    sql_text varchar2(4000),
    sql_bind varchar2(30),
    day timestamp);

-- 프로시저 생성
CREATE OR REPLACE PROCEDURE hr.fga_trail_proc(
    object_schema IN varchar2,
    object_name IN varchar2,
    policy_name IN varchar2)
IS
    PRAGMA AUTONOMOUS_TRANSACTION; -- 독립 트랜잭션
BEGIN
    INSERT INTO fga_emp_log
    VALUES (object_schema,object_name, policy_name, sys_context('USERENV','SESSION_USER'),sys_context('USERENV','CURRENT_SQL'),
    sys_context('USERENV','CURRENT_BIND'),
    localtimestamp);
    
    COMMIT;
    
END fga_trail_proc;
/

-- FGA 정책 생성
-- statement_types를 따로 지정해주지 않으면 select만 감사하게 된다.
BEGIN
    dbms_fga.add_policy(
                        object_schema => 'HR',
                        object_name => 'EMPLOYEES',
                        policy_name => 'EMP_POL',
                        handler_schema => 'HR' , -- 프로시저 스키마 설정
                        handler_module => 'FGA_TRAIL_PROC', -- 프로시저명 설정
                        enable => TRUE);
END;
/

- SELECT sys_context('USERENV','SESSION_USER') FROM DUAL; : 현재 유저에 대한 환경에서 SESSION 유저 아이디값 리턴
- SELECT sys_context('USERENV','CURRENT_SQL') FROM DUAL; : 정책에 걸린 SQL문 리턴
- SELECT sys_context('USERENV','CURRENT_BIND') FROM DAUL; : 정책에 걸린 BIND 변수 리턴

INSA SESSION

SELECT * 
FROM hr.employees
WHERE employee_id = 200;

SELECT employee_id, salary, commission_pct, department_id 
FROM hr.employees; 

SELECT salary, commission_pct
FROM hr.employees
WHERE department_id = 10;

DBA SESSION

SELECT * FROM dba_audit_policies;

SELECT * FROM hr.fga_emp_log; -- 내가 생성한 log 감사 테이블

FGA 정책 중지

BEGIN
    dbms_fga.disable_policy(
                        object_schema => 'HR',
                        object_name => 'EMPLOYEES',
                        policy_name => 'EMP_POL');
END;
/

FGA 정책 재실행

BEGIN
    dbms_fga.enable_policy(
                        object_schema => 'HR',
                        object_name => 'EMPLOYEES',
                        policy_name => 'EMP_POL');
END;
/

감사 LOG 파일 이관작업

이관 테이블스페이스 생성

CREATE TABLESPACE audit_aux
DATAFILE '/u01/app/oracle/oradata/ORA19C/audit_aux.dbf' SIZE 10M
AUTOEXTEND ON NEXT 1M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

AUD$ 테이블을 audit_aux 테이블스페이로 이관 프로그램

BEGIN
    dbms_audit_mgmt.set_audit_trail_location(
        audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std ,
        audit_trail_location_value => 'audit_aux'); -- 변경 테이블스페이스 이름
END;
/

FGA_LOG$ 테이블을 audit_aux 테이블스페이로 이관 프로그램

BEGIN
    dbms_audit_mgmt.set_audit_trail_location(
        audit_trail_type => dbms_audit_mgmt.audit_trail_fga_std ,
        audit_trail_location_value => 'audit_aux'); -- 변경 테이블스페이스 이름
END;
/

이관 확인

SELECT *
FROM dba_tables 
WHERE table_name IN ('AUD$' , 'FGA_LOG$');