Capture an Oracle 10g audit trail in XML format

by Bob Watkins | Mar 19, 2007 7:33:00 PM

Tags: Financial accounting, Databases, Bob Watkins, Oracle Application Server 10g, Oracle Database, Oracle Corp., audit, database, SYS.AUD$, operating system, XML, Oracle Tips Newsletter

Takeaway: Oracle 10g can be configured to write its audit logs to XML files in operating system directories. This tip shows the configuration changes that need to be made, as well as how to access the resulting XML files.

The Oracle database has long had the ability to audit selected types of operations against the database, storing the audit trail in a system table. This table, SYS.AUD$, resides in the data dictionary. On some operating systems, you can also write audit records to the operating system's own event logging subsystem.

Oracle 10g added a third option: The ability to write to files in an operating system directory. These files can be written in XML.

There are several benefits to using XML instead of SYS.AUD$ for auditing, including:

• XML can easily be processed by tools outside the database, filtered for events of interest, combined with audit logs from other systems, and formatted for HTML display.
• Operating system files can provide stronger security than the SYS.AUD$ table, especially if it's desirable to protect the audit trail from viewing or modification even by the DBA.
• Audit logs stored outside the database continue to be available even when the database instance is down, but they can still be queried from within the database via a new view, V$XML_AUDIT_TRAIL.

To activate auditing to XML files, two initialization parameters need to be set. First, identify the directory to which the audit files will be written by setting:

audit_file_dest='directorypath'

Note that this is not a directory object such as the Data Pump utilities use, but the actual pathname of the operating system directory.

Second, set the audit_trail parameter:

audit_trail=xml

Finally, restart the Oracle instance. The audit_trail parameter is static and requires that Oracle be restarted for it to take effect. However, you can change the audit_file_dest parameter dynamically via the ALTER SYSTEM command if the DEFERRED parameter is used:

ALTER SYSTEM SET audit_file_dest='directorypath' DEFERRED

Existing sessions will keep logging to the original location, but new sessions will log to the new destination.

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.

Print/View all Posts Comments on this article

Documentation josir | 03/22/07

Oracle Database Security Guide 10.2 bwatkins | 03/22/07