Security Roles   «Prev  Next»
Lesson 5Default roles
Objective Grant default and non-default roles.

Grant Default (non-default roles)

Roles are a powerful mechanism for implementing security schemes. A user can have one or more directly assigned roles, plus others assigned as default roles. A default role is assigned to a user whenever he or she logs on to an Oracle database. A default role must have already been granted to a user before it can be a default role.

Syntax

You can assign a default role to a user by using either the CREATE USER command or the ALTER USER command. Use the keywords DEFAULT ROLE and then the name of the role or roles that you want to assign as defaults.
For instance, to assign the roles BIDDER and BROWSER to user BID1, who already exists, you would use the command: 
ALTER USER BID1 DEFALT ROLE BIDDER, BROWSER;

There are roles that are predefined for your Oracle database, briefly described in this System Defined Roles.

Additional Keywords

You can assign roles individually as default roles. If a user has been granted several roles, you can use the keyword ALL to make all the available roles into default roles. To make all the roles granted to user BID1 above into default roles, use the following syntax: 

ALTER USER BID1 DEFAULT ROLE ALL;

ALTER USER BID1 DEFAULT ROLE 
ALL EXCEPT ADMINISTRATOR;
If you want to assign most of the roles available to a user as default roles, you can use the ALL keyword along with the EXCEPT keyword to exclude specific roles. To make all the roles granted to user BID1 into default roles, except for the ADMINISTRATOR role, use the following command. Click the View Code button.

Changing Roles at runtime

You can also assign roles for a user at runtime. To do this, use the SQL command SET ROLE, followed by the role name or names.
The SET ROLE command can use the ALL and ALL EXCEPT keywords. Using the SET ROLE command automatically disables all roles that are not mentioned in the command. All roles for the user would be disabled except the role of BIDDER if user BID1 issued the following SQL command: 
SET ROLE BIDDER;

The next lesson shows how to protect a role with a password.