Oracle Database Administration: The Essential Refe

Password-enabled roles

Roles can have associated passwords, which are established using the IDENTIFIED BY clause in the CREATE ROLE or ALTER ROLE commands. When a role has an associated password, the user must provide the password at the time the role is enabled with the SET ROLE command. The two most common scenarios for this are as follows:

• A role is needed to allow an application to have the object privileges necessary to function properly. The role is granted as a non-default role to the user. The application has the password available to it and provides the password independently of the user. This ensures that the user is not able to use ad hoc query and update tools like SQL*Plus to access the objects directly outside the application environment.

• The user occasionally requires elevated privileges, but does not normally want to run with the privileges enabled. In this case, the role can be established as a non-default role with a password, requiring the user to explicitly enable the role and provide a password through the SET ROLE command. A user does not have to know the password for any default roles, unless the role needs to be reenabled after a SET ROLE command.