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
container=current;
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
container=current;
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
container=current;
Thoughts
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.
Cheers
Tim…
