CREATE ROLE statement

CREATE [ OR REPLACE ] ROLE { role-name  | FOR USER userid }
[ WITH ADMIN [ ONLY ] administrator-userid  [,...] ]

role-name Use this variable to assign a name to the new role. This name must be unique across all users and roles in the database.
OR REPLACE clause Use this clause to create the role if it does not already exist or replace its administrators if it does exist.
FOR USER userid clause Use this clause to convert the specified user into a user-extended role that can be assigned to others. The user must not already be extended as another role.
WITH ADMIN and WITH ADMIN ONLY administrator-userid clause Optionally specify administrators for the role. WITH ADMIN means that administrator-userid can exercise the role and administer it. WITH ADMIN ONLY means that administrator-userid can only administer the role. If no clause is specified, then any user with the MANAGE ROLES system privilege can administer the role.
The min_role_admins database option controls the minimum number of administrators required for each role. If you do not specify enough administrators when creating the role, the statement returns an error.

The name of the new role must not begin and end with 'SYS_' and '_ROLE', respectively. For example SYS_MyBackup_ROLE is not an acceptable name for a user-defined role, whereas MyBackup_ROLE and SYS_MyBackup are acceptable.

If an ADMIN clause is specified, only the specified users can administer the roles. If no ADMIN clause is specified, then by default the role is granted to the MANAGE ROLES system privilege, with administrative rights only. This means that global administrators can administer the role.

To create a user-extended role (that is, to extend a user to be a role), use the CREATE ROLE FOR USER userid syntax.

Use the GRANT statements to grant system privileges to the role.

You must have the MANAGE ROLES system privilege to create a new role.

If the OR REPLACE clause is specified and the role already exists, you must also have administrative rights over the role.

None.

SQL/2008 Vendor extension.

The following statement creates the Sales role. Any user with the MANAGE ROLES system privilege can administer the role.

CREATE ROLE Sales;

The following statement extends user JaneSmith to become a role that can be assigned to others.

CREATE ROLE FOR USER JaneSmith;

The following statement creates the role Finance with MaryJones and JeffTurkott as role administrators with administrative rights (only) for the role.

CREATE ROLE Finance 
WITH ADMIN ONLY MaryJones, JeffTurkott;

The following example replaces the existing Finance role created in the previous example, replacing MaryJones and JeffTurkott with EllenChong and DaveLexx as role administrators, this time with exercise rights to the role.

CREATE OR REPLACE ROLE Finance
WITH ADMIN EllenChong, DaveLexx;

CREATE ROLE statement

Syntax

Parameters

Remarks

Privileges

Side effects

Standards and compatibility

Example

See also