A user can temporarily assume the roles and system privileges of another user (also known as impersonation) to perform operations, provided he or she already has the minimum required privileges to perform the task to begin with.
For example, suppose User1 is responsible for performing a key task, but he or she is unavailable. User2 has sufficient privileges to complete the task, but has additional privileges not available to User1. If User2 performs the task, it may complete differently than when performed by User1. To avoid this, User2 temporarily assumes the roles and system privileges specific to User1, and performs the task.
Impersonation is achieved by first granting a user the SET USER system privilege, and then issuing the SETUSER statement to initiate the impersonation.
To impersonate another user, you must have been granted, at minimum, all of the roles and system privileges with the same or higher administrative privileges, as the user you are impersonating. This is called the at-least criteria.
Impersonation criteria validation occurs when the SETUSER statement is executed, not when the SET USER system privilege is granted. This is because roles and system privileges granted to both the impersonator and impersonatee may change over time. If at the moment of SETUSER execution, if the user does not meet all criteria, impersonation does not begin. However, if the all criteria is met on a subsequent SETUSER execution, impersonation begins.
You may ask why, if a user already has all the privileges he or she needs to perform a task that someone else normally performs, the user does not just perform the task as themselves. The reason is that if the impersonating user has more privileges than he or she needs to perform the task, even though the extra privileges are not required for the task, the additional privileges can affect the output of the task. By impersonating the user who normally performs the task, it negates this possibility.
A task requiring the CREATE TABLE system privilege must be performed.
The task is usually performed by User1, who is unavailable. User2 has been identified as the backup user to carry out the task.
Since both User1 and User2 have been granted Role1, User2 has the required system privilege to perform the task. However, since User2 has also been granted Role2, which includes higher system privileges with respect to creating tables (the ability to create tables owned by other users), there is the potential for the task to complete differently than if it was performed by User1.
To negate this possibility, instead of User2 running the task, User2 impersonates User1 and completes the task.
Once the SET USER system privilege to impersonate another user is granted, it remains in effect until it is revoked.
Once you issue the SETUSER statement, and successful impersonation begins, it remains in effect until you manually terminated the impersonation, begin impersonating another user, or the current session ends. It is recommended that impersonation be terminated as soon as the required tasks are completed.
While a user is impersonating another user, roles and privileges and their related administrative rights can be granted to or revoked from the impersonator or impersonatee as long as doing so does not violate the criterion behind the impersonation. If the grant or revoke violates the impersonation criteria, an error message appears, and the statement fails.
For example, UserA is successfully impersonating UserB. Someone tries to grant a new role to UserA, but not to UserB. Since this grant would not result in a violation of the criteria for UserA to impersonate UserB (UserA still has at least all of the roles and privileges granted to UserB), the grant is successful. If, however, the new role grant was to UserB instead of UserA, the grant statement would fail because it would result in a violation of the criteria (UserB would have been granted more roles than UserA).
In a Multiplex configuration, if an impersonation is active in a connection present in the coordinator, and an attempt is made to grant or revoke roles and privileges that would violate the impersonation criterion, the connection containing the active impersonation is dropped. Since dropping the connection also terminates the impersonation, violation of criteria is no longer an issue, the GRANT or REVOKE statement executes successfully.
When you impersonate another user, the user ID of the impersonated user appears in the transaction log, not yours. However, since the SETUSER statement also appears in the transaction log, it is easy to determine whether the task was executed by the actual user or by someone using impersonation.