Lesson 6	Password protected roles
Objective	Create password-protected roles.

Password Protected Roles

Creating password-protected roles in Oracle involves a multi-step process. Here's a step-by-step guide to help you accomplish this:

Creating the Role: The first step in the process is to create the role. You can use the CREATE ROLE statement for this, specifying the role name and the IDENTIFIED BY clause followed by the password:
```
CREATE ROLE role_name IDENTIFIED BY password;
```
Replace role_name with the name you want to give the role, and password with the password you want to assign to the role. Remember to ensure the password adheres to Oracle's password complexity rules, if any have been configured.
Assigning Privileges to the Role: After you have created the role, you need to assign privileges to it. You can do this using the GRANT statement. For example, if you want to grant the SELECT privilege on a table named employees to the role, you can use the following command:
```
GRANT SELECT ON employees TO role_name;
```
Replace role_name with the name of the role you created in the first step. You can assign multiple privileges to a role, depending on what actions you want the role to be able to perform.
Assigning the Role to a User: The next step is to assign the role to a user. You can do this using the GRANT statement, similar to how you granted privileges to the role.
Here is an example:
```
GRANT role_name TO username;
```
Replace role_name with the name of the role and username with the name of the user to whom you want to assign the role. The user can now use the SET ROLE statement to activate the role:
```
SET ROLE role_name IDENTIFIED BY password;
```
Replace role_name with the name of the role and password with the password you assigned to the role. If the correct password is provided, the role will be activated and the user will have access to the privileges assigned to the role.

Remember, all of these steps require the user to have the necessary administrative privileges in Oracle. If you're not an administrator, you'll need to request these privileges from your database administrator.
Also, always keep in mind the principle of least privilege: only grant the minimum privileges necessary for a role or user to perform their intended tasks. This helps to maintain security and minimize potential damage in the event of a security breach.

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:

You can require a password for access, using the keyword BY and the password, as in the command:

CREATE ROLE BIDDER IDENTIFIED 
BY bidder;

Using this method assigns a single password to the role, and when a user attempts to assign the role using the

SET ROLE

command, they will have to supply a password.

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.
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:

SET ROLE ALL EXCEPT hr_admin;

The next lesson is about granting users the ability to administer roles.