HomeOracle Page 2 - Roles and Auditing for Oracle Database XE
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).
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
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.
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.
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.
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
Enable auditing. Send audit records to an operating system file.
Enable auditing. Send audit records to theSYS.AUD$table.
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.