Tips and wisdom from 15 years of DBA experience

Friday, October 21, 2011

Audit SELECT on one table

What seems like such a simple task is a bit more painful than it needs to be. The task is "Capture all SQL statements against table X." 


$ sqlplus "/ as sysdba"
SQL> show parameter audit_trail;
(if it shows "db,extended", you are done with setup. Go onto the next section)
SQL> alter system set audit_trail=db,extended scope=spfile;
SQL> shutdown;
SQL> startup;
SQL> show parameter audit_trail;
(verify that the parameter equals "db,extended")

Turning on Auditing:

$ sqlplus "/ as sysdba"
SQL> audit select on schema.table by access; -- if you do not specify by access, then by default "by session" will be used which only audits the first statement of a given session.
SQL> delete from sys.aud$; -- only do this if you are SURE you can safely delete all audit data...makes debugging simpler.
SQL> select count(*) from dba_audit_trail;
(should be zero)
SQL> exit;
$ sqlplus non-sys-user/pw
SQL> select count(*) from schema.table; -- select from the audited table...
SQL> exit;
$ sqlplus "/ as sysdba"
SQL> select count(*) from dba_audit_trail;
(should be non-zero)
The interesting columns are sql_text and sql_bind in dba_audit_object.  To look at the data you can do something like this:
SQL> column sql_bind format a50
SQL> column sql_text format a50
SQL> set lin 200
SQL> select username, to_char(timestamp, 'DD-MON-YYYY HH24:MI:SS'), sql_bind, sql_text from dba_audit_object;
That's it.