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."
Setup:
$ 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.
1 comment:
But this audit only works with explicit SELECT on the table. If a query to this table is through PL/SQL, the access is NOT audited.
Post a Comment