CREATE ROLE Statement

Quick Links:

Syntax

CREATE [ OR REPLACE ] ROLE { role_name | FOR USER userID }
   [ WITH ADMIN [ ONLY ] admin_name [...,], [ SYS_MANAGE_ROLES_ROLE ]

Parameters

(back to top)

role_name – unless you are using the OR REPLACE clause, role_name cannot already exist in the database.

OR REPLACE – role_name must already exist in the database. If role_name does not already exist, a new user-defined role is created. All current administrators are replaced by those specified in the admin_name [..] clause as follows:
- All existing role administrators granted the WITH ADMIN OPTION not included on the new role administrators list become members of the role with no administrative rights on the role.
- All existing role administrators granted the WITH ADMIN ONLY OPTION not included on the new role administrators list are removed as members of the role.
When using the OR REPLACE clause, if an existing role administrator is included on the new role administrators list he or she retains his or her original administrative rights if they are higher than the replacement rights. For example, User A is an existing role administrator originally granted WITH ADMIN rights on the role. New role administrators are granted WITH ADMIN ONLY rights. If User A is included on this list, User A retains the higher WITH ADMIN rights.

FOR USER – when using the FOR USER clause without the OR REPLACE, userID must be the name of an existing user that currently does not have the ability to act as a role.

admin_name – list of users to be designated administrators of the role.

WITH ADMIN – each admin_name specified is granted administrative privileges over the role in addition to all underlying system privileges. WITH ADMIN clause is not valid when SYS_MANAGE_ROLES_ROLE is included on the list.

WITH ADMIN ONLY – each admin_name specified is granted administrative privileges only over the role, not the underlying system privileges.

SYS_MANAGE_ROLES_ROLE – allows global role administrators to administer the role. Can be specified in conjunction with the WITH ADMIN ONLY clause.

Examples

(back to top)

Example 1 – creates the role Sales. Only global role administrator can administer the role.
```
CREATE ROLE Sales
```
Example 2 – extends the existing user Jane to act as a role.
```
CREATE OR REPLACE ROLE FOR USER Jane
```
Example 3 – creates the role Finance with Mary and Jeff as role administrators with administrative rights to the role. Global role administrators cannot administer this role.
```
CREATE ROLE Finance 
WITH ADMIN Mary, Jeff
```
Example 3 – creates the role Marketing with Mary and Jeff as role administrators. Global role administrators can also manage this role.
```
CREATE ROLE Finance 
WITH ADMIN ONLY Mary, Jeff, SYS_MANAGE_ROLES_ROLE
```
Example 4 – Finance is an existing role with Harry and Susan as role administrators with administrative rights. You want to keep Susan as an administrator, replace Harry, and add the global role administrator. The new role administrators will have administrative rights only.
This statement keeps Susan as an administrator, but Susan retains administrative rights to the role since the original administrative rights granted were higher. Harry is replaced by Bob and Sarah, with administrative rights only, and the global role administrator is added to the role. Harry remains a member of the role, but has no administrative rights.
```
CREATE OR REPLACE ROLE Finance 
WITH ADMIN ONLY Susan, Bob, Sarah, SYS_MANAGE_ROLE_ROLE
```

Usage

(back to top)

If you specify role administrators (admin_name), but do not include the global role administrator (SYS_MANAGE_ROLES_ROLE), global role administrators will be unable to manage the new role. Therefore, it is recommended that you not specify role administrators during the creation process. Use the OR REPLACE clause to add them afterwards.

If you do not specify an ADMIN clause, the default WITH ADMIN ONLY clause is used and the default administrator is the global roles administrator (SYS_MANAGE_ROLES_ROLE).

When replacing role administrators, if the role has a global role administrator, it must be included on the new role administrators list or it is removed from the role.

However, when using the WITH ADMIN clause to grant role administrators, since the clause is not valid for global role administrators, you must use the GRANT ROLE statement to re-add the global role administrator (SYS_MANAGE_RILES_ROLE) to the role. Failure to perform this grant means global role administrators are unable to manage the role.

Standards

(back to top)

ANSI SQL–Compliance level: Transact-SQL extension.

Permissions

(back to top)

Create a new role – Requires the MANAGE ROLES system privilege.
OR REPLACE clause – Requires the MANAGE ROLES system privilege along with administrative rights over the role being replaced.