Oracle Audit Parameters

Commands to enable auditing :


SQL> SHOW PARAMETER AUDIT
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
SQL> ALTER SYSTEM SET audit_trail=db,extended scope=spfile;
SQL> ALTER SYSTEM SET audit_sys_operations=true scope=spfile;
SQL> shut immediate
SQL> startup
-----------------------------------------------------------------------
Command to enable auditing for a schema :


sql>AUDIT ALL BY student BY ACCESS;
sql>AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY student BY ACCESS;
sql>AUDIT EXECUTE PROCEDURE BY student BY ACCESS;
-----------------------------------------------------------------------
Command to check audit trail :


SELECT username, obj_name, sql_text FROM dba_audit_trail
SELECT username,
       extended_timestamp,
       owner,
       obj_name,terminal,
       action_name,SQL_TEXT
FROM   dba_audit_trail
ORDER BY timestamp;
----------------------------------------------------------------------
Commands to remove audting :


DELETE FROM sys.aud$;
DELETE FROM sys.aud$ WHERE obj$name='';
SELECT  FROM sys.aud$