Roles

A role is a group of privileges that you can assign to a user or another role. The following points summarize the benefits of roles:

• Rather than assigning privileges one at a time directly to a user, you can create a role, assign privileges to that role, and then grant that role to multiple users and roles.

• When you add or delete a privilege from a role, all users and roles assigned that role automatically receive or lose that privilege.

• You can assign multiple roles to a user or role.

• You can assign a password to a role.

As you can see from these points, roles can help you manage multiple privileges assigned to multiple users.

Creating Roles

To create a role, you must have the CREATE ROLE system privilege. As you ll see in a later example, the store user also needs the ability to grant the CREATE USER system privilege with the ADMIN option. The following example connects as system , and grants the required privileges to store :

 CONNECT system/manager GRANT CREATE ROLE TO store; GRANT CREATE USER TO store WITH ADMIN OPTION; 

Table 9-8 shows the roles you ll create shortly.

You create a role using the CREATE ROLE statement. The following statements connect as store and create the three roles shown in Table 9-8:

 CONNECT store/store_password CREATE ROLE product_manager; CREATE ROLE hr_manager; CREATE ROLE overall_manager IDENTIFIED by manager_password; 

Table 9-8: Roles to be Created

Role Name	Has Permissions to
product_manager	Perform SELECT , INSERT , UPDATE , and DELETE operations on the product_types and products tables.
hr_manager	Perform SELECT , INSERT , UPDATE , and DELETE operations on the salary_grades and employees tables. Also, hr_manager is able to create users.
overall_manager	Perform SELECT , INSERT , UPDATE , and DELETE operations on all the tables shown in the previous roles; overall_manager will be granted the previous roles.

Notice overall_manager has a password of manager_password .

Granting Privileges to Roles

You grant privileges to a role using the GRANT statement. You can grant both system and object privileges to a role, as well as grant another role to a role. The following example grants the required privileges to the product_manager and hr_manager roles, and grants these two roles to overall_manager :

 GRANT SELECT, INSERT, UPDATE, DELETE  ON product_types TO product_manager; GRANT SELECT, INSERT, UPDATE, DELETE  ON products TO product_manager; GRANT SELECT, INSERT, UPDATE, DELETE  ON salary_grades TO hr_manager; GRANT SELECT, INSERT, UPDATE, DELETE  ON employees TO hr_manager; GRANT CREATE USER TO hr_manager; GRANT product_manager, hr_manager TO overall_manager; 

Granting Roles to a User

You grant a role to a user using GRANT . The following example grants the overall_manager role to steve :

 GRANT overall_manager TO steve; 

Checking Roles Granted to a User

You can check which roles have been granted to a user by querying user_role_privs . Table 9-9 defines the columns in user_role_privs .

Table 9-9: Some Columns in user_role_privs

Column	Type	Description
username	VARCHAR2(30)	Name of the user to whom the role has been granted.
granted_role	VARCHAR2(30)	Name of the role granted to the user.
admin_option	VARCHAR2(3)	Whether the user is able to grant the role to another user or role. Equal to YES or NO .
default_role	VARCHAR2(3)	Whether the role is enabled by default when the user connects to the database. Equal to YES or NO .
os_granted	VARCHAR2(3)	Whether the role was granted by the operating system.

The following example connects as steve and queries user_role_privs :

  CONNECT steve/button   SELECT *   FROM user_role_privs;  USERNAME GRANTED_ROLE ADM DEF OS_ ------------------ -------------------------- --- --- --- STEVE OVERALL_MANAGER YES YES NO 

A user who creates a role is also granted that role by default. The following example connects as store and queries user_role_privs :

  CONNECT store/store_password   SELECT *   FROM user_role_privs;  USERNAME GRANTED_ROLE ADM DEF OS_ ------------------ -------------------------- --- --- --- STORE CONNECT NO YES NO STORE HR_MANAGER YES YES NO STORE OVERALL_MANAGER YES YES NO STORE PRODUCT_MANAGER YES YES NO STORE RESOURCE NO YES NO 

Notice store has the roles CONNECT and RESOURCE in addition to the roles store created earlier.

Note  

CONNECT and RESOURCE are built-in roles that were granted to store when you ran the store_schema.sql script. As you ll see in the next section, the CONNECT and RESOURCE roles contain multiple privileges.

Checking System Privileges Granted to a Role

You can check which system privileges have been granted to a role by querying role_sys_privs . Table 9-10 defines the columns in role_sys_privs .

Table 9-10: Some Columns in role_sys_privs

Column	Type	Description
role	VARCHAR2(30)	Name of the role.
privilege	VARCHAR2(40)	System privilege granted to the role.
admin_option	VARCHAR2(3)	Whether the privilege was granted with the ADMIN option. Equal to YES or NO .

The following example retrieves the rows from role_sys_privs ( assuming you re still connected as store ):

  SELECT *   FROM role_sys_privs;  ROLE PRIVILEGE ADM ------------------------ --------------------------------- --- CONNECT ALTER SESSION NO CONNECT CREATE CLUSTER NO CONNECT CREATE DATABASE LINK NO CONNECT CREATE SEQUENCE NO CONNECT CREATE SESSION NO CONNECT CREATE SYNONYM NO CONNECT CREATE TABLE NO CONNECT CREATE VIEW NO HR_MANAGER CREATE USER NO RESOURCE CREATE CLUSTER NO RESOURCE CREATE INDEXTYPE NO RESOURCE CREATE OPERATOR NO RESOURCE CREATE PROCEDURE NO RESOURCE CREATE SEQUENCE NO RESOURCE CREATE TABLE NO RESOURCE CREATE TRIGGER NO RESOURCE CREATE TYPE NO 

Checking Object Privileges Granted to a Role

You can check which object privileges have been granted to a role by querying role_tab_privs . Table 9-11 defines the columns in role_tab_privs .

Table 9-11: Some Columns in role_tab_privs

Column	Type	Description
role	VARCHAR2(30)	User to whom the privilege was granted.
owner	VARCHAR2(30)	User who owns the object.
table_name	VARCHAR2(30)	Name of the object on which privilege was granted.
column_name	VARCHAR2(30)	Name of the column (if applicable ).
privilege	VARCHAR2(40)	Privilege on the object.
grantable	VARCHAR2(3)	Whether the privilege was granted with the GRANT option. Equal to YES or NO .

The following example queries role_tab_privs where role equals HR_MANAGER :

  SELECT *   FROM role_tab_privs   WHERE role='HR_MANAGER';  ROLE OWNER ------------------------------ ------------------------------ TABLE_NAME COLUMN_NAME ------------------------------ ------------------------------ PRIVILEGE GRA ---------------------------------------- --- HR_MANAGER STORE EMPLOYEES DELETE NO HR_MANAGER STORE EMPLOYEES INSERT NO HR_MANAGER STORE EMPLOYEES SELECT NO HR_MANAGER STORE EMPLOYEES UPDATE NO HR_MANAGER STORE SALARY_GRADES DELETE NO HR_MANAGER STORE SALARY_GRADES INSERT NO HR_MANAGER STORE SALARY_GRADES SELECT NO HR_MANAGER STORE SALARY_GRADES UPDATE NO 

Making Use of Privileges Granted to a Role

Once a user has been granted a privilege via a role, they can use that privilege to perform the specified task. For example, steve has the overall_manager role. overall_manager was granted the product_manager and hr_manager roles. product_manager was granted the SELECT object privilege on the products and product_types tables. Therefore, steve is able to retrieve rows from these tables as shown in the following example:

  CONNECT steve/button   SELECT p.name, pt.name   FROM store.products p, store.product_types pt   WHERE p.product_type_id = pt.product_type_id;  NAME NAME ------------------------------ ---------- Modern Science Book Chemistry Book Supernova Video Tank War Video Z Files Video 2412: The Return Video Space Force 9 DVD From Another Planet DVD Classical Music CD Pop 3 CD Creative Yell CD 

Default Roles

By default, when a role is granted to a user, that role is enabled for that user. This means when the user connects to the database, the role is automatically available to them. To enhance security, you can disable a role by default; when the user connects, they will have to enable the role themselves before they can use it. If the role has a password, the user must enter that password before the role is enabled. For example, the overall_manager role has a password of manager_ passsword , and overall_manager is granted to steve . In the example you ll see next, you ll disable overall_manager so that steve has to enable this role and enter the password before he can use it.

You can alter a role so that it is no longer a default role using the ALTER ROLE statement. The following example connects as system and alters steve so that overall_manager is no longer a default role:

 CONNECT system/manager ALTER USER steve DEFAULT ROLE ALL EXCEPT overall_manager; 

When you connect as steve , you need to enable overall_manager using SET ROLE :

 CONNECT steve/button SET ROLE overall_manager IDENTIFIED BY manager_password; 

Once you ve set the role, you can use the privileges granted to that role. You can set your role to none using the following statement:

 SET ROLE NONE; 

You can also set your role to everything except overall_manager using the following statement:

 SET ROLE ALL EXCEPT overall_manager; 

By assigning passwords to roles and setting roles to not be enabled by default for a user, you introduce an additional level of security.

Revoking a Role

You revoke a role using REVOKE . The following example connects as store and revokes the overall_manager role from steve :

 CONNECT store/store_password REVOKE overall_manager FROM steve; 

Revoking Privileges from a Role

You revoke a privilege from a role using REVOKE . The following example connects as store and revokes all privileges on the products and product_types tables from product_manager (assuming you re still connected as store ):

 REVOKE ALL ON products FROM product_manager; REVOKE ALL ON product_types FROM product_manager; 

Dropping a Role

You drop a role using DROP ROLE . The following example drops the overall_manager , product_manager , and hr_manager roles (assuming you re still connected as store ):

 DROP ROLE overall_manager; DROP ROLE product_manager; DROP ROLE hr_manager;