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;
/

Oracle 11g DBMS_FGA包的使用

相關文章