Roles and Auditing for Oracle Database XE

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.