Nowadays, many organizations face such problems as leaking of confidential information and time-management issues. If you are working in Tableau, the above-mentioned problems can be solved (at least partially) by displaying only the data that the user should see, therefore the user won’t be distracted by irrelevant information or information that should not be shown. Tableau does not handle filtering data for an individual user using Permissions, which only affect what Tableau Server content a user can see. So, to enforce user-based data entitlements in Tableau you have to use Row Level Security.
All secure techniques for achieving Row Level Security in Tableau depend on each user having a distinct username in Tableau Server and that the user be logged in as that username.
So, let’s look at the basic conditions for creating a reliable RLS:
1. All needed users and groups are created in Tableau Server;
2. You need to be connected to Tableau Server or Tableau Online because the desktop needs to download list of users and groups from the server;
3. User Filter on the Data Source level (according to the filter order in Tableau the source filter is applied in the first order)
Embedded Data Sources allow an end user with Web Edit or Download to edit Calculated Fields. So embedded Data Sources are less secure and should be used only if Web Edit/Download will always be disabled.
The main options to implement Data Row-Level Security are next:
I. Solely in Tableau
a. Live or Extract connection
b. User Filter built and applied per workbook or data source
II. Mixed approach, where user information in Tableau Server or Tableau Online corresponds with data elements in the database
a. Live or Extract connection
b. Leverage Data Server
c. Use Data Source Filters (e.g. username()=[user column from security table in database] or calculated Field based on username() tableau function)
Let’s consider an example of implementing RLS solely in Tableau with user filters which are defined in Tableau Desktop. First of all, we create all needed users and groups in Tableau Online.
Then, click “Server” on the menu bar of Tableau Desktop -> Create User Filter. Next appears a list of available fields that we can base the user filter on.
If you are not signed to into Tableau Server or Tableau Online you may be prompted to do so. The window will open and ask you to enter your credentials.
Assume that we have some geo-based data and we assign each user to their appropriate region, so only one user maps to any given row of data.
The new user calculation appears under measures in the Data pane. Just like your other fields, you can use it in one or more visualizations by adding it to the filters shelf: from the Data pane drag User Filter to the Filters shelf. In the Filter dialog box that opens, select True, and then click OK. Note: If you are not signed in to Tableau Server or Tableau Online, the True option is not visible. In Tableau Desktop, sign in to Tableau Server or Tableau Online to select it. In the lower right corner of Tableau Desktop is a user emulator, which allows us to see what the view will look like if a specific user sign in. You may toggle between some of the users and the view will be filtered to show only their relevant values.
Also, we can use a hybrid approach to RLS. Let’s take a look at an example right away. So, this time we connect to both the Orders (orders with respective sales persons) which contains actual data for visualization and the People tables (entitlement table). Data table is joined to entitlement table containing user names and key values. The People table is an example of having data elements in the database that associate users to specific pieces of data like Users to Regions.
To create a User Filter that leverages this data we can use Calculated Field. We’ll create a formula that checks if the user name matches the sales person field.
Once this Calculated Field is created, we can use it in a similar fashion to the filter in the previous example. It can be dragged to the filter shelf or applied to multiple worksheets through embedded as s Data Source Filter. Now the data source will be filtered to the logged in user, and they cannot get around the data security model, owing to the data source filter.
This is relatively simple example, in real life it can be more complicated. Information about other user functions and complexity of entitlement tables can be found in sections below.
‘People’ table, mentioned above, is an example of Entitlement or Secure table. Entitlement table is a single combination of attributed that the data will be filtered on. For example, filter on ‘Category’, ‘Sub-Category’ and ‘Region’ is a single entitlement. Standard database design practices mean you rarely have a single table that fits the criteria for the entitlements table. Most data aren’t necessarily mapped one-to-one to a single user; it’s very likely the data security is organized either by role, organization name, or both.
You may even need to do UNIONs to get all of the rows necessary to represent that most granular level of data, and particular calculations to generate a unique Composite Key field. Certainly, you can do this in Tableau’s JOINs dialog. But it’s a lot to ask of an end user to set up every time, when the logic should always stay the same. So more practical is creating a View, so that you only have to JOIN that single ‘security table” view to the data table in Tableau.
As you already know, User Functions can be used to create user filters or column-level security filters that affect visualizations published to Tableau Server or Tableau Online, so that only certain people can see your visualization. Let’s look at them in more detail:
· FULLNAME: FULLNAME( )
Returns the full name for the current user. This is the Tableau Server or Tableau Online full name when the user is signed in; otherwise the local or network full name for the Tableau Desktop user.
Example: [Manager]=FULLNAME( )
If manager Joe Copling is signed in, this example returns True only if the Manager field in the view contained Joe Copling. When used as a filter, this calculated field can be used to create a user filter that only shows data that is relevant to the person signed in to the server.
· ISFULLNAME: ISFULLNAME(string)
Returns true if the current user’s full name matches the specified full name, or false if it does not match. This function uses the Tableau Server or Online full name when the user is signed in; otherwise it uses the local or network full name for the Tableau Desktop user.
Example: ISFULLNAME(“Joe Copling”)
This example returns true if Joe Copling is the current user, otherwise it returns false.
· ISMEMBEROF: ISMEMBEROF(string)
Returns true if the person currently using Tableau is a member of a group that matches the given string. If the person currently using Tableau is signed in, the group membership is determined by groups on Tableau Server or Tableau Online. If the person is not signed in, this function returns false.
Example: IF ISMEMBEROF(“Sales”) THEN “Sales” ELSE “Other” END
· ISUSERNAME: ISUSERNAME(string)
Returns true if the current user’s username matches the specified username, or false if it does not match. This function uses the Tableau Server or Online username when the user is signed in; otherwise it uses the local or network username for the Tableau Desktop user.
Example: ISUSERNAME(“jcopling”)
This example returns true if jcopling is the current user; otherwise it returns false.
· USERNAME: USERNAME( )
Returns the username for the current user. This is the Tableau Server or Tableau Online username when the user is signed in; otherwise it is the local or network username for the Tableau Desktop user.
Example: [Manager]=USERNAME( )
If the manager jcopling was signed in, this function would only return True when the Manager field in the view is jcopling. When used as a filter this calculated field can be used to create a user filter that only shows data that is relevant to the person signed in to the server.
To represent different hierarchical relationships to the data, one effective technique is to have a row in the Entitlements View representing each of the most granular level of security filtering. You will probably need to construct a de-normalized “entitlements view” out of many different tables that store different pieces of the user entitlements.
Let’s look at this example:
1) CEO
2) Manager
3) Regional manager
To represent this in your Entitlements View for some source table that contains data by department and regions, the CEO will have a row for every department and region. Each manager will have a row for department that belong to their responsibility and all regions. Each regional manager will have a row only for his/her department and region(s).
For hierarchically complicated entitlements tables you may need to use multiple fields to join them with table which contains data. This is known as a Composite Key in database terminology, and there are two ways you can handle it:
1) Create an actual Composite Key field in your Entitlements View and in your Data Table. Then JOIN the two on that single Composite Key field
2) JOIN the Entitlements View to the Data Table on each field that makes up the Composite Key.
Most organizations have a hierarchy, and often there is the desire that those higher in the hierarchy can access the data of all of those who report to them. You can construct hierarchical filters, like so:
IF ISMEMBEROF(‘C-level) THEN 1
ELSEIF ISMEMBEROF(‘Managers’) THEN
IF [Manager Username Field] = USERNAME() and [department(entitlement)] = [department(data table)] THEN 1 ELSE 0 END
ELSE 0 END
Tableau Extracts take whatever table relationships from the Live Connection screen and combine them into a single table through one query, the results of which are transformed in a single table in the Extract file.
Starting in Tableau 2018.3, the Hyper extract engine has been updated with an option to bring in Multiple Tables, specifically with the intention of making Row Level Security easier to implement. Using Multiple Tables can vastly reduce the time it takes to generate an extract, by completely avoiding the “row duplication blow-up”.
Starting in 2018.3:
· The design for row level security will be the same in both live connections and extracts
· Extract files with security will create much faster
· Best practices for entitlements tables are now feasible in Extracts
Conclusion: You will need to do your filtering yourself, in one of two ways:
1. Convert the each table to Custom SQL and add the appropriate filter clauses
2. Put the filtering logic into a View in your database
The Entitlements Table should be a de-normalized View of whatever entitlements are necessary to filter the data at the most granular level. In almost all cases, this means the View should contain:
- A column for Username, containing the exact usernames in Tableau Server, and…
- A row for each level of the most granular entitlements to the Data Object.
In a de-normalized, single table extract, this may blow up the extract size to enormous proportions instantly. But in a multiple table extract, both tables stay the same size, no matter how big either one is. And since to Hyper quite good at JOINing and filtering, the technique remains very fast even on large data sets
To implement Row Level Security without your data “blowing up”, you should use the CONTAINS() method. The simplest high-performance method that does not duplicate any rows is to include the list of all allowed usernames in a comma-delimited field in your data, then look for it using the CONTAINS() function:
CONTAINS([Security Users Field], USERNAME()) which may help to check if complex security fields contain certain registered usernames from Tableau Server.