Creating a User-Defined Role

Create a new user-defined role.

Prerequisites

Requires the MANAGE ROLES system privilege.

Task

A user-defined role cannot have a login password. When creating a user-defined role, you can appoint administrators for the role, and indicate whether they are also to be members of the role. If you do not specify any administrators, the global role administrator (any user granted the MANAGE ROLES system privilege) becomes the default administrator of the role.

However, if at least one role administrator is specified during conversion, global role administrators will be unable to manage the role because the SYS_MANAGE_ROLES_ROLE system privilege is not automatically granted to the role with administrative rights. For this reason, it is strongly recommended that you either do not define any role administrators when creating a role (add them after creation), or explicitly grant the SYS_MANAGE_ROLES_ROLE system privilege with administrative rights only along with any role administrators during the creation process.

Role administrators can be added and removed after creation. When creating a role, if the new role name already exists, the statement fails.

To create a new user-defined role, execute one of these statements:

Create Condition	Statement
Global role administrator only; no role administrators	CREATE ROLE `role_name`
Role administrators with no role membership; no global role administrator	CREATE ROLE `role_name` WITH ADMIN ONLY `admin_name [,...]`
Role administrators with role membership; no global role administrator^*	CREATE ROLE `role_name` WITH ADMIN `admin_name [,...]`
Role administrators with no role membership; with global role administrator^*	CREATE ROLE `role_name` WITH ADMIN ONLY SYS_MANAGE_ROLES_ROLE, `admin_name [,...]`

^*Since global role administrators cannot be granted membership in a role, you cannot include SYS_MANAGE_ROLES_ROLE in the administrators list when creating a role with role administrators granted membership in the role (WITH ADMIN option). It can, however, be included when creating a role with role administrators not granted membership in the role (WITH ADMIN ONLY option).

Example:

This statement creates the role Sales with no role administrators specified. Any user with the MANAGE ROLES system privilege is a default administrator of this role.

 CREATE ROLE Sales

This statement creates the role Marketing with Jane and Bob acting as role administrators, but are not granted membership in the role. It also allows global role administrators to manage the role.

 CREATE ROLE Marketing WITH ADMIN ONLY SYS_MANAGE_ROLES_ROLE, jane, bob