Auditing Your Database

Once you have "built your fortress", you need to patrol the perimeter for intruders. While it might be more fun to stroll around the data center with a machine gun for a few hours each day, we can achieve our goal through the Oracle database audit feature.

Before you can audit database activity, you must first set the AUDIT_TRAIL system parameter, which is normally set to FALSE. Audit information can be written to the SYS.AUD$ table in the database, or to files outside the database. You need to decide which method you will use.

• AUDIT_TRAIL=DB saves audit data in the SYS.AUD$ table
• AUDIT_TRAIL=DB,EXTENDED saves SQLText and SQLBind data as CLOB columns. Only available in 10g.
• AUDIT_TRAIL=OS saves the data to a text file specified by the AUDIT_FILE_DEST parameter
• AUDIT_TRAIL=XML saves the audit records to an XML format file.
• AUDIT_TRAIL=XML,EXTENDED includes the SQLText and SQLBind values in the XML file. Only available in 10g.

Where you decide to keep the information may depend on who will view it. If you have a security office who will check the data, or the data is subject to government regulation such as HIPAA or Sarbanes Oxley, saving the data to an OS or XML format file that can be reviewed by the authorities and archived for posterity may be the best choice. If you are going to review the audit data, store it in the database.

Wherever you decide to keep the audit data, make sure that there is sufficient space, and that the data is secure. In other words, audit the audit records.

Once AUDIT_TRAIL has been set, and the database restarted, you can begin defining audit statements. At a minimum, you should audit the following:

• Changes to SYS.AUD$
• Failed login attempts
• Creation of new users
• Changes to profiles
• Grants of DBA privileges
• Changes to audits

Set AUDIT_SYS_OPERATIONS=TRUE to enable the auditing of operations performed by the user SYS, and users connecting with SYSDBA or SYSOPER privileges. The audit records will be written to the location identified by the AUDIT_TRAIL parameter.

Use the following script to turn on some basic auditing features
View script

Once you start collecting data, you need to inspect it. The following script is a simple query for viewing some of the data in DBA_AUDIT_TRAIL view.
View script

Certain activities, such as the adding or dropping of data files, are recorded in the alert log. Be sure to check the alert log for unusual activity.

Check the listener.log for failed login attempts.
TNS-01169: The listener has not recognized the password

Audit Tools

The Lumigent AuditDB tool provides a comprehensive tool for auditing database activity, including user activity and authorizations, role and privilege allocation, object accessibility, procedural source changes, RDBMS patch levels, and operating system file and parameter settings. I do not have direct experience with this tool, but it sounds like an invaluable tool for databases that are subject to Sarbanes-Oxley or HIPAA requlation.
Visit the Lumigent Web site

AppRadar from Application Security, Inc. is a real-time database monitoring and auditing tool designed for environments with requlatory compliance requirements.
Visit the Application Security, Inc. Web Site

Links to more information about auditing