Query Impersonation

BEFORE STARTING

Before you begin, please understand what is Query Impersonation.

You will learn

1. The scenarios for applying Tableau Query Impersonation
2. How to use Tableau Query Impersonation

Overview of scenario and process

When sharing the data within an organization, it is necessary to consider different factors such as employee hierarchy, organizational unit, and region to create multiple separate views. However, through the query impersonation feature , users can define data access policies in the dataset, so that Tableau users within the organization can only view data related to their own permissions.

Tableau Query Impersonation Steps

Step 1: Enable impersonation permission

The user connecting to Canner Enterprise from Tableau Server need to have impersonation permission to impersonate other users for querying in Canner Enterprise. Assuming the connecting user is User A, you need to go to the Members page and enable the impersonation permission for User A.

Enter the Members page and click on the Edit option.

Turn on the toggle for Impersonation. Once enabled, the connecting user will have the permission to impersonate other users.

Step 2: Define data permission

Using column-level security as an example.

When users create a View or Materialized View in Canner Enterprise, they can define data permission by using the above functions in the SQL syntax.

Permission : When the user in the workspace role is not Owner, the address column in the table will return *** to the user.

SELECT
    (CASE 
        WHEN get_workspace_role() != 'owner' THEN '***' 
        ELSE address::text 
    END)
FROM 
    customer_27487

Save as a view.

Step 3: Set Canner Enterprise as a Tableau data source

Please refer to this tutorial for how to set up Canner Enterprise as a Tableau data source.

Set up Initial SQL

After setting up the connection information, do not click on Sign in button temporarily and switch to the Initial SQL tab. Initial SQL is a set of SQL statements that can be executed when Tableau connects to a database.

In addition, Tableau also supports passing parameters to Initial SQL, such as the user name of the current TableauServerUser.

Parameter	Description	Example of returned value
TableauServerUser	The user name of the current server user. Use when setting up impersonation on the server. Returns an empty string if the user is not signed in to Tableau Server.	jsmith

By combining the methods above, when connecting Tableau Server to Canner Enterprise, we can enter the following syntax. This will impersonate the current Tableau Server user when executing the query.

impersonate '[TableauServeruser]'

After completing the settings, click the Sign In button to connect. If you encounter the following error message, it means that the user connecting to Canner Enterprise does not have the privilege to impersonate anyone. Please refer to Step 1 to enable impersonation.

ERROR: UserId 377e8838-8c6f-45df-801d-3604c130a5ab didn't have the privilege to impersonate anyone

Step 4: Execute query impersonation

First, let's check which users are in the workspace that Tableau Server connects to. There are two users in this workspace as shown below.

When the user astro logs in to Tableau and views the dataset created in Step 2, Tableau will first execute the Initial SQL to impersonate the user astro for querying.

impersonate 'astro'

According to the data permission we defined for this dataset, since the role of user astro in the workspace is not owner, the address column will show ***.

Similarly, when the user tom logs in to Tableau and views the dataset created in Step 2, Tableau will first execute the Initial SQL to impersonate the user tom for querying.

impersonate 'tom'

According to the data permission we defined for this dataset, since the role of user tom in the workspace is owner, the address column will display the original value.