Home arrow Oracle arrow 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).

  1. Roles and Auditing for Oracle Database XE
  2. Using Database Auditing
By: Apress Publishing
Rating: starstarstarstarstar / 1
February 03, 2011

print this article



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


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.

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.

>>> More Oracle Articles          >>> More By Apress Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: