Database Technologies‎ > ‎Oracle‎ > ‎

Auditing

Auditing in Oracle 10g.


We could audit which is user is accessing which objects of the database by enabling audit on the database.


We have 6 Modes in which we could enable Auditing, this are done by setting AUDIT_TRAIL init parameter

AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }

none                  :   Means auditing disabled
os                       :   Logs are maintained in OS Audit feature
XML                   :   Audit logs are stored as XML file in the OS.
XML,extended   :   Similar to XML but SQL_BIND,SQL_TEXT are also logged.
db/true               :   Audit in logged in SQL Table SYS.AUD$

 
Now to Audit the access by specific user.
 
SQL>AUDIT ALL BY <USERNAME> BY ACCESS;
SQL>AUDIT SELECT TABLE,UPDATE TABLE,INSERT TABLE,DELETE TABLE BY <USERNAME> BY ACCESS;
 
 
To check the acces log we use the DBA_AUDIT_TRAIL view
COLUMN username FORMAT A10
COLUMN owner    FORMAT A10
COLUMN obj_name FORMAT A10
COLUMN extended_timestamp FORMAT A35
SELECT username,
       extended_timestamp,
       owner,
       obj_name,
       action_name
FROM   dba_audit_trail
WHERE  owner = '<USERNAME>'
ORDER BY timestamp;
 
 

 
To check the users for whom auditing is enabled and the options for which
auditing is enabled we use the DBA_STMT_AUDIT_OPTS view
 
SQL>SELECT * FROM DBA_STMT_AUDIT_OPTS WHERE USER_NAME='<USERNAME>';
 
Comments