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 ONLYenvironment!!!

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s