Row Level Data Security in Tableau - Part 2

Introduction

This is part 2 of implementing Row Level Data Security in Tableau. In the previous article, I explained how to implement Row Level Data Security in Tableau - Part 1 using security configuration kept in Tableau Server Groups. I'll recommend to go through the previous part first.

In this part, we'll implement Row Level Data Security in Tableau using security configuration partially kept in database.

Business Requirement - Section 2 - (Security Configuration Partially kept in the database)

Let’s now extend the requirement for restricting access at the data attributes level. We have a database field named ‘Field Category’. Since we have data related to profit and loss statement, following is data in the field category column in the database.

• Sales
• Cost of Sales
• Gross Profit
• Branch Direct Expenses
• GO Direct Expense
• Dept. Operating Margin
• Operating Margin
• Operating Profit Before Fin. Cost/ Income
• Financing Cost/ Income
• Operating Profit After Fin. Cost/ Income
• Group Overheads (Management Fees)
• Net Profit
• GO Direct Expense (HO)
• Group Overheads

We have four different levels of organization managers access levels (President, Line Manager, Branch Manager, and Branch Department Manager) which is already discussed above, now the business wants these field categories to be viewable based on the user organization manager level.

Field category based Access control matrix contains the categories which should be visible to each level:

Translation of the above matrix is as follows:

• President: He should be able to see all branches, all department for all field categories.
• Line Manager: He should be able to see only his department across all branches for field categories from Sales to Operating Margin.
• Branch Manager: He should be able to see all departments for his branch for field categories from Sales to Net Profit.
• Branch Department Manager: He should be able to see his department for his branch for field categories from Sales to Operating Margin.

Solution Concept

Since this requirement is an extension to the requirement mentioned in Business Requirement in section1, we shall use all the work which was previously done like we had created server groups, assigned users to respective server groups and then we had created a calculated field which was used as a filter in the tableau sheets to restrict data. Such configuration is important w.r.t Tableau Interviews as well as Tableau Certifications, so check out the tableau certification dumps related to security and more topics.

Now we shall follow these steps to achieve this enhanced requirement:

• Create an Access Level Field in the database
• Update the User Filter
• Test Access Control

Create an Access Level Field in the Database

Since we have four levels of access control, we shall create a character field of length 4 in the database and each character will represent the access level allowed. Every record coming from the database will bring information about these columns.

Following is the format of this field XXXX along with its translation.

Possible combinations of this access level field will look like this. An empty cell means a particular level cannot access the data. The following screenshot explains the access control for all combinations.

We have provided the sample data in Excel file for better understanding and clarification.

Update the User Filter

We shall use ACCESS_LEVEL database field in the calculated field of user filter to identify the allowed access level and accordingly write the logic.

For example, now we shall modify the already written user filter calculated field by duplicating it and writing the logic for president level as follows:

We have added one condition checking the first character of the database field ACCESS_LEVEL. If the first character is ‘E’ and the logged-in user is a member of PRESIDENT group, then it returns 1 else it returns 0.

In this way, data is controlled based on membership of the user in server groups along with the allowed access level of the record based on ACCESS_LEVEL. In short, with the help of ACCESS_LEVEL field, data access will be controlled based on field category level as well.

Once we write the code for all levels, the User Filter logic looks like this:

After we update the code of user filter calculated field, apply this field in the filter pane to filter the data access as shown below:

Test Access Control

Once user filter is applied, it's time now to validate the logic for all four levels.

Checking Data Access for President

User Muhammad is President and should be able to see all branches and departments and he should be able to see field categories. In order to check his accessible data, let’s select user Muhammad as shown below:

It’s clear from the above screenshot that Muhammad is able to see all branches and all departments across the company and he is also able to see all field categories, i.e., all combinations in ACCESS_LEVEL fields where the first character ‘E’ is viewable to the president.

Checking Data Access for Line Manager – Service

User Wasim is Service Line Manager and should be able to see all branches across the company for the Service department. Regarding the field categories, Line Manager is allowed to see the field categories from Sales to Operating Margin.

In order to check his accessible data, let’s select user Wasim as shown below:

It’s clear from the above screenshot that Wasim is able to see all branches for the Service department and all field categories where the second character of ACCESS_LEVEL field is ‘L’ are viewable to Line Manager.

Checking Data Access for Branch Manager – Sharjah

User Jeffry is Sharjah Branch Manager and should be able to see all departments in Sharjah Branch. In order to check his accessible data, let’s select user Jeffry as shown below:

It’s clear from the above screenshot that Jeffry is able to see all departments for Sharjah branch and all field categories where the third character of ACCESS_LEVEL field is ‘B’ are viewable to Branch Manager.

Checking Data Access for Branch Department Manager – Sharjah Service Manager

User Manoj is Sharjah Service Manager and should be able to see only the Service department in Sharjah. In order to check his accessible data, let’s select user Manoj as shown below:

It’s clear from the above screenshot that Manoj is able to see only Service department for Sharjah branch and all field categories where the fourth character of ACCESS_LEVEL field is ‘D’ are viewable to Branch Department Manager.

Conclusion

We have checked all the four levels one by one and saw that data is viewable as per the configuration done in ACCESS_LEVEL database field.

Although this could also be handled by using the server groups, it could be more tedious and risky since any addition of new field category will require to add new server group and all IF conditions would have to be reviewed in that case. So in such scenarios, it is recommended to go ahead with a hybrid approach where more consistent logic could be placed in server groups whereas configuration which can be changed with time could be placed in the database level.

History

• 25^th June, 2020: Initial version