CREATE ROLE statement

Creates or replaces a role, creates a user-extended role, or modifies administrators for a role.

Syntax

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

Parameters

OR REPLACE clause
Use this clause to create the role if it does not already exist or replace its administrators if it does exist.
role-name
Use this parameter to specify the name of the role. This name must be unique across all users and roles in the database.
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.

Remarks

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.

Privileges

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.

Side effects

None.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

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;