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