Understand the Requirements for Impersonation

There are four criteria to 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 said roles and system privileges with similar or higher administrative rights.
Note: 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 higher 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 said to be granted Role1 with similar administrative rights. User1 and User2 are also said to be granted Role1 with higher administrative rights that User3.
If the target user has been granted a system privilege which supports extensions, the clauses used to grant the system privilege to the impersonator are a super-set of those used for the target user. Currently, only the SET USER and CHANGE PASSWORD system privileges support extensions.
Note:
- The ANY clause is considered a super-set of the target_roles_list and target_users_list clauses. If the target user has been granted the SET USER system privilege with an ANY grant, the impersonator must also have the ANY grant.
- If the target user has been granted the SET USER system privilege with both the target_roles_list and target_users_list clauses, the impersonator must also have been granted the system privilege with the two clauses, and the target list of each clause must be equal to or a super-set of the corresponding clause grant of the target user. For example, if the target lists of both the impersonator and target user contain User1, User2 and Role1, Role2, respectively, the target list grants for each clause are said to be equal. Alternately, if the target list grants of the impersonator contain User1, User2, Role1, Role2, respectively, while the target list grants of the target user contain User1, Role2 only, the target list grants of the impersonator are said to be a super-set of the target user.
- If the target user has been granted the SET USER system privilege with a single target list clause, the target list of the impersonator must be equal to or a super-set of the list of the target user. For example, the target_user_list of both the impersonator and the target user contain User1 and User2 (equal) or the impersonator list contains User1, User2, while the target user contains User2; User1, User2 (impersonator list) is a super-set of User2 (target user list).
- By definition, a user can always impersonate themselves. Therefore, if the target user has been granted the right to impersonate the impersonator, this does not violate the equal to or a super-set of criteria requirement of the impersonator. For example, User3 is the impersonator and User4 is the target user. The target_user_list for User3 contains User4 and User5. The target_user_list for User4 contains User3 and User5. If you remove the impersonator from the target list, the target list of User3 meets the criteria requirement.

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 target_users_list clause for User1 and User4.
User3 was granted the SET USER system privilege with the target_users_list clause for User1, User2, User4 and, User5, and the ANY WITH ROLES target_roles_list clause for Role1 and Role2.
User4 was granted the SET USER system privilege with the ANY clause and the target_roles_list clause for Role1.
User5 was granted the SET USER system privilege with the target_users_list clause for User4 and the ANY WITH ROLES target_roles_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).

User1 and User4 can impersonate each other because they each have the ANY grant. (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 target_roles_list clause. (Criteria 4)

User3 can successfully impersonate User2 because:

User3 is granted the right to impersonate User2 via the target_users_list clause. (Criteria 1)
The target_users_list clause for User3 is a super-set of User2. (Criteria 4) Though User3 has a grant with the target_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 target_users_list clause. (Criteria 1)
The target_users_list clause list for User3 is a super-set of User5. (Criteria 4)
The target_roles_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 target_users_list clause that is not a sub-set of the grant to User5. (Criteria 4)
User3 has a grant with a target_roles_list clause that is not a subset. (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 higher 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 has 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 target_users_list clause.
User9 as been granted the SET USER system privilege to impersonate User10 with the target_users_list clause.

User8 can successfully impersonate User9 because:

User8 is granted the right to impersonate User9 via the target_users_list clause. (Criteria 1)
The target_users_list clause list for User8 is a super-set of User9. (Criteria 4)
Both User8 and User9 are granted Role5, with User8 granted higher 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, requirement 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 less administrative rights to the role than for User8. (Criteria 3)

Validation of criterion 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.