Lesson 38 - ROLEs

ROLE

A role is a set or group of privileges that can be granted to users or another role. 

Creating a Role

To create a role, you must have CREATE ROLE system privileges.

Syntax :

Role without password

CREATE ROLE rolename [ NOT IDENTIFIED ;

Role with password

CREATE ROLE rolename  IDENTIFIED  {BY password | USING [schema.] package | EXTERNALLY | GLOBALLY } ;

Where ,

 Rolenaame  -  Any user defined name that  will refer to the grouping of privileges or Role.

NOT IDENTIFIED    - No password is required to enable the role.

IDENTIFIED   -  a user must be authorized by a specified method before the role is enabled.

 BY password   -   password to enable the role.

 USING package  -  phrase means that you are creating an application role - a role that is enabled only by applications using an authorized package.

EXTERNALLY    -   user must be authorized by an external service to enable the role. An external service can be an operating system or third-party service.

GLOBALLY   -  user must be authorized by the enterprise directory service to enable the role.

The following are few privileges that can be granted to any roles,

Select   -  To query the table or view using a select statement.

Insert  -  To add new rows

Update  -  To update  existing rows in the table using the update statement.

Delete  - To delete rows from the table using the delete statement.

References   - To create a constraint that refers to the table.

Alter   -  To change the table definition using the alter table statement.

Index    - To create an index on the table using the create index statement

Execute   -  To execute the  function/procedure

Examples :

SQL> create role ReadonlyUsers;

Role created.

SQL> grant select on emp to ReadonlyUsers;

Grant succeeded.

SQL> grant ReadonlyUsers to Sam;

Grant succeeded.

SQL> revoke ReadonlyUsers from  Sam;

Revoke succeeded.

-         To remove the role from database

SQL> drop role ReadonlyUsers;

Role dropped.