Auditing in Oracle 10g.
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended } none : Means auditing disabled 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. |
Database Technologies > Oracle >