Capture an Oracle 10g audit trail in XML format
Thursday, January 03, 2008 03:26 PM
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.
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.
Play video
There are currently no comments for this post.