GRANT SET USER Statement

Quick Links:

Syntax

GRANT SET USER ( <target_users_list> 
      | ANY 
      | ANY WITH ROLES <target_roles_list> )
  TO <userID [,...]> 
  [ WITH ADMIN [ ONLY ] OPTION | WITH NO ADMIN OPTION ]

Parameters

(back to top)

target_users_list must consist of existing users with login passwords and is the potential list of target users who can no longer be impersonated by grantee users. Separate the user IDs in the list with commas.

ANY the potential list of target users for each grantee consists of all database users with login passwords.

ANY WITH ROLES< target_roles_list> the <target_role_list> must consist of existing roles, and the potential list of target users for each grantee must consist of database users with login passwords that have a subset of roles in <target_role_list>. Separate the list of roles with commas.

userID each <userID> must be the name of an existing user or immutable role. The list must consist of existing users with login passwords. Separate the userIDs in the list with commas.

WITH ADMIN OPTION (valid in conjunction with the ANY clause only) The user can both issue the SETUSER command to impersonate another user and grant the SET USER system privilege to another user.

WITH ADMIN ONLY OPTION (valid in conjunction with the ANY clause only) The user can grant the SET USER system privilege to another user, but cannot issue the SETUSER command to impersonate another user.

WITH NO ADMIN OPTION the user can issue the SETUSER command to impersonate another user, but cannot grant the SET USER system privilege to another user.

Examples

(back to top)

Example 1
grants Sally and Laurel the ability to impersonate Bob, Sam, and Peter:
```
GRANT SET USER (Bob, Sam, Peter) TO (Sally, Laurel)
```
Example 2
grants Mary the right to grant the SET USER system privilege to any user in the database. However, since the system privilege is granted with the WITH ADMIN ONLY OPTION clause, Mary cannot impersonate any other user.
```
GRANT SET USER (ANY) TO Mary WITH ADMIN ONLY OPTION
```
Example 3
grants Steve and Joe the ability to impersonate any member of Role1 or Role2:
```
GRANT SET USER (ANY WITH ROLES Role1, Role2) TO Steve, Joe
```

Usage

(back to top)

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. If no administrative clause is specified in the grant statement, the WITH NO ADMIN OPTION clause is used.

If regranting the SET USER system privilege to a user, the effect of the regrant is cumulative.

By default, the SET USER system privilege is granted to the SYS_AUTH_SSO_ROLE compatibility role with the WITH NO ADMIN OPTION clause, if they exist.

The granting of the SET USER system privilege to a user only grants the potential to impersonate another user. Validation of the at-least criteria required to successfully impersonate another user does no occur until the SETUSER statement is issued.

Standards

(back to top)

ANSI SQL–Compliance level: Transact-SQL extension.

Permissions

(back to top)

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.