Oracle 11g DBMS_FGA包的使用

DBMS_FGA包可以用于审计在数据库中执行的DML语句和SELECT语句

需要注意的是，执行失败的SQL不会被记录到审计记录中

例如下面语句中的失败语句，重复的主键字段插入SQL不会被记录
SQL> insert into t values(3,'Dalian',10,100);

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t add primary key(a);

Table altered.

SQL> insert into t values(3,'Dalian',10,100);
insert into t values(3,'Dalian',10,100)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C004949) violated

SQL> /
insert into t values(3,'Dalian',10,100)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C004949) violated

--创建审计策略
BEGIN   
DBMS_FGA.ADD_POLICY (
   object_schema      =>  'SCOTT',
   object_name        =>  'EMP',
   policy_name        =>  'mypolicy1',  
   enable             =>   TRUE,
   statement_types    =>  'INSERT, UPDATE, DELETE, SELECT',
   audit_column_opts  =>   DBMS_FGA.ANY_COLUMNS);
END;
/

-- 批量创建脚本
SELECT 'DBMS_FGA.ADD_POLICY (object_schema => ' || '''' || OWNER || '''' || ', object_name => ' || '''' || TABLE_NAME || ''''
|| ', policy_name => ' || '''' ||'POL_' || TABLE_NAME || '''' || ', enable => TRUE' || ', statement_types => '''
|| 'INSERT, UPDATE, DELETE, SELECT''' || ', audit_column_opts => DBMS_FGA.ANY_COLUMNS);'
FROM DBA_TABLES WHERE OWNER = 'SCOTT';

BEGIN
DBMS_FGA.ADD_POLICY (object_schema => 'SCOTT', object_name => 'SMDP_SUBSCRIPTIONS', policy_name => 'POL_SMDP_SUBSCRIPTIONS', enable => TRUE, statement_types => 'INSERT, UPDATE, DELETE, SELECT', audit_column_opts => DBMS_FGA.ANY_COLUMNS);
DBMS_FGA.ADD_POLICY (object_schema => 'SCOTT', object_name => 'T', policy_name => 'POL_T', enable => TRUE, statement_types => 'INSERT, UPDATE, DELETE, SELECT', audit_column_opts => DBMS_FGA.ANY_COLUMNS);
DBMS_FGA.ADD_POLICY (object_schema => 'SCOTT', object_name => 'TAB_1', policy_name => 'POL_TAB_1', enable => TRUE, statement_types => 'INSERT, UPDATE, DELETE, SELECT', audit_column_opts => DBMS_FGA.ANY_COLUMNS);
DBMS_FGA.ADD_POLICY (object_schema => 'SCOTT', object_name => 'TAB_2', policy_name => 'POL_TAB_2', enable => TRUE, statement_types => 'INSERT, UPDATE, DELETE, SELECT', audit_column_opts => DBMS_FGA.ANY_COLUMNS);
DBMS_FGA.ADD_POLICY (object_schema => 'SCOTT', object_name => 'EMP', policy_name => 'POL_EMP', enable => TRUE, statement_types => 'INSERT, UPDATE, DELETE, SELECT', audit_column_opts => DBMS_FGA.ANY_COLUMNS);
DBMS_FGA.ADD_POLICY (object_schema => 'SCOTT', object_name => 'OSMSR_EUICC_HISTORIES', policy_name => 'POL_OSMSR_EUICC_HISTORIES', enable => TRUE, statement_types => 'INSERT, UPDATE, DELETE, SELECT', audit_column_opts => DBMS_FGA.ANY_COLUMNS);
END;
/

--通过视图来查看SQL结果
select * from dba_fga_audit_trail;

--开启审计策略
BEGIN
DBMS_FGA.ENABLE_POLICY (
object_schema    =>  'SCOTT',
object_name      =>  'EMP',
policy_name      =>  'mypolicy1',
enable           =>   TRUE);
END;
/

--关闭审计策略
BEGIN
DBMS_FGA.DISABLE_POLICY (
object_schema   =>  'scott',
object_name     =>  'emp',
policy_name     =>  'mypolicy1');
END;
/

使用后需要删除审计策略
--删除审计策略
BEGIN
DBMS_FGA.DROP_POLICY (
object_schema   =>  'scott',
object_name     =>  'emp',
policy_name     =>  'mypolicy1');
END;
/

批量删除语句
SELECT 'DBMS_FGA.DROP_POLICY (object_schema => ' || '''' || OWNER || '''' || ', object_name => ' || '''' || TABLE_NAME || ''''
|| ', policy_name => ' || '''' ||'POL_' || TABLE_NAME || '''' ||  ');'
FROM DBA_TABLES WHERE OWNER = 'SCOTT';

BEGIN
DBMS_FGA.DROP_POLICY (object_schema => 'SCOTT', object_name => 'SMDP_SUBSCRIPTIONS', policy_name => 'POL_SMDP_SUBSCRIPTIONS');
DBMS_FGA.DROP_POLICY (object_schema => 'SCOTT', object_name => 'T', policy_name => 'POL_T');
DBMS_FGA.DROP_POLICY (object_schema => 'SCOTT', object_name => 'TAB_1', policy_name => 'POL_TAB_1');
DBMS_FGA.DROP_POLICY (object_schema => 'SCOTT', object_name => 'TAB_2', policy_name => 'POL_TAB_2');
DBMS_FGA.DROP_POLICY (object_schema => 'SCOTT', object_name => 'EMP', policy_name => 'POL_EMP');
DBMS_FGA.DROP_POLICY (object_schema => 'SCOTT', object_name => 'OSMSR_EUICC_HISTORIES', policy_name => 'POL_OSMSR_EUICC_HISTORIES');
END;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2131219/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2131219/