Auditing

[msuresh]

How to audit all the actions performed by a particular user.
I enabled audit and issued a command like this

audit all by ;

When i select from sys.aud$ after making some select/update login/logoff etc.it shows only select and not update.Any help.

[padmam]

Hi,

You have to write a Trigger so that when INSERT, UPDATE, or DELETE before/after statement is issued by the users is audited.

Create a Table and then create a Trigger as follows

CREATE OR REPLACE TRIGGER Trigger_name BEFORE INSERT OR DELETE OR UPDATE ON USER.Table_name FOR EACH ROW

If you need a detail information on Audit of this Type i can send you a example which we recently did .

Cheers

Padmam

[marcio.68]

All queryes run by user logged

Try create 2 tables :

AUDIT_USER
Name Null? Type
--------------- -------- ---------------
SQL_ADDRESS RAW(4)
SID NUMBER
SERIAL# NUMBER
USER# NUMBER
USERNAME VARCHAR2(30)
MACHINE VARCHAR2(64)
PROGRAM VARCHAR2(64)
INCLUDE DATE

SQL_TEXT_USER
Name Null? Type
--------------- -------- -------------
ADDRESS RAW(4)
PIECE NUMBER
SQL_TEXT VARCHAR2(64)

And create procedure contents :

Insert /*+ append */
Into audit_user
nologging
Select sess.sql_address, sess.sid, sess.serial#, sess.user#, sess.username, sess.machine, sess.program, sysdate
from v$session sess
where sess.user# = 'USER'
;
commit
;
Insert /*+ append */
Into sql_text_user
nologging
Select address, piece, sql_text
From v$sqltext
Where address IN (select sql_address from audit_user)
and address NOT IN (select address from sql_text_user)
order by piece
;
commit
;

[msuresh]

Padmam,
Can you send me the script with an example.