Oracle 11g: Auditing - EXEMPT ACCESS POLICY with FGAC

In one of the previous posting, I demonstrated Fine Grain Access Control (FGAC) feature in Oracle.
Oracle turned on several audit options in 11g release. One of the audit options is EXEMPT ACCESS POLICY. Following STMT (statement) level audit options are turned on in release Oracle 11.1.0.7, by default.

ALTER SYSTEM	CREATE ANY TABLE	ALTER ANY PROCEDURE
SYSTEM AUDIT	ALTER ANY TABLE	DROP ANY PROCEDURE
CREATE SESSION	DROP ANY TABLE	ALTER PROFILE
CREATE USER	CREATE PUBLIC DATABASE LINK	DROP PROFILE
ALTER USER	GRANT ANY ROLE	GRANT ANY PRIVILEGE
DROP USER	ALTER DATABASE	CREATE ANY LIBRARY
ROLE	CREATE ANY PROCEDURE	EXEMPT ACCESS POLICY
CREATE ANY JOB	GRANT ANY OBJECT PRIVILEGE	CREATE EXTERNAL JOB

For default audit options set in 11g release please check here.

In this article, I’ll demonstrate the effect of default audit setting on FGAC policies. Specifically, audit option EXEMPT ACCESS POLICY is explored.

For setup and demonstration of FGAC for this demonstration, please click here.

Setup

Please run this DEMO in TEST ONLY environment!!!

1.   Set up FGAC users, grants, table, functions, policy as described here.
2.   Ensure all STMT, PRIV, Object, User level audit options are off.

$ sqlplus  / as sysdba

 

SQL*Plus: Release 11.1.0.7.0 - Production on Thu Aug 23 18:08:16 2012

 

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> show user;

USER is "SYS"

SQL> select count(*) from dba_stmt_audit_opts;

 

  COUNT(*)

----------

         0

 

SQL> select count(*) from dba_priv_audit_opts;

 

  COUNT(*)

----------

         0

 

SQL> select count(*) from dba_obj_audit_opts;

 

  COUNT(*)

----------

         0

 

SQL>

3.   Clear the AUD$ table. Not necessary, but help in monitoring the audit records generated.

 SQL> select count(*) from dba_audit_trail;

 

  COUNT(*)

----------

      4425

 

SQL> truncate table aud$;

 

Table truncated.

 

SQL> select count(*) from dba_audit_trail;

 

  COUNT(*)

----------

         0

 

SQL>

Enable audit options and monitor

The other major change in auditing with 11g release is BY SESSION auditing is deprecated. This is demonstrated below as well.

Connect as user A and select from HR.T. Verify the audit trail.

SQL> connect a/a

Connected.

SQL> select count(*) from hr.t;

 

  COUNT(*)

----------

         5

 

SQL> connect / as sysdba;

Connected.

SQL> select count(*) from dba_audit_trail;

 

  COUNT(*)

----------

         0

 

SQL>

Grant EXEMPT ACCESS POLICY privilege to user A, select from HR.T and verify the audit trail.

SQL> connect a/a

Connected.

SQL> select count(*) from hr.t;

 

  COUNT(*)

----------

         5

 

SQL> connect / as sysdba;

Connected.

SQL> select count(*) from dba_audit_trail;

 

  COUNT(*)

----------

         0

 

SQL> grant exempt access policy to a;

 

Grant succeeded.

 

SQL> connect a/a;

Connected.

SQL> select count(*) from hr.t;

 

  COUNT(*)

----------

        10

 

SQL> connect / as sysdba;

Connected.

SQL> select count(*) from dba_audit_trail;

 

  COUNT(*)

----------

         1

 

SQL>

Note that audit record was generated for the access of table HR.T by user A.

Also, with 11g release Oracle is deprecating BY SESSION auditing. Though the audit option by default is set by session (if BY ACCESS clause is not used), the auditing is performed for EVERY access event, as demonstrated below.

 

• I'll connect as user A, and execute query on table HR.T mutliple times (within same session).
• We'll see that there are multiple audit records for each access to the table T by user A.
• However once the privilege is revoked for user A, POLICY comes back into effect and no audit records are generated.

SQL> connect a/a;

Connected.

SQL> select count(*) from hr.t;

 

  COUNT(*)

----------

        10

 

SQL> connect / as sysdba;

Connected.

SQL> select count(*) from dba_audit_trail;

 

  COUNT(*)

----------

         1

 

SQL> connect a/a;

Connected.

SQL> select count(*) from hr.t;

 

  COUNT(*)

----------

        10

 

SQL> /

 

  COUNT(*)

----------

        10

 

SQL> /

 

  COUNT(*)

----------

        10

 

SQL> /

 

  COUNT(*)

----------

        10

 

SQL> /

 

  COUNT(*)

----------

        10

 

SQL> connect / as sysdba

Connected.

SQL>  select count(*) from dba_audit_trail;

 

  COUNT(*)

----------

         6

 

SQL> connect / as sysdba;

Connected.

SQL> revoke exempt access policy from a;

 

Revoke succeeded.

 

SQL> connect a/a

Connected.

SQL> select count(*) from hr.t;

 

  COUNT(*)

----------

         5

 

SQL> connect / as sysdba;

Connected.

SQL> select count(*) from dba_audit_trail;

 

  COUNT(*)

----------

         6

 

SQL>

As noted above, an audit record is generated for every access to the table using the privilege of EXEMPT ACCESS POLICY.

In certain environment this could result in generation of excessive audit record.

I believe, if the base table governed by FGAC policy, is used in a view as scalar sub-query resulting in access of the table twice, then an audit record will be generated for each access of the base table within single access of the view.

I’ll validate this in my next posting.

Till then Bye.

References

Arup Nanda’s post on Oracle 11g new features.
http://www.oracle.com/technetwork/articles/sql/11g-security-100258.html