Audit select queries against specified tables by specified users

I have a requirement to audit all SELECT queries (by access) against specified tables by specified users.

As far as I can tell, this can’t be done with normal auditing; it only supports auditing all queries by specified users or all queries agains specified tables (by any user). This is a problem because there are system accounts that would generate too much audit and needs to be excluded from audit.

This leaves fine grained auditing, where it is possible to do this by specifying appropriate predicate for audit_condition parameter in dbms_fga.add_policy. However, FGA has unfortunate effect of writing duplicate rows in audit trail when parallel query is used; not desirable when the goal is to minimize audit information.

Currently, I’m planning to go with FGA and frequently remove the duplicate rows. Is there any better way to implement this auditing strategy?

I have found a way to prevent duplicate rows in FGA audit trail with parallel query

First create a function that return 1 if AUTHENTICATION_METHOD is PQ_SLAVE in USERENV context (coordinator process gets 0)

create or replace function is_pg_slave return integer as
begin
  if (sys_context('USERENV', 'AUTHENTICATION_METHOD') = 'PQ_SLAVE') then
    return 1;
  else 
    return 0;
  end if;
end;
/

Then add policy to table

begin
      dbms_fga.add_policy(
        object_name       => 'table_name',
        policy_name       => 'noaudit_pq_slave',
        audit_condition   => 'is_pg_slave = 0',
        statement_types   => 'SELECT');
end;
/

Now only the query coordinator is audited, so no matter what the parallel degree is, only one row is inserted into fga audit trail.

I have not noticed any performance issues with this approach compared to normal auditing or no auditing.