RelationalDBDesign RelationalDBDesign



Auditing Features  «Prev  Next»
Lesson 5 Viewing the audit trail
Objective Generate audit trail reports.

Accessing and Viewing the Audit Trail in Oracle

Of course, collecting audit information is just the beginning. You will no doubt want to analyze the audit information you have collected.

AUDIT data dictionary views

As we mentioned earlier, audit records normally go into a table in the database called AUD$ in the SYS schema. This table is fairly complex, so Oracle provides a number of data dictionary views that make it easier for you to view audit records.
Some of these views are as follows:

View
Description
Corresponding view
Additional notes

USER_AUDIT_TRAIL
This view lists all audit records for the current user.
A corresponding view, DBA_AUDIT_TRAIL, lists all audit records for all users.
Some of the most relevant fields in this view are USERNAME, which is the name of the user who performed the audited action; TIMESTAMP, which is the time the action was performed; OWNER and OBJ_NAME, which identify the object the action was performed on; and ACTION_NAME, which is the name of the action performed on the object.
USER_AUDIT_SESSION
This view lists all the audit records for connecting and disconnecting from a database session.
A corresponding view, DBA_AUDIT_SESSION, lists all these statements for all users.
This view is frequently used when you are auditing to monitor security.
USER_AUDIT_STATEMENT
This view lists all audit records for the GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM commands.
A corresponding view, DBA_AUDIT_STATEMENT, lists all these statements for all users.
USER_AUDIT_OBJECT
This view lists all audit records for all statements that apply to database objects.
A corresponding view, DBA_AUDIT_OBJECT, lists all these statements for all users.
On some systems, these views are not automatically created by Oracle. For these systems, you will have to log on as user SYS and run the SQL script CATAUDIT.SQL.


Viewing audit records

Once you have collected a set of audit records, you can simply use standard SQL statements against the data dictionary views to see the results of your auditing.
In the next lesson, you will learn how to audit specific privileges.

Auditing Sql Statements - Quiz

Click the Quiz link to test your knowledge on auditing SQL statements.
Auditing SQL Statements - Quiz

Viewing Audit Records - Exercise

Click the Exercise link below to practice using auditing.
Viewing Audit Records - Exercise