Audit

BY SESSION

Specify BY SESSION if you want Oracle Database to write a single record for all SQL statements of the same type issued and operations of the same type executed on the same schema objects in the same session.

BY ACCESS

Specify BY ACCESS if you want Oracle Database to write one record for each audited statement and operation.

WHENEVER SUCCESSFUL/NOT SUCCESSFUL

Specify WHENEVER SUCCESSFUL to audit only SQL statements and operations that succeed.

Specify WHENEVER NOT SUCCESSFUL to audit only statements and operations that fail or result in errors.

If you omit this clause, then Oracle Database performs the audit regardless of success or failure.

FORMAT:

AUDIT <Operation> BY ACCESS/SESSION WHENEVER SUCCESSFUL/ NOT SUCCESSFUL

SOME EXAMPLE:

SQL>ALTER SYSTEM SET AUDIT_TRAIL=DB SCOPE=SPFILE;

SQL>STARTUP FORCE;

SQL>CREATE USER siam IDENTIFIED BY siam#1

DEFAULT TABLESPACE users

TEMPORARY TABLESPACE temp

QUOTA UNLIMITED ON users;

SQL>AUDIT SELECT TABLE,UPDATE TABLE,

INSERT TABLE,DELETE TABLE

BY siam BY ACCESS;

SQL> AUDIT INSERT,SELECT,UPDATE,DELETE ON siam.test BY SESSION;

SQL> AUDIT CREATE TABLE BY siam;

Or

SQL> AUDIT TABLE BY siam;

SQL>AUDIT ALTER ON siam.atest BY ACCESS;

SQL> CONN siam/siam#1

SQL> CREATE TABLE atest(id number);

SQL> INSERT INTO atest values(3);

SQL> UPDATE atest

SET id=2

WHERE id=3;

SQL>CONN SYS AS SYSDBA

SQL> COLUMN username FORMAT A10

SQL>COLUMN extended_timestamp FORMAT A35

SQL>COLUMN obj_name FORMAT A10

SQL>COLUMN extended_timestamp FORMAT A35

SQL>SELECT username,extended_timestamp,

obj_name,action_name

FROM dba_audit_trail

WHERE owner ='SIAM'

ORDER BY timestamp;

ANOTHER EXAMPLE OF TRACKIN LOGIN AND LOGOUT TIME:

SQL>CREATE USER mahi IDENTIFIED BY mahi#1

DEFAULT TABLESPACE users

TEMPORARY TABLESPACE temp

QUOTA UNLIMITED ON users;

SQL>GRANT CREATE SESSION,CREATE TABLE TO mahi;

SQL> AUDIT SESSION BY mahi BY SESSION;

SQL>CONN mahi/mahi#1

From sys

SQL> select username,action_name,

to_char(timestamp,'DDMMYYYY:HHMISS') timestamp

from dba_audit_session;

From mahi

SQL> DISCONNECT;

From sys execute the same command.

Note: If you have not clearly understand BY ACCESS and BY SESSION Do the above things again using BY ACCESS[SQL> AUDIT SESSION BY mahi BY ACCESS].

For Auditing All Record for a particular user:

SQL> AUDIT ALL BY obayda;

Canceling Audit:

For canceling Audit just use NOAUDIT.

Example:

SQL> NOAUDIT SESSION BY mahi;

Setting Default Auditing Options: Example

The following statement specifies default auditing options for objects created in the future:

SQL>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.