1

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?

| improve this question | |
  • I want to add that this duplicate logging also can cause performance issues, which is why I am currently looking for a solution avoiding it in the first place. It looks like with parallel processing, each parallel process triggers its own log entry (instead of leaving that to the coordinator). – Izzy Nov 7 '13 at 16:10
  • There is a Metalink/MOS note on "Auditing with Oracle Parallel Server" which might include helpful information. I currently have no access to MOS, so I cannot check – but maybe someone else could do that and give some feedback? – Izzy Nov 15 '13 at 12:11
  • I finally got hold of a copy of this MOS document. It's from 1998 and only addresses some things for OPS unrelated to our issue here (Database-wide vs. Single-instance Auditing, Pinging of the sys.aud$ segment header, Pinging of sys.aud$ data blocks and false pinging in the system tablespace). Luckily, your answer already gives a useful work-around (+1). Thanks! – Izzy Nov 28 '13 at 12:35
2

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.