CREATE ROLE

Purpose

Use the CREATE ROLE statement to create a role, which is a set of privileges that can be granted to users or to other roles. You can use roles to administer database privileges. You can add privileges to a role and then grant the role to a user. The user can then enable the role and exercise the privileges granted by the role.

A role contains all privileges granted to the role and all privileges of other roles granted to it. A new role is initially empty. You add privileges to a role with the GRANT statement.

If you create a role that is NOT IDENTIFIED or is IDENTIFIED EXTERNALLY or BY password, then Oracle Database grants you the role with ADMIN OPTION. However, if you create a role IDENTIFIED GLOBALLY, then the database does not grant you the role.

See Also:

GRANT for information on granting roles
ALTER USER for information on enabling roles
ALTER ROLE and DROP ROLE for information on modifying or removing a role from the database
SET ROLE for information on enabling and disabling roles for the current session
Oracle Database Security Guide for general information about roles and Oracle Database Heterogeneous Connectivity Administrator's Guide for a detailed description and explanation of using global roles

Prerequisites

You must have the CREATE ROLE system privilege.

Syntax

create_role::=

Description of the illustration create_role.gif

Semantics

role

Specify the name of the role to be created. Oracle recommends that the role contain at least one single-byte character regardless of whether the database character set also contains multibyte characters. The maximum number of user-defined roles that can be enabled for a single user at one time is 148.

Some roles are defined by SQL scripts provided on your distribution media.

See Also:

GRANT for a list of these predefined roles and SET ROLE for information on enabling and disabling roles for a user

NOT IDENTIFIED Clause

Specify NOT IDENTIFIED to indicate that this role is authorized by the database and that no password is required to enable the role.

IDENTIFIED Cl au se

Use the IDENTIFIED clause to indicate that a user must be authorized by the specified method before the role is enabled with the SET ROLE statement.

BY password The BY password clause lets you create a local role and indicates that the user must specify the password to the database when enabling the role. The password can contain only single-byte characters from your database character set regardless of whether this character set also contains multibyte 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.

Caution:

When you grant a role to a user, the role is granted as a default role for that user and is therefore enabled immediately upon logon. To retain the security benefits of an application role, you must ensure that the role is not a default role. Immediately after granting the application role to a user, issue an ALTER USER statement with the DEFAULT ROLE ALL EXCEPT role clause, specifying the application role. Doing so will enforce the rule that, in subsequent logons by the user, the role will not be enabled except by applications using the authorized package.

See Also:

Oracle Database Security Guide for information on creating a secure application role

EXTERNALLY Specify EXTERNALLY to create an external role. An external user must be authorized by an external service, such as an operating system or third-party service, before enabling the role.

Depending on the operating system, the user may have to specify a password to the operating system before the role is enabled.

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.

If you omit both the NOT IDENTIFIED clause and the IDENTIFIED clause, then the role defaults to NOT IDENTIFIED.

Examples

Creating a Role: Example The following statement creates the role dw_manager:

CREATE ROLE dw_manager;

Users who are subsequently granted the dw_manager role will inherit all of the privileges that have been granted to this role.

You can add a layer of security to roles by specifying a password, as in the following example:

CREATE ROLE dw_manager
   IDENTIFIED BY warehouse;

Users who are subsequently granted the dw_manager role must specify the password warehouse to enable the role with the SET ROLE statement.

The following statement creates global role warehouse_user:

CREATE ROLE warehouse_user IDENTIFIED GLOBALLY;

The following statement creates the same role as an external role:

CREATE ROLE warehouse_user IDENTIFIED EXTERNALLY;