IMPERSONATE
Purpose
Use this statement to impersonate another user identity.
Prerequisite
You need to have the system privilege IMPERSONATE ANY USER
or specific object privilege IMPERSONATION ON <user/role>
.
In case of a role, the impersonated user must have granted this role.
Syntax
impersonate::=
Usage Notes
-
This command has two main use cases:
- Explicit activation of (DBA) privileges: To avoid that administrators gain full privileges after they log in, you
can create additional users whose identity can be easily adopted through
IMPERSONATE
. This lets you avoid any mistake. - 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 this feature to avoid duplicate user management and just use one generic BI user.
- Explicit activation of (DBA) privileges: To avoid that administrators gain full privileges after they log in, you
can create additional users whose identity can be easily adopted through
- For user, you can specify either an SQL identifier or a string value (single quotation marks).
-
After the
IMPERSONATE
command, a user loses all privileges of the current user and switches to the identity of another user. IMPERSONATE
is not allowed as long as the current transaction has open write locks. In this case, you have to execute eitherCOMMIT
orROLLBACK
.- You'll find the granted impersonation privileges in system tables EXA_DBA_IMPERSONATION_PRIVS and EXA_USER_IMPERSONATION_PRIVS. The system tables EXA_DBA_SESSIONS, EXA_DBA_AUDIT_IMPERSONATION, EXA_DBA_IMPERSONATION_LAST_DAY, and EXA_DBA_IMPERSONATION_LAST_DAY contain information about sessions in which the identity has been changed through impersonation.
There is no statement for withdrawing an impersonation. To revert to the user that initiated the session, the user has to impersonate again. See the examples below.
Examples
--Jeff does not have DBA privileges, but instead has the right to impersonate a technical DBA to perform any DBA tasks.
-- Login as user Jeff
IMPERSONATE TECHNICAL_DBA; --user with DBA permissions
--Now Jeff is acting as a DBA and can perform DBA tasks. When he is done, he can go back to his regular user
IMPERSONATE JEFF;
-- Your Tableau server is set up to use a single technical user to log into the database
-- In the Initial SQL for the datasource, Tableau sends an Impersonation command to get the local user's privileges
impersonate [TableauServerUser];
-- Now queries are run with the permissions of the user, not the technical user.