Oracle/PLSQL: Roles

A role is a set or group of privileges that can be granted to users or another role. This is a great way for database administrators to save time and effort.

Creating a Role

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

The syntax for creating a role is:

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

Note: If both the NOT IDENTIFIED and IDENTIFIED phrases are omitted in the CREATE ROLE statement, the role will be created as a NOT IDENTIFIED role.

The role_name phrase is the name of the new role that you are creating. This is how you will refer to the grouping of privileges.

The NOT IDENTIFIED phrase means that the role is immediately enabled. No password is required to enable the role.

The IDENTIFIED phrase means that a user must be authorized by a specified method before the role is enabled.

The BY password phrase means that a user must supply a password to enable the role.

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

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

The GLOBALLY phrase means that a user must be authorized by the enterprise directory service to enable the role.

For example:

CREATE ROLE test_role;

This first example creates a role called test_role.

CREATE ROLE test_role IDENTIFIED BY test123;

This second example creates the same role called test_role, but now it is password protected with the password of test123.

Grant Privileges (on Tables) to Roles

You can grant roles various privileges to tables. These privileges can be any combination of select, insert, update, delete, references, alter, and index. Below is an explanation of what each privilege means.

Privilege	Description
Select	Ability to query the table with a select statement.
Insert	Ability to add new rows to the table with the insert statement.
Update	Ability to update rows in the table with the update statement.
Delete	Ability to delete rows from the table with the delete statement.
References	Ability to create a constraint that refers to the table.
Alter	Ability to change the table definition with the alter table statement.
Index	Ability to create an index on the table with the create index statement.

The syntax for granting privileges on a table is:

grant privileges on object to role_name

For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a role named test_role, you would execute the following statement:

grant select, insert, update, delete on suppliers to test_role;

You can also use the all keyword to indicate that you wish all permissions to be granted. For example:

grant all on suppliers to test_role;

Revoke Privileges (on Tables) to Roles

Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.

The syntax for revoking privileges on a table is:

revoke privileges on object from role_name;

For example, if you wanted to revoke delete privileges on a table called suppliers from a role named test_role, you would execute the following statement:

revoke delete on suppliers from test_role;

If you wanted to revoke all privileges on a table, you could use the all keyword. For example:

revoke all on suppliers from test_role;

Grant Privileges (on Functions/Procedures) to Roles

When dealing with functions and procedures, you can grant roles the ability to execute these functions and procedures. The Execute privilege is explained below:

Privilege	Description
Execute	Ability to compile the function/procedure. Ability to execute the function/procedure directly.

The syntax for granting execute privileges on a function/procedure is:

grant execute on object to role_name;

For example, if you had a function called Find_Value and you wanted to grant execute access to the role named test_role, you would execute the following statement:

grant execute on Find_Value to test_role;

Revoke Privileges (on Functions/Procedures) to Roles

Once you have granted execute privileges on a function or procedure, you may need to revoke these privileges from a role. To do this, you can execute a revoke command.

The syntax for the revoking privileges on a function or procedure is:

revoke execute on object from role_name;

If you wanted to revoke execute privileges on a function called Find_Value from a role named test_role, you would execute the following statement:

revoke execute on Find_Value from test_role;