Security Roles   «Prev  Next»

Lesson 6Password protected roles
ObjectiveCreate password-protected roles.

Password Protected Roles

The primary method for authenticating users is the logon/password pair. No user can access anything in an Oracle database without first logging on. Occasionally you may want to add an additional level of security to a particular role.

Authorizing roles

You can add authorization to a role by adding the keyword IDENTIFIED to the basic CREATE ROLE statement. There are three ways to do this:
  1. You can require a password for access, using the keyword BY and the password, as in the command:

BY bidder;

Using this method assigns a single password to the role, and when a user attempts to assign the role using the
command, they will have to supply a password.
  1. The operating system can authorize use of the role, if you use the EXTERNAL keyword. To do this, you must also create roles whose names match the roles defined in the operating system, and you can only enable roles that are associated with the user by the operating system.
  2. You can use the Oracle Security Service to authorize use of the role, by using the GLOBALLY keyword.

Enabling Roles

Roles can be enabled or disabled selectively in each database session. If you have two concurrent sessions, the roles in effect for each session can be different. Use the SET ROLE role_list statement to enable one or more roles. role_list is a comma delimited list of roles to enable. This list can include the keyword ALL, which enables all the roles granted to the user. You can optionally append a list of roles to exclude from the ALL list by specifying ALL EXCEPT exclusion_list. If a role has a password associated with it, the keywords IDENTIFIED BY password must immediately follow the role name in the role_list.

For example, to enable the password-protected role HR_ADMIN, together with the unprotected role EMPLOYEE, execute the following:
SET ROLE hr_admin IDENTIFIED BY "zinjarthrapus", employee;

To enable all roles except HR_ADMIN, run this:
The next lesson is about granting users the ability to administer roles.