Required to allow a user to temporarily assume the specific roles and system privileges (impersonate) of another user.
Suppose a user who is responsible for performing a key task is unavailable. A backup user is identified. At a minimum, this backup user must have sufficient privileges to complete the task; however, depending on the nature of the task to be performed, if the backup user has additional privileges not available to the original user, there is the potential for these additional privileges to result in the task completing differently than for the original user. Negate this potential by allowing the backup user to temporarily assume the roles and system privileges specific to the unavailable user. The backup user "impersonates" the regular user until the key task is finished.
For one user to impersonate another user, the grantee (impersonating) user must have been granted at least all of the roles and system privileges, with the same or higher administrative privileges, as those already granted to the target (impersonated) user. The grantee can have been granted additional roles, system privileges, or higher administrative privileges, but not fewer. While a user is impersonating another user, you cannot grant additional privileges to the impersonated user or revoke existing privileges from the impersonating user if doing so invalidate the "at-least" criteria of the SET USER system privilege.
Validation of the at-least criteria occurs when the SETUSER command to impersonate another user is issued, not when the SET USER system privilege is granted to a user. When the SETUSER command is issued, if the grantee fails to meet any of the at-least requirements, a permission denied error message appears.
When one user impersonates another, the user ID of the target user, not the grantee user, is recorded in the audit logs. However, since the act of impersonation (issuance of the SETUSER command) is also recorded in the audit logs, you can determine whether or not a task was executed by the actual user or an impersonating user.
Use the SET USER system privilege only as a temporary measure. While a user is impersonating another user, any roles or system privileges granted to the grantee user are unavailable until the impersonation is terminated. It is strongly recommended that you terminate an impersonation as soon as the required tasks are completed, to allow the grantee to regain their normal roles and system privileges. If you do not deliberately terminate impersonation, it is automatically terminated as soon as the grantee user ends the current session or successfully begins impersonating a different user.
A task requiring the CREATE TABLES system privilege needs to 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 as himself or herself. However, since User2 has also been granted Role2, which includes higher system privileges with respect to creating tables, there is the potential for the task to complete differently than if performed by User1.
To negate this possibility, User2 can impersonate User1 to complete the task.
User2 can successfully impersonate User1 because they both have been granted Role1, which meets the at-least criteria. However, User1 cannot successfully impersonate User2 because User1 has not been granted Role2 and does not meet the at-least criteria.
Even though both users have been granted Role1, User5 cannot successfully impersonate User4 because he or she has fewer administrative rights to Role1 than User4, which fails the at-least requirement. However, User4 can impersonate User5 because he or she has more administrative rights to Role1 than User5, which meets the at- least requirement.