When Auditing Attacks


I had a particularly annoying problem with Oracle auditing a few days ago. I thought I would write about it here in case anybody knows a solution, or if anyone at Oracle cares to add the functionality I need. 🙂

The problem

I was asked to audit selects against tables in a particular schema when issued by several users. For the sake of this post let’s assume the following.

  • SCHEMAOWNER : The owner of the tables that are to be audited.
  • USER1, USER2, USER3 : The three users whose select statements are to be audited.

So I decided the write an audit policy.

Option 1 : Audit all selects, regardless of schema

My first thought was to do this.

create audit policy my_select_policy
actions select
when q'~ sys_context('userenv', 'session_user') in ('USER1','USER2','USER3') ~'
evaluate per session

The problem is it produced masses of audit records, most of which were referencing selects against objects owned by other schemas. I have so many records I can’t actually purge the audit trail. I’m having to wait until the next partition is created in a month, so I can drop the current partition and shrink the data files. 🙁

Option 2 : Explicitly list all objects to be audited

So I need to make the policy more granular, which I can do by explicitly referencing all objects I want to audit, like this.

create audit policy my_select_policy
actions select on schemaowner.tab1,
select on schemaowner.tab2,
select on schemaowner.tab3
when q'~ sys_context('userenv', 'session_user') in ('USER1','USER2','USER3') ~'
evaluate per session

The problem is there are loads of tables in the schema, so doing this is a pain. I could generate the statement using a script, but even then if someone adds a new table the audit policy wouldn’t pick it up.

What I really want

What I really want is to be able to limit the action to a specific schema. The specific syntax is not important. The result is what matters. Maybe something like this.

-- Use and explicit schema reference,
create audit policy my_select_policy
actions select on schema schemaowner
when q'~ sys_context('userenv', 'session_user') in ('USER1','USER2','USER3') ~'
evaluate per session


I don’t believe the current syntax for audit policies allows them to be limited by schema, so I’m faced with generating masses of unnecessary audit records, or having to explicitly name every table. 🙁

This all sounded kind-of familiar, and when I did a bit of Googling I found this note by Pete Finnigan. So I’m not alone in finding this frustrating.



Author: Tim...

DBA, Developer, Author, Trainer.

4 thoughts on “When Auditing Attacks”

  1. I can’t fix your problem but I’d recommend changing your audit interval partitions to days. Much better for cleanup (I have clients who keep audit locally for only a few days) and you’re never going to hit max interval numbers.

    Sadly, this won’t help with cleanup as that’s always done with a DELETE command (as it’s done based on DBID and you could have other stuff hanging around in there) and doesn’t truncate the partitions.

    Perhaps you could run a series of small cleanups with a timestamp advancing every 10 minutes in a loop?

    As for your audit policy, I’d go with the individual table option, ensuring it’s all in 1 policy to minimise the impact.

  2. No solution, but I’d probably go for the first approach and add the “only toplevel” clause to the policy to reduce the amount of records being created in the audit trail ..


  3. Sadly that option is currently not available ( https://asktom.oracle.com/ords/f?p=100:11:::::P11_QUESTION_ID:9547361400346532157#:~:text=and%20Chris%20said…,audited%20under%20the%20specific%20schema? )…

    I think for now your best bet is you can create a script that create an sql file on schedule (every x hours) for your option 2?

    It’s a pain, but I don’t see any other way..

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.