Home > Oracle > Oracle Auditing

Oracle Auditing

Query 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 = ‘AUDIT_TEST’
ORDER BY timestamp;

Query XML format OS Audit

COLUMN db_user       FORMAT A10
COLUMN object_schema FORMAT A10
COLUMN object_name   FORMAT A10
COLUMN extended_timestamp FORMAT A35

SELECT db_user,
extended_timestamp,
object_schema,
object_name,
action
FROM   v$xml_audit_trail
WHERE  object_schema = ‘AUDIT_TEST’
ORDER BY extended_timestamp;

Privilege level

Audit records can be found in DBA_PRIV_AUDIT_OPTS.
SQL> select * from DBA_PRIV_AUDIT_OPTS;

Object Level
Audit records can be found in DBA_OBJ_AUDIT_OPTS.
SQL> select * from DBA_OBJ_AUDIT_OPTS;
 

Maintenance

The audit trail must be deleted/archived on a regular basis to prevent the SYS.AUD$ table growing to an unacceptable size.

To delete all audit records from the audit trail:
SQL> DELETE FROM sys.aud$;
 

 

 

 

 

Advertisements
Categories: Oracle
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: