Oracle FGA细粒度审计

  如果你想要审计表上，在某个时间，哪些人，操作哪些DML语句，用FGA是个不错的选择。

SQL> select * from v$version;
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL>drop table test purge;

SQL>create table test as select * from dba_objects;

SQL> exec DBMS_FGA.ADD_POLICY(object_schema=>‘LCAM_TEST‘,object_name=>‘test‘,policy_name=>‘FGA_1‘,enable=>TRUE,statement_types=>‘UPDATE,delete‘,audit_trail=>DBMS_FGA.DB+DBMS_FGA.EXTENDED,audit_column_opts=>DBMS_FGA.ANY_COLUMNS);--statement_types可以设置select,insert,delete,update

SQL> col SQL_TEXT format a80
SQL> select SQL_TEXT,TIMESTAMP from DBA_FGA_AUDIT_TRAIL order by TIMESTAMP;

SQL> update test set subobject_name=object_id where rownum=1;
SQL> select SQL_TEXT,TIMESTAMP from DBA_FGA_AUDIT_TRAIL order by TIMESTAMP;
update test set subobject_name=object_id where rownum=1                         01-12月-14

SQL> update test set subobject_name=object_id where rownum<100;--可以看到是按照SQL语句来审计的，不是按照行变动审计
SQL> select SQL_TEXT,TIMESTAMP from DBA_FGA_AUDIT_TRAIL order by TIMESTAMP;
update test set subobject_name=object_id where rownum<100                       01-12月-14
update test set subobject_name=object_id where rownum=1                         01-12月-14

SQL> delete from  sys.fga_log$;
SQL> commit;
SQL> select SQL_TEXT,TIMESTAMP from DBA_FGA_AUDIT_TRAIL order by TIMESTAMP;

SQL>

还有让审计失效、激活、删除的方法：

exec DBMS_FGA.DISABLE_POLICY (object_schema=>‘LCAM_TEST‘,object_name=>‘test‘,policy_name=>‘FGA_1‘);
exec DBMS_FGA.ENABLE_POLICY (object_schema=>‘LCAM_TEST‘,object_name=>‘test‘,policy_name=>‘FGA_1‘);
exec DBMS_FGA.DROP_POLICY(object_schema=>‘LCAM_TEST‘,object_name=>‘test‘,policy_name=>‘FGA_1‘);

官方文档的位置是：

Oracle? Database PL/SQL Packages and Types Reference  11g Release 2 (11.2)   DBMS_FGA

Table 66-2 ADD_POLICY Procedure Parameters

											ANY_COLUMNS