Oracle Roles

Oracle Roles can be defined as a set or group of privileges that can be assigned or granted to any user or to any other Oracle Role.


Oracle Roles Creation Syntax

In order to create a role in oracle plsql we must have CREATE ROLE system privilege.

Oracle Roles Creation Syntax is:

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

In the above Oracle Role create syntax:

role_name is the name of the Oracle Roles which we are creating. This name will be used for referring the role or set of privileges.

NOT IDENTIFIED phrase means that the role will be immediately enabled and no password will be required for enabling the role.

IDENTIFIED phrase means that a user must be authorized before enabling the role.

By PASSWORD phrase means that the user must provide the password for enabling the role

USING PACKAGE phrase means that we are creating an application role and the role can be enabled only by an application using an authorized package.

EXTERNALLY phrase means that the user trying to enable the role must be authorized by an external service for enabling the role. External service can be OS level or 3rd party service.

GLOBALLY phrase means that the user must be authorized by enterprise level directory service for enabling the role.

NOT INDENTIFIED and INDENTIFIED are optional fields, if omitted, the role created is defaulted to NOT INDENTIFIED role.


Oracle Roles: Creating default Oracle Role Example

Let’s create an Oracle Role by using the CREATE Role command

CREATE ROLE role_test;

The above Oracle CREATE ROLE statement will create a ROLE named ‘role_test’.


Oracle Roles: Creating IDENTIFIED BY Oracle Role Example

Let’s create an Oracle Role having a password using IDENTIFIED BY

CREATE ROLE role_test
IDENTIFIED BY roletest1234;

The above CREATE ROLE statement will create a role named ‘role_test’ which is password protected by ‘roletest1234’ password.

Hope above Oracle Roles information was helpful to you.

Please share the same with others also.


Tagged , , , , , , . Bookmark the permalink.