|Lesson 5||Default 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.
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
BROWSER to user
BID1, who already exists, you would use the
ALTER USER BID1 DEFALT ROLE BIDDER, BROWSER;
There are roles that are predefined for your Oracle database, briefly described in this
System Defined Roles.
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.
SET ROLE command can use the
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.