Oracle_Auditing

create or replace PROCEDURE FGA_AUDIT_PRODECURE
(object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2 ) AS
/**********************************************************************
    author: mdockery
    purpose: accumulate count of audit events

    Policy to audit DML to a table:
         BEGIN DBMS_FGA.ADD_POLICY (
          object_schema => 'MDOCKERY',
          object_name => 'CUST',
          policy_name => 'FGA_CUST_CHG_POLICY',
          audit_condition => NULL,
          audit_column => NULL,
          handler_schema => 'MDOCKERY',
          handler_module => 'FGA_AUDIT_PRODECURE',
          enable => true,
          statement_types => 'INSERT,UPDATE,DELETE' );
        end;

    create table FGA_audit_event_cool_tbl(audit_event_count    number);
       Note: audit_event_no acts as counter for # times the proc has been executed.

    View detailed audit info
    select OBJECT_SCHEMA, OBJECT_NAME, STATEMENT_TYPE, DB_USER, OS_USER,
    TIMESTAMP, scn, POLICY_NAME, SQL_TEXT  from dba_fga_audit_trail
   **********************************************************************/
  count number;
begin

  select nvl(max(audit_event_count),0) into count from FGA_audit_event_cool_tbl;
  insert into FGA_audit_event_cool_tbl values (count+1);
    commit;
end;