Use row level security in Tableau to control who is able to see what data within a Tableau dashboard and Tableau data source. I tend to view permissions / data visibility in Tableau in two ways.
- What projects / workbooks / dashboards are visible to the user. This can provide a better user experience – only permit the user to see what is relevant to them
- What data is visible to a user within a dashboard
Row level permissions, and hence this post, refer to #2 – controls what a user can see when opening a dashboard or connecting to a published data source.
Activate row level security using a calculated field. Tableau functions can check who is logged in and if that logged in user belongs to any Tableau Server groups. The relevant Tableau functions are USERNAME() and ISMEMBEROF.
This information can control the rows of data a user is able to view.
Tableau Row Level Security using ISMEMBEROF
Firstly, the more straightforward row level security; using Tableau Server groups and the Tableau ISMEMBEROF function.
ISMEMBEROF checks if the user is a member of a specified Tableau Server group.
For example, perhaps there is a division who can only see Furniture sales. Create a group on your Tableau Server / Online for users who can see furniture sales; call the group “Furniture”.
Using the Tableau Superstore dataset (see your My Tableau Repository – Datasources), which contains Furniture sales within the Category field, use ISMEMBEROF and create a calculated field to control who can see the Furniture category sales:
[Category]="Furniture" AND ISMEMBEROF("Furniture")
This is a boolean calculated field; it returns True if the user is a member of the Furniture Tableau Server group AND the Category is Furniture. Otherwise it returns False.
Use the calculated field as a data source filter and set to True. You have now activated row level security in Tableau!
Note, you don’t have to put the calculated field as a data source filter, you can use it as a normal filter should you only want it to impact specific worksheets.
Personally, when working in an environment with published data sources, I always publish the data source with the row level security activated. This enables users to browse the data source, using the Edit functionality, without being able to see data they’re not permitted to see. With embedded security, the user doesn’t have any way online to remove it. This is particularly useful if you have any self-service analytics in place for users. (Disclaimer – if they can download a published data source they will be able to remove the security filter.)
As well as securing data, this also improves Tableau performance, as it reduces the amount of data to process for each user. If the data source contains 1m records, but the user is only allowed to see 100k, Tableau only has to render 10% of total records, which is far quicker.
Tableau Row Level Security using USERNAME()
To check what the logged in user is allowed to see, within the data source the username of the user must appear alongside the record. There are two ways to do this, but one performs better than the other (but has other disadvantages…).
As with ISMEMBEROF, use calculated field, but this time with the USERNAME() function. The USERNAME function returns the Tableau username of the logged in user. This probably isn’t their actual name; it’s the name by which Tableau recognises them, which could be a code, a number, or their actual name. In Tableau Server, assuming you have permission, go and check the Users to see the name Tableau uses in your environment.
To begin, relationships are the best performing way to implement granular row level security.
Tableau Relationships for row level security
The introduction of Tableau Relationships made this technique possible. Tableau Relationships allow joining of different data sets to create a data source. For the case of row level security, this means joining a security table to a table of data.
For example, using the Orders data from the Superstore spreadsheet as the main data table, with a security table to control what a user is able to see, such as:
Join this security table in the Tableau data source using Tableau relationships.
Next, create a calculated field to check the name of the logged in user:
USERNAME()=[Name]
Add it as a data source filter, set to True, and the user will only see those records in the dashboard.
The filter combined with the join removes all records from Orders which the user isn’t allowed to see. Using the above example, user “bsmith” will only see records in the Furniture category, all other categories would return False in the calculation, so Tableau disregards them.
This technique provides far better dashboard load times compared with the “permissions-string” method outlined below.
The downside is with data blending. A limitation of data blending using data sources built with relationships is it can only be the primary data source in a data blend. Using a data source built with relationships it will error as a secondary source.
Using a string of usernames for row level security
This technique has been around for a long time. First written about on this site back in 2015 (see option 2 in the original post), it still has value, especially if using a pre-relationships version (2020.1 or earlier) of Tableau.
This method requires some database / ETL work. All users permitted to view a row of data should have their Tableau username added to a delimited string of other users able to see the record. Delimiting by something little used in either usernames or as a delimiter, such as a pipe (|) or dollar sign ($), is advised.
Your string is likely to be something such as:
|bsmith|jwilson|charper|bpitt|cronaldo|
The string should be an additional field in the data source and be against every record. This usually requires some database work to build. For example:
The calculated field to use this string checks if the name of the logged in user appears in the list of usernames. If it does, it returns True, otherwise False.
CONTAINS([VisibilityString],"|"+USERNAME()+"|")
To implement row level security, add this field as a data source filter and set to True.
The performance of this is not as good as using the Relationships, however, this technique does allow flexible data blending.
Combining Tableau User functions for robust row level security
The functions for row level security are described in isolation above. The reality tends to be more nuanced.
Often both ISMEMBEROF and USERNAME() are combined to provide more granular control. For example, it could be members of a “Furniture Management” Tableau Server group can see all Furniture records without restriction, but other users are controlled by their username.
A calculated field can test all conditions:
(ISMEMBEROF("Furniture Management") AND [Category] = "Furniture") OR USERNAME() = [NameField]
Use Tableau user functions and Tableau server groups to secure your Tableau data sources.