Revoking the SET USER System Privilege from a User

Remove the ability of a user to impersonate other users, and to administer the SET USER system privilege.

Prerequisites

The SET USER system privilege granted with administrative rights.

Task

The SET USER system privilege can be granted to a user multiple times, using different clauses. For example, User1 is granted the SET USER system privilege once using the ANY clause and again with the target_users_list clause. In cases of multiple grants, the same form of the clause used for the GRANT must be used to revoke it. If the system privilege is revoked from User1 using the ANY clause, the grant with the target_users_list clause remains in effect. The net effect is that User1 is now limited to impersonating users on the target_users_list. Alternately, if the system privilege is revoked from User1 using the target_users_list clause, the grant with the ANY clause remains in effect. The net effect in this scenario is that User1 can continue to impersonate any user in the database.

Note: These examples assume User1 meets all criteria for successful impersonation.

To revoke the SET USER system privilege, execute one of these statements:

Revoke Type	Description
Administrative rights to system privilege only	REVOKE ADMIN OPTION FOR SET USER ( ANY ) FROM `user_ID [,...]`
System privilege to impersonate any database user, including administrative rights	REVOKE SET USER FROMFROM `user_ID [,...]`
System privilege to impersonate specified users	REVOKE SET USER ( `target_users_list` ) FROM `user_ID [,...]`
System privilege to impersonate specified roles	REVOKE SET USER ( ANY WITH ROLES `target_roles_list` ) FROM `user_ID [,...]`

Example:

These statements remove the ability for Sam to impersonate any database user:

REVOKE SET USER (ANY) FROM Sam
or
REVOKE SET USER FROM Sam

This statement removes administrative rights only to the SET USER system privilege from Frank. Frank can still impersonate any user in the database.

REVOKE ADMIN OPTION FOR SET USER (ANY) FROM Frank

This statement removes the ability of Bob and Jeff to impersonate Mary, Joe, or Sue only.

REVOKE SET USER (Mary, Joe, Sue) FROM Bob, Jeff

This statement removes the ability of Mary to impersonate any member of the Sales1 role:

REVOKE SET USER (ANY WITH ROLES Sales1) FROM Mary

This statement removes the ability of Sarah to impersonate Joe or Sue, or any member of the Sales2 role:

REVOKE SET USER (Joe, Sue), (ANY WITH ROLES Sales2) FROM Sarah

This statement removes the ability of Joan to impersonate any member of the Marketing1 or Marketing2 roles:

REVOKE SET USER (ANY WITH ROLES Marketing1, Markeing2) FROM Joan