Question: I have been
dba_audit_trail view for auditing,
What is the
dba_audit_trail view and what auditing
information is contained in this dba_audit_trail
Answer: The dba_audit_trail is a
view based on the aud$ table which decodes most of the
values inside the table to present it in a user
understandable manner. For instance, the column spare1 in
aud$ actually stores the Operating System User Name.
For a full treatment of dba_audit_trail, see the book
Oracle privacy security auditing.
os_username in this view gets its value from that column in
the aud$ table. Similarly, the column action# column holds
the user's action in a numeral form. For example, the number
108 indicates granting system privilege.
decodes the numerical representation for the actions and
presents the information in the column action_name.
Therefore, this view is actually more useful for analysis
than the aud$ table itself.
The following views are
based on the dba_audit_trail view.
- user_audit_trail: This view
is based on all the columns of the dba_audit_trail view,
but presents information on the current logged in user
only. Unlike other data dictionary view, there is no
equivalent all_audit_trail view, as it is not relevant
in this case.
- dba_audit_exists: This is an
audit trail of the entries where the operation failed
due to non-existing objects. This is actually filtered
from the view dba_audit_trail where the return code is a
value other than zero.
- dba_audit_session: This
records all database connect and disconnect activity. It
also records if the action failed, for example, failure
due to a security violation.
- dba_audit_statement: This
view contains information about users entering
statements that do not actually access the data inside
an object, e.g. alter system, grant, revoke on objects,
- dba_audit_object: Finally,
this view shows all the audit records that were based on
database object accesses: e.g. a user selected
from a table, or altered another table, etc.
Also see this
books on Oracle auditing:
Oracle Forensics: Oracle Security Best Practices