FGA(Fine Grating Auditing)

SQL> conn hr/hr

SQL>@$ORACLE_HOME/sqlplus/demo/demobld.sql

-- HR schema에서 emp,dept 테이블에 대한 통계정보 생성을 해야만, Auditing이 제대로 된다.

SQL>analyze table emp compute statistics;

SQL>analyze table dept compute statistics;

grant select on emp to sh;

grant select on dept to sh;

conn system/manager

begin

   dbms_fga.add_policy(

         OBJECT_SCHEMA=>'HR',

         OBJECT_NAME=>'EMP',

         policy_name=>'AUD_EMP_SAL_MGR',

         AUDIT_CONDITION=>'deptno in (10,20)',

         AUDIT_COLUMN=>'sal,mgr',

         handler_schema=>'system',

         handler_module=>'INS_EMP_TRAIL',

         enable=>TRUE);

   end;

/

CREATE table AUD_EMP_TRAIL (

  object_schema varchar2(80),

  object_name varchar2(80),

  policy_name varchar2(80)

);

CREATE PROCEDURE system.INS_EMP_TRAIL(

  p_object_schema varchar2,

  p_object_name varchar2,

  p_policy_name varchar2)

AS 

BEGIN

   insert into system.AUD_EMP_TRAIL

   values( p_object_schema,p_object_name,p_policy_name);

end;

/

1) sh로 접속

   conn sh/sh

   select * from hr.emp;

2) sh로 접속 HR의 EMP 테이블의 empno,ename,sal을 조건없이 조회

   show user

   select empno,ename,sal from hr.emp;

3) system으로 접속

   conn system/manager

   select ename from hr.emp where detpno=20;

   select * from hr.emp where deptno in (10,20,30);

데이터 딕셔너리를 통해  Auditing 정보가 정상적으로 생성되었는 확인

show user

select to_char(timestamp,'YYYYDDHHH24MI') as timestamp

       db_user,policy_name,sql_text

from dba_fga_audit_trail;

select * from aud_emp_trail;