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.
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.
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.
· Truncate AUD$, and verify by selecting count from DBA_AUDIT_TRAIL.
· Check DBA_SYS_PRIVS for access for user HR and user A.
· Grant the EXEMPT ACCESS POLICY privilege to HR user. Grant SELECT on view HR.VIEW_T to user A.
· Connect as user A and select from HR.VIEW_T. Also note that policy is by-passed for user A.
· 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!