|Oracle® Database SQL Language Reference
12c Release 1 (12.1)
|PDF · Mobi · ePub|
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
If you create a role that is
IDENTIFIED or is
password, then Oracle Database grants you the role with
OPTION. However, if you create a role
GLOBALLY, then the database does not grant you the role. A global role cannot be granted to a user or role directly. Global roles can be granted only through enterprise roles.
GRANT for information on granting roles
ALTER USER for information on enabling roles
SET ROLE for information on enabling and disabling roles for the current session
Oracle Database Security Guide for general information about roles
Oracle Database Enterprise User Security Administrator's Guide for details on enterprise roles
You must have the
ROLE system privilege.
To specify the
CONTAINER clause, you must be connected to a multitenant container database (CDB). To specify
ALL, the current container must be the root.
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 length of the role name is 30 bytes. The maximum number of user-defined roles that can be enabled for a single user at one time is 148. If you are creating a common role, then the name of the role must begin with
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
IDENTIFIED to indicate that this role is authorized by the database and that no password is required to enable the role.
IDENTIFIED clause to indicate that a user must be authorized by the specified method before the role is enabled with the
BY password The
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
package clause lets you create a secure 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.
See Also:Oracle Database Security Guide for information on creating a secure application role
Depending on the operating system, the user may have to specify a password to the operating system before the role is enabled.
If you omit both the
IDENTIFIED clause and the
IDENTIFIED clause, then the role defaults to
If the current container is a pluggable database (PDB), then specify
CURRENT to create a local role in that PDB. If the current container is the root, then you can specify
ALL to create a common role. The name of the common role must begin with
c##. If the current container is a PDB and you omit this clause, then
CURRENT is the default. If you omit this clause and the current container is the root, then
ALL is the default.
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
The following statement creates global role
CREATE ROLE warehouse_user IDENTIFIED GLOBALLY;
The following statement creates the same role as an external role:
CREATE ROLE warehouse_user IDENTIFIED EXTERNALLY;
The following statement creates local role
role1 in the current PDB. The current container must be a PDB when you issue this statement:
CREATE ROLE role1 CONTAINER = CURRENT;
The following statement creates common role
c##role1. The current container must be the root when you issue this statement:
CREATE ROLE c##role1 CONTAINER = ALL;