Editor’s Note 2: There is a newer, simplified overview of just “how to make RLS work in Tableau” available now: Quick Explanation of Row Level Security in Tableau. This post and the whitepaper provide a more thorough explanation of “why” and how to set up your database.

Editor’s Note: The official (and improved) whitepaper version of this is available from Tableau here

The techniques outlined in this post are applicable to Live Connections and Multi-Table Extracts (available in Tableau 2018.3+). If you need to use Extracts and are on a version of Tableau prior to 2018.3, please see Keeping Your Extracts From Blowing Up .

How does Row Level Security work?

At a high level, the process is:

1. Determine the Username
2. Get the Data Entitlements for just that User
3. Filter the Data by those Entitlements

“Standard” Row Level Security

This is the most “standard” model of Row Level Security possible in Tableau. It will work for both Live Connections and Multi-Table Extracts.

It consists of:

• An Entitlements View: A denormalized view which has rows for all possible Usernames and their matching entitlements
• A Data View: This can be a single denormalized View or multiple Tables that are JOINED together. It is most easily modeled as a single View, and there is no reason not to reduce it down to a single Table when taking Extracts.
• JOIN Clauses between the Entitlements View and the Data View on every field which makes up part of an Entitlement
• All of these Views are in the Same Database/Schema.

Note on the term “View”: When you see a View mentioned here, use all necessary database techniques to make that the most optimized possible actual database object. It does not strictly have to be a “View”; rather, we’re indicating that you bring everything together as opposed to doing ALL of the JOINs in Tableau

What is an “Entitlement”?

The word Entitlement has been used many times so far, so it’s worth defining:

Entitlement: A single unique combination of Attributes that the Data View will be filtered on. These Attributes must exist in Column(s) of the Data View.

Example: In a geographical hierarchy of Region, Sub-Region, and Country, each unique combination would be an Entitlement.

Representing Entitlements

There are two main methods for representing Entitlements in a relational database:

1) Deepest Granularity Entitlements: A row for every Entitlement

2) “Sparse” Entitlements: A row for every level in the hierarchy of Entitlements (including the Deepest Granularity)

The following are examples of each:

Deepest Granularity Denormalized Table of Potential Entitlements

entitlement_id	region_id	sub_region_id	country_id	country_name
APAC-JPN-JP	APAC	JPN	JP	Japan
APAC-SEA-KH	APAC	SEA	KH	Cambodia
APAC-SEA-ID	APAC	SEA	ID	Indonesia
APAC-SEA-MY	APAC	SEA	MY	Malaysia
APAC-SEA-MM	APAC	SEA	MM	Myanmar (Burma)
APAC-SEA-PH	APAC	SEA	PH	Philippines
APAC-SEA-TH	APAC	SEA	TH	Thailand
APAC-SEA-VN	APAC	SEA	VN	Vietnam
APAC-SK-KR	APAC	SK	KR	South Korea

Sparse Entitlements Denormalized Table of All Possible Entitlements

entitlement_id	region_id	sub_region_id	country_id	country_name
ALL	NULL	NULL	NULL
APAC	APAC	NULL	NULL
APAC-JPN	APAC	JPN	NULL
APAC-SEA	APAC	SEA	NULL
APAC-SK	APAC	SK	NULL
APAC-JPN-JP	APAC	JPN	JP	Japan
APAC-SEA-KH	APAC	SEA	KH	Cambodia
APAC-SEA-ID	APAC	SEA	ID	Indonesia
APAC-SEA-MY	APAC	SEA	MY	Malaysia
APAC-SEA-MM	APAC	SEA	MM	Myanmar (Burma)
APAC-SEA-PH	APAC	SEA	PH	Philippines
APAC-SEA-TH	APAC	SEA	TH	Thailand
APAC-SEA-VN	APAC	SEA	VN	Vietnam
APAC-SK-KR	APAC	SK	KR	South Korea

While the “Deepest Granularity” method explicitly lists every possible entitlement combination, it does not have any entries for the levels of the hierarchy.

“Sparse Entitlements” instead allows using “NULL” to represent an “All at this Level in the Hierarchy” condition. The full set of entitlements possible thus not only includes the most granular definitions, but also steps at each level in the hierarchy that can see anything below them.

Choosing an Entitlements Method

Which method should you choose? We don’t have any way of performance testing in all situations, and everyone’s RDBMS system will differ in what is optimal. The Sparse Entitlements method requires less rows in the Roles-to-Entitlements table, but at the cost of more complexity in the JOINing / filtering.

There is an intermediary technique, called “All Access” or “Deepest Granularity”, which is appropriate if most people in the organization have small sets of entitlements, with another set of people who can see everything. It is described further down in this article.

Hierarchies (Do you really need them?)

You may have noticed that this example is purely hierarchical (the three attributes are linked to one another); however, the concept of defining entitlements does not require that all of the attributes be in a strict hierarchy. As long as you have the JOINs in place for each Column / Attribute, it should work correctly.

You can get a performance benefit if you use the Deepest Granularity method and everything is hierarchical: you only need to do a single JOIN, on the deepest level of the hierarchy (since the higher levels of the hierarchy are implied by the deepest level). This only works if all of the attributes at the lowest level are distinct: if you are JOINing on a unique ID, then you would be clear just to JOIN on that deepest level attribute, but if there is a chance for duplication in the real values (for example, a Central sub-region under both AMER and EMEA), then you’ll need to JOIN on all the columns to achieve the effect of a distinct key value.

Tying Users to Entitlements

So far, we’ve only looked at the Entitlements themselves. Now we’ll look at how to tie the Usernames to the Entitlements. In general, most systems link Usernames to a Role, which is a specific set of Entitlements. This allows for easily changing or removing a user from the Role, while still maintaining a record of the Role and its entitlements. It can be useful to see the performance of a Regional Director, even if it wasn’t the same person over time.

In this structure, you would have a Roles table, which defines the absolute set of Roles:

Role Table

role_id	role_name
RD-APAC	APAC Regional Director
SRD-APAC-SEA	Sub-Regional Director, South East Asia
CD-APAC-JPN-JP	Country Director, Japan
CD-APAC-SK-KR	Country Director, South Korea
CD-APAC-SEA-TH	Country Director, Thailand

And finally a mapping table which lists all of the Entitlement IDs for each Role ID. Notice this is a many-to-many table: it can be very long, but remains skinny, and in both columns you can have many duplicated entries.

There are two variations, depending on whether you do the Deepest Granularity method or the Spare Entitlements Method:

Deepest Granularity Mapping Table, Roles-to-Entitlements

role_id	entitlement_id
RD-APAC	APAC-JPN-JP
RD-APAC	APAC-SEA-KH
RD-APAC	APAC-SEA-ID
RD-APAC	APAC-SEA-MY
RD-APAC	APAC-SEA-MM
RD-APAC	APAC-SEA-PH
RD-APAC	APAC-SEA-TH
RD-APAC	APAC-SEA-VN
RD-APAC	APAC-SK-KR
SRD-APAC-SEA	APAC-SEA-KH
SRD-APAC-SEA	APAC-SEA-ID
SRD-APAC-SEA	APAC-SEA-MY
SRD-APAC-SEA	APAC-SEA-MM
SRD-APAC-SEA	APAC-SEA-PH
SRD-APAC-SEA	APAC-SEA-TH
SRD-APAC-SEA	APAC-SEA-VN
CD-APAC-JPN-JP	APAC-JPN-JP
CD-APAC-SK-KR	APAC-SK-KR
CD-APAC-SEA-TH	APAC-SEA-TH

Sparse Entitlements Mapping Table

role_id	entitlement_id
RD-APAC	APAC
SRD-APAC-SEA	APAC-SEA
CD-APAC-JPN-JP	APAC-JPN-JP
CD-APAC-SK-KR	APAC-SK-KR
CD-APAC-SEA-TH	APAC-SEA-TH

You’ll notice how much smaller this Entitlements Mapping table is than the one above (and this is simply 4 people with a limited subset of the APAC region itself). It’s also very easy to understand where someone lives in the hierarchy with this type of an entitlement mapping structure. (This example just happens to be 1 – 1 with roles and entitlements, but that is not always the case).

Finally, you map Users to Roles using another many-to-many mapping table. The username here needs to match either the Username or Full Name in the Tableau Server, as those are the only two values available via the User Functions in Tableau Desktop (yes, you can do string calculations on those values in Tableau if you need to massage the values available from this table).

role_id	username
RD-APAC	regional_director@company.com
SRD-APAC-SEA	sub_regional_director@company.com
CD-APAC-JPN-JP	japan_manager@company.com
CD-APAC-SK-KR	south_korea_manager@company.com
CD-APAC-SEA-TH	thailand_manager@company.com

Alternatively, you can just assign Entitlements directly to a User using the Tableau Username/Full Name in a Users-to-Entitlements Many-to-Many Mapping table. You’ll have to manage more directly in that table but it removes an extra set of JOINs to manage.

Entitlements View

For optimal performance (and it make for the simplest set of tables if bringing into an Extract), you’ll want to JOIN all of these tables together into a single Entitlements view. Yes, this will result in a “blown up” version of the Entitlements, but it shouldn’t result in the data table itself blowing up.

The JOINs (in our theoretical example) would be:

SELECT *
FROM users_to_roles_map ur
INNER JOIN roles_to_entitlements_map re ON ur.role_id = re.role_id
INNER JOIN entitlements_view e ON re.entitlement_id = e.entitlement_id

Now you’ll have a View (again, store in the database however is most optimal) which has usernames and all the columns of entitlement information.

Filtering the Data by the Entitlements

The “Standard Method” of Row Level Security in Tableau uses a JOIN between the Data View and the Entitlements view. We recommend this because the same technique can be used with Live Connections and Multi-Table Extracts (2018.3+).

JOINs for Deepest Granularity Method

You can represent the Deepest Granularity form of this in SQL like the following (although VizQL will produce whatever complex queries it needs, this is the basic form):

SELECT *
FROM data d
INNER JOIN entitlements e ON 
d.attribute_a = e.attribute_a AND
d.attribute_b = e.attribute_b AND ...
WHERE e.username = USERNAME()

For our example, the JOIN condition will be

ON d.region_id = e.region_id AND
d.sub_region_id = e.sub_region_id AND
d.country_id = d.country_id

You do this in Tableau in the Data Connection dialog. Just set up an INNER JOIN and add the fields from each table on each side.

JOINs for Sparse Entitlements Method

The SQL for the Sparse Entitlements method is more complex. If you are using Custom SQL (only possible for Live Connections), it would look like:

SELECT * FROM data d 
INNER JOIN entitlements e ON 
(e.region_id = d.region_id OR ISNULL(e.region_id) 
AND (e.sub_region_id = d.sub_region_id OR ISNULL(e.sub_region_id) 
AND (e.country_id = d.country_id OR ISNULL(e.country_id)

You can’t do this level of complexity in Tableau’s JOIN dialog, but you can get a near identical effect by doing the following (Major hat tip to Dan Cory at Tableau who immediately described this technique when presented with this Row Level Security pattern.) :

In the Tableau Desktop Data Connection pane, you can only do simple equality JOIN definitions (=, <>, <, >, <=, >=) and not write more complex JOIN clauses. However, you can define JOIN Calculations, which allows for the following pattern:

In the calculation editor that pops up, just put the integer value 1.

Now do the same for the other table. This creates what is called a Cross JOIN (yes, theoretically this is a “data blow up”, but luckily in both a Live Connection and Multi-Table Extract scenario, it shouldn’t be processed that way, due to the filters that follow).

Now we go into a Sheet to define the Row Level Security filters we want to apply.

The first filter is the Username limit, which will reduce down the total rows of entitlements:

[Username]=USERNAME()

Now you have the choice to do separate calculations for each of the levels in the hierarchy, or you can combine them all together. Those calculated views look like:

[region_id] = [region_id (Entitlements View)] OR ISNULL([region_id (Entitlements View)]

or a combined one for all levels:

([region_id] = [region_id (Entitlements View)] OR ISNULL([region_id (Entitlements View)])

AND

([sub_region_id] = [sub_region_id (Entitlements View)] OR ISNULL([sub_region_id (Entitlements View)])

AND

([country_id] = [country_id (Entitlements View)] OR ISNULL([country_id (Entitlements View)])

Place these calculated fields all as Data Source Filters and they will now give you the correct results based on the hierarchy you have defined. The magic is in the ISNULL calculations — this creates the effect of the NULL in the particular entitlement column to be matched up to ALL items in the other column.

“All Access” or “Deepest Granularity”: An intermediate solution

Hat tip to Tableau’s Fearghal Gunning for coming up with this technique!

Let’s take a simpler case — imagine you basically have two access levels in your organization: people who can see everything (“All Access”) or people with some reasonably definable subset of entitlements (“Deepest Granularity”). In this case, we just need a way to circumvent a JOIN for the All Access users, while maintaining the Deepest Granularity JOINs for all other users.

For this technique, we’re going to use Tableau’s Groups functionality to give us an “override” in the JOIN calculation we create.

From the Fact View, you will do a LEFT JOIN, then two JOIN conditions. The first JOIN should JOIN on the column that represents the Deepest level of Granularity (in our example, country_id).

Then create a second JOIN Condition, with a JOIN Calculation on the each side (similar to what we did above in the Sparse Entitlements technique). On the Left Side (the Fact View), simply set the calculation value to

True

Then on the right side (the Entitlements View), the calculation should be:

IF ISMEMBEROF(‘Admins’) THEN False

ELSE True

END

The way this works is that when a user is a member of the ‘Admins’ Tableau Group, there no matches at all in the Entitlements View, so in a LEFT JOIN situation you get the entirety of the Fact View and all NULLs for the columns from the Entitlements View (zero duplication). In the case where the user is not part of the override Tableau Group, the column will match up with the values in the Entitlements View on the field representing the  Deepest Granularity of the hierarchy (and the TRUE = TRUE doesn’t affect anything at all).

Then you go into a sheet, and create a Calculated Field for the user, which you will make a Data Source Filter:

[Username] = USERNAME() OR ISNULL([country_id] (Entitlements View)])

This will evaluate true for all rows when the Group override is working, or it will filter down to only the user’s deepest granularity in the hierarchy (country_id in this example).

Will this perform better than the “Sparse Entitlements” method? That’s a completely valid question and one in which we’re always looking for input on! For a Live Connection, you’ll have to check that the Data Source Filter RLS calculation is successfully limiting the result set rather than “blowing up” on the database side then reducing down.

Why the Standard Method doesn’t cause Data to “Blow Up”

For those used to Tableau Data Extracts prior to 2018.3, you’re probably looking at this and saying “that’s totally crazy, the end result will be enormous”. If in fact you were to materialize the JOINs and bring it all in as one table (which is what a Single Table Extract does), that is exactly what would happen.

Thankfully, most databases can be optimized so that they never actually answer these types of queries by actually bringing all the rows together before filtering down. Your initial JOINs and security calculations in Tableau are just telling the VizQL engine about the relationships and filters to be sent. VizQL will writes an optimized query, which the RDBMS system will then find an optimal Query Plan for. Things like Primary and Foreign Key Relationships and Indexes will be taken into account (hopefully!) to process the resulting query in something like the following order:

1. Determine the Entitlement Rows, filtered on the username
2. JOIN that filtered set of Entitlement Rows to the Fact Table, using available Indexes and Key Relationships
3. Max Number of Rows = Rows in Data Table (If a user is entitled to everything)

“Blowing Up” happens when database processes the query in this order:

1. JOIN all of the Entitlement Rows to the Fact Table
2. Max Possible Size = Number of Entitled Users * Rows in Fact Table  (Every user * every row)
3. Filters down to user’s rows

When Row Level Security is Built Into Your Database

• In Oracle, if you have VPD set up for the database users, you can use Initial SQL in Tableau 9.3 to take advantage of the existing security filtering
• In SQL Server 2016, you can set up Row Level Security in the database, based on database user. Tableau’s Impersonate User functionality will set the user correctly for you, and SQL Server 2016 will do the filtering. See Russell’s exploration here.
• In previous versions of SQL Server, you can create Views with the row level security WHERE clauses built in that reference the current user’s role, then give users only access to those specific Views. In this case, Impersonate User will work. It is a similar concept to how SQL Server 2016 works, but the DBA must make every View they want to expose instead of having the security logic applied automatically to every table or view. You can reference the username of the user who is being impersonated using the SYSTEM_USER constant value, per Stack Overflow
  • Each View would require a WHERE clause includes a reference like [Entitlements Table].[Username] = SYSTEM_USER

Note that these require the usernames to exist on the database as users with permissions / roles in the database. This is more likely in an internal organization use case than in an externally facing Tableau deployment. If the usernames are not database users, but simply exist in the data, then you can implement a solution starting with Tableau 9.3 using Initial SQL with Parameters.

Alternate Live Connection Database Techniques

Sub-Selects in a WHERE clause instead of JOINs

The other way of filtering down rows in SQL is through a WHERE clause, rather than a JOIN. You can put a SELECT statement in the WHERE clause (a sub-select) which returns only values necessary to filter the main table. Logically, this is exactly the same as INNER JOINing, however sometimes defining things this way causes the query optimizer in an RDBMS to come up with a different set of steps to compute the result set, which could be faster than how it plans the queries Tableau sends using the “Standard” method.

There is currently no way to do a Sub-Select Filter in Tableau’s Data Connection screen. However, when using Live Connections, you can send your own arbitrary SQL Using Pass-Through Functions (RAWSQL) for Row-Level Security

Using Stored Procedures / Functions to get Security Context

Although you can connect Tableau to a Stored Procedure as if it were a Table, the actual mechanism of this (storing the result into a temp table on the database server) does not always lead to optimal performance. If the main purpose of the stored procedure is to determine the security context then apply it to another table, you can potential separate the security logic out into a Function, which can then be called using RAWSQL Functions.