Granting the SET USER System Privilege to a User

Allow one user to impersonate another user in the database. The system privilege can be granted with or without administrative rights.

Prerequisites

Requires the SET USER system privilege granted with administrative rights.
Each target user specified (target_users_list) is an existing user or user-extended role with a login password.
Each target role specified (target_roles_list) must be an existing user-extended or user-defined role.

Task

A user can be granted the ability to impersonate any user in the database (ANY) or only specific users (target_users_list) or members of specific roles (ANY WITH ROLES target_roles_list). Administrative rights to the SET USER system privilege can only be granted when using the ANY clause.

If no clause is specified, ANY is used by default.

When regranting the SET USER system privilege to a user, the effect of the grant is cumulative.

If no administrative clause is specified when using the ANY clause, WITH NO ADMIN OPTION is granted.

WITH NO ADMIN OPTION is the only valid administrative clause with the target_users_list or target_roles_list clauses.

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

Grant Type	Statement
System privilege to impersonate any database user, with full administrative rights	GRANT SET USER (ANY) TO `user_ID [,...]` WITH ADMIN OPTION
System privilege to impersonate any database user, with administrative rights only	GRANT SET USER (ANY) TO `user_ID [,...]` WITH ADMIN ONLY OPTION
System privilege to impersonate any database user, with no administrative rights	GRANT SET USER (ANY) TO `user_ID [,...]` WITH NO ADMIN OPTION
System privilege to impersonate specified users	GRANT SET USER (`target_users_list`) TO `user_ID [,...]`
System privilege to impersonate any member of specified roles	GRANT SET USER (ANY WITH ROLES `target_roles_list`) TO `user_ID [,...]`
System privilege to impersonate specified users and members of specified roles	GRANT SET USER (`target_users_list`), (ANY WITH ROLES `target_roles_list`) TO `user_ID [,...]`

Example:

Both these statements grant Sam the ability to impersonate any database user:

GRANT SET USER (ANY) TO Sam
or
GRANT SET USER TO Sam

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

GRANT SET USER (Mary, Joe, Sue) TO Bob, Jeff

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

GRANT SET USER (ANY WITH ROLES Sales1) TO Mary

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

GRANT SET USER (Joe, Sue), (ANY WITH ROLES Sales2) TO Sarah

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

GRANT SET USER (ANY WITH ROLES Marketing1, Marketing2) TO Joan