One of the biggest problems you’re going to run into while publishing to Tableau Server is how to deal with data security. Implementing Row-level security isn’t quite the most straight-forward thing to do within Tableau, but it is possible. In fact, Tableau has an option called “Create User Filter” to try to make this easier. However – that built in feature is very manual and only works for a small group of people or very specific circumstances. Chances are you are going to need something a bit more advanced.
There are several different types of row-level security that you may want based upon the business need. See below for our agenda today.
- “I want a dashboard that filters for data related directly to the user viewing the dashboard.”
- “I want a dashboard that filters for data related to a particular user’s group, LOB, etc.”
- “I only want users within a particular AD group to see data on this dashboard.”
- Final thoughts: Security Check
The Tableau packaged workbook for this file can be downloaded here.
Method #1: “I want a dashboard that filters for data related directly to the user viewing the dashboard.”
Assumptions:
- You have access to Tableau Server
- There is a function within Tableau called USERNAME() that returns the username of the person running the dashboard on the server. If you have an employee table in your database that mimics the username on Tableau server, you’re good to go.
Example of my user table:
Example of the USERNAME() function within Tableau when logged into the Tableau Server as myself:
Step 1: Determine main table structure
No matter what you decide, you must be able to map back a particular user to a single row of data. If you’re dealing with operational data such as a ticketing system (where a user may be in an “assignee” field or the like) then you may already be good to go.
For example:
However, if the username is NOT already within your data – you’ll need to create a security table to map users back to the main data – OR – map based upon another dimension that exists both within the user table and the main data. A good example of this might be Line of Business (Covered in Method 2) or Location. It all depends how granular your row-level security needs to be.
In my example, I’ll be reporting on a ticketing table where users enter in problem tickets for the helpdesk or other departments to solve.
Main Data:
Armed with each table – I can now join them together within Tableau to produce visualizations of all ticketing information. First, we show everything without any filtering.
Step 2: Join Main Data to User Data on the User Identifier
Step 3: Construct User Filter in Tableau
Construct a T/F calculation within Tableau as shown below where the function USERNAME() equals your user identifier dimension.
Now add that filter into the Data Source Filters where it equals True. Note we are NOT adding into individual sheets but rather in the Data Source Filter as it’s more secure and doesn’t rely on a developer accidentally removing from a single sheet – OR – the user removing it themselves through the Tableau Server Edit function (if you have that enabled).
Note again that you MUST be signed into the Tableau Server for this to work – otherwise your USERNAME() function will not return, causing a blank dashboard.
One logged-in, you will be able to see the workbook has filtered for you only:
Method #2: “I want a dashboard that filters for data related to a particular user’s group, LOB, etc.”
Assumptions:
- You have access to Tableau Server.
- There is a function within Tableau called USERNAME() that returns the username of the person running the dashboard on the server. If you have an employee table in your database that mimics the username on Tableau server, you’re good to go.
Example of my user table:
Example of the USERNAME() function within Tableau when logged into Tableau Server as myself:
Step 1: Determine main table structure
No matter what you decide, you must be able to map back users to rows of data.
For example:
In my example, I’ll be reporting on the Budget vs. Actual by
Line of Business – which does not map back to a particular user, but rather on the
user’s Line of Business.
Main Data:
Armed with each table – I can now join them together within Tableau to produce visualizations of all ticketing information. First, we show everything without any filtering.
Step 2: Join Main Data to User Data on the User Identifier
You’ll notice the calculations on the dashboard appear incorrectly – and this is an important distinction when using row level filtering. The need to join a user table to main data often causes duplication. Once you implement a User Filter within the Data Source Filter these duplications should disappear as it’ll now be displaying a single users’ data (happens in the next step!). However – if your unique setup appears to still duplicate even after the filter, you may need to use a combination of LOD filtering to fix (not something to worry about in this tutorial).
Step 3: Construct User Filter in Tableau
Construct a T/F calculation within Tableau as shown below where the function USERNAME() equals your user identifier dimension.
Now add that filter into the Data Source Filters where it equals True. Note we are NOT adding into individual sheets but rather in the Data Source Filter as it’s more secure and doesn’t rely on a developer accidentally removing from a single sheet – OR – the user removing it themselves through the Tableau Server Edit function (if you have that enabled).
Note again that you MUST be signed into the Tableau Server for this to work – otherwise your USERNAME() function will not return, causing a blank dashboard.
One logged-in, you will be able to see the workbook has filtered for you only:
Method #3: “I only want users within a particular AD group to see data on this dashboard.”
This is a rather unique use-case which I’ve used heavily in the industry. You either must have the AD group within Tableau Server – or – you must have access to a table that can map AD groups back to users within your organization.
Assumptions:
- You have access to Tableau Server
- There is a function within Tableau called USERNAME() that returns the username of the person running the dashboard on the server. If you have an employee table in your database that mimics the username on Tableau server, you’re good to go.
- As stated before – You either must have the AD group within Tableau Server – or – you must have access to a table that can map AD groups back to users within your organization.
Let’s first solve the problem as if everything is simple, then I’ll show you what to do if not.
Example of my user table:
Example of the USERNAME() function within Tableau when logged in as myself:
Step 1: Determine main table structure
No matter what you decide, you must be able to map back a particular user to a single row of data. If you’re dealing with operational data such as a ticketing system (where a user may be in an “assignee” field or the like) then you may already be good to go.
For example:
However, if the username is NOT already within your data – you’ll need to create a security table to map users back to the main data – OR – map based upon another dimension that exists both within the user table and the main data. A good example of this might be Line of Business (Covered in Method 2) or Location. It all depends how granular your row-level security needs to be.
In my example, I’ll be reporting on a ticketing table where users enter in problem tickets for the helpdesk or other departments to solve.
Main Data:
Armed with each table – I can now join them together within Tableau to produce visualizations of all ticketing information. First, we show everything without any filtering.
Step 2: Join Main Data to User Data on the User Identifier
Step 3: Construct AD Filter in Tableau
Now we must decide what being a member of the AD group means:
- Only users in one particular group can see ANY
data on this dashboard
- Why would I want to do this here and not in the Tableau Server? Imagine you can’t, or do not want to, separate workbooks into multiple projects. This means that you may end up with a folder where (let’s say) a “Manager” and “Team Member” both have access to dashboards in the same project. If “Manager” is controlled by an AD group you can filter others out using it. Downside is that the dashboard will go blank and not completely disappear. I’ve had to do this several times out of necessity.
- Members can see different data depending upon the group they are in
For #1, we’ll be making use of the ISMEMBEROF() function as a T/F flag with a fake AD Group.
As in both Method #1 and #2, add this filter as ‘T’ into the Data Source Filters and now the dashboard will either display data if you are in the AD group or display nothing.
Note again that you MUST be signed into the Tableau Server for this to work – otherwise your ISMEMBEROF() function will not return, causing a blank dashboard.
For #2, we’ll have to get a little more creative. If filters are simple you could use the following:
If your dataset is very large and what each AD group can see becomes very complex – you’re better off offloading this work to the database itself and creating security tables that map back to the main data, producing fields you can filter on. I’m hoping to write up how to do that in a future post as it becomes extremely complex, but certainly work showing others how to solve. It’s not a very common thing people need to do.
Lastly – I promised I would show what to do if you are not able to get the AD group into Tableau Server.
The “easiest” solution is to add an AD Membership table that can join back to the User ID
Sample table:
Through this additional table we can skip over the ISMEMBEROF() function all together and do filter on the adgroup field that can be added into the Data Source Filters.
Final Thoughts: Security Check
Some recommendations on how to ensure your row-level security is, and stays, secure.
- Make sure your filters exist in the Data Source Filters and not the sheet.
- Disable Web Edit permissions for your users/customers (otherwise they can simply remove the filters and see the data anyway).
- Impersonate your users so you can see what they will see (see screenshot below). This is an option that only shows when logged into the Tableau Server.
