Oracle Auditing

select name,value from v$parameter where name='audit_trail';

ALTER SYSTEM SET AUDIT_TRAIL=DB SCOPE=SPFILE;

Shutdown

startup

--

select * from dba_priv_audit_opts;

select * from dba_audit_session;

select * from dba_audit_trail;

select * from dba_stmt_audit_opts

union

select * from dba_priv_audit_opts;

select * from dba_audit_exists;

select * from dba_audit_object;

select * from dba_audit_session;

select * from dba_audit_statement;

select * from dba_audit_trail;

select * from dba_obj_audit_opts;

select * from dba_priv_audit_opts;

select * from dba_stmt_audit_opts;

----

audit all by KGUPTA2 by access;

noaudit all by KGUPTA2;

audit create session by access;

audit audit system by access;

audit grant any privilege by access;

audit grant any object privilege by access;

audit grant any role by access;

audit create user by access;

audit create any table by access;

audit create public database link by access;

audit create any procedure by access;

audit alter user by access;

audit alter any table by access;

audit alter any procedure by access;

audit alter database by access;

audit alter system by access;

audit alter profile by access;

audit drop user by access;

audit drop any procedure by access;

audit drop any table by access;

audit drop profile by access;

audit select table, insert table, update table, delete table by payroll by access;

--

Auditing user activity with the Oracle audit command

Oracle has syntax for auditing specific user activity. To audit the activity of user KGUPTA2 we could issue these audit commands:

Audit all Oracle user activity.

This audits everything including DDL (create table), DML (inserts, updates, deletes) and login/logoff events:

audit all by kGUPTA2 by access;

Audit all Oracle user viewing activity:

audit select table by KGUPTA2 by access;

Audit all Oracle user data change activity:

audit update table, delete table,insert table by KGUPTA2 by access;

Audit all Oracle user viewing activity:

audit execute procedure by KGUPTA2 by access;

AUDIT INSERT, UPDATE ON LDBO.ACCOUNTS by access;

AUDIT ALL ON LDBO.ACCOUNTS_SEQUENCE;

Setting Default Auditing Options: Example The following statement specifies default auditing options for objects created in the future:

AUDIT ALTER, GRANT, INSERT, UPDATE, DELETE ON DEFAULT;

Any objects created later are automatically audited with the specified options that apply to them, if auditing has been enabled:

If you create a table, then Oracle Database automatically audits any ALTER, GRANT, INSERT, UPDATE, or DELETE statements issued against the table.

If you create a view, then Oracle Database automatically audits any GRANT, INSERT, UPDATE, or DELETE statements issued against the view.

If you create a sequence, then Oracle Database automatically audits any ALTER or GRANT statements issued against the sequence.

If you create a procedure, package, or function, then Oracle Database automatically audits any ALTER or GRANT statements issued against it.

SEQUENCE--- ALTER,AUDIT,GRANT,SELECT

TABLE OR VIEW -- ALTER,AUDIT,COMMENT,DELETE,GRANT,INDEX,INSERT,LOCK,RENAME,SELECT,UPDATE

audit update table, delete table,insert table by FRED by access;

---------------------------

audit all on ldbo.tbllocktable;

noaudit select on ldbo.tbllocktable;

select obj_name, sessionid, username, ses_actions, timestamp from dba_audit_trail where obj_name='TBLLOCKTABLE';

you'll get a result like (columns have been shortened for readability):

OBJ_NAME SESSIONID USERNAME SES_ACTIONS TIMESTAMP

-------- ---------- -------- ------------------- ------------------

TBLLOCKTABLE 23242623 LDBO -S-------------- 10-JUL-10

TBLLOCKTABLE 23122413 UIPL6724 ---------S------ 10-JUL-10

TBLLOCKTABLE 23092613 USSB0256 ---------S------ 10-JUL-10

TBLLOCKTABLE 23242311 LDBO ---------S------ 10-JUL-10

TBLLOCKTABLE 23092651 UIPL6722 ---------S------ 10-JUL-10

TBLLOCKTABLE 23242678 LDBO -S-------------- 10-JUL-10

The TIMESTAMP column indicates the time of the first audited action within the session. The SES_ACTIONS column is a session summary—a string of 16 characters, one for each action type in the order ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, REFERENCES, EXECUTE, READ. (Positions 15, and 16 are reserved for future use). The characters are: - for none, S for success, F for failure, and B for both.

select obj_name, sessionid, username, ses_actions, timestamp from dba_audit_trail where obj_name='TBLLOCKTABLE';

-S--------------

---------S------

---------S------

---------S------

---------S------

-S--------------

----------S-----

----------S-----