Oracle Security Fine Grained Auditing Enhancements Oracle Security Tips by Burleson Consulting

This is an excerpt from the bestselling book "Oracle Privacy Security Auditing", a complete Oracle security reference with working Oracle security scripts.
 

---

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
   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’,
      statement_types => ‘SELECT’

  );
end;
/

To audit insert, delete, and update for the same table on the same policy condition and columns, we can use:

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’,
      statement_types => ‘SELECT,INSERT,UPDATE,DELETE’
  );
end;
/

The above code writes an entry into the table fga_log$ when the table is subjected to insert, update, delete, and select statements; when the auditing condition is satisfied and the audit columns are referenced.

This newly introduced facility is extremely helpful in cases where row level auditing was captured using table-level triggers in the prior releases. Using the DML FGA policies, the row level triggers can be retired.

Since FGA can also execute a procedure known as handler module when the audit conditions are satisfied, the policy can be extended to perform other tasks, not just writing to the audit trails. Some of these tasks are sending emails, or setting a column to a certain value that has an indicator to cause a future job to be kicked off, etc.

To support the new functionality, the view dba_audit_policies has been changed. Here is a description of the view in Oracle 10g.

OBJECT_SCHEMA     NOT NULL VARCHAR2(30)
OBJECT_NAME       NOT NULL VARCHAR2(30)
POLICY_NAME       NOT NULL VARCHAR2(30)
POLICY_TEXT                VARCHAR2(4000)
POLICY_COLUMN              VARCHAR2(30)
PF_SCHEMA                  VARCHAR2(30)
PF_PACKAGE                 VARCHAR2(30)
PF_FUNCTION                VARCHAR2(30)
ENABLED                    VARCHAR2(3)
SEL                        VARCHAR2(3)
INS                        VARCHAR2(3)
UPD                        VARCHAR2(3)
DEL                        VARCHAR2(3)

The last four columns highlighted in bold are new in this release and they indicate the triggering event of this policy. For instance, if a policy is defined for SELECT statements, the column SEL will show YES, else it will show NO. Similarly, INS, UPD and DEL show triggering settings for Insert, Update and Delete, respectively.

Similarly, the view dba_fga_audit_trail also has some additional columns to support the added functionality. Table 13.3 describes the new columns of this view.

COLUMN NAME	DESCRIPTION
STATEMENT_TYPE	The type of statement – INSERT, UPDATE, DELETE, or SELECT that triggered the auditing.
EXTENDED_TIMESTAMP	The extended timestamp of the trail entry. See the corresponding entry in Table 13.1 for more explanation.
PROXY_SESSIONID	If the user is an enterprise user and connects using a proxy user, the SID of the session of the proxy user connection is shown here.
GLOBAL_UID	If the user is an enterprise user, the global user ID.
INSTANCE_NUMBER	The instance number if using RAC
OS_PROCESS	The Operating System process id.
TRANSACTIONID	The transaction identifier, if the statement is part of a transaction.
STATEMENTID	The statement id. For more detailed explanation, see the chapter on Auditing.
ENTRYID	The entry identifier in the audit trail. See the Chapter on Auditing for more details.

Table 13.3. New Columns of dba_fga_audit_trail

 

This is an excerpt from the book "Oracle Privacy Security Auditing". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle security and auditing scripts.