Establishing an Audit Policy
Auditing consumes system resources. The audit trail table contains a row for every audit item saved. Auditing also uses additional session slots. Therefore, we recommend that you keep the amount of auditing to a minimum, and that you turn on more detailed auditing only as required.
Depending upon your requirements, the audit trail information will have varying life spans. You may need to keep a record of every access to a table to satisfy legal requirements, or you may only want to know the distribution of access to the database by department over a month’s time. You may not care how often a table is queried, but you may need to keep track of which rows have been modified, who did it, when it was done, why, what column was changed, the old and new values, and which program was used. In other words, the old journalist mantra: who, what, where, when, why, and how?
It is our experience that the following policy works well:
Use AUDIT CONNECT to keep track of who is using the database, how often they access the database, and how much I/O is generated by that user. This information will be useful later on to augment any monitoring you are doing for capacity planning. It will also allow you to answer questions from management about who is using the database.
Use AUDIT DBA to record the use of any activity that requires DBA privileges. This will be invaluable to you at some point when you are trying to figure out which DBA dropped a user and when it was done.
Use row-level triggers to audit table activity only where there is a legal or financial requirement to maintain that information.
On a periodic basis, usually weekly or monthly, clean out the audit trail table. Summarize connection information into a different table, copy historical access information with a retention period to a separate table, and delete the rows from the audit trail table. The next section describes audit trail maintenance.
When there is a need to know more about access to specific tables, turn on auditing for that table. The two most common scenarios for this are: