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.

In Oracle, roles are used to manage security privileges, and they can be granted to users in different ways. Default roles are automatically enabled for a user upon login, while non-default roles require manual enabling during a session. The following steps outline how to grant default and non-default roles in Oracle:

Granting Default Roles:

Connect to the Oracle Database as a user with the necessary administrative privileges, such as a DBA or a user with the ALTER USER system privilege.
Use the ALTER USER statement to grant the role as a default role. The syntax for the ALTER USER statement is:
```
ALTER USER username
DEFAULT ROLE role_name [, role_name2 ...];
```
Replace 'username' with the name of the target user, and 'role_name' with the name of the role to be granted as a default role. You can grant multiple default roles by separating role names with commas.
Example:
```
ALTER USER scott
DEFAULT ROLE hr_manager, sales_manager;
```
This command grants the 'hr_manager' and 'sales_manager' roles as default roles to the user 'scott'. Upon login, these roles will be automatically enabled for the user.

Granting Non-Default Roles:

Connect to the Oracle Database as a user with the necessary administrative privileges, such as a DBA or a user with the GRANT ANY ROLE system privilege.
Use the GRANT statement to grant the role to the target user. The syntax for the GRANT statement is:
```
GRANT role_name TO username;
```
Replace 'username' with the name of the target user, and 'role_name' with the name of the role to be granted as a non-default role.
Example:
```
GRANT inventory_manager TO scott;
```
This command grants the 'inventory_manager' role as a non-default role to the user 'scott'. The role will not be automatically enabled upon login and must be enabled manually using the SET ROLE statement during a session.

Enabling Non-Default Roles:

For users with non-default roles, the roles can be enabled during a session using the SET ROLE statement. The syntax for the SET ROLE statement is:

SET ROLE role_name [, role_name2 ...];

Replace 'role_name' with the name of the non-default role to be enabled. You can enable multiple roles by separating role names with commas.
Example:

SET ROLE inventory_manager;

This command enables the 'inventory_manager' role for the current user session, allowing the user to access the associated privileges.
In summary, to grant default and non-default roles in Oracle, use the ALTER USER statement for default roles and the GRANT statement for non-default roles. Default roles are automatically enabled upon login, while non-default roles must be enabled manually using the SET ROLE statement during a session.

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.