Auditing Nuances

I had posted it earlier on my other blog - Oracle Security Musings (http://www.proligence.com/blog/index.html). I reproduce here.

This came up in the Oracle-L maiilng list, which I used to frequent and I responded to this thread.

In regular Oracle auditing (via the AUDIT command), what happens when the statement that causes the audit trail is rolled back? Does the trail disapper, too?

The answer may startle you. It doesn't! Audit entry generation is not part of the transaction; it might have been implemented through autonomous transactions or some other means; but it is outside the scope of the tranasaction. Here is a little test to prove it.

Make sure that audit_trail is set to DB.

create table atest1 (col1 number, col2 number, col3 number, col4 number);
insert into atest1 values (1,1,1,1);
audit update on atest1 by access;
update atest1 set col1 = 2;

Do NOT commit.

From another session as user SYS,

select action_name, obj_name, ses_actions, returncode from dba_audit_trail;

ACTION_NAME OBJ_NAME SES_ACTIONS RETURNCODE
----------- -------- ----------- -----------------
UPDATE ATEST1 0

The entry is there even if the transaction is not committed.

Now rollback the update and check the audit trail; it will be there.

If the auditing option were BY SESSION, instead of action, the ACTION_NAME would have been SESSION REC and the column SES_ACTIONS would've been"----------S-----". Beware of this side-effect of the audit functionality. It may not be what you wanted to develop!