Roles and Auditing for Oracle Database XE

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).

Default Roles

By default, all roles granted to a user are enabled when the user connects. If a role is going to be used only within the context of an application, the role can start out disabled when the user is logged in; then it can be enabled and disabled within the application. If the user KELLYC has CONNECT , RESOURCE , HR_CLERK , and DEPT30 roles, and you want to specify that HR_CLERK and DEPT30 are not enabled by default, you can use something like the following:

SQL> alter user kellyc default role all
2>     except hr_clerk, dept30;
User altered.

When KELLYC connects to the database, she automatically has all privileges granted with all roles except for HR_CLERK and DEPT30 . The user KELLYC may explicitly enable a role in her session by using SET ROLE :

SQL> set role dept30;
Role set.

When she is done accessing the tables for department 30, she can disable the privileges provided by the role in this session:

SQL> set role all except dept30;
Role set.

Password-Enabled Roles

To enhance security in the database, you can assign a password to a role. The password is assigned to the role when it’s created:

SQL> create role dept99 identified by is183le;
Role created.
SQL> grant dept99 to kellyc;
Grant succeeded.
SQL> alter user kellyc default role all except hr_clerk, dept30, dept99;
User altered.

When the user KELLYC is connected to the database, either the application she is using will provide or prompt for a password, or she can enter the password when she enables the role:

SQL> set role dept99 identified by is183le; Role set.

To prevent the user from extracting a role password from a PL/SQL application, you can either encrypt the PL/SQL procedure itself or store the role password in a database table that only the procedure has access to. As a result, the application user cannot retrieve the role password itself and must use the application to obtain authorization through the role.

{mospagebreak title=Using Database Auditing}

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 the SYS.AUD$ table.

Enable auditing. Send audit records to the SYS.AUD$ table, and record additional information in the CLOB columns SQLBIND and SQLTEXT .

The parameter AUDIT_TRAIL is not dynamic; you must shut down and restart the database for a change in the AUDIT_TRAIL parameter to take effect. When sending audit information to the SYS.AUD$ table, the size of the table should be carefully monitored so as not to impact the space requirements for other objects in the SYS tablespace. It is recommended that the rows in SYS.AUD$ be periodically archived and the table truncated. Oracle provides the role DELETE_CATALOG_ROLE to 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.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan