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>';


==
By default sys.aud$ is in system tablespace, it make whole lot sense to move it to its own tablespace.

select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name='AUD$'
 
OWNER   SEGMENT_NAME  SEGMENT_TYPE       TABLESPACE_NAME                BYTES/1024/1024
------- ------------- ------------------ ------------------------------ ---------------
SYS     AUD$          TABLE              SYSTEM                                      16
 
Package dbmsa_audit_mgmt is provided to move the same.


BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'AUDIT_DATA');
END;
/
 
PL/SQL procedure successfully completed.
 
Comments