Oracle审计audit的技巧使用，追踪oracle后台数据库的DML动作
create or replace package aa_pkg_monitor_business_sql is
procedure pro_monitor_drop_policy(object_schema in varchar2);
procedure pro_monitor_add_policy(object_schema in varchar2);
procedure pro_monitor_drop_main;
procedure pro_monitor_add_main;
end;
/
create or replace package body aa_pkg_monitor_business_sql is
/*
truncate table sys.fga_log$ ;
select t.* from dba_fga_audit_trail t;
*/
procedure pro_monitor_drop_policy(object_schema in varchar2) is
cursor cur_del is
select owner, table_name from dba_tables a where owner = object_schema;
begin
for v_cur in cur_del
loop
begin
dbms_fga.drop_policy(object_schema => v_cur.owner, ---
object_name => v_cur.table_name, ---
policy_name => v_cur.table_name);
exception
when others then
null;
end;
end loop;
end pro_monitor_drop_policy;
--------------------------------------------------------------------
--------------------------------------------------------------------
procedure pro_monitor_add_policy(object_schema in varchar2) is
cursor cur_del is
select owner, table_name
from dba_tables a
where owner = object_schema and
a.table_name not like 'SRP%';
begin
for v_cur in cur_del
loop
begin
dbms_fga.add_policy(object_schema => v_cur.owner, --
object_name => v_cur.table_name, --
policy_name => v_cur.table_name,
statement_types => 'SELECT,UPDATE,DELETE,INSERT',
enable => true);
exception
when others then
null;
end;
end loop;
end pro_monitor_add_policy;
--------------------------------------------------------------------
--------------------------------------------------------------------
procedure pro_monitor_drop_main is
begin
pro_monitor_drop_policy('CCARE');
pro_monitor_drop_policy('CRMPUB');
pro_monitor_drop_policy('INVENTORY');
pro_monitor_drop_policy('ISAP');
pro_monitor_drop_policy('OSMS');
pro_monitor_drop_policy('ECARE');
pro_monitor_drop_policy('CHANNEL');
end pro_monitor_drop_main;
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
procedure pro_monitor_add_main is
begin
pro_monitor_drop_main;
pro_monitor_add_policy('CCARE');
-- pro_monitor_add_policy('CRMPUB');
pro_monitor_add_policy('INVENTORY');
--pro_monitor_add_policy('ISAP');
---pro_monitor_add_policy('OSMS');
end pro_monitor_add_main;
end;
/
