Using Database Auditing - Oracle

In this ninth part of a ten-part article series on securing Oracle Database XE, you'll learn about default roles, password-enabled roles, and database auditing. This article is excerpted from chapter 31 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

TABLE OF CONTENTS:

By: Apress Publishing

Poor Best

Rating:

/ 1

February 03, 2011

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

Oracle provides a number of different auditing methods for you to monitor what kinds of privileges are being used as well as what objects are being accessed. Auditing does not prevent the use of privileges but it can provide useful information to uncover abuse or misuse of privileges.

In Table 31-11, we summarize the different types of auditing in an Oracle database.

Table 31-11. Auditing Types

Auditing Type	Description
Statement auditing	Audits SQL statements by the type of statement regardless of the specific schema objects being accessed. One or more users can also be specified in the database to be audited for a particular statement.
Privilege auditing	Audits system privileges, such as CREATE TABLEor ALTER INDEX. As with statement auditing, privilege auditing can specify one or more particular users as the target of the audit.
Schema object auditing	Audits specific statements operating on a specific schema object (e.g., UPDATEstatements on the DEPARTMENTStable). Schema object auditing always applies to all users in the database.
Fine-grained auditing	Audits table access and privileges based on the content of the objects being accessed. Uses the package DBMS_FGAto set up a policy on a particular table.

In the next few sections, we’ll review how a DBA can manage audits of both system and object privilege use. When the granularity is required, a DBA can use fine-grained auditing to monitor access to certain rows or columns of a table, not just whether the table was accessed.

Auditing Locations

Audit records can be sent to either the SYS.AUD$ database table or an operating system file. To enable auditing and specify the location where the database saves audit records, set the initialization parameter AUDIT_TRAIL to one of the four values in Table 31-12.

**Table 31-12.** *Auditing Options*
Parameter Value	Action
NONE,FALSE	Disable auditing.
OS	Enable auditing. Send audit records to an operating system file.
DB,TRUE	Enable auditing. Send audit records to theSYS.AUD$table.
DB_EXTENDED	Enable auditing. Send audit records to theSYS.AUD$table, and record additional information in theCLOBcolumnsSQLBINDandSQLTEXT.

The parameterAUDIT_TRAILis not dynamic; you must shut down and restart the database for a change in theAUDIT_TRAILparameter to take effect. When sending audit information to theSYS.AUD$table, the size of the table should be carefully monitored so as not to impact the space requirements for other objects in theSYStablespace. It is recommended that the rows inSYS.AUD$be periodically archived and the table truncated. Oracle provides the roleDELETE_CATALOG_ROLEto use with a special account in a batch job to archive and truncate the audit table.

Please check back next week for the conclusion to this article series.