Oracle Tips by Burleson

Chapter 13 Oracle 10g Enhancements

Using Extended Audit Functionality, several types of events can be audited, without any additional coding.

Fine Grained Auditing (FGA) Enhancements

Fine Grained auditing has been expanded to a full solution for all types of database access in Oracle 10g. In Oracle 9i, only select statements could be audited; 10g has expanded this functionality to audit even DML statements such as insert, update and delete. To support this, a new parameter has been introduced in the add_policy procedure of the package dbms_fga.

In chapter 11, where the Fine Grained Auditing has been described in detail, the following code sample was used to turn on auditing for a certain table.

begin
   dbms_fga.add_policy (
      object_schema=>'CLAIM_SCHEMA',
      object_name=>'CLAIMS',
      policy_name=>'LARGE_CLAIM',
      audit_condition=>
        'CLAIM_AMOUNT>500 OR PAID_AMOUNT>500',
      audit_column=>
        'SSN, PROC_CODE’
  );
end;
/

This was used to turn auditing on only for select statements against the table. The same can be now be rewritten as:

begin