Removing a Role Administrator from a Role

Remove a role administrator from a role.

Prerequisites

Administrative privilege over the role.

Task

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. You can remove role administrators only as long as the this minimum is still met after removal.

When removing a role administrator, if role administration was originally granted to the user using the WITH ADMIN OPTION clause, revoking role administration removes only their ability to manage the role (grant, revoke, drop), not the ability to use the underlying system privileges of the role (membership). However, if role administration was originally granted to the user using the WITH ADMIN ONLY OPTION clause, revoking role administration has the same effect as revoking the role entirely, as there was no membership associated with the role.

To remove a role administrator from a role, execute one of these statements:

Removal Type	Statement
Remove role administrator, but retain membership in the role.	REVOKE ADMIN OPTION FOR ROLE `role_name` FROM `admin_name [,...]`
Remove role administrator along with membership in the role.	REVOKE ROLE `role_name` FROM `admin_name [,...]`

Removal Type

Statement

Remove role administrator,

but retain membership in the role.

REVOKE ADMIN OPTION FOR ROLE role_name

FROM admin_name [,...]

Remove role administrator

along with membership in the role.

REVOKE ROLE role_name

FROM admin_name [,...]

Example:

This example assumes that both Mary and Sarah are currently role administrators of the Sales role. Mary has been granted both membership in the role and the ability to administer the role. Sarah, however, has been granted only the ability to administer the role, not membership. Due to the different administration levels granted, executing this statement to revoke administrative rights from the Sales role has a different impact on each administrator:

REVOKE ADMIN OPTION FOR ROLE Sales FROM Mary, Sarah

It results in the loss of Mary's ability to administer the Sales role, but retains her membership of the role. It completely removes the Sales role from Sarah.