User Filtering with a Security Table in Tableau Server
“User Filtering” aka “Row-level Security” is a nifty feature available in Tableau Server. It is the process of restricting the data a user can view based on who is viewing the dashboard. Simple examples would be something like…
“we want our regional managers to only see the data for the areas with which they operate”
“our brand managers shouldn’t be allowed to see sales figures for brands they don’t own”
“we want to publish information on salary data, but each individual should only be able to access their own details”
The First Way
Historically, there are two methods for implementing User Filtering in Tableau Server. The first, is a feature build into the Tableau Desktop product. Developers can navigate to the ‘Server’ pane on the toolbar and (providing they are signed in, and viewing a worksheet), select the option to ‘Create User Filter’.
Users will then select the field with which they want to apply the filter against, and then map the values to the relevant groups or individual user which should have access to said rows of data.
Once I create this user filter, I can then use the set which it creates on the filter shelf which will then restrict the view based on the user whom is logged in.
The Other Way
The above is a handy feature, but it doesn’t really work at scale. This is because you may have thousands of different values within the Region field; or there may be many levels of complexity and different role types within the organisation. In these cases, we would recommend a different approach, which is merging your datasource with a security table. This method is what I’m going to cover in this blog. However, for more information about the former method, there is a great knowledge base article available, curated by Tableau themselves.
So let’s start with a scenario.
We own a business, and within our business we have a hierarchy; alongside our hierarchy, we also have an array of business units. We want our sales data to be permissioned so that each individual within the business has access to the data that is relevant and appropriate (security wise) to them.
Let’s start with our Directors, these individuals make decisions across the entire business, and are also senior management, so need and have the right to access all data.
We then have our Category Managers these individuals need access to all data relating to products sold within their category.
We also have Regional Managers who’s role it is to understand maximise profit in their geographic region, and therefor they need all data relating to sales in the area they own.
Finally we have our Region and Category Subordinates who’s role it is to focus on a specifc category of products, within a specific geography.
Step 1 with this solution is to create a table (using whatever datasource format you like) of people assigned to the different roles within our business.
Step 2 is then to create a table which gives information on the fields and data within said fields that each user role should be able to see. This information doesn’t have to be stored in a separate table, but it’s easier to manage if you have multiple people in the same role. Storing the data in separate tables can also improve performance thanks to multi-table extracts.
Now it’s time to bring this all into Tableau. Let’s start by bringing our actual data into the data window, in this case, the orders table from Sample Superstore.
We should then bring in our data table which contains our roles and the values with which they should have access to.
And finally we should bring our users in…
Though we aren’t quite finished yet, we can build out a view that allows us to understand if everything looks right at this point.
You’ll see in the case of Dan Raines, who is the Regional Manager for the South area; that he has sales figures visible for all categories, but only within the south region, which is expected.
The final step is to create our user filter, this can be used on any worksheet with which we want to restrict access based on the user role.
To create our user filter we will create a calculated field, which will simply match the logged in user, against the username column in our dataset, returning a boolean result (true/false).
This calculated field can then be dropped onto the filter shelf, with the value set to ‘TRUE’.
And voila, we have user filtering that is easily scalable, and potentially completely automated should have you hold systems which hold information about roles within the business and so on.
The final step before publishing our dashboard to the server would be to create an extract of our datasource now that we know it is worknig correctly.
To do this we must navigate back to the datasource window and select ‘Extract’, we should the ‘Edit’ the extract properties, as by default the Extract will be generated into a single table, which would multiply the size of our dataset in proportion to the different user roles. We want to use a ‘Multi Table’ extract as outlined earlier in this post.
Once the extract is created, we can then go ahead and publish our datasource and user-filtered dashboards to the Tableau Server for consumption by our employees.
Before I Go
Here’s a few articles which may be of interest…