How to Use Row Level Security in Tableau
Introduction:
A Tableau dashboard can be set to restrict the data as per the permissions allocated to users. For example, while an employee can see the data just related to his job, a manager can be able to see much more data to cover multiple employees or multiple teams. This is called row-level security, and often Tableau developers have to address this issue when building dashboards for multiple users. There are several ways to achieve this row level data security in Tableau. Some of the most common use cases are discussed in this blog.
How to Achieve it:
1) Manual User Filter
The most common method of restricting data is by mapping data manually when publishing it on the Tableau Server (or Tableau Cloud). Let us assume a data set which needs to be assigned to a few users based on their regional affiliation as shown below.
From Tableau desktop, sign-in to your Tableau Server (or Tableau Cloud) and from the top menu Server > Create User Filter > choose the desired data field to create a user filter.
A popup window will appear, where you can restrict individual users based on their affiliation.
Once the user filter is activated, a field will appear in the data pane as shown below.
Using this filter, it is possible to create permissions of a desired level and data will be filtered automatically based on the user profile.
This technique is easy for implementing row level security when only a few users are concerned. However, in most of the cases, it is time consuming to manually map all the users and long term maintenance of the workbook becomes tedious.
2) Dynamic filter using an external join
In this technique, as the name suggests, an external table is created and joined with the main dataset. The external table, which is easy to maintain, will contain a list of usernames and their corresponding country affiliation.
Joining the main data with this external table will create a large dataset which can have duplicate rows but when the user filter is applied, the data will eventually get filtered out on the row level. In this case a dynamic filter needs to be created as a calculated field as shown below.
Use this as a filter in the dashboard, and choose only the “True” values.
By controlling the external table, the data can be restricted dynamically and users will have access to only their relevant data. Joining the external table with the original data source may result in an extremely large dataset, and depending on its volume and number of users, there may be some performance issues.
3) Filtering the Data at Source
Using the previous technique, the user filter can be enabled before the data is imported by Tableau. As mentioned in the previous case, the user filter USERNAME() = [User].
When connecting to the data, the data source page has an option to add filters. Clicking on the “Add” button will show a popup window, where you can select the column for applying the filter as shown below.
Choose the “Condition” tab and use the formula to permit row level data for users. Enabling this filter at the source level, the data will be filtered and displayed as per individual security settings.
Enabling this filter at the source level, the data will be filtered and displayed as per individual security settings.
Precaution necessary in this scenario, is to publish the data source separately and not to be embedded in the workbook. Because if the data source is embedded and if a user has permissions to web edit or download the workbook, then the row level security becomes nonexistent and the filters can be removed easily.
4) Dynamic filter using Data Policy
The data management policy in Tableau Server and Tableau Cloud allows users to implement row-level security through data policies on virtual connections. This technique uses virtual connection which allows each connection having its own extract and security management policy thereby row level data security can be controlled seamlessly.
A data policy has three main components:
(A) The tables it applies to are called the policy tables. These tables are filtered.
(B) A policy column is the column used to filter data. By creating a data policy, a column needs to be specified to filter the data.
(C) The policy condition is an expression that is evaluated for every row at query time. This expression is a user function, such as USERNAME() or FULLNAME(), when TRUE, the row is shown in the query.
More on Data policy can be found here.
5) Impersonation when accessing the database
Some databases such as Microsoft SQL Server can be configured with row level security based on user permissions. When connecting to such databases from Tableau, the data connection can be set-up with “impersonation.”
To enable row level security, the data source is published on Tableau server with the admin credentials, and any user who can access the published data source in Tableau Server will access it through the impersonation option which will automatically restrict the rows based on user permissions.
Summary:
Several techniques are discussed here to enable row level data security in Tableau. Depending on the use case, data can be restricted in Tableau by one of the few available methods.
