Now that you know you need Row-Level Security (RLS) and you are familiar with the basic concepts and building blocks, we can take a deeper look at the different RLS models we can build within Tableau. Just remember, reality often provides a lot more nuance than these examples, so there are times when components of each of these are mixed or interesting filtering logic is written; even though these can be full solutions, they are also building blocks themselves. Below, we will describe three current options and one outmoded option, list their pros and cons, show a short example of how the respective entitlements table should be set up and show the basic setup within Tableau.
The options below are based in large part on work done by Bryant Howell, Miranda Osterheld, Fearghal Gunning, and Ron Dugger in their Tableau whitepaper, Best Practices for Row Level Security in Tableau with Entitlements Tables. We have done our best to simplify these options, as well as provide commentary and examples.
In Options 1 through 3, we need to make sure we are using multi-table/physical extracts in Tableau or a live connection to a performant database.
If you are on a Tableau version before 2018.3, we highly recommend upgrading to a newer version. However, if this is not an option, feel free to skip to option 4.
After considering the notes above, let’s think about how we should evaluate the pros and cons of our RLS options’ performance:
- A performant database or Tableau’s multi-table/physical extracts (2018.3+) can do a great job of filtering down our entitlements table to only the current logged-in user before joining to the rest of our data. Given this, the size of the entitlements table itself does not matter that much.
- Consideration should be given to how the entitlements table is created with preferences towards automated ETL solutions.
- Since the size of the entitlements table does not matter as much, we want to focus on simplifying the join and filter logic on the Tableau side of things to get the best performance possible.
Row-Level Security Option 1: Deepest Granularity
In this solution, all permissions are explicitly named in the entitlements table at the deepest level we want to provide security.
Pros: No entitlement is implied and there is no use of the ISMEMBEROF() function. Join and filtering logic is simple.
Cons: The entitlements table itself is longer, which could require more ETL work.
Entitlements Table
Territory ID is the deepest level at which we want to provide security, so every user’s permissions will be described down to this level:
Tableau Setup
Row-Level Security Option 2: Hybrid
In this solution, we will create a Tableau Server group for users who should see everything (User 5, our super user). Everyone else has their permissions explicitly named in the entitlements table. We will also write some logic in a join calculation that accounts for our super users.
Pros: The entitlements table itself does not need to include full details for our super users, which can simplify the ETL. Filter logic is relatively simple.
Cons: The SUPER USER group membership implies full access. If a member of the SUPER USER group should only see specific geographies, the solution does not account for it. Join logic is more complex than Deepest Granularity.
Entitlements Table
User 5 gets put in the “Super User” Tableau Group and will not be represented in the entitlements table. Everyone else gets described like the Deepest Granularity above:
Tableau Setup
Row-Level Security Option 3: Sparse Entitlements
In this solution, users at higher levels of access in the hierarchy are indicated with NULLs in fields where they have full access.
Pros: Hierarchies can be handled well. The entitlements table itself can be much smaller.
Cons: Lots of security access is made implicitly through the IFNULL evaluations, and the filtering logic which contains this requires more compute to execute. Join logic is a cross-join (1=1, every record is joined to every other record).
Entitlements Table
User 3 should see all of Brazil, so their permissions are just described by their access to Brazil. User 5 can see all countries and all territories, so both are null. You can think of the NULLs as All:
Tableau Setup
Row-Level Security Option 4: User Concatenated List (for Versions Older Than 2018.3)
**This solution should only be used in versions older than 2018.3 unless there are extenuating circumstances.**
For a specific row of the entitlements level, users with access are concatenated into a list with a delimiter on both sides of the name for clear identification with a CONTAINS() function. Each row of the entitlements table is at the deepest level you wish to provide security.
Make sure you have a delimiter on both side of each username to avoid accidentally grabbing a similar username that happens to be shorter:
- e.g. bstephenson would return TRUE for CONTAINS([Approved Users], ‘bstephens’)
- |bstephenson| would not return TRUE for CONTAINS([Approved Users], ‘|bstephens|’)
Pros: The entitlements table does not duplicate data since the entitlements table is at the grain of the data (this is key in versions before 2018.3).
Cons: The CONTAINS() function is expensive to perform.
Entitlements Table
We want to provide security at the Territory ID level of our data, so each row is representing this:
Tableau Setup
A Quick Note on Tableau’s Upcoming Centralized Row-Level Security (CRLS)
Tableau is expected to include Centralized RLS only through Tableau Data Management in an upcoming release. This will allow RLS policies to be implemented on the Server in a single location. All the components we will mention in this blog series should retain their relevance, but the process to implement and maintain these solutions should be even easier! Keep a lookout for another blog when we get our hands on it.
