Home » RDBMS Server » Security » Auditing DML (10g Rel2-64-bit RHEL 4-64bit)
Auditing DML [message #439004] Wed, 13 January 2010 10:21 Go to next message
smunir362
Messages: 310
Registered: September 2007
Senior Member
I have table1,table2 and table3 in a abc schema. I want to enable audit for DML(insert,update,delete) statements against these tables for all the db users. How can I do that.
Re: Auditing DML [message #439005 is a reply to message #439004] Wed, 13 January 2010 10:28 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
The documentation gives the full syntax with examples
Re: Auditing DML [message #439006 is a reply to message #439004] Wed, 13 January 2010 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not forget to set audit_trail parameter and restart the instance.

Regards
Michel
Re: Auditing DML [message #439008 is a reply to message #439006] Wed, 13 January 2010 10:41 Go to previous messageGo to next message
smunir362
Messages: 310
Registered: September 2007
Senior Member
I already follow these steps.

1. set audit_trail=DB and restart instance;
2. SQL>AUDIT INSERT, UPDATE ON oe.customers;
3. Insert record in oe.customers table.
4. Then check audited record by query
Select * from db_audit_trail;
but in action name column I saw SESSION REC. But it should be insert.
Re: Auditing DML [message #439016 is a reply to message #439008] Wed, 13 January 2010 11:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

use CUT & PASTE so we can see what you do & how Oracle responds
Re: Auditing DML [message #439017 is a reply to message #439008] Wed, 13 January 2010 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> Select * from db_audit_trail;
Select * from db_audit_trail
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Do NOT explain what you did, show us by copying and pasting your SQL*Plus session.

Regards
Michel

[Updated on: Wed, 13 January 2010 11:02]

Report message to a moderator

Re: Auditing DML [message #439018 is a reply to message #439017] Wed, 13 January 2010 11:04 Go to previous messageGo to next message
smunir362
Messages: 310
Registered: September 2007
Senior Member
ok thanks.
Re: Auditing DML [message #440652 is a reply to message #439018] Tue, 26 January 2010 08:59 Go to previous messageGo to next message
smunir362
Messages: 310
Registered: September 2007
Senior Member
I successfully configure audit for objects.
But now I want to store old vale also in case of update data.
How to do it.
Re: Auditing DML [message #440653 is a reply to message #440652] Tue, 26 January 2010 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use a trigger.

Regards
Michel
Re: Auditing DML [message #440655 is a reply to message #440653] Tue, 26 January 2010 09:05 Go to previous messageGo to next message
smunir362
Messages: 310
Registered: September 2007
Senior Member
U mean trigger on object after (insert,update,delete).
But is there any other way. I want to do it at system level.
Re: Auditing DML [message #440658 is a reply to message #440655] Tue, 26 January 2010 09:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, there is no other way.
You can activate fine-grained auditing but it is still a trigger on each table (even if it is an internal trigger).

Regards
Michel
Re: Auditing DML [message #440661 is a reply to message #440658] Tue, 26 January 2010 09:14 Go to previous messageGo to next message
smunir362
Messages: 310
Registered: September 2007
Senior Member
using Fine Grained can I store old vale. If yes in which table it logs these informations.
Re: Auditing DML [message #440675 is a reply to message #440661] Tue, 26 January 2010 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know did you search in the documentation?

Regards
Michel
Re: Auditing DML [message #441761 is a reply to message #440675] Wed, 03 February 2010 00:40 Go to previous message
smunir362
Messages: 310
Registered: September 2007
Senior Member
I check the help but could not find my solution in FGA. All the avaliable solutions are trigger on the table.
My question is how can I use FGA for storing old and new vales.
Previous Topic: Script to list users with select on v$views
Next Topic: Stop Export To DBA User
Goto Forum:
  


Current Time: Thu Mar 28 18:58:35 CDT 2024