Fine Grained Auditing (FGA), introduced with Oracle9i, can be understood as ‘policy-based auditing’. As opposed to the standard auditing functionality, FGA lets you specify the conditions necessary for an audit record. FGA policies are programatically bound to the object (table, view) by using the ‘dbms_fga’ package.
You have to have a valid set of Fine-Grained Audit policies to make it possible to specify access to the following items:
- A set of database objects (views or tables)
- A specific type of data (queried with SELECT, INSERT, UPDATE, and DELETE SQL statements)
- A specific column
- A specific value of a column
Procedure
begin
dbms_fga.add_policy
( object_schema=>’HR’
, object_name=>’EMPLOYEES’
, policy_name=>’SELECT_COUNT’
, audit_column => ‘FIRST_NAME’
, audit_condition => ”
, handler_schema => ‘AUDITEDUSER’
, handler_module => ‘AUDIT_HANDLER.HANDLE_ACCESS’
);
end;
CREATE OR REPLACE PACKAGE AUDIT_HANDLER
is
PROCEDURE HANDLE_ACCESS
( object_schema VARCHAR2
, object_name VARCHAR2
, policy_name VARCHAR2
);
end;
/CREATE OR REPLACE PACKAGE BODY AUDIT_HANDLER
is
PROCEDURE HANDLE_ACCESS
( object_schema VARCHAR2
, object_name VARCHAR2
, policy_name VARCHAR2
) is
PRAGMA AUTONOMOUS_TRANSACTION;
begin
insert into TABLENAME
( whodidit, whenwasit, sql_executed)
values
( user, systimestamp, sys_context(‘userenv’,’current_sql’))
;
commit;
end HANDLE_ACCESS;
end;
/begin
dbms_fga.add_policy
( object_schema=>’SCHEMANAME’
, object_name=>’TABLENAME’
, policy_name=>’ACCESS_HANDLED’
, audit_column => ‘COLUMNNAME’
, audit_condition => ”
, handler_schema => ‘AUDITEDUSER’
, handler_module => ‘AUDIT_HANDLER.HANDLE_ACCESS’
);
end;
/begin
dbms_fga.drop_policy
( object_schema=>’SCHEMANAME’
, object_name=>’TABLENAME’
, policy_name=>’ACCESS_HANDLED’
);
end;
Source : Oracle Offical Documents