使用fga审计表

使用fga审计表

1.新建审计策略

BEGIN   
DBMS_FGA.ADD_POLICY (
   object_schema      =>  'SCOTT', 
   object_name        =>  'EMP', 
   policy_name        =>  'SCOTT_EMP_DQML',  
   enable             =>   TRUE, 
   statement_types    =>  'INSERT, UPDATE, DELETE, SELECT', 
   audit_column_opts  =>   DBMS_FGA.ANY_COLUMNS);
END;

2.查看审计策略

select * from DBA_AUDIT_POLICIES;

3.开启审计策略

BEGIN
DBMS_FGA.ENABLE_POLICY (
object_schema    =>  'SCOTT',
object_name      =>  'EMP',
policy_name      =>  'SCOTT_EMP_DQML',
enable           =>   TRUE);
END;
/

4.关闭审计策略

BEGIN
DBMS_FGA.DISABLE_POLICY (
object_schema   =>  'SCOTT',
object_name     =>  'EMP',
policy_name     =>  'SCOTT_EMP_DQML');
END;
/

5.更加精细的审计策略

BEGIN
    SYS.DBMS_FGA.ADD_POLICY  (
    object_schema    => 'SCOTT'
    ,object_name     => 'EMP'
    ,policy_name     => 'SCOTT_EMP_DQML_ENO_ENAME'
   ,audit_condition       => 'UPPER(SYS_CONTEXT(''USERENV'',''MODULE''))  LIKE ''%TOAD%'' '
   ,audit_column          => eno,ename
   ,handler_schema        => null
   ,handler_module        => null
   ,enable                => TRUE
   ,statement_types       =>'SELECT,INSERT,UPDATE,DELETE'
   ,audit_trail           => SYS.DBMS_FGA.DB+SYS.DBMS_FGA.EXTENDED
     			--DBMS_FGA.DB表示记录将被保存到数据库中,DBMS_FGA.EXTENDED表示如果sql语句中带有绑定变量也会被记录下来.
      			--如果是这样选audit_trail => SYS.DBMS_FGA.DB表示不会记录绑定变量
      			--SYS.DBMS_FGA.DB+SYS.DBMS_FGA.EXTENDED改成SYS.DBMS_FGA.XML+SYS.DBMS_FGA.EXTENDED表示记录保存成xml文件
			--xml文件所在目录可以通过SHOW PARAMETER AUDIT_FILE_DEST查看,如果要更改目录ALTER SYSTEM SET AUDIT_FILE_DEST = directory_path DEFERRED;
 ,audit_column_opts     => SYS.DBMS_FGA.ALL_COLUMNS)
END;

audit_condition：审计策略的条件，符合条件的会被记录审计中

audit_trail：审计结果存放形式及是否记录绑定变量

6.删除审计策略

begin
  sys.dbms_fga.drop_policy(
      object_schema => 'SCOTT',
  	  object_name => 'EMP',
      policy_name => 'SCOTT_EMP_DQML_ENO_ENAME');
  end;
  /

7.查看审计记录

SELECT * FROM DBA_FGA_AUDIT_TRAIL;