Tuesday, July 31, 2007

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!

6 comments:

boy said...

Thai entertainment news updates. Click now !!
เลขเด็ด

independent said...

I like your content. It's fun and informative. Come to my watch, click howtobymild4 | howtobymild5 | howtobymild1 | howtobymild3 | howtobymild2

garin said...

This article is very good. We would like to leave a good article. To read more articles, click here >> chelsea

เชลซี

ข่าวเชลซี

ข่าวบอล

โปรแกรมฟุตบอล

edok69 said...

This article is very good. We would like to leave a good article. To read more articles, click here >>>
edok19.hatenablog.com
www.bloglovin.com
5e68b4dadffdc.site123.me
babyedok.wixsite.com
medium.com

chanee said...

A good website is useful.
เว็บคาสิโนออนไลน์
คาสิโนออนไลน์ เครดิตฟรี
บอลออนไลน์
สูตรบาคาร่าฟรี
เล่นบาคาร่าออนไลน์ฟรี
บาคาร่า พันทิป
โหลดบาคาร่าออนไลน์
สล็อตออนไลน์ฟรีเครดิต ไม่ต้องฝาก
สล็อตออนไลน์ มือถือ
สล็อตออนไลน์ฟรีเครดิต2019

Anonymous said...

Good article about Oracle.


_________________________________
I work with roberta model ai

Translate