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.
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;
Assign Roles as as Default Roles
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.