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::=

Impersonate Statement

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.
  • 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 either COMMIT or ROLLBACK.
  • 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

IMPERSONATE other_user;
--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.