8.10 How do I…Delete audit information?Problem
I have had database auditing enabled for some time now, and the data audit data takes up significant space. Also, some of the audit information is no longer necessary. How do I delete audit information?
Technique
To disable a particular auditing event, but still enable auditing to continue for other auditing actions, use the NOAUDIT command. NOAUDIT is essentially the opposite of AUDIT. It turns off all object, statement, and system privilege auditing based on the auditing options selected. The syntax of the NOAUDIT command appears in Figure 8.35. To disable auditing altogether from the database, modify the initialization file, and change the line with AUDIT_TRAIL from DB or OS to NONE. If you want to remove the auditing data dictionary views, run the $ORACLE_HOME/RDBMS/ADMIN/CATNOAUD.SQL script. To delete the audit data, you must either TRUNCATE the AUD$ data dictionary table or delete the unnecessary data from the table.
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. Also, if you have not created audit trail entries from How-To 8.9, do so now.
2. Remove the auditing for people connecting to the database as the WAITE user account by running CHP8_35.SQL, shown in Figure 8.36.
3. Remove the auditing from the SENSITIVE_DATA table by running CHP8_36.SQL, shown in Figure 8.37.
4. To delete auditing data within the database, use the TRUNCATE command on the SYS.AUD$ data dictionary table. This is the only data dictionary table that you should modify. Load CHP8_37.SQL into the SQL buffer and run it.
SQL> GET CHP8_37.sql
1 TRUNCATE TABLE SYS.AUD$
SQL> /
Table truncated.
SQL>
5. To stop auditing altogether from the database, you must modify your initialization file and change the parameter AUDIT_TRAIL to NONE.
How It Works
Step 1 creates all objects, audit options, and audit trail entries. Step 2 removes auditing from users connecting as the WAITE user account. Step 3 removes auditing of SELECT, INSERT, UPDATE, and DELETE for the SENSITIVE_DATA table. Step 4 shows how to delete auditing data from the audit trail. Step 5 shows how to stop auditing altogether for the entire database.
Comments
To prevent the data in the audit trail for growing without bounds and taking up disk space, you can either delete data, turn off auditing options, or turn off auditing altogether. By default, Oracle8 audits every startup, shutdown, and connections as DBA user accounts into operating system files. The files are located by default in $ORACLE_HOME/RDBMS/AUDIT in UNIX, and \ORANT\DATABASE in Windows NT.