oracle 精确审计,Oracle fgs（精细审计）使用

Oracle fgs(精细审计)使用

1)grant execute on dbms_fga to scott

2)添加审计策略(scott)

begin

DBMS_FGA.ADD_POLICY(

object_schema => 'scott',

object_name => 'emp',

policy_name=> 'chk_hr_emp2',

audit_condition => 'job = ''SALESMAN'' ',

audit_column => 'sal',

statement_types => 'insert,update,delete,select');

end;

或者

begin

DBMS_FGA.ADD_POLICY (

object_schema      =>  'scott',

object_name        =>  'emp',

policy_name        =>  'mypolicy1',

audit_condition    =>  'sal < 100',    #如果是insert操作，则要满足该条件才能产生审计条目

audit_column       =>  'comm,sal',     #要对这两列同时update时无需符合上面的条件，无需提交，就有审计条目产生

handler_schema     =>   NULL,

handler_module     =>   NULL,

enable             =>   TRUE,

statement_types    =>  'INSERT, UPDATE',

audit_trail        =>   DBMS_FGA.XML + DBMS_FGA.EXTENDED,

audit_column_opts  =>   DBMS_FGA.ANY_COLUMNS);

end;

Check db enable fga information:

select * from DBA_AUDIT_POLICIES;

3)对emp表进行操作，只要条件中有用到job＝SALESMAN'或者访问了sal列，都产生审计的条目

select * from emp where sal=1000;

select * from emp where job='SALESMAN';

4)查看审计结果(只有audit_trail=db才有数据),DBA_FGA_AUDIT_TRAIL的数据来源于SYS.FGA_LOG$：

SQL> select LSQLTEXT,OBJ$SCHEMA,OBJ$NAME ,POLICYNAME  from SYS.FGA_LOG$;

SQL> select count(*) from DBA_FGA_AUDIT_TRAIL;

5)AUDIT_TRAIL＝os 则审计的信息产生在操作系统目录下，与以前的audit一样

6)删除审计策略：

QL> begin

DBMS_FGA.DROP_POLICY ('scott','emp','mypolicy1');

end;

7)disable审计策略：

bein

DBMS_FGA.DISABLE_POLICY (

object_schema   =>  'scott',

object_name     =>  'emp',

policy_name     =>  'mypolicy1');

end;

8)enable审计策略：

begin

DBMS_FGA.ENABLE_POLICY (

object_schema    =>  'scott',

object_name      =>  'emp',

policy_name      =>  'mypolicy1',

enable           =>   TRUE);

end;