Creating or Dropping a Role - Oracle

To create a role, you use theCREATE ROLEcommand, and you must have theCREATE ROLEsystem privilege. Typically, you grant this only to database administrators or application administrators. Here’s an example:

SQL> create role hr_admin;
Role created.

By default, no password or authentication is required to enable or use an assigned role. As with creating users, you can authorize use of a role by requiring a password for the role (creating the role with theIDENTIFIED BY PASSWORD clause), using the operating system’s authentication methods (creating the role with theIDENTIFIED EXTERNALLYclause), or using a network or directory service (creating the role with theIDENTIFIED GLOBALLYclause).

Granting Privileges to a Role

Assigning a privilege to a role is very straightforward; you use theGRANTcommand just as you would assign a privilege to a user:

SQL> grant select on hr.employees to hr_clerk;
Grant succeeded.
SQL> grant create trigger to hr_clerk;
Grant succeeded.

In this example, we’ve assigned an object privilege and a system privilege to theHR_CLERKrole.

Assigning or Revoking Roles

Once you have the desired system and object privileges assigned to the role, you can assign the role to a user, using familiar syntax:

SQL> grant hr_clerk to kellyc;
Grant succeeded.

Any other privileges granted to theHR_CLERKrole in the future will automatically be useable byKELLYCbecauseKELLYChas been granted the role.

Roles may be granted to other roles; this allows a DBA to have a hierarchy of roles, making role administration easier. For example, there could be roles namedDEPT30,DEPT50, andDEPT100, each having object privileges to tables owned by each of those departments. An employee in department 30 would be assigned theDEPT30role, and so forth. The president of the company would like to see tables in all departments; but rather than assigning individual object privileges to the roleALL_DEPTS, you can assign the individual department roles toALL_DEPTS:

SQL> create role all_depts;
Role created.
SQL> grant dept30, dept50, dept100 to all_depts;
Grant succeeded.
SQL> grant all_depts to kellyc;
Grant succeeded.

The roleALL_DEPTSmay also contain individual object and system privileges that do not apply to individual departments, such as object privileges on order entry tables or accounts receivable tables.

Revoking a role from a user is very similar to revoking privileges from a user:

SQL> revoke all_depts from kellyc;
Revoke succeeded.

The privileges revoked will no longer be available to the user the next time they connect to the database. However, it is worth noting that if another role contains privileges on the same objects as the dropped role, or privileges on the objects are granted directly, the user retains these privileges on the objects until these and all other grants are explicitly revoked.

Please check back next week for the ninth part of this article series.