Home arrow Oracle arrow Roles and Auditing for Oracle Database XE

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

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

print this article



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 userKELLYChasCONNECT,RESOURCE,HR_CLERK, andDEPT30roles, and you want to specify thatHR_CLERKandDEPT30 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.

WhenKELLYCconnects to the database, she automatically has all privileges granted with all roles except forHR_CLERKandDEPT30. The userKELLYCmay explicitly enable a role in her session by usingSET 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 userKELLYCis 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.

>>> 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: