In-depth look at the impersonation at-least criteria

A user can successfully impersonate another user only if the at-least criteria are met. Validation of the criteria occurs when a SETUSER statement is executed, not when the SET USER system privilege is granted. If a user fails to meet any of the criteria when the SETUSER statement is issued, the impersonation attempt fails, and an error is returned.

There are four criteria for successful impersonation:

The impersonator has been granted the right to impersonate the target user.
The impersonator has, at minimum, all the roles and system privileges granted to the target user.
The impersonator has been granted the roles and system privileges with similar or more administrative rights.

For the purposes of meeting administrative rights criteria, the WITH ADMIN OPTION and WITH ADMIN ONLY OPTION clauses are considered to grant similar administrative rights. They are also considered to grant more administrative rights than the WITH NO ADMIN OPTION clause. For example, User1 is granted Role1 with the WITH ADMIN OPTION clause, User2 is granted Role1 with the WITH ADMIN ONLY clause, and User3 is granted Role1 with the WITH NO ADMIN OPTION clause. User1 and User2 are considered to have Role1 with similar administrative rights. User1 and User2 are also considered to have Role1 with more administrative rights than User3.
If the target user has been granted a system privilege which supports additional parameters, also known as extensions, (for example, you can provide a list of user IDs when granting the SET USER system privilege), the clauses used to grant the system privilege to the impersonator are a superset of those used for the target user.

Currently, only the SET USER and CHANGE PASSWORD system privileges support extensions. The extensions specified when granting SET USER and CHANGE PASSWORD to the impersonator must be equal to, or a super-set of, those specified when SET USER or CHANGE PASSWORD were granted to the target user. This is evaluated as follows:
- The ANY extension is considered a superset to a role-list extension (list of roles) and a users-list extension (list of users). If the target user has the SET USER system privilege with the ANY extension (for example, GRANT SET USER ANY TO user1), the impersonator must also have the ANY extension.
- If the target user has the SET USER system privilege with both the role-list and users-list extensions, the impersonator must also have the system privilege with the two extensions, and list for each extension must be equal to, or a superset to, the corresponding extension of the target user. For example, if the extension lists of both the impersonator and the target user contain User1, User2 and Role1, Role2, respectively, then the target list grants for each clause are considered equal. Alternately, if users-list for the impersonator contain User1, User2, Role1, Role2, respectively, while users-list for the target user contain User1, Role2 only, the impersonator's users-list is considered a superset to that of the target user.
- If the target user has the SET USER system privilege with a single list extension, the extension list of the impersonator must be equal to, or a superset to, the list of the target user. For example, if the user_list of both the impersonator and the target user contain User1 and User2, they are considered equal. If user_list for the impersonator contains User1, User2, while user_list for the target user contains only User2, then user_list for the impersonator is considered to be a superset to user_list of the target user.
So if the impersonator has the SET USER or CHANGE PASSWORD system privileges with extensions that limit who they can use them on, but the target user has the system privileges with no limitations, the impersonation will fail because the at-least criteria regarding extensions has not been met.

Scenario 1

Assuming that the second and third criterion is met, consider the following scenario:

There are five users: User1, User2, User3, User4, and User5.
There are two roles: Role1 and Role2.
User1 was granted the SET USER system privilege with the ANY clause.
User2 was granted the SET USER system privilege with the user-list clause for User1 and User4.
User3 was granted the SET USER system privilege with the user-list clause for User1, User2, User4 and, User5, and the ANY WITH ROLES role-list clause for Role1 and Role2.
User4 was granted the SET USER system privilege with the ANY clause and the role-list clause for Role1.
User5 was granted the SET USER system privilege with the user-list clause for User4 and the ANY WITH ROLES role-list for Role1.
User1 and User4 can successfully impersonate User2, User3, and User5 because each is granted the SET USER system privilege with the ANY clause. (Criteria 4).

User2, User3, and User5 cannot impersonate User1 or User4 because they do not have the ANY grant. (Criteria 4)

User2 cannot impersonate User3 or User5 because:

User2 is not granted the right to impersonate these users. (Criteria 1)
The SET USER system privilege is not granted to User2 with the role-list clause. (Criteria 4)

User3 can successfully impersonate User2 because:

User3 is granted the right to impersonate User2 via the user-list clause. (Criteria 1)
The user-list clause for User3 is a super-set of User2. (Criteria 4) Though User3 has a grant with the role-list clause, it is not required to satisfy the requirements for impersonation of User2 because the latter does not have the same grant.

User3 can successfully impersonate User5 because:

User3 is granted the right to impersonate User5 via the user-list clause. (Criteria 1)
The user-list clause list for User3 is a super-set of User5. (Criteria 4)
The role-list clause lists for User3 and User5 are equivalent. (Criteria 4)

User5 cannot impersonate any other user because:

User1 and User4 have an ANY grant (Criteria 4)
User2 and User3 have a grant with a user-list clause that is not a sub-set of the grant to User5. (Criteria 4)

Scenario 2

Assuming that the first and fourth criteria are met, consider the following:

There are two users: User6 and User7.
There are two roles: Role4 and Role5.
User6 has been granted Role4 with the WITH ADMIN OPTION clause, Role5 with the WITH ADMIN ONLY OPTION clause, and the MANAGE ANY USER system privilege with the WITH ADMIN OPTION clause.
User7 has been granted Role4 with the WITH ADMIN OPTION clause and Role5 with the WITH NO ADMIN OPTION clause.

User6 can successfully impersonate User7 because:

Both User6 and User7 are granted Role4 and Role5. It does not matter that User6 is granted additional privileges (MANAGE ANY USER system privilege). (Criteria 2)
User6 is granted Role4 with equivalent administrative rights as User7. User6 is granted Role5 with more administrative rights than User7. (Criteria 3)

User7 cannot impersonate User6 because:

User7 is granted Role4 and Role5, but not the MANAGE ANY USER system privilege. (Criteria 2)
User7 is granted Role5 with lower administrative rights than User6. (Criteria 3)

Scenario 3

Consider the following:

There are three users: User8, User9 and User10.
There are two roles: Role5 and Role6.
User8 has been granted Role5 with the WITH ADMIN OPTION clause, and the MANAGE ANY USER system privilege with the WITH ADMIN OPTION clause.
User9 and User10 have been granted Role5 with the WITH NO ADMIN OPTION clause.
User8 has been granted the SET USER system privilege to impersonate User9 and User10 with the user-list clause.
User9 has been granted the SET USER system privilege to impersonate User10 with the user-list clause.

User8 can successfully impersonate User9 because:

User8 is granted the right to impersonate User9 via the user-list clause. (Criteria 1)
The user-list clause list for User8 is a super-set of User9. (Criteria 4)
Both User8 and User9 are granted Role5, with User8 granted more administrative rights to the role than User9. (Criteria 2 and 3)

User8 can successfully impersonate User10 because:

User8 is granted the right to impersonate User10. (Criteria 1)
Since User10 is not granted the SET USER system privilege, criteria 4 is not applicable.
Both User8 and User10 are granted Role5, with the same administrative rights to the role. (Criteria 2 and 3)

User9 cannot impersonate User8 because:

User9 is not granted the right to impersonate User8. (Criteria 1)
Though both User8 and User9 are granted Role5, the grant for User9 is with fewer administrative rights to the role than for User8. (Criteria 3)

Validation of criteria occurs when the SETUSER statement is executed, not when the SET USER system privilege is granted. If a user fails to meet any of the criteria when the SETUSER statement is issued, a permission denied message appears, and the impersonation does not begin.

In-depth look at the impersonation at-least criteria

Scenario 1

Scenario 2

Scenario 3

See also