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!