[ORACLE] FGA (Fine-Grained Auditing) (감시) 설정

1. Table에 FGA 설정 TEST

 

1) 유저생성

CREATE USER USER1 IDENTIFIED BY "oracle4321" ; GRANT CONNECT, RESOURCE, CREATE VIEW TO USER1 ; CREATE TABLE USER1.T_TEST1 ( C1 NUMBER , C2 VARCHAR(10) ); INSERT INTO USER1.T_TEST1 VALUES(1,'TEST1'); COMMIT;

2) 감사 설정

begin   dbms_fga.add_policy(object_schema => 'USER1',   object_name => 'T_TEST1',   policy_name => 'T_TEST1_DEL_POLICY', --  audit_condition => NULL, --  audit_column => NULL,   statement_types => 'delete'   audit_trail => dbms_fga.xml + dbms_fga.extended ); end;

 

# audit_trail 파라메터 옵션값

parameter value	description
dbms_fga.db	FGA records를 database상에만 남긴다
dbms_fga.db+dbms_fga.extended	(위의 내용) + 실행된 SQL문 및 bind variable에 실제 입력된 값까지 기록을 남긴다.
dbms_fga.xml	FGA records를 XML files형태로만 남긴다
dbms_fga.xml+dbms_fga.extended	(위의 내용) + 실행된 SQL문 및 bind variable에 실제 입력된 값까지 기록을 남긴다.

3) audit 내용 확인

SELECT * FROM DBA_AUDIT_POLICIES;

4) delete 발생

DELETE FROM USER1.T_TEST1 WHERE C1 = 1; COMMIT;

5) log 확인

select * from dba_fga_audit_trail; select * from fga_log$;

6) 삭제

begin  dbms_fga.drop_policy(object_schema => 'USER1',  object_name => 'T_TEST1',  policy_name => 'T_TEST1_DEL_POLICY'); end;

 

참고

 

FGA(Fine-Grained Auditing) DBMS_FGA AUDIT..

 

데이터베이스 감시 보안 기능