Oracle Roles.

Preamble

Oracle Role is a set or group of privileges that can be granted to users or other roles. It is a great way for database administrators to save time and effort.

Create Oracle Role

The role is created to logically group permissions for users. Note that in order to create a role, you must have privileges to create the system role.

Syntax for creating a role in Oracle/PLSQL:

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

• role_name – The name of the new role that you create. This is how you will refer to privilege grouping.
• NOT IDENTIFIED – This means that the role is immediately enabled. No password is required to enable the role.
• IDENTIFIED – Means that the user must be authorized before the role will be enabled.
• BY password – This means that the user must enter the password to enable the role.
• USING package – Indicates that you are creating an application role – A role that is enabled only for applications that use an authorized package.
• EXTERNALLY – This means that the user must be authorized by an external service to enable the role. The external service can be an operating system or a third party service.
• GLOBALLY – Indicates that the user must be authorized by the enterprise directory service to enable the role.

Note:
If both NOT IDENTIFIED and IDENTIFIED are omitted from the CREATE ROLE sentence, the role will be created as NOT IDENTIFIED.

Let’s consider an example of how to create a role in Oracle/PLSQL.

CREATE ROLE test_role;

This is the first example of creating a role called test_role.

CREATE ROLE test_role
IDENTIFIED BY test345;

The second example creates the same role called test_role, but now it is protected by the password test345.

Grant TABLE Privileges to Role

Once you have created a role in Oracle, your next step is to provide privileges for that role.

In the same way that you grant privileges to users, you can grant privileges to the role. Let us start by giving preference to the role in the table. The preference table can be any combination of: SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX or ALL.

Syntax to provide table privileges for the role in Oracle/PLSQL:

GRANT privileges ON object TO role_name;
privileges

Privileges for role assignment. This can be any of the following values:

• object – The name of the database object to which you grant privileges. If you are granting privileges to a table, that would be the name of the table.

role_name

The title of the role to which these privileges will be granted.

Let’s consider some examples of how to grant table privileges to a role in Oracle/PLSQL.

For example, if you want to grant SELECT, INSERT, UPDATE and DELETE privileges to the supplier table for the test_role name role, then follow the GRANT sentence below:

GRANT select, insert, update, delete ON TO test_role;

You can also use the ALL keyword to show that everyone must be given permissions.

GRANT all ON TO test_role;

Revoke Table Privileges from Role

Once you have granted privileges to the role table, you may have to cancel all or some of these privileges. To do this, you can run the revoke command. You can override any combination of: SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER INDEX, or ALL.

Syntax to override table privileges for a role in Oracle/PLSQL:

REVOKE privileges ON object FROM role_name;
privileges

Cancellation privilege for the role. This can be any of the following values:

• Object – The name of the database object for which you cancel the privileges. If you cancel the privileges to a table, that would be the name of the table.
• role_name – The name of the role for which you are going to cancel these privileges.

Consider an example of how to override table privileges for a role in Oracle.

For example, if you want to override the DELETE table preference on a supplier table from a role named test_role, you would follow the following REVOKE sentence:

REVOKE delete ON FROM test_role suppliers;

If you want to override all privileges to the suppliers table from the test_role role, you can use the ALL keyword.

For example:

REVOKE all ON FROM test_role;

Grant Function/Procedure Privileges to Role

When working with functions and procedures, you can provide roles to perform these functions and procedures.

Syntax to provide EXECUTE privileges to functions / procedures for the role in Oracle/PLSQL:

GRANT EXECUTE ON object TO role_name;

• EXECUTE – Ability to compile a function / procedure and the ability to perform the function / procedure directly.
• Object – The name of the database object for which you grant privileges. In case you grant privileges
  EXECUTE per function or procedure, that would be the name of the function or procedure.
• role_name – The name of the role that will be granted EXECUTE privileges.

Let’s look at an example of how to grant an EXECUTE privilege to a function or procedure in a role in Oracle.

For example, if you have a function named Find_Value and you want to grant access to a role named test_role, then follow the GRANT sentence below:

GRANT execute ON Find_Value TO test_role;

Revoke Function/Procedure Privileges from Role

Once we have granted EXECUTE privileges to a function or procedure on a role, we may have to override those privileges for that role. To do this, we can run the REVOKE command.

Syntax to override a function or procedure preference from a role in Oracle:

REVOKE execute ON object FROM role_name;

• EXECUTE – Canceling the ability to compile a function/procedure and the ability to perform the function/procedure itself.
• Object – The name of the database object you are revoking the privileges for. If you cancel the EXECUTE privilege of a function or procedure, this would be the name of the function or procedure.
• role_name – The name of the role for which you are revoking the EXECUTE privilege.

Consider an example of how to override EXECUTE privileges for a function or procedure on a role in Oracle.

If you want to override the EXECUTE preference for a function called Find_Value from the test_role role, follow the following REVOKE sentence:

REVOKE execute ON Find_Value FROM test_role;

Grant Role to User

Now that you have created the role and assigned permissions for the role, you will have to grant the role to certain users.

Syntax to provide the role to the user in Oracle:

GRANT role_name TO user_name;

• role_name – The name of the role that you want to grant.
• user_name – The name of the user to be given the role.

Let’s consider an example of how to give a role to a user in Oracle:

GRANT test_role TO jon;

In this example, we will provide a role called test_role for a user named jon.

Enable/Disable Role (Set Role Statement)

To enable or disable a specific role for the current session, you can use the SET ROLE operator.

When a user logs on to Oracle, all roles are enabled by default, but roles not by default must be enabled using the SET ROLE statement.

Syntax for SET ROLE operator in Oracle:

SET ROLE
( role_name [ IDENTIFIED BY password ] | ALL [EXCEPT role1, role2,... ] | NONE );

• role_name – The name of the role you want to enable.
• IDENTIFIED BY password – The password for the role to enable it. If a role has no password, this parameter can be omitted.
• ALL – This means that all roles must be enabled for this current session, except those that are listed in EXCEPT.
• NONE – Disables all roles for the current session (including all default roles).

Let us consider an example of how to enable a certain role in Oracle.

For example:

SET ROLE test_role IDENTIFIED BY test234;

This example will include a role called test_role with password test234.

Set role as DEFAULT Role

The default role means that the role is always enabled for the current session at login. You do not need to use the SET ROLE operator to set the default role, you must use the USER ALTER operator.

Syntax to install the role as DEFAULT ROLE in Oracle:

ALTER USER user_name
DEFAULT ROLE
( role_name | ALL [EXCEPT role1, role2,... ] | NONE );

SQL tutorial: How to create Roles in Oracle Database

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.