This documentation is for versions of Tableau prior to 2018.3

If you are using the 2018.3 version of Tableau or later, you’ll want to implement Row Level Security using Multi-Table Extracts and the “Standard” Method of Row Level Security. Read all about them at

How to set up your Database for Row Level Security in Tableau

If you are on a version prior to 2018.3, continue

EDITOR’S NOTE 2018-05-17: At the current time, it has been determined that the CONTAINS() solution below performs very poorly in the Hyper extract system in all releases of Tableau 10.5 and the 2018.1.0 release. The performance issues have been corrected in the 2018.1.1 release, but will not appear in any version of 10.5. If you are currently using it in a pre-10.5 version of Tableau, please only upgrade to 2018.1.1 and after.

In Part Two of the series (see Part One here), we’ll go through two solutions to Row Level Security that are a bit more programmatic and require some work outside of just Tableau Desktop calculations, but our often the recommended solution due to their speed and flexibility.

These scenarios will be the ones most often encountered and solved in the real world. You’ll see an example first from a customer story and then one continually implemented and recommended by Tableau’s own Professional Services organization.

Hybrid Group Solution: One Group with Access to All, Individual Security for all other users

This example was inspired by a blog post from one of our many amazing customers. You can find a full write up here. In essence, you end up with individualized access for those users who should only see their own data, with the ability to give access to All the data to anyone in a one other Tableau Group.

This hybrid solution does request getting creative with your SQL skills in the data prep area.

First, you will be joining to your security table on the lowest level of granularity that you want to put security on (in our case State+Category).  The data will be duplicated and you  use the USERNAME() security calculation to filter down the individual values since their names will be present on every row in a column after you create the join.

For the group level access, which is not attached to a singular user name, you actually have to union the original data source with a denotation that this is a row tied to the group security and not an individual, perhaps something like ‘Group’ or ‘All’ in the Name column. That result set will be UNIONed with an original set of the data that we’ll write our group security against.

The total amount of duplication will depend on how many individuals have access to individual rows at that lowest level of joining. Here’s a very basic example with one row of fact data being joined to our security table:

User

Facts

SQL

Result Set

The main thing to note is that our original fact table (or original row in this case) is untouched in the first row. The user specific rows are underneath, with Jordan and Rebecca given individual access to the one row. Here’s the resulting hybrid security calc within Tableau:

You can imagine that taking care of the individual users in one fell swoop can help save heaps of time in writing out what was a long security calc, especially with a high cardinality dimension at our lowest security level.

Advanced Solutions: Groups, CONTAINS(), and FULLNAME()

If you have read this far, you are probably well aware of how fast Tableau Data Extracts are; it’s the reason you are trying to implement Row Level Security on them rather than your existing data infrastructure. They are so fast that some solutions which would never be recommended in a database are actually just as efficient (or more) than techniques that resemble more traditional database techniques.

EDITOR’S NOTE 2018-05-17: At the current time, it has been determined that the CONTAINS() solution below performs very poorly in the Hyper extract system in all versions of Tableau 10.5 and 2018.1.0. If you are currently using it in a pre-10.5 version of Tableau, please test thoroughly before attempting any upgrades. The performance issues have been corrected in the 2018.1.1 release, but will not appear in any version of 10.5. If you are currently using it in a pre-10.5 version of Tableau, please only upgrade to 2018.1.1 and after.

The CONTAINS() function

The simplest high-performance method that does not duplicate any rows is to include the list of all allowed usernames in a comma-delimited field in your data, then look for it using the CONTAINS() function:

CONTAINS([Security Users Field], USERNAME())

If your [Security Users Field] looks like ‘Matt,Bryant,Jordan’ (or is much much longer), this will work, and it is lightning-fast. This is totally counter-intuitive, in that you would never do a string search calculation in a database, but the Tableau Professional Services team assures me that this their preferred method.

Obviously, getting all of the names you need into a single column will require some SQL magic on your part, in the database, to flatten the security table into single rows of delimited values. If that isn’t possible, there are even more solutions using some of Tableau’s other features:

USERNAME() solutions

As we’ve seen with many of the aforementioned solutions, a major difficulty for group security lies in the ISMEMBEROF() calculation, which requires a string literal as an argument within, instead of a call to a column within the data. If we could write a calculation like ISMEMBEROF([Region]) where a user would belong to a Tableau Server group that matches up to a value in the Region column, then these multiple line calculations would be unnecessary. We can’t do this however, since the ISMEMBEROF() check happens before Tableau even looks at the data source that we’re connecting to.

The question then becomes: Is there anything that we can dynamically call on to check someone’s roles/access in a more automated fashion?
Thankfully the answer is yes, and the Tableau Professional Services team has some wisdom surrounding it. Before we dive too deeply into this solution, this will be applicable to Servers set up to provision users with Local Authentication and mainly for an embedded/OEM use case where users will NOT be accessing the actual Tableau Server Portal.

The crux of this solution is being able to call on the content that we’ll store in the Display Name field on Tableau Server. This can be arbitrarily populated for each user and even programmatically set using Tableau’s REST API. The data stored here can be called upon dynamically using the FULLNAME() function within Tableau Desktop and compared to values within the original dataset.
As always, this is best shown with an example against some simple sample data:

This is an example where one user will be filtered for one particular dimension that’s in the dataset. User 1 has access to two advertisers in the data, user2 has access to a specific campaign, and user3 can see three subnetworks. These are all dimensional fields within the dataset itself, which we’ll begin to compare these values to.

You can understand why the embedded scenario is one that lends itself more to this use case, as you wouldn’t want to sign into Tableau Server and see “advertiser | ‘19707’,’20307’” in the top right corner as their username. Also, these values are editable by users within the Tableau Server Portal, and we certainly wouldn’t want someone defining their own access to the data. I digress.

One of the best parts about this solution is absolutely NO joining onto security tables. All user security is set up within the Display Name of Tableau Server and taken care of with a few simple calculations within Tableau. Each data source you want to secure is going to need to contain these calculations:

A. Hierarchy Filter Level (Which dimension are we going to filter on?)

TRIM(LOWER (SPLIT(FULLNAME(),”|”,1)))

In the above example this would return advertiser if user1 were signed in

B. Hierarchy Filter Values (Which values within that dimension can this user see?)

IF NOT ISNULL([Hierarchy Filter Level])
THEN SPLIT(FULLNAME(),”|”,2)
ELSE NULL
END

Against the example, this would return ‘19707’,’20307’ if user1 were signed in

C. Hierarchy Filtering Field (returning the actual value in the dataset itself)

CASE [Hierarchy Filter Level]
WHEN “subnetwork” THEN “‘”+ STR([Sub Network Id]) + “‘”
WHEN “advertiser” THEN “‘” + STR([Advertiser Id]) + “‘”
WHEN “campaign” THEN “‘” + STR([Campaign Id]) + “‘”
ELSE NULL
END

Against a datasource this would return the actual value in the data for the desired dimension. If user 1 were signed in, Hierarchy Filter Level would return advertiser, therefore making this calc return the Advertiser ID wrapped in quotes to compare to Calculation B,. which are the values that define the individual’s access.
D. User Filter

ISMEMBEROF(“See All Network”)
OR
CONTAINS([Hierarchy Filter Values],[Hierarchy Filter Field])

The final step is to compare the values in calculation B to those in calculation C. The CONTAINS() function takes care of that, with the ISMEMBEROF() giving access to those who can see the entirety of the data. Filtering to the TRUE values in this instance will give you a clean and maintainable Row Level Security solution. These calculations need only change when the filtering philosophy as a whole will change, since all new users that are added to the system will have their attributes passed to calculations A and B automatically.

Multiple field access

The case where a single user’s access will be defined by more than one field is taken care of in similar fashion. Defining the dimension within the Display Name field is eschewed for a simple pipe delimited list of the values that someone has access to, let’s learn from example:

We see that the notes here help us figure out what the necessary calculations are:

A. Filter Values: Network

SPLIT(FULLNAME(),”|”,1)

B. Filter Values: Advertiser

SPLIT(FULLNAME(),”|”,2)

C. Filter Values: Campaign

SPLIT(FULLNAME(),”|”,3)

D. User Filter

ISMEMBEROF(“See All Network”)
OR
CONTAINS([Filter Values: Subnetwork],”‘” + STR([Sub Network Id]) + “‘”)
OR
CONTAINS([Filter Values: Advertiser],”‘” + STR([Advertiser Id]) + “‘”)
OR
CONTAINS([Filter Values: Campaign],”‘” + STR([Campaign Id]) + “‘”)
The philosophy is largely the same, only we don’t have to define the CASE as we did in the simpler example, since each CASE is now broken out into a different calculation. This also allows us to combine some steps and get to our finished User Filter a bit faster.

Even as we go through these more advanced examples, you can get the sense that there’s no silver bullet for creating row level security with extracts. It’s likely you’ll bring together a few of the techniques in the past two posts to create the exact solution that would fit your needs, but these are the current best practices for the real world scenarios that we have encountered with Tableau customers. I’d encourage everyone to visit the Tableau Ideas forum to upvote this specific ask of enabling dynamic calls to the dataset using ISMEMBEROF() and have your voice heard on any other ideas you might have to make this process easier.