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