There’s a great Whitepaper which really goes in depth on how to configure Tableau for Row Level Security, but sometimes it’s nice to have a quick overview that cuts right to the chase. Over a lot of years of explaining how it works, I hope this will outline the standard way of implementing RLS in Tableau in a way that helps you get results as quickly as possible.

Row Level Security is the process of securely filtering data down so a user only see what they should once they are looking at a data source. You could call it “User Data Filtering” just as easily. In essence, you are limiting the returned rows – hence “row level security”.

Basics of Filtering in Tableau

A Filter in Tableau simply results in a WHERE clause in the SQL query.

If we make a Tableau Filter like:

[Region] IN ('West', 'South')

That will translate approximately into SQL like this when Tableau actually queries the database:

SELECT {some fields}
FROM data d
WHERE d.region IN ('West', 'South')

Whatever your level of SQL experience is, the key to much of the following is this statement, which I promise is true: “There are many situations in SQL where a JOIN results in the same thing as a WHERE clause”. If this is confounding now that’s okay – hopefully it will make sense as we go through how everything works.

Making the Filter Dynamic based on User

For RLS, we need to make the Filter dynamic, so that the restricted values change for each user who looks at the report.

For the standard best practice in Tableau, we need some other table which lists what a user can see. We refer to that as an Entitlements Table.

In this simple example, the Entitlements Table might look like (we’ll get into more complexity later in the article):

username	region
User1	West
User1	South
User2	East
User3	West
User4	West

If we have two tables, data and entitlements, the most direct way to make that query above dynamic would be:

SELECT {some fields}
FROM data d
WHERE d.region IN (
  SELECT e.region
  FROM entitlements e
  WHERE e.username = '[TableauUsername]'
)

The second query inside the IN ( ) portion is called a ‘sub-query’. Because it returns only a single column, it can be used to make the filter list use the results of the query.

It works by filtering the Entitlements table down to just the results for that single user, which then restricts what is returned by the data table. Logically, for User1, this turns into exactly the hard-coded query we started with:

SELECT {some fields}
FROM data d
WHERE d.region IN ('West', 'South')

However, Tableau doesn’t currently have a way for us to effectively specify the sub-query into a filter, so we need to find another way to get this result.

Remember what I said about JOIN being an equivalent to a WHERE sometimes? In this case, we can do the following, which Tableau does have the ability to specify:

SELECT {some fields}
FROM data d
JOIN entitlements e ON d.region = e.region
WHERE e.username = '[TableauUsername]'

Setting up Tableau for RLS

There are two distinct steps to get a query like the one above, done it two separate parts of Tableau:

1. Creating the INNER JOIN between the Data Table and the Entitlements Table
2. Creating a Data Source Filter that filters based on the Tableau username

More recent versions of Tableau have Relationships. Because Relationships can result in different JOIN types depending on certain situations, using the Physical layer to specify the INNER JOIN is the technique that is guaranteed to result in the SQL queries I am describing in this post. It is possible that using the Logical Relationships will also get the same results.

INNER JOINs

An INNER JOIN restricts to exact matches. You can INNER JOIN in Tableau on multiple columns – if your Entitlements Table has several columns which need to match to the columns in your Data Table, just define all of those INNER JOIN relationships (they must be = relationships).

Making a Data Source Filter on a Calculated Field

We make our query dynamic by filtering on the TableauUsername property. To use that, we need to make a Calculated Field like:

[username] = USERNAME()

The [username] is a reference to the username column of the Entitlements Table, while the USERNAME() function is what will return the TableauUsername property i.e. the username of the user who is signed into Tableau Server (works with any sign-in / SSO method).

You may want to put an “exception” clause in so that certain Groups of users can see all data. To do this, expand your Calculated Field like:

IF ISMEMBEROF('GroupNameToSeeAll') THEN 1
ELSEIF [username] = USERNAME() THEN 1
ELSE 0
END

We use 0 for False and 1 for True in the above due to how Tableau’s calculation language works. The ISMEMBEROF(groupName) function returns True if a user is a member of the Group that is specified.

The last step is to make this calculated field into a Data Source Filter. You may want to drop the Calculated Field on the Filter Shelf in Tableau Desktop first just to make sure it is doing what you want before setting it as Data Source Filter.

Dealing with Deep Hierarchies

The INNER JOIN technique is actually very well suited for complex, specific entitlements, allowing you to specify exact entitlements to a granular level. It works when the Entitlements Table can define exactly the set of things values for all columns that a user can access. This makes it a very useful technique when users all have a known set of entitlements of a reasonable size.

However, an INNER JOIN requires a full match on all columns to work, which means that for people whose roles should give them access to very wide ranges of items, the INNER JOIN technique requires a row in the Entitlements Table for every possible permutation.

Many organizational hierarchies are better represented with some concept of “all” for certain columns, while other columns might have some elements specified. But this isn’t possible with the INNER JOINs technique above in Tableau, because we can’t have which columns are in the JOIN clause change dynamically.

More Complex Entitlements Tables

One main benefit of this technique is that you can represent an “ALLOW ALL” in your Entitlements Table by using a NULL. It also allows us to ignore some columns for some users.

The next examples will use this more complex Entitlements table:

username	role	region	sub-region	segment
User1	Salesperson	West	NULL	NULL
User1	Salesperson	South	Mid-South	NULL
User2	Salesperson	East	Northeast	NULL
User3	Regional Director	West	NULL	Consumer
User4	VP	NULL	NULL	Consumer

JOIN Calculations to get around INNER JOIN Restrictions

An INNER JOIN only returns records that have a match in both tables. If we might be filtering based on a column, but we also might not, depending on the user, and we can’t change the JOIN dynamically, then we really need the JOIN not to exclude anything, and then use WHERE clauses to do the restriction.

To get this effect in Tableau, you make an INNER JOIN but do a JOIN Calculation (at the bottom of the field list) on both table. Each calculation simply has the value 1, which makes the JOIN clause true in all situations.

The effect in SQL from this is equivalent to this:

SELECT {fields}
FROM data d
INNER JOIN entitlements e ON 1 = 1

If you’re used to writing SQL statements, you are thinking to yourself right now, “That’s a bad idea!” We’re not done yet though – by using a Data Source Filter, this exact form of the query should never run, except perhaps with an administrator’s view. Remember our Calculated Field from earlier?:

IF ISMEMBEROF('GroupNameToSeeAll') THEN 1
ELSEIF [username] = USERNAME() THEN 1
ELSE 0
END

When this is in place as a Data Source Filter, the resulting query will look like:

SELECT {fields}
FROM data d
INNER JOIN entitlements e ON 1 = 1
WHERE e.username = '[TableauUsername]'

The amount of rows that end up being JOINed from the entitlements table should be restricted on username, keeping the queries much smaller than they would be without such a filter clause.

Filtering on Different Columns and Implementing “Allow All”

At this point, we’re not filtering anything except the rows of data in the Entitlements table. Because the JOIN clause is 1 = 1, the JOIN itself isn’t restricting down the data like it is in the basic INNER JOIN technique.

To actually have Row Level Security, we need additional Calculated Fields that implement our logic for when to restrict the values on a column.

We know our Username() based Data Source Filter will always be applying, so we don’t have to reference USERNAME in these other Calculated Fields. You will make a Calculated Field for each, setting them all as Data Source Filters filtering to the value of 1. All of them will basically follow a pattern like

 IF ISMEMBEROF('GroupNameToSeeAll') OR [role] == 'CEO'
     THEN 1
 ELSEIF 
 [region (in data)] = [region (entitlements)] OR ISNULL([region (entitlements)])
     THEN 1
 ELSE 0
 END 

We’ll walk through line-by-line:

The first statement is the override to Show All, if necessary. I’ve put in two tests here, to show that you can either use Tableau Groups (via ISMEMBEROF() ) or check one of the columns in the Entitlements Table for values.

IF ISMEMBEROF('GroupNameToSeeAll') OR [role] == 'CEO'
THEN 1

Next we do another combined line which implements the rule of “IF NULL THEN SHOW ALL”:

ELSEIF 
 [region (in data)] == [region (entitlements)] OR ISNULL([region (entitlements)])
THEN 1

The first part of the OR statement matches the column from the Data Table with the appropriate column from the Entitlements Table. This is equivalent to the JOIN ON clause in the basic INNER JOIN method.

The second part, using ISNULL(), allows for rows where there is a NULL specified rather than any actual matching value.

Additional Complexity in the Calculated Fields

The example above shows all of the pieces necessary to construct rules of any complexity to pick and apply different types of filtering based on either Tableau Group membership or other columns within the Entitlements Table.

For example, rather than having separate Calculated Fields for each column, you could make a single Calculated Field which determines which columns to filter on like:

 IF [role] == 'CEO'
     THEN 1
 ELSEIF 
    [role] == 'Regional Director' THEN
       IIF [region (in data)] = [region (entitlements)] , 1 , 0 )
 ELSEIF 
    [role] == 'Salesperson' THEN
       IFF( [region (in data)] = [region (entitlements)] AND [sub-region (in data)] = [sub-region (entitlements)] , 1 , 0)
ELSEIF 
    [role] == 'VP' THEN
       IFF( [segment (in data)] = [segment (entitlements)] , 1 , 0)
 ELSE 0
 END 

For reference:

username	role	region	sub-region	segment
User1	Salesperson	West	NULL	NULL
User1	Salesperson	South	Mid-South	NULL
User2	Salesperson	East	Northeast	NULL
User3	Regional Director	West	NULL	Consumer
User4	VP	NULL	NULL	Consumer