Analyzing the Audit Trail

Now you have captured audit data while the complete transaction profile was run against the database. If your AUDIT_TRAIL initialization parameter is set to any of the following values, you can query the view DBA_COMMON_AUDIT_TRAIL for audit data that will help you identify parameters for your candidate DBV policy:

DB

DB, EXTENDED

XML

XML, EXTENDED

Many queries will use join information from the DBA_COMMON_AUDIT_TRAIL view, the DBA_OBJECTS view, and the AUDIT_ACTIONS table. To simplify the complexity of the queries presented in this section, we’ve taken the liberty of including a view named AOS_COMMON_AUDIT_TRAIL that already has the required joins. The view definition is as follows:

sys@aos> CREATE OR REPLACE VIEW aos_common_audit_trail AS
SELECT
   aud.audit_type
 , aud.session_id
 , aud.proxy_sessionid
 , aud.statementid
 , aud.entryid
 , aud.extended_timestamp
 , aud.global_uid
 , aud.db_user
 , aud.client_id
 , aud.econtext_id
 , aud.ext_name
 , aud.os_user
 , aud.userhost
 , aud.os_process
 , aud.terminal
 , aud.instance_number
 , aud.object_schema
 , aud.object_name
 , aud.policy_name
 , aud.new_owner
 , aud.new_name
 , aud.action
 , aud.statement_type
 , aud.audit_option
 , aud.transactionid
 , aud.returncode
 , aud.scn
 , aud.comment_text
 , aud.sql_bind
 , aud.sql_text
 , aud.obj_privilege
 , aud.sys_privilege
 , aud.admin_option
 , aud.os_privilege
 , aud.grantee
 , aud.priv_used
 , aud.ses_actions
 , aud.logoff_time
 , aud.logoff_lread
 , aud.logoff_pread
 , aud.logoff_lwrite
 , aud.logoff_dlock
 , aud.session_cpu
 , aud.obj_edition_name
 , act.name action_name
 , obj.subobject_name
 , obj.object_id
 , obj.data_object_id
 , obj.object_type
 , obj.created
 , obj.last_ddl_time
 , obj.timestamp
 , obj.status
 , obj.temporary
 , obj.generated
 , obj.secondary
 , obj.namespace
FROM dba_common_audit_trail aud
 , audit_actions act
 , dba_objects obj
WHERE aud.action = act.action (+)
AND aud.object_name = obj.object_name (+)
AND aud.object_schema = obj.owner (+)
/
View created.