Today, organizations of all sizes suffer from a few common ailments such as compromise in confidentiality and time-management issues. How nice would it be to have a solution to show the viewers the information they need to see. Row Level Security in Tableau is that solution!
This article discusses all you need to get started with Row Level Security in Tableau and the outline of it is as follows;
- Prerequisites for Creating Row Level Security in Tableau
- Introduction to Row Level Security
- Ways to Implement Row Level Security
- Hierarchical Relationships & Composite Keys
- When is Row Level Security Built Into Your Database?
Prerequisites for Creating Row Level Security in Tableau
1. You need to create users and groups in Tableau Server;
2. You need to be connected to Tableau Server/Tableau Online because the Tableau Desktop will download the list of users and groups from there
3. Your User Filter should be on the same Data Source level according to the filter order in Tableau
♠NOTE: Embedded Data Sources are less secure and should be used only if Web Edit/Download will be disabled.
Now that you know what you need, let’s try to understand what this complex term actually means, shall we?
Introduction to Row Level Security(RLS)
Before understanding Row Level Security(RLS) in Tableau, you need to understand some terminologies, namely, Entitlement and Entitlement Tables.
An Entitlement is basically a single unique combination of Attributes that the Data View will be filtered on. These Attributes must exist in Column(s) of the Data View. It could be a geographical hierarchy of a Region, Sub-Region, and Country, or any other combination you could think of.
An Entitlement Table is one combination of attributions that the data will be filtered on. For example, filter on Region, Sub-Region, and Country(as mentioned previously) is a single entitlement. Standard database design practices mean, you seldom have a single table that fits the criteria for the entitlements table. Most data aren’t usually mapped one-to-one to a single user; very often the data security is organized either by role, organization name, or both.
A basic breakdown of the process can be explained in the following steps:
- Determine the Username
- Get your Data Entitlements for said User, just that User
- Filter your Data by those Entitlements
There are 2 main options to implement Row-Level Security:
Using only Tableau
One way to implement Row Level Security(RLS) is solely through Tableau. You can do so using:
- Live/Extract connection
- User Filter built and applied per data source
Let’s consider an example of implementing Row Level Security using only Tableau with user filters defined on Tableau Desktop. First of all, we create the users and groups required on Tableau Online.
Click on the Menu Bar >Server > Create User Filter. A list of available fields appears before you to begin your user filter form.
Also, if you are not already signed to into Tableau Server/Online you may be prompted to do so at this stage.
Your brand new user calculation appears under measures in the Data pane just like your other fields.
Using Hybrid Approach
Another way to implement Row Level Security is through a mixed approach where user information in Tableau Server or Tableau Online corresponds to data elements in the database.
- Live/Extract connection
- Leverage Data Server
- Use Data Source Filters
So, in this example we connect to both the Orders with respective sales representatives. This contains actual data for visualization and the entitlement table which is the People’s Table. Data table gets joined to the entitlement table that conatins user names and key values. The table is an instance of having components in the database that associate users to specific data elements such as Users to Regions.
We use a Calculated Field to create a User Filter that leverages this data, creating the following formula that checks if the user name matches the sales representative field.
USERNAME() = [Username]
Once this Field is created, it can be dragged to the filter shelf or applied to multiple worksheets through embedded as a Data Source Filter.
Hierarchial Relationships & Composite Keys
Most organizations have a hierarchy, and often there is the requirement that those higher in the hierarchy can access the data of all of those who report to them but not vice versa. Let us, for instance, look at the following hierarchy to understand the concept better. We have 3 levels to consider:
- CEO(Level 3)
- Team Leads(Level 2)
- Employees(Level 1)
To represent various hierarchesl of the data, one effective technique is to have a row in the Entitlements View representing the most granular level of each security filter. You will probably need to construct one view out of many different tables that store different parts of the user entitlements.
To represent this in your Entitlements View for data by department and regions, the CEO will have a row for every department and region. Each Team Lead will in turn, only have a row for department that belong to their responsibility. Each Employee under a Team Lead will probably have none.
For hierarchically complicated entitlements tables you may need to use multiple fields to join them with the main table. This is known as a Composite Key.
It can be handled in 2 ways:
- Create a Composite Key field in your Entitlements View and your Data Table. Then JOIN the two
- JOIN the Entitlements View to the Data Table for every field that makes up the Composite Key
A simple example of hierachial filters is as follows:
IF ISMEMBEROF(‘level 3') THEN 1ELSEIF ISMEMBEROF(‘Team Leads’) THENIF [Team Lead Username Field] = USERNAME() and [subdivision(entitlement)] = [subdivision(data table)] THEN 1 ELSE 0 ENDELSE 0 END
When is Row Level Security Built Into Your Database?
- In Oracle, if you set up VPD for the database users, you can use Initial SQL in Tableau. This way you can make use of the existing security filtering
- In post 2016 versions of SQL Server, you can set up Row Level Security in the database, based on the user. Tableau’s Impersonate-User-functionality will set the user correctly for you, while SQL Server does the filtering.
This brings us to the end of this article on Row Level Security in Tableau.
If you wish to learn Tableau, Edureka has a curated course on Tableau training which covers various like conditional formatting, scripting, linking charts, dashboard integration, Tableau integration with R, and more.
Got a question for us? Please mention it in the comments section and we will get back to you at the earliest.