You can specify role administrators at role creation time, or after the role has been created.

In this example, the role myRole is created, and MaryM and TomS are granted the role with administrative rights. After creation, only MaryM and TomS will be able to grant or revoke the role.

CREATE ROLE myRole WITH ADMIN MaryM, TomS;

In this example, the role myRole is created, a privilege is added to it, and then the user ID PeriW is added as an administrator (only).

CREATE USER PeriW IDENTIFIED BY PeriW;
CREATE ROLE myRole;
GRANT SELECT ANY TABLE to myRole;
GRANT ROLE myRole to PeriW WITH ADMIN ONLY OPTION;

When you create a role but do not specify an administrator during the creation, the MANAGE ROLES system privilege is automatically granted administrative rights (only) to the new role. This way, any user with the MANAGE ROLES system privilege can administer the role.

MANAGE ROLES is unique in this way—it is the only system privilege that can have roles granted to it (WITH ADMIN ONLY OPTION) and revoked from it.

If no administrator is specified at role creation time—causing it to be automatically granted to MANAGE ROLES—and then administrators are specified for the roles later, then those administrators, plus anyone with the MANAGE ROLES system privilege, can administer the role.

If you create a role and specify administrators during the creation, only those administrators can administer the role—global administrators cannot administer the role unless MANAGE ROLES was one of the administrators you specified. To create a role and include MANAGE ROLES as one of the administrators, you execute a statement similar to the following. When specifying the MANAGE ROLES system privilege, you must use its internal representation, SYS_MANAGE_ROLES_ROLE. Also, you can only grant a role to the MANAGE ROLES system privilege, you can only grant with administrative rights only (WITH ADMIN ONLY); otherwise, an error is returned.

CREATE ROLE myRole WITH ADMIN ONLY MaryM, TomS, SYS_MANAGE_ROLES_ROLE;

MaryM, TomS, and any user with the MANAGE ROLES system privilege can administer the role. However, MaryM and TomS will not be able to exercise the role.

You can also grant administration rights to the MANAGE ROLES system privilege after a role is created, by executing a statement similar to the following. Again, you must remember to specify WITH ADMIN ONLY OPTION:

GRANT ROLE myRole TO SYS_MANAGE_ROLES_ROLE WITH ADMIN ONLY OPTION;

If you created a role and you want to remove the global administrator's ability to administer, you can revoke MANAGE ROLES from it by executing a statement similar to the following:

REVOKE ROLE myRole FROM SYS_MANAGE_ROLES_ROLE;

If you have a role that already has an administrator assigned to it and you want to change it to another user ID, grant the role to the new administrator (specifying WITH ADMIN or WITH ADMIN ONLY), and then revoke administrator privileges from the first administrator.

The min_role_admins database option controls the minimum number of administrators a role should have. If you try to revoke administration rights from a user, the statement can fail if doing so would mean there are fewer administrators than the value set by the min_role_admins option. However, this is not a problem as long as you have other users who have global role administration rights (MANAGE ROLE system privilege), as these global role administrators can administer roles for which no administrators have been specified.

When you create a role, set the administrators as part of the CREATE ROLE statement. If you do so, consider granting the MANAGE ROLES system privilege to the administrators.

For example, the following statement creates the role myRole and sets SallyG and IrisM as administrators:

CREATE ROLE myRole WITH ADMIN SallyG, IrisM, SYS_MANAGE_ROLES_ROLE;

• CREATE ROLE statement
• GRANT statement
• min_role_admins option