Accessing Audit Trails for Database Events

Event monitors can be created in TA to watch for certain changes to the tables and indexes in the Oracle database. However, TA cannot monitor the changes that occur within the Oracle database unless audit trails can be accessed in the database. Oracle database events/auditing is implemented through the standard Oracle Audit Trail feature. Make sure that the database initialization parameter, AUDIT_TRAIL is set to ‘DB.’ Once this parameter is initialized, you must shut down and restart your database before the new setting can take effect. Make sure that the Oracle environment has access to the standard audit trail views.

Note: These views (except STMT_AUDIT_OPTION_MAP) are created by the CATALOG.SQL and CATAUDIT.SQL scripts.

Note: Consult with your Database Administrator before running the Grant queries.

View Description

Query Syntax

STMT_AUDIT_OPTION_MAP

Contains information about auditing option type codes. Created by the SQL.BSQ script at CREATE DATABASE time.

GRANT Privilege Type ON STMT_AUDIT_OPTION_MAP to username;

AUDIT_ACTIONS

Contains descriptions for audit trail action type codes.

GRANT Privilege Type ON AUDIT_ACTIONS to username;

ALL_DEF_AUDIT_OPTS

Contains default object-auditing options that will be applied when objects are created.

GRANT Privilege Type ON ALL_DEF_AUDIT_OPTS to username;

DBA_STMT_AUDIT_OPTS

Describes current system auditing options across the system and by user.

GRANT Privilege Type ON DBA_STMT_AUDIT_OPTS to username;

DBA_PRIV_AUDIT_OPTS

Describes current system privileges being audited across the system and by user.

GRANT Privilege Type ON DBA_PRIV_AUDIT_OPTS to username;

DBA_OBJ_AUDIT_OPTS USER_OBJ_AUDIT_OPTS

Describes auditing options on all objects. The USER view describes auditing options on all objects owned by the current user.

GRANT Privilege Type ON DBA_OBJ_AUDIT_OPTS to username;

GRANT Privilege Type ON USER_OBJ_AUDIT_OPTS to username;

DBA_AUDIT_TRAIL USER_AUDIT_TRAIL

Lists all audit trail entries. The USER view shows audit trail entries relating to current user.

GRANT Privilege Type ON DBA_AUDIT_TRAIL to username;

GRANT Privilege Type ON USER_AUDIT_TRAIL to username;

DBA_AUDIT_OBJECT USER_AUDIT_OBJECT

Contains audit trail records for all objects in the system. The USER view lists audit trail records for statements concerning objects that are accessible to the current user.

GRANT Privilege Type ON DBA_AUDIT_OBJECT to username;

GRANT Privilege Type ON USER_AUDIT_OBJECT to username;

DBA_AUDIT_SESSION USER_AUDIT_SESSION

Lists all audit trail records concerning CONNECT and DISCONNECT. USER view lists all audit trail records concerning connections and disconnections for the current user.

GRANT Privilege Type ON DBA_AUDIT_SESSION to username;

GRANT Privilege Type ON USER_AUDIT_SESSION to username;

DBA_AUDIT_STATEMENT USER_AUDIT_STATEMENT

Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database, or for the USER view, issued by the user.

GRANT Privilege Type ON DBA_AUDIT_STATEMENT to username;

GRANT Privilege Type ON USER_AUDIT_STATEMENT to username;

DBA_AUDIT_EXISTS

Lists audit trail entries produced BY AUDIT NOT EXISTS.

GRANT Privilege Type ON DBA_AUDIT_EXISTS to username;

DBA_AUDIT_POLICIES

Shows all the auditing policies on the system.

GRANT Privilege Type ON DBA_AUDIT_POLICIES to username;

DBA_FGA_AUDIT_TRAIL

Lists audit trail records for value-based auditing.

GRANT Privilege Type ON DBA_FGA_AUDIT_TRAIL to username;

To verify if a user already has the above privileges, run this query:

select GRANTEE, TABLE_NAME, PRIVILEGE FROM SYS.DBA_TAB_PRIVS WHERE (GRANTEE='username' AND PRIVILEGE ='SELECT' );