How to Enable User Impersonation from Tableau to Snowflake
Publishing to Tableau Server or Tableau Online is a powerful way to enable all users across your organization to explore your data without having to be a SQL expert. The Data Platform team can create data sources from Snowflake in Tableau, and publish those for your entire organization to consume.
Consider this realistic example of a large financial institution’s “data user” makeup. They have 35 power users on their Data Platform team, and 6,000 users on their Data Engineering/Data Science teams, who of course need direct access to Snowflake for diverse consumption patterns. However, they also have a large number of non-technical BI dashboard users (20,000!) that should only have access to pre-curated published data, but not direct access to Snowflake. Publishing to Tableau Server or Tableau Online helps unblock that huge data user group.
However, when publishing to Tableau Server or Tableau Online, for authentication you must either:
- Option 1: Embed the credentials to the underlying database when publishing, or
- Option 2: Create a Tableau extract, or
- Option 3: Prompt the consuming user for their credentials to the underlying database when reading from the published Tableau data source.
Here is how this decision appears in Tableau (note: if extract was chosen, this option is not presented):
If you are democratizing data across your organization, which is your goal by publishing to Tableau Server or Tableau Online, options 1 and 2 are typically non-starters.
Because not everyone in your organization should be able to see everything the power user publishing the data can see, and when you embed credentials or create an extract, it is based on the access level of the user publishing the data.
To better understand this, lets update that diagram from earlier:
As you can see from this new diagram, the controls required on the two user groups outside of the Data Platform Team are unique to that group, this means the controls required on the Data Engineering/Science team will not match the controls you require on your Business Users who come from “all walks of life” across your organization. Because of this, sharing credentials across any user groups is not feasible. To state it simply, when you need any semblance of granular controls, options 1 and 2 are not viable.
Because of this, you must authorize access per consuming user, which requires a live connection from Tableau (no extracts, no shared credentials) to Snowflake. “Live connection” means that every time a consuming user views the data in Tableau Server or Tableau Online, their credentials are used to query Snowflake, and all configured fine-grained access control policies for that consuming user are enforced.
Option 3 solves this problem by prompting users for their database credentials when viewing the published data source, which in turn creates a live connection as described above. There are three problems with this approach, though:
- Hard to Manage: Everyone must have an account in Snowflake, which leaves you managing all of your users and their appropriate Snowflake roles – and remember, that set of users has highly complex and unique controls.
- Risk: This approach increases the surface area of a potential database breach, since all those users have credentials to the database. Additionally, any mistakes made when configuring Snowflake roles are amplified.
- User Experience: The user is annoyingly prompted for their credentials every time they want to look at a published data source.
You probably understand all this, which is why you are reading this blog in the first place…
Enter the solution: impersonation.
Benefits of User Impersonation
What if, instead of using any of the options above, you could dynamically impersonate the consuming user that is analyzing the published data? In this world, the Data Platform team could publish data to Tableau Server or Tableau Online and configure it in a way that never prompts the consuming users for credentials, yet enables them to see the data they are allowed to see – without requiring or even giving the option for those users (or their complex roles) to exist in Snowflake.
This allows you to put data in the hands of everyone in your organization without overwhelming your Data Platform team.
How Immuta Enables User Impersonation
Immuta is an access control solution that allows data engineering teams to centrally manage policy across their data ecosystem. By applying an attribute-based access control (ABAC) model, Immuta provides scalability and automation to greatly simplify data access policy management. There’s too much detail on how this all works in Immuta to cover in this particular blog (see here for more information on how Immuta and Snowflake provide industry-leading data governance), but Immuta also enables you to manage impersonation (and the associated fine-grained data policies) from Tableau to Snowflake.
Immuta solves the three problems above:
- Easy to Manage: Provides scalable and automated policy enforcement to simplify access management.
- Lowers Risk: Does not require the business users to exist in Snowflake and makes policy management easy to understand, evolvable without fear, and error-free.
- Seamless User Experience: The user is not prompted for credentials.
How to Enable Impersonation from Tableau to Snowflake:
Registering metadata with Immuta
Immuta centralizes metadata about your tables and users, and using that metadata, you can centrally build highly abstract policy intent (again, we recommend reading this for more detail).
For the purposes of impersonation, you would want to configure Immuta both to your tables in Snowflake (for the data metadata) and your identity manager, such as Okta, LDAP, Active Directory, etc (for the user metadata). It is possible to also consolidate user metadata from other systems outside of your identity manager. The identity manager you configure to Immuta should be the same identity manager that Tableau Server and/or Tableau Online uses. Note that using an identity manager is not required; alternatively, accounts in Immuta can be linked to Tableau accounts.
Configuring your Immuta Impersonation User:
Immuta can support multiple Snowflake instances, and when configuring Immuta to a Snowflake instance, you have the option to enable impersonation support. In doing so, you are asked to provide the name of a new ROLE that Immuta will create in your Snowflake instance, which will possess this impersonation power.
Once Immuta is configured and has created that ROLE with impersonation power in Snowflake, you can run the command below in Snowflake to give select users access to it. These should be the same power users that will be publishing data to Tableau Server and/or Tableau Online.
GRANT ROLE impersonation role TO USER snowflake power user;
TIP: You do not want this group of users to include the user whose credentials were used to register the data metadata in Immuta, because that user will always be exempt from policy.
Creating the data source in Tableau:
In Tableau, when creating the data source and providing the credentials to connect to Snowflake, the credentials used must belong to one of the power users that was granted access to the impersonation ROLE described above. Remember, you must also include that impersonation ROLE when configuring the Snowflake connection (in this example, we named the ROLE
TABLEAU_IMPERSONATION_DEMO when configuring Immuta):
Don’t click “Sign In” just yet; Tableau has this nifty trick where, when you configure a data source connection, you can specify “initial SQL” in the 2nd tab that will run on connection to the database. In this case, we can make that initial SQL an Immuta-specific SET command that will specify the user we want to impersonate. Obviously, we do not want the consuming user to be hard coded in the Initial SQL. So, to make this work, Tableau has also provided handy parameters you can place in the initial SQL:
In this example, the
TableauServerUser parameter will be dynamically replaced at runtime by the current user attempting to read the published data from Tableau Server or Tableau Online. Immuta understands that SET command, and because it knows the configured Snowflake data source connection was created with the ROLE allowing impersonation, Immuta will treat the connection as if it’s the consuming user, without prompting the consuming user for credentials. Had you configured the Snowflake connection with credentials without impersonation power, Immuta would ignore that SET command in the Initial SQL.
It’s important to remember this consuming user does not have to exist in Snowflake at all (but they certainly can). They simply need to exist in both Immuta (to build the policies) and in Tableau.
Publishing the data source to Tableau Server or Tableau Online:
This example shows publishing to Tableau Online, but the process will work the same for Server. Really, all you need to remember to do is ensure that you use the “
Embedded password” option instead of “
This may be a little confusing at first blush, but remember, you want all consuming users to connect as the user with impersonation power (the embedded connection). This is because at connection time, the Initial SQL will trigger the impersonation of the real consuming user thanks to Immuta. This, again, is why we configured the data source connection with the appropriate impersonation ROLE (
TABLEAU_IMPERSONATION_DEMO) in the prior step.
Building Snowflake Access Control Policies with Immuta:
We could write at length about the different layers of dynamic policies you can enforce using Immuta, but instead, take a look at this quick demo that puts this blog into action.