OraFAQ Forum: Server Administration » Auditing insert, update, select, delete on a table

Home » RDBMS Server » Server Administration » Auditing insert, update, select, delete on a table

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

Auditing insert, update, select, delete on a table [message #174544]

Mon, 29 May 2006 08:27

orausern
Messages: 826
Registered: December 2005

Senior Member

Hi

I need to have a detailed audit on ALL activities of a table..ie I need to know all the SELECT, INSERT,UPDATE, DELETE against the table(DDLs against the table need not be audited..)

how can this be done? it should be such that the exact sql statement fired against the table should be captured..can this be done via fga or some other means?

appreciate ur help..

Report message to a moderator

Re: Auditing insert, update, select, delete on a table [message #174560 is a reply to message #174544]

Mon, 29 May 2006 11:49

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Would this answer your question (or give you an idea)? If that's not the way you want to do that, you should enable auditing on the database.

Report message to a moderator

Re: Auditing insert, update, select, delete on a table [message #174596 is a reply to message #174560]

Tue, 30 May 2006 00:45

girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)

Senior Member

I hope this article helps

http://www.securityfocus.com/infocus/1689

--Girish

Report message to a moderator

Re: Auditing insert, update, select, delete on a table [message #174603 is a reply to message #174596]

Tue, 30 May 2006 01:13

orausern
Messages: 826
Registered: December 2005

Senior Member

thank you very much, Littlefoot,Girish. This inputs are very useful...

Littlefoot, ur script takes care of insert, update or delete, but what about select? how select queries against a table can be audited? simple db audit perhaps may not be the solution , becoz it is necessary to capture exact sql being fired againt the table..

Report message to a moderator

Re: Auditing insert, update, select, delete on a table [message #174611 is a reply to message #174603]

Tue, 30 May 2006 01:35

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

As far as I can tell, you can not audit SELECT statements in such a way. Triggers can trap only INSERT, UPDATE and DELETE statements. To audit SELECTs, you'll have to enable auditing on your database.

You'd use schema object auditing and issue something like

AUDIT SELECT ON emp;

Read more about auditing here.

Report message to a moderator

Re: Auditing insert, update, select, delete on a table [message #174620 is a reply to message #174611]

Tue, 30 May 2006 02:03

orausern
Messages: 826
Registered: December 2005

Senior Member

thank you very much Littlefoot. I shall try that way out.

Report message to a moderator

Previous Topic:	Installing OLAP services & changing option values
Next Topic:	Error:ORA-00600 : internal error code

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 11:38:09 CDT 2024