oracle服务器运行dbms,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;

/