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.