Replacing Existing Role Administrators on a Role

Replace current role administrators with new administrators.

Prerequisites

Requires administrative privilege over the role, or the MANAGE ROLES system privilege, if the role has a global role administrator.

Task

Replacing role administrators can involve changing the users and roles who can act as administrators and their level of administrative rights on the role. Depending on the extent of the replacement, there are two approaches that can be taken. Each approach handles the replacement task very differently, and have very different net effects on role and global administrators. The first approach allows you to selectively replace the administrators of an existing role. The second approach allows you to completely replace all existing role administrators with new role administrators. It is important to note that replacement of administrators using the second approach includes the global role administrator.

The first approach is a two step process. It involves adding new role administrators and removing existing administrators from the role. Since the minimum number of administrators requirement must be met at all times through the process, it is recommended that you add before you remove. With this approach, if the role has a global role administrator, it is retained unless it is explicitly removed.

The second approach is a one step process, but has a much broader impact. It involves defining a new list of role administrators. All current role administrators are overwritten with new role administrators. If any current role administrators are to continue in this capacity, you must include them in the list of replacement role administrators. The list replaces all existing administrators, with the following behavior:

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.
An existing role administrator included on the new role administrators list retains his or her original administrative rights if they are higher than the replacement rights. For example, if the new role administrators are granted WITH ADMIN ONLY rights, and UserA (an existing role administrator who was originally granted the role with WITH ADMIN rights) is included on the new list, UserA retains the higher WITH ADMIN rights.
If the role has a global role administrator, it is removed from the role unless it is explicitly included on the new role administrators list.
If the role has a global role administrator, and the new role administrators are granted WITH ADMIN rights, the global role administrator cannot be included in the list, since it cannot be granted WITH ADMIN rights. However, since it is not included on the list, it is removed from the role.

The replacement role command can be issued as long as the replacement administrative option is equal to or higher than the current level. To lower the administrative level, all role administrators must first be removed (revoked) from the role and then be regranted.

A minimum number of role or global role administrators (as defined by the MIN_ROLE_ADMINS database option) with a login password must exist for each role at all times. When replacing role administrators, if the number of replacement administrators would violate the minimum requirement, the replacement fails.

To replace role administrators, execute one of these statements:

Replacement Option	Statement
Replace select role administrators with administrative only rights; no role membership	GRANT ROLE `role_name` TO `admin_name [,...]` WITH ADMIN ONLY OPTION REVOKE ADMIN OPTION FOR ROLE `role_name` FROM `admin_name [,...]`
Replace select role administrators with administrative and role membership	GRANT ROLE `role_name` TO `admin_name [,...]` WITH ADMIN OPTION REVOKE ADMIN OPTION FOR ROLE `role_name` FROM `admin_name [,...]`
Replace all role administrators with administrative rights only; no role membership. Remove the global role administrator, if exists.	CREATE OR REPLACE ROLE `role_name` WITH ADMIN ONLY `admin_name [,...]`
Replace all role administrators with administrative rights and role membership. Remove the global role administrator, if exists.	CREATE OR REPLACE ROLE `role_name` WITH ADMIN `admin_name [,...]`
Replace all role administrators with administrative rights only, including the global role administrator.^*	CREATE OR REPLACE ROLE `role_name` WITH ADMIN ONLY SYS_MANAGE_ROLES_ROLE, `admin_name [,...]`
Replace all role administrators with full administrative rights. Restore the global role administrator to the role^*	CREATE OR REPLACE ROLE `role_name` WITH ADMIN `admin_name [,...]` GRANT ROLE `role_name` TO SYS_MANAGE_ROLES_ROLE WITH ADMIN ONLY OPTION

^*SYS_MANAGE_ROLES_ROLE can only be granted to a role using the WITH ADMIN ONLY option. Therefore, when the CREATE OR REPLACE statement includes the WITH ADMIN ONLY option, SYS_MANAGE_ROLES_ROLE can be included in the administrator list. When the CREATE OR REPLACE statement uses the WITH ADMIN option, a separate grant statement is required to grant SYS_MANAGE_ROLES_ROLE to the role using the WITH ADMIN ONLY option.

Examples:

Sales has Mary and Bob as role administrators with full administrative rights. Sales has a global role administrator.

Execute these statements to remove Bob as a role administrator and replace him with Sarah and Jeff, with the same administrative rights. Bob remains a member of Sales with no administrative rights.

GRANT ROLE sales TO Sarah, Jeff WITH ADMIN OPTION 
REVOKE ADMIN OPTION FOR ROLE Sales FROM Bob

Execute these statements to replace the existing role administrators (Mary and Bob) with Sarah and Jeff, with full administrative rights. Since the global role administrator cannot be included on the list (cannot be granted with full administrative rights), it must be explicitly regranted to the role after replacement of the role administrators.

CREATE OR REPLACE ROLE Sales WITH ADMIN Sarah, Jeff
GRANT ROLE sales TO SYS_MANAGE_ROLES_ROLE WITH ADMIN ONLY OPTION

Execute these statements to replace the existing role administrators (Mary and Bob) with Bob and Sarah with administrative rights only. To preserve the global role administrator, it must be included on the list. Since Bob is to remain as a role administrator, and originally had higher administrative rights than the new role administrators, he retains the original higher administrative rights.

CREATE OR REPLACE ROLE Sales WITH ADMIN ONLY Bob, Sarah, SYS_MANAGE_ROLES_ROLE