Usage of roles makes the managing and controlling of privileges an easy to handle process. Roles are named group of privileges that you can assign to users or other roles. Roles are the easiest and quickest way for granting permission to users.
- Every role within a database has to be unique with exclusive user names as well as role names.
- Roles are not like schema objects as they are not contained in any schema. This means that when a user who has created a role is dropped, this has no effect on the role.
Roles are often created by Database administrators for a given database application. In order for the application to run, you need to grant all necessary privileges to a secure application role. Furthermore you can even grant this secure application role to users or other roles. It is possible for an application to have several different roles, each of which in turn is granted different set of privileges. These set of privileges regulate the data access while the application is in usage.
In order to prevent unauthorized usage of the privileges granted to a role, the DBA can secure it with a password at the time of the creation of that role. Usually an application is designed in such a way that it enables a proper role whenever started. Hence as a result there is no need for the application user to provide a password for an application role.
Advantages of Roles in Oracle Database
- 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 that are assigned the particular 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.
Roles can be used for granting permissions to users in a swift and easy way. Although one way is to use roles defined by the Oracle Database yet you can create your own roles with privileges pertaining to your requirements in order to get more control and continuity.
CREATE ROLE name_role NOT IDENTIFIED| IDENTIFIED BY password | USING [schema.] package | EXTERNALLY| GLOBALLY;
Create and Role both are the oracle reserved keyword followed by the name of a role. Oracle recommends that the name of the role contain at least one single-byte character regardless of whether the database character set also contains multi-byte characters. The maximum number of user-defined roles that can be enabled for a single user at one time is 148
Then we have NOT IDENTIFIED clause. This is an option clause and indicates that this role is authorized by the database and that no password is required to enable the role.
In case you want to provide security to your role to prevent unauthorized use of the privileges granted to the role, we have Identified clause.
IDENTIFIED BY clause indicates that a role must be authorized by the specified method and helps the oracle engine in authenticating the role. In Oracle there are 4 ways of authenticating a role.
If you omit both the NOT IDENTIFIED clause and the IDENTIFIED clause, then the role defaults to NOT IDENTIFIED.
- By Password: Password clause lets you create a local role and indicates that the user must specify password to the database when enabling the role. Remember here: passwords are case sensitive. Passwords can consist of only single-byte characters from your database character set regardless of whether the character set also has multi-byte characters
- USING package: The USING package clause lets you create an application role, which is a role that can be enabled only by applications using an authorized package. If you do not specify schema, then the database assumes the package is in your own schema.
- Externally: This clause helps you in creating an external role. These external roles and their authentication are managed by external services such as operating system for enabling the role.
- Globally: Specify GLOBALLY to create a global role. A global user must be authorized to use the role by the enterprise directory service before the role is enabled at login.
Query 1: Create a NOT IDENTIFIED Role
Let’s create a very simple role by the name of Demo1.
CREATE ROLE demo1 NOT IDENTIFIED;
Query is pretty simple. First we have create role both of which are the Oracle reserved keywords followed by the name of the role which is Demo1 in our case, Name of the role is user defined thus you can give whatever name you want. Then at the end of the statement we have NOT IDENTIFIED clause, NOT IDENTIFIED clause indicates that this role is authorized by the database and no password is required to enable the role. This is an optional clause statement thus if you want you can skip it.
Query 2: Role with Identified By Password clause
CREATE ROLE Demo2 IDENTIFIED BY password123;
The first half of this query is very identical to the first query but in the second half we have Identified By clause. IDENTIFIED BY clause indicates that a role must be authorized by the specified method. In our case the specific method is password. Followed by Identified By clause we have our password.
Query 3: External Role
In this example we will create an external role. The authentication of external roles is handled by external services such as your Operating System.
CREATE USER Demo3 IDENTIFIED externally;
On executing query, oracle will create an external role.
Query 4: Global Role
In this example we will create a global role. The authentication of global roles is handled by enterprise directory service before the role is enabled at login
CREATE USER Demo3 IDENTIFIED externally;
Assign Privileges to the Roles
The way of assigning privileges to the roles is very similar to the way we assign privileges to a user. To assign privileges to a role a user must have GRANT ANY PRIVILEGE system privilege. You can grant Object privilege, system privilege as well as a role to a role.
There are few things which you should take care when granting roles. Such as:
- You cannot grant a role to itself
- A role cannot be granted circularly
- Though you can grant system privilege WITH ADMIN OPTION to a role but same is not true in case of Object Privileges means you cannot grant object privileges with GRANT OPTION to a role.
1. How to grant System Privilege to a role
For the demonstration I will grant Create table system privilege to the role Demo1 which we create above.
GRANT create table TO Demo1 WITH ADMIN OPTION;
2. How to grant Object Privilege to a Role.
For the demonstration I will grant SELECT object privilege on employees table of HR user to the role Demo1.
GRANT select ON hr. employees TO demo1;
3. How to Grant a Role to another Role
Above we have created two different roles by the name of Demo1 and Demo2. Where Role Demo1 has “Create table” system privilege and “Select” object privilege on employees table of HR user. For the demonstration I will grant the Role Demo1 to Role Demo2
Grant demo1 TO demo2;
Assign Role to the Database users.
Any user in the database could be granted a Role however the user with GRANT ANY ROLE only in turn can grant or revoke any role to and from any user and role provided it is not a GLOBAL role. Moreover a user when granted a role with ADMIN OPTION is also eligible to grant or revoke a role to and from any role or user.
To grant a role to any user or to any role we use GRANT DCL statement. Such as, say you want to grant role demo1 which we created above to user SCOTT. For that, the query will be.
GRANT demo1 TO scott;
If you want you can also grant a role WITH ADMIN OPTION also. Such as
GRANT demo1 TO scott WITH ADMIN OPTION;
Similarly you can grant any role to any roles except itself in the database.
So that’s all about Roles in Oracle Database. Hope it helped you in understanding the topic better. Please do share it on your social networking and help me reach more people. Thanks & have a great day!
SQL Script and Presentation used
You can DOWNLOAD SQL script and presentation used in the Video and in this article.
SQL Script used in Video and in this article
I have used copy cloud to share these resourses. You can also join copy cloud and get 15GB free cloud storage for lifetime. If you will use this referal link https://copy.com?r=j7eYO7
You will get 5GB extra free cloud storage means total 20GB free cloud storage.