There are a number of great articles that explain how to enable row-level security inside of a workbook (see here, here, and here), but I haven’t come across any that show how to do that inside of a TDE (i.e. a published Tableau data source). While setting up row-level security in a workbook is useful, that model falls apart very quickly when you want the same security to persist across more than a handful of workbooks.
Why would we want to have the security handled in the TDE vs. inside each workbook? For us, there were three key reasons:
- We wouldn’t have to replicate the same user filters across every workbook that pointed to the TDE. We expect to have dozens of workbooks (if not more) pulling from the same TDE.
- We could allow users to create their own workbooks against the TDE using web authoring but ensure that they only saw the slice of the TDE they were supposed to.
- Any changes to the security model would only have to be implemented inside of the TDE without having to make any changes to the connected workbooks; they would just inherit the changes.
Setting it Up
After some experimentation, the solution ended up being pretty straightforward and simple. These are the steps we took to make it work:
1. Identify the users and determine what level of data security they need. For us, there are two types of users – (a) users who are assigned to specific customers (i.e. they can only see data for those customers), and (b) users who are assigned to one or more regional LDAP groups that our Tableau Server uses for authentication (i.e. they can see all data within those regions).
2. For the first group of users, we added their employee IDs to the TDE. Since we use Alteryx to generate the TDEs, this involved adding a join between the main data set and a secondary data file from Salesforce.com containing the relationships between these users and their customers (e.g. a customer number mapped to an employee ID).
A zoom on the actual Join portion of the workflow. We joined the main data stream to the account mapping table based on customer code.
By creating this join we definitely increase the size of the resulting TDE (i.e. from roughly 1.5M rows to just under 5M rows), so this method may not perform well on massive data sets but we saw no performance degradation (and Alteryx generates the whole thing in under 30 minutes). This is also why we only add in the employee IDs of those users who are assigned to specific customers; all other users (i.e. the second group described above) are handled via their LDAP group entitlements and the ISMEMBEROF function shown in Step 3 below.
3. Connect to the TDE from Tableau Desktop and create a calculated field that determines whether the authenticated user belongs to one or the other group and then filters the data accordingly. You want this calculation to evaluate to either True or False.
4. Add this calculated field as a data source filter (right-click on the data connection and select Edit Data Source Filters), selecting “True” as the only value to keep. Then publish the data source to Tableau Server.
Now when I open, create or edit any workbook pointing to that data source, the data is filtered for my credentials without the workbook containing any user filters or blends with secondary data sources. This also prevents people from removing these user filters from the downloaded workbooks and thereby circumventing the security model.
While applying data security at the database level and connecting to it live is arguably a more robust solution overall, that approach certainly takes longer to set up and may perform slower than a TDE unless significant effort is employed to optimize those queries. The method I described in this blog post allows us to take advantage of the myriad benefits of Tableau extracts while still delivering data security via a very easy-to-maintain model.