Tableau Query Impersonation
This feature is only available after version 2.4.3
Before you begin, please understand what is Query Impersonation.
You will learn
- The scenarios for applying Tableau Query Impersonation
- 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.