How to Successfully Implement Tableau’s Row-Level Security

Before I even get started, first I want to give a shout out and a big THANK YOU to Chris Beck – a Tableau Technical Sales Consultant here in Atlanta. He was the one that had this idea and recommended I give it a shot. Row-level security is critical in my field of working with Human Resource data, so I really appreciate his help on this.

Before we dive into the details, let me first convince you of some of the benefits of using this approach.

You can easily retrofit existing workbooks. You don’t have to make a single change to an existing sheet or dashboard. This is very convenient because it allows you to design and build your workbook without worrying about security and then add it in at the end.

It’s fast. This technique allows you to maintain data extracts for your main data source – which means you get all the performance benefits that go along with extracts.

It is very extendable. We have used this technique with 7 different types of security – division level, department, even down to person level and more. All of them work well. It is very easy to add a new type of security – just make sure you have the proper security map and then follow the steps outlined in this article to apply it to your workbook.

The Problem

Summary: How can I apply row-level security using a live connection to a security table and an extract for my data set?

I manage the HR Reporting & Analytics team at Cox Enterprises, and a part of my team’s job is to build reports/dashboards that can be deployed to a broad user base. Like most large organizations, we have somewhat complex security rules around who can see what… and whom they can see it for. Fortunately, these security rules are most often defined in our source applications. We were able to create a security map that defines what keys a user has access to, and through that we can define various types of security and leverage the same security map to secure our reporting.

Let’s look at an example with the Superstore dataset. This dataset has the following fields:

 

Next we need to define a security map. Now our organization has built a custom table and process to load this map, but in this example we’ll create a simple one in Excel. Let’s say we have two different users – Hal, who should only have access to the Corporate segment, and another, Chris, who has access to all segments: Corporate, Consumer, and Home Office. For this example I’ve chosen to implement row-level security based on the segment, but that is arbitrary… you could implement security for any key you’d like: by department, region, etc.  Anyways, for this example the security map would look like this:

 

Although in this example the Security Map is small, in reality this table can become quite large. Currently we have 500+ users with a combined count of ~92 Million security keys.

Given the size of that table, it is unrealistic to create an extract for it – instead, we want connect to this table with a live connection, filter on the rows for the logged in user, and apply that security to our extract of the Superstore dataset.

The Solution

Summary: Use Cross Data Source Filtering to apply row-level security to our extract while maintaining a live connection to the security map.

Next we will walk through how to apply this row level security to the Superstore dataset.

1.) First we will make sure our data set is an extract. Although this is not a required step, for us this was a big part of the value of this approach. Our data sets are often large, and being able to store them as an extract significantly improves the performance of the workbook.

 

 

2.) Next we will add a second Data Source for our security map. I loaded our simple security map into a table in an Oracle database for this example. It is important that each user only have a unique list of security keys for each security type. In other words, you wouldn’t want ‘CSHORT’ to have two rows for ‘Corporate’ in the example below. This is because when you apply the cross data source filter it essentially invokes a join. Having multiple rows will cause the results in your main data set to cartesian and may cause incorrect results.

 

 

We will leave this connection as a “Live” connection:

3.) Next we want to limit the results of the Security Table based on the user that is logged into Tableau. We will do this by applying a data source filter.

Select “Add” and pick the “Username” field. We will add a filter based on a Condition. The formula compares the username of the logged into Tableau user using Tableau’s delivered USERNAME() function. Both sides are wrapped in UPPER() to make sure that both sides are in the same case.

 

 

After clicking “OK” I can see that the filter is working because it is only keeping my username:

 

 

4.) Now we can prepare our data set to have the security applied.

In this example we have decided to secure the data set by segment. This means that the security map tells you which user has access to which segment. For this reason, we need to set segment as the security key for our data set (the extract). Select the Superstore data set, right click Segment, and create a calculated field:

 

 

It is critical that both your dataset and your security map have the exact same data type and name for the security key – this includes the capitalization of the field. In this example we are going to use “Segment” as our security key, so we’re just creating a copy of that field and labeling it “SCRTY_KEY”.

When Tableau imported my security map table it actually re-labelled the field “Scrty Key”, so went in and renamed it “SCRTY_KEY” so that it was consistently named in both my Superstore data source and my Security data source.

 

 

5.) Apply the cross data source filter by creating a new sheet dropping a count of records onto the sheet and adding the SCRTY_KEY as a filter and selecting “Use All”. Make sure that when you do this you use the Security data source. Finally apply the filter to “All Using Related Data Source” – this is the secret trick that will cause the two data sources.

 

 

6.) I can check that it is working because I see that my user has access to three rows, as expected:

 

 

When I switch the user over to HAL I see only the one row from the security table – so far, so good!

 

 

Finally, we switch to the “Overview” tab and while switching between the two user accounts I can see that the security is being successfully applied:

 

First as CHRIS:

 

And next as HAL (notice that only one segment shows up)

 

Conclusion

We have been using this type of row-level security successfully now for a few months, and it has proved very useful. Now that we’ve walked through an example, you can understand how this technique could be used to retrofit security to an existing workbook. The main benefit for us was finding a way to implement this security on-top of an existing data extract – which allows us to maintain the performance benefits of the extract. Finally, this technique is flexible because we can easily expand the types of security that we want to apply to fit our varying requirements.

However, there are a few limitations I’d like to discuss:

1.) You need a security map. This was not a big deal for us because we had already developed the security map prior to trying to implement the row-level security in Tableau. However, if you’re starting from scratch this may be a bigger consideration. Loading and maintaining the security map could become tedious if you don’t have the right technology to support it. Also, if your security map is going to grow to a large size, like ours, you’ll need to make sure it is properly designed. Our security map is partitioned by user – and since we only join to it for a single user at a time, this allows it to scale as we add more and more users to our environment. We also operate in an environment where accounts are driven by Active Directory, which allows us to have the mapping from Username to the security keys. These are the same usernames used to log into the Tableau server.

2.) You have to apply the security at the workbook level. One thing I wish I could do is to publish a “secured” data source to our user community that they could leverage to build their own visualizations. However, the way this technique works you have to actually apply the cross data source filter in the workbook to make the security work. This means you can’t just publish the data source separately for others to use. For smaller data sets we have been able to publish live connections that are pre-joined to the security map, but this is not possible for extracts.

In addition to limiting our ability to publish the data source, this limitation also means that you have to apply the security condition on every workbook you develop. This isn’t a big deal, since it is a quick process, but it would be nice if this type of row-level security definition was a part of the data source setup in Tableau to help make the process less of a “hack”.

3.) Performance Considerations – Although I have timed running various workbooks using this approach and compared that to other techniques we have used, I have not done extensive performance analysis on how applying these cross data source filters impacts the performance of the queries that are executed. This is an area I would like to explore more carefully in the near future.  Obviously, workbooks with this type of row-level security applied are slower than ones without it – but to me it seems the biggest impact is on the initial load of the workbook. Clicking from sheet to sheet or applying filters does not seem to re-issue the security query. This is another detail I’d like to explore further at some point in the future.

Hopefully this technique is useful for you! Please leave a comment if you have feedback or questions, I’d love to hear how others are solving this type of challenge.