Remove a role administrator from a role.
When removing a role administrator, if role administration was originally granted to the user using the WITH ADMIN OPTION clause, revoking role administration only removes 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.
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 [,...] |
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 only been granted 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
For Mary, this statement results in the loss of her ability to administer the Sales role, but retains her membership of the role. For Sarah, this statement revokes the Sales role completely from her.