Forum Stats

  • 3,840,350 Users
  • 2,262,592 Discussions
  • 7,901,236 Comments

Discussions

unified auditing in vpd using synonyms

This question pertains to unified auditing in Oracle 12c Enterprise edition (release 12.2.0.1.0).

I'm trying to use a policy to audit queries and DML on a few tables in an application DB that is implemented as a VPD. In general, it works as advertised but I've encountered a peculiar problem when using synonyms in the SQL that seems somehow to be related to the existence of a security policy on the tables.

Here's the problem:  When the SQL is issued via an interactive query tool (SQL*Plus, SQL Developer, or TOAD), actions on a target table do not result in records in the unified audit trail if - and only if - the statement uses a synonym for the table. If the table name is qualified with the schema name, the action is captured in the audit trail. This is true regardless of whether the schema name is explicitly specified when the audit policy is created. This problem does not occur when the target table has no security policy on it.

However, when the same SQL in the same user security context is issued via an application (e.g., a .NET application that uses ODP for data access), the actions are captured in the unified audit trail.  Everything works as you would expect including capture of the VPD predicate that was applied.

The following is a description of a simple test I used to verify what we’ve observed.

Basic conditions:

- SCHEMA1 is an account that has the AUDIT_ADMIN role

- X is a table owned by SCHEMA1 that includes a column called COMPANY_ID

- X has no security policy applied to it

- X is the target of a unified audit policy that audits queries on table X by all users

- There is a public synonym (X) for SCHEMA1.X

- USER1 is an account with SELECT privileges on X

Step-by-step breakdown using TOAD or SQL*Plus:

-  USER1 issues a query on X using the synonym, e.g. "select * from X".  This returns all records from X and results in a record for that action in the UNIFIED_AUDIT_TRAIL

- Then SCHEMA1 applies a security policy to X limiting the records visible to accounts subject to the policy based on application context - "company_id = sys_context('app_cntxt', 'company_id')"

-  After re-logging in to establish the application context, USER1 queries X in the same way, i.e. "select * from X". As expected, the query returns the correct subset of records according to the policy but in this case no audit record is written.

-  USER1 changes the query to refer explicitly to the table owner, i.e. "select * from SCHEMA1.X". This returns the correct subset of records and results in a record in the audit trail including the VPD predicate.

- SCHEMA1 then drops the security policy on X.

- USER1 issues the query as originally stated with the synonym and replicates the first result.

This is true for the use of either private or public synonyms and regardless of whether in creating the audit policy the table name is qualified with the owner name or not.

I have not been able to find a reference anywhere to anyone having a similar problem, nor anything in the documentation that would lead me to believe that there might be a problem using unified audit policies with a VPD.

This leads me to think I must be doing something wrong. Except for the particular set of conditions described the audit policy works perfectly.  I have no idea what could be going on but functionally, it looks as though when using a synonym for a target table that has a security policy on it, the auditing function doesn't recognize that the target of the audit policy and the target of the SQL are the same object.

As shown above the VPD predicate is simple. The audit policy used to test is also very simple - of the form: 

                create audit policy TST_UNAUDIT

                actions SELECT on X;

or

                create audit policy TST_UNAUDIT

                actions SELECT on SCHEMA1.X;

I'm hoping this is due to an oversight or something stupid on my part, but I’m unable to see it. Any insight or help would be greatly appreciated.

Answers