OraFAQ Forum: Security » AUDIT tracking for ROLLBACK and COMMIT

Home » RDBMS Server » Security » AUDIT tracking for ROLLBACK and COMMIT (Oracle 10g (10.2.0.1.0),Windows Server 2003, 64 bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

AUDIT tracking for ROLLBACK and COMMIT [message #385368]

Mon, 09 February 2009 05:28

rizimazhar
Messages: 34
Registered: August 2008
Location: Pakistan

Member

Hello Everybody

I am a little new in auditing.
I have deployed it successfully. And i can track all changes for HR user based upon the following audit commands settings:

AUDIT ALL BY HR BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY HR BY ACCESS;
AUDIT EXECUTE PROCEDURE BY HR BY ACCESS;

I am also able to see the sql statements and bind variables as well, as i have used audit_trail="db,extended" option.

I am concerned about one thing, this audit throws all audit data that i need, but i want to see the audit entries for the following commands as well:

ROLLBACK and COMMIT

Can anybody tell me how to achieve that?

Regards

Rizwan

Report message to a moderator

Re: AUDIT tracking for ROLLBACK and COMMIT [message #385374 is a reply to message #385368]

Mon, 09 February 2009 05:46

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

As far as I know, there is no auditing facility for these statements.

Regards
Michel

[Updated on: Mon, 09 February 2009 05:46]

Report message to a moderator

Re: AUDIT tracking for ROLLBACK and COMMIT [message #385393 is a reply to message #385368]

Mon, 09 February 2009 06:40

rizimazhar
Messages: 34
Registered: August 2008
Location: Pakistan

Member

ANY OTHER VALUABLE ADVICES...
CZ I NEED TO HAVE THIS INFORMATION.

Report message to a moderator

Re: AUDIT tracking for ROLLBACK and COMMIT [message #385399 is a reply to message #385393]

Mon, 09 February 2009 06:52

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Don't post in UPPER case.

Regards
Michel

Report message to a moderator

Re: AUDIT tracking for ROLLBACK and COMMIT [message #385404 is a reply to message #385368]

Mon, 09 February 2009 07:19

rizimazhar
Messages: 34
Registered: August 2008
Location: Pakistan

Member

Thats agreed but i need to know is there any possibility for the above mentioned requirement...

Positive replies would be highly obliged...

Report message to a moderator

Re: AUDIT tracking for ROLLBACK and COMMIT [message #385405 is a reply to message #385404]

Mon, 09 February 2009 07:24

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

As I said there is no audit statements for commit and rollback, there is no trigger on commit or rollback...

Regards
Michel

Report message to a moderator

Re: AUDIT tracking for ROLLBACK and COMMIT [message #405997 is a reply to message #385405]

Mon, 01 June 2009 08:19

Armour09
Messages: 2
Registered: June 2008

Junior Member

yes. I have same problem .

I think we can audit commit or rollback. because when I select audit_actions, I see that commit and rollback. commit action id 44,rollback action id 45 .
if rollback and commit have action id ,we can audit this.

are you sure ?

Report message to a moderator

Re: AUDIT tracking for ROLLBACK and COMMIT [message #406001 is a reply to message #405997]

Mon, 01 June 2009 08:50

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

if rollback and commit have action id ,we can audit this.

Wrong.
If I have money I can buy a car but this does not mean I have a license to drive it.

Regards
Michel

Report message to a moderator

Re: AUDIT tracking for ROLLBACK and COMMIT [message #671646 is a reply to message #385368]

Tue, 11 September 2018 09:50

altink
Messages: 1
Registered: September 2018

Junior Member

Use the ALL STATEMENTS new (starting in 11g R2) shortcut.
It will audit anything of Top-Level SQL for the user, but it does get the COMMIT and ROLLBACK.
So far it looks the only way. Can't get them specifically (as an usual - ex. DELETE TABLE).

It should be the same for SAVEPOINT too, have not tested.
If one has, please post.

regards
Altin

Report message to a moderator

Re: AUDIT tracking for ROLLBACK and COMMIT [message #671647 is a reply to message #671646]

Tue, 11 September 2018 10:21

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Why would one post in a 9 years old topic about a desupported version?
If you have a specific question, please create a new topic.

Report message to a moderator

Re: AUDIT tracking for ROLLBACK and COMMIT [message #671651 is a reply to message #671646]

Tue, 11 September 2018 12:38

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

I didn't know that. I've just tested (in 18.3) and yes, AUDIT ALL STATETMENTS does capture COMMITs. However, after a quick look I don't see how to connect a COMMIT to the DML, so it still isn't possible to know what was committed and what was rolled back.

Report message to a moderator

Re: AUDIT tracking for ROLLBACK and COMMIT [message #671653 is a reply to message #671651]

Tue, 11 September 2018 13:27

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I have not this version to check but dba_autdit_trail has a TRANSACTIONID column which should be the same for the COMMIT or ROLLBACK statement and the other statements they cover.

Report message to a moderator

Previous Topic:	JDBC connections on TLS/2484 and TCP/1521
Next Topic:	authorization Required.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 02 15:49:32 CST 2025