FGAC / EXEMPT and resulting Audit records – More explored

In my previous post I demonstrated, the potential for excessive audit record generation in Oracle 11g. In this post, I’ll explore the effect of a “Keyed function call” from view (or select statements) on generation of BY ACCESS audit records.

I’ll use the same setup as described in the previous post for this demonstration too.

Setup:

In addition to previous setup, we’ll create a function T_ROW that selects and returns value of T.C2 for given key T.C1.

Here is the function…

CREATE OR REPLACE

  FUNCTION t_row(

      in_id NUMBER)

    RETURN VARCHAR2

  AS

    v2 VARCHAR2 (100);

  BEGIN

    SELECT t.c2 INTO v2 FROM hr.t WHERE t.c1 = in_id;

    RETURN v2;

  END;

We’ll modify the view, to select a column using the function, show below…

create or replace view view_t as

SELECT t."C1",

  t."C2",

  (SELECT COUNT(*) FROM t t1 WHERE t1.c1 = t.c1

  ) scalar_cnt,

  t_row(t.c1) func_value

FROM t;

Let us verify the result-set of the view.

SQL> revoke exempt access policy from hr;

 

Revoke succeeded.

 

SQL> connect a/a;

Connected.

SQL> set line 200 ;

SQL> set wrap off;

SQL> col func_value format a20;

SQL> select * from hr.view_t;

 

        C1 C2           SCALAR_CNT FUNC_VALUE

---------- ------------ ---------- --------------------

         1 AAAA                  1 AAAA

         2 AAAA                  1

         3 AAAA                  1 AAAA

         4 AAAA                  1

         5 AAAA                  1 AAAA

         6 AAAA                  1

         7 AAAA                  1 AAAA

         8 AAAA                  1

         9 AAAA                  1 AAAA

        10 AAAA                  1

 

10 rows selected.

 

SQL>

Check the result set above and let us review the cause of this behavior.

User A has been granted EXEMPT ACCESS POLICY system privilege. So when user A, selects from HR.VIEW_T which directly accesses table T (twice) the FGAC policy function is by-passed, returning all 10 rows.

However, since the column FUNC_VALUE is derived from a call to function HR.T_ROW, which executes under the privilege of user HR (check definer rights versus invoker rights) and user HR cannot bypass FGAC policy (though HR owns all the objects) only rows that satisfy the FGAC policy are returned. For a quick review of FGAC policy function defined effect, click here.

Review Audit Trail:

Now with this set up let us check the audit record generated by user A when accessing the view HR.VIEW_T.

SQL> select * from hr.view_t;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3335345748

 

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    10 |    80 |     3   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |

|*  2 |   TABLE ACCESS FULL| T    |     1 |     3 |     3   (0)| 00:00:01 |

|   3 |  TABLE ACCESS FULL | T    |    10 |    80 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("T1"."C1"=:B1)

Here table HR.T is accessed twice (using FTS), and for each access and audit record is generated for each access. One of the audit record is for EXPLAIN plan.

SQL> select username, count(*) from dba_audit_trail group by username;

 

USERNAME                         COUNT(*)

------------------------------ ----------

A                                       3

 

SQL>

Now let us see what happens, if we add an index on T.C1 and use the index for accessing the view HR.VIEW_T.

We’ll create an index, add primary key and gather statistics.

SQL> create index t_indx on t (c1);

Index created.

 

SQL> alter table t add (constraint t_pk primary key (c1) using index t_indx enable validate);

Table altered.

 

SQL> insert into t select level + 10 c1, 'BBBBB' from dual connect by level < 10001;

10000 rows created.

 

SQL> commit;

Commit complete.

 

SQL>  analyze table t compute statistics ;

Table analyzed.

 

SQL> analyze table t compute statistics for all indexed columns;

Table analyzed.

 

SQL>

Let us check the explain plan and audit records when user A selects from HR.VIEW_T.

SQL> select * from hr.view_t

10010 rows selected.

 

Execution Plan

----------------------------------------------------------

Plan hash value: 700470346

 

----------------------------------------------------------------------------

| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |        | 10010 | 80080 |     9   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |        |     1 |     3 |            |          |

|*  2 |   INDEX RANGE SCAN| T_INDX |     1 |     3 |     1   (0)| 00:00:01 |

|   3 |  TABLE ACCESS FULL| T      | 10010 | 80080 |     9   (0)| 00:00:01 |

----------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("T1"."C1"=:B1)

 

 

Statistics

----------------------------------------------------------

      11595  recursive calls

        127  db block gets

      32080  consistent gets

         13  physical reads

      23228  redo size

     175242  bytes sent via SQL*Net to client

       7757  bytes received via SQL*Net from client

        669  SQL*Net roundtrips to/from client

         25  sorts (memory)

          0  sorts (disk)

      10010  rows processed

 

SQL>

The audit records generated is still 3.

SQL> select count(*) from dba_audit_trail;

 

  COUNT(*)

----------

         5

 

SQL>

 
The experiment above demonstrates that in release 11.1.0.7 of Oracle 11g, audit records generated due to EXEMPT ACCESS POLICY (per access) with FGAC policy in effect, is managed as expected.

However, under certain conditions I have experienced excessive audit record generation.

I’ll explore that more in future postings.

 

Till then BYE!!!!

Excessive AUDIT records with FGAC/EXEMPT privilege

Last week, I posted an article demonstrating the audit record generation with EXEMPT ACCESS POLICY in effect in Oracle 11g.

In this article, I'll demonstrate the potential for undesired audit record generation, due to in-effective audit configuration.

Setup:

1.      User HR ownes the table T, as defined in my previous post.

2.      User A, FGAC policy, and policy functions are also defined in the referenced post.

3.      I'll define a view, HR.VIEW_T using table HR.T. The script for view is below.

SELECT t."C1",

  t."C2",

  (SELECT COUNT(*) FROM t t1 WHERE t1.c1 = t.c1

  ) scalar_cnt

FROM t

;

Note: The scalar sub-query in the view definition.

4.      Grant user A only the SELECT privilege on view HR.VIEW_T.

5.      Grant the user HR (the owner of view) EXEMPT ACCESS POLICY system privilege.

Demonstration:

·         Truncate AUD$, and verify by selecting count from DBA_AUDIT_TRAIL.

SQL> connect /  as sysdba;

Connected.

SQL> truncate aud$;

truncate aud$

            *

ERROR at line 1:

ORA-03290: Invalid truncate command - missing CLUSTER or TABLE keyword

 

SQL> truncate table aud$;

Table truncated.

 

SQL> select count(*) from dba_audit_trail;

         0

 

SQL>

·         Check DBA_SYS_PRIVS for access for user HR and user A.

SQL> select * from dba_sys_privs;

 

SQL>

·         Grant the EXEMPT ACCESS POLICY privilege to HR user. Grant SELECT on view HR.VIEW_T to user A.

SQL> grant exempt access policy to hr;

 

SQL> connect hr/hr;

Connected.

SQL> grant select on view_t to a;

 

Grant succeeded.

 

SQL>

·         Connect as user A and select from HR.VIEW_T. Also note that policy is by-passed for user A.

SQL> connect a/a;

Connected.

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

 

        10

 

SQL> /

 

        10

 

SQL> /

 

        10

 

SQL> connect / as sysdba;

Connected.

SQL> select count(*) from dba_audit_trail;

 

         6

 

SQL> connect a/a;

Connected.

SQL> select * from hr.view_t;

 

         1 AAAA                          1

         2 AAAA                          1

         3 AAAA                          1

         4 AAAA                          1

         5 AAAA                          1

         6 AAAA                          1

         7 AAAA                          1

         8 AAAA                          1

         9 AAAA                          1

        10 AAAA                          1

 

10 rows selected.

 

SQL> connect / as sysdba;

Connected.

SQL> select count(*) from dba_audit_trail;

 

         9

 

SQL>

·         Note that there are 9 audit records generated, although only FOUR selects have been performed over two sessions.

·         These records are generated due to BY ACCESS option and not BY SESSION.

Depending on the usage of the view in an application, this could result in excessive audit record generation. 

Also, suppose there is a PL/SQL function defined that accepts key of table as input parameter and performs a KEYED retrieval of data from table. Such a function can easily (and often) used in views a scalar sub-queries. In such implementation each select executed will be treated as AN ACCESS to the table and audit record will be generated.

 

It is important to monitor the content of DBA_AUDIT_TRAIL view.

 

More explorations next week. Till then BYE!

 

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