Managing the Audit Trail

So you have decided to turn on auditing. Thousands of audited events later, you may begin to wonder at what point you should take a look at the audit trail table. First of all, remember that the audit trail table, SYS.AUD$, resides in the data dictionary, consequently stored in the SYSTEM tablespace. As you should know, the SYSTEM tablespace should be isolated from other tablespaces and protected at all times.

Normally, the SYSTEM tablespace is relatively small compared to application-related tablespaces in the database, typically somewhere between 50MB and 100MB. If auditing is turned on, you may want to think about adding a datafile to the SYSTEM tablespace to accommodate any additional space that may be required for auditing, depending on the current usage of the SYSTEM tablespace. However, in most cases, adding additional space for auditing should not be necessary if the process is properly managed.

---

NOTE:  
The SYS.AUD$ table cannot be moved from the SYSTEM tablespace. However, storage parameters can be modified to control the growth of this table in the SYSTEM tablespace. You might choose to set the NEXT extent to a low number, such as 10KB or 20KB, and specify a smaller number for MAXEXTENTS to provide the table with a growth cap. Eventually, when the table reaches the value for MAXEXTENTS and cannot extend further, an error will be returned and you will be forced to archive the table and purge its contents. This is a good safeguard to ensure that the SYSTEM tablespace is not affected by a growing audit trail.

---

How much space is required for auditing? There is no way to answer this question without knowing the level of auditing that will take place, the frequency of audited events, and the length of time audit records will be maintained (or the maximum size allowed for the audit trail table). Eventually, you will probably have to archive the contents of SYS.AUD$ to another table or file, and then purge the SYS.AUD$ table. This is one of the very few times the DBA should ever manually manipulate one of the tables in the data dictionary.

At some point, guidelines for auditing must be set, such as the following:

•  The specific events that will be audited.

•  The maximum amount of audit data allowed before archiving must occur.

•  The expected availability of archived audit information.

•  The users that will have the privilege of auditing.

---

NOTE:  
Auditing should be narrowed down as much as possible to avoid unnecessary database overhead.

---

Auditing the Audit Trail

You may even want to audit access against the audit trail itself. Doing so falls into the category of object auditing. For example:

SQL> audit select, insert, update, delete

on SYS.AUD$

by access;



Audit succeeded.

Purging the Audit Trail

The DBA can purge the audit trail table by deleting all records in the table or by truncating the table:

SQL> delete from sys.aud$;



43 rows deleted.

or

SQL> truncate table sys.aud$;



Table truncated.

Archiving the Audit Trail

Before purging data from the audit trail table, it is probably a good idea to archive the data first. It is usually better to be safe than sorry when dealing with any type of data.

The easiest way to archive the audit trail before purging it is to create a table based on the contents of SYS.AUD$, as in the following example. After a successful copy has been made, the original table can be purged:

SQL> create table audit_copy as

2 select * from sys.aud$;



Table created.



SQL> delete from sys.aud$;



45 rows deleted.

After a copy of SYS.AUD$ has been made, you can append data to the table from future clean-up routines by inserting the contents of SYS.AUD$ into your copy table:

SQL> insert into audit_copy

 2 select * from sys.aud$;



43 rows created.



SQL> delete from sys.aud$;



45 rows deleted.

There are several methods for backing up the audit trail before it is purged. One method to consider is the EXPORT utility. After an EXPORT dump file has been generated, it can be stored with other files in an archive directory at the operating system level. These files can also be compressed to conserve disk space. Dump files can easily be imported back into the database whenever the data is needed.