Replacing Existing Role Administrators on a Role

Replace current role administrators with new administrators.

Prerequisites

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

Task

Replacing role administrators involves 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 you can take. Each approach has 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. Using the second approach includes replacing the global role administrator.

The first approach is a two-step process: Add new role administrators, then remove existing administrators from the role. You must meet the minimum number of administrators requirement throughout; therefore, SAP recommends that you add new administrators before you remove existing ones. If the role has a global role administrator, it is retained unless you explicitly remove it.

The second approach is a one-step process, but has a much broader impact: Define 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 that are not included on the new role administrators list become members of the role with no administrative rights.
All existing role administrators granted the WITH ADMIN ONLY OPTION that are 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, the new role administrators are granted WITH ADMIN ONLY rights. User1, who was originally granted the role with WITH ADMIN rights, and is included on the new list, retains the higher WITH ADMIN rights.
If the role has a global role administrator, it is removed from the role unless you explicitly include it on the new role administrators list.
If new role administrators are granted WITH ADMIN rights, an existing global role administrator cannot be included in the list, since it cannot be granted WITH ADMIN rights. It is removed from the role.

You can issue the replacement role command as long as the replacement administrative option is equal to or higher than the current level. To lower the administrative level, first remove (revoke) all role administrators from the role, and then regranted them.

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 violates the minimum requirement, the replacement fails.

To replace role administrators, execute one of:

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 be granted to a role only 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, you must issue a separate grant statement 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 replacing 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