Oracle Database Administration: The Essential Refe

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 ...