Jan 9, 2009

Audit feature in Oracle 10g

TO audit what users are doing in Database

Assuming that the "audtest" user is to be audited:

CONNECT sys/password AS SYSDBA

AUDIT ALL BY audtest BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE,DELETE TABLE BY audtest BYACCESS;
AUDIT EXECUTE PROCEDURE BY audtest BY ACCESS;
-----------------------------------------
These options audit all DDL & DML issued by "audtest", along with some system events.

* DDL (CREATE, ALTER & DROP of objects)
* DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).
* SYSTEM EVENTS (LOGON, LOGOFF etc.)

------------------------------------------------------------
select s.name,d.userid,d.userhost,d.terminal,d.obj$name,d.spare1,d.ntimestamp#
from sys.aud$ d,sys.audit_actions s
where d.obj$creator=user_name and s.action=d.action# ;

--------------------------
select username,
terminal,
action_name,
to_char(timestamp,'DDMMYYYY:HHMISS') timestamp,
to_char(logoff_time,'DDMMYYYY:HHMISS') logoff_time,
returncode
from dba_audit_session
-----------------------------------------
select username,
priv_used,
obj_name,
to_char(timestamp,'DD-MON-YYYY HH24:MI') timestamp,
returncode
from dba_audit_trail
where priv_used is not null
and priv_used<>'CREATE SESSION'

No comments: