IMPERSONATE

Purpose

Use this statement to impersonate another user.

Prerequisite

You must have the system privilege IMPERSONATE ANY USER or the specific object privilege IMPERSONATION ON <user/role>. In case of a role, the impersonated user must have granted this role.

Usage notes

• The IMPERSONATE command has two main use cases:

  Explicit activation of (DBA) privileges

  To avoid that administrators gain full privileges immediately after they log in, you can create additional users whose identity can be adopted by using IMPERSONATE.

  Authentication by third party tools

  In some organizations, single sign-on authentication through LDAP or Kerberos is not possible. If the BI tool is already doing user-based authentication, you can use IMPERSONATE to avoid duplicate user management and just use one generic BI user.

• The impersonated user can be identified using either an SQL identifier or a string value (using single quotation marks).

• When IMPERSONATE is invoked, the user loses all their current privileges and will only have the privileges of the impersonated user.

• IMPERSONATE is not allowed if the current transaction has open write locks. In this case, you must execute either COMMIT or ROLLBACK.
• There is no statement for withdrawing an impersonation. To revert to the user that initiated the session, the user must impersonate again as the original user. See also Examples.
• For information about granted impersonation privileges, see the system tables EXA_DBA_IMPERSONATION_PRIVS and EXA_USER_IMPERSONATION_PRIVS.
• For information about sessions where impersonation was used, see the system tables EXA_DBA_SESSIONS, EXA_DBA_AUDIT_IMPERSONATION, EXA_DBA_IMPERSONATION_LAST_DAY, and EXA_DBA_IMPERSONATION_LAST_DAY.

Examples

IMPERSONATE other_user;

-- A user is logged in as USER_1. This user does not have DBA privileges, 
-- but has the right to impersonate a technical DBA to perform DBA tasks.

IMPERSONATE TECHNICAL_DBA;
-- USER_1 is now acting as a DBA and can perform DBA tasks. 
-- When done with the DBA tasks, USER_1 reverts the impersonation.
IMPERSONATE USER_1;

-- Your Tableau server is set up to use a single technical user 
-- that can log in to the database. In the initial SQL for the 
-- data source, Tableau uses the impersonation command to get 
-- the local Tableau server user's privileges:

IMPERSONATE [TableauServerUser];

-- Queries are now run with the permissions of the local user.