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