This Tableau Security article is part-2 in series of “Data Security in Tableau” articles. We strongly recommend to checkout our tableau certification preparation guide first. In the previous article, we discussed a step by step approach to implement security done solely in Tableau Server. Here in this article, we will use another approach i.e. Hybrid approach (security done both in database and Tableau Server).
Security done through Hybrid Technique (Using Tableau Server and Database)
How to do it:
This is done by creating a data source level filter in the data source. In this technique, configuration or user access is defined in the database.
Requirement Statement:
We have super store data and we want to give access to users based on Customer segment. We have two users in our Tableau Server. One user should be having access on 2 customer segment whereas other user should be able to see other two customer segment. Admin User should be able to see all 4 customer segments.
In this approach we have created this access matrix in excel file, which you can do in a database table. Access matrix looks like this:
Step by Step Solution:
Let’s connect to Tableau Server and see the users in our Tableau Server. We have a site TEST which has the following 3 users.
Open Tableau desktop and connect to Super Store excel data source.
After connecting to excel file of Super Store, Drag Order Sheet to query the data as shown in the figure below:
Using Tableau Desktop, connect to Tableau Server site which contains the users:
After connecting to the site, you will be able to see all the users in that site at the right bottom corner as shown below:
Drag customer segment on the rows shelf, clearly seen there are 4 customer segments in the data.
In order to create the security, Go to data source and click Add button on the data source:
This will open a dialog box to open excel file which contains the database configuration.
Select the user access matrix excel file
It will add the user matrix file in the same data source and will look like this:
Make sure that both Orders and Sheet 1 are joined based on Customer Segment
Click on Add Filter button
And add the following condition:
After clicking on Add button, select the ‘User Name’ field
Go to Condition Tab, select the ‘By formula’ Option and write the following condition:
USERNAME() =[User Name]
In the above condition, USERNAME() is the Tableau function, which contains the login of the user and [User Name] field contains the User from the User Access Matrix excel sheet.
By writing above condition, data sources filters data based on user and customer segment is filtered due to join condition created at the data source level.
Test and Validate the Security:
In order to validate the security, Tableau has given the user selection option from the bottom.
Select the user Arun in the list of user from the bottom as show below:
Once you select User Arun, It will show his respective two customer segements in the view:
Now select the user Syed and it will show his configured customer segments
Now select the Admin user for which we had configured to show all 4 customer segments
This is the way, we can restrict the data based on configuration in the database. In this example, we have taken excel file instead of database table, But same user access configuration can be maintained in the database. Also get access to our data blending tutorial here.
Limitation of this Technique:
This approach has a limitation that somebody should be having access on the database table, who can edit the users access in case of any changes. Probably you have to depend on DBA or you have to create a small interface to manage this configuration table.
Reference Files:
