8.9 How do I…View audit information?

Problem

I have enabled database auditing, as described in How-To 8.8. I would like to analyze the audit trail to get information about database activity. How do I view audit information?

Technique

Look in the AUD$ table, known as the audit trail, which contains all auditing information, or look at the files in the AUDIT_FILE_DEST directory. When your Oracle8 database is created, the CATAUDIT.SQL script, located in the $ORACLE_HOME/rdbms/admin directory, is run through the CATALOG.SQL script. These scripts create several data dictionary views that make analyzing the AUD$ table easier, such as DBA_AUDIT_TRAIL (all audits), DBA_AUDIT_SESSION (session audits), and DBA_AUDIT_OBJECT (object and statement audits). By querying DBA_OBJ_AUDIT_OPTS, you can determine the audit options for all objects. By querying DBA_PRIV_AUDIT_OPTS, you can determine the audit options for sessions and privileges.

Steps

1. Connect to SQL*Plus as the WAITE account. If you have not already created the SENSITIVE_DATA table and AUDIT commands from How-To 8.8, do so now.

2. Ensure that auditing is working. The SYS.AUD$ table contains every audit entry. Because nothing has been done to create an audit entry, there should be nothing in the table. Do a count on the table to be sure:

SQL> SELECT COUNT(*) FROM SYS.AUD$;

COUNT(*)

--------

0

SQL>

Generate the first audit record by connecting to SQL*Plus again as the WAITE user. Step 4 from How-To 8.8 enabled auditing for each time someone connects as the WAITE user account. Again, do a count on the SYS.AUD$ table:

SQL> SELECT COUNT(*) FROM SYS.AUD$;

COUNT(*)

--------

1

SQL>

3. Create an additional audit record by running CHP8_32.SQL as shown in Figure 8.34. This script will SELECT from the SENSITIVE_DATA table, adding an audit trail entry as a result from the previous How-To.

4. At this point, two records are in the audit trail. To see the records, you can select from the DBA_AUDIT_SESSION view for session audits, and the DBA_AUDIT_OBJECT view for object audits. Load CHP8_33.SQL into the SQL buffer, and run it.

SQL> START CHP8_33.sql

1 SELECT USERNAME, TERMINAL, ACTION_NAME,

2 TO_CHAR (LOGOFF_TIME, ‘DD-MON-YYYY HH:MI:SS’) LOGOFF_TIME

3 FROM SYS.DBA_AUDIT_SESSION

SQL>/

USERNAME TERMINAL ACTION_NAME LOGOFF_TIME
-------- -------- ----------- -----------
WAITE WAITE LOGON 10-JAN-1998 03:15:33

SQL>

This shows all the session auditing. Now, select from the DBA_AUDIT_OBJECT view. Load CHP8_34.SQL into the SQL buffer, and run it.

SQL> START CHP8_34.sql

1 SELECT USERNAME, TERMINAL,

2 ACTION_NAME, OBJ_NAME,

3 TO_CHAR (TIMESTAMP, ‘DD-MON-YYYY HH:MI:SS’) TIME

4 FROM SYS.DBA_AUDIT_OBJECT

SQL> /

USERNAME TERMINAL ACTION_NAME OBJ_NAME TIME
-------- -------- ----------- -------- ----
WAITE ARI KAPLAN SESSION REC SENSITIVE_DATA 19-SEP-1997 12:47:26

SQL>

How It Works

Step 1 repeats How-To 8.8, if necessary. Step 2 ensures that auditing is functioning by counting from the SYS.AUD$ table, adding an auditing record by reconnecting as WAITE, and by counting again from the SYS.AUD$ table. Step 3 creates another audit record by selecting from the SENSITIVE_DATA table. Step 4 shows the session audit data by selecting from the DBA_AUDIT_SESSION data dictionary view. It then shows the object audit data by selecting from the DBA_AUDIT_OBJECT data dictionary view.

Comments

This How-To explains how to view data that has been created in the audit trail. How-To 8.10 shows how to delete audit trail entries. To determine which object, statements, and privileges are being audited, you can query the DBA_OBJ_AUDIT_OPTS view, the ALL_DEF_AUDIT_OPTS view, or the DBA_PRIV_AUDIT_ OPTS view.