Tag: crls

All for Some, and Some for All

Row Level Security: Implementing “All Access” or “Deepest Granularity” methodologies

Virtual Connections, released last year, allowed Tableau an easy way to deploy row level security at scale. You can easily build governance policies in a single place, against a single table, and have them flow down to your entire organization. These policies can be easily audited and edited as your business needs change, and you can be assured that your changes will flow down to all content living downstream of the VConn. The only remaining hurdle is figuring out the appropriate policy for your data.

Tableau’s base recommendation for RLS is to create an entitlements table with one row per user per “thing they should access”, or entitlement. A sample table might look like the below.

For every Person, a Region, and for every Region, a Person.

This works perfectly well for a small group of users, and even scales well as your users and entitlements grow! Where it can begin to struggle, however, is when people have access to multiple regions. I’ve written a post for managing multi-entitlement access, but there’s another type of user it didn’t account for: the superuser. Whether it’s an exec, manager, or simply an entirely different business unit (analysts, for example), there’s often a swath of users who should be able to access everything. We could individually enumerate each user and give them access to every single entitlement, but imagine a scenario in which we have 15,000 entitlements and 15,000 users. Our entitlements table could balloon to tens of millions rapidly!

The old approach, detailed in our RLS whitepaper, required joining 2 additional tables to your fact table. VConns, as currently built, only allow for a single join, so this requires a new approach. Good news, though, it’s a relatively simple approach.

  1. Create a group on your Tableau Server for all of your “superuser” folks. I simply called mine “Superusers”. Add all of your superusers to this group.
  2. Add 1 row to your entitlements table with “Superuser” in both columns.
  3. Modify your fact table. There are a couple things we’ll have to do here.
    • Duplicate the column you use for your Entitlements join (the Region column, in my example).
    • Union your table to itself.
    • In the unioned copy of the table, replace all values in the Entitlements column with “Superuser”

I’ll show these modifications with some images. Consider the below fact table (only 3 rows).

I’ll union this table to itself, doubling the size (6 rows now). Add a new column for Entitlements (as a copy of the Region column). In rows 4-6, however, the Region has been replaced by the word “Superuser” in the entitlements column.

The green indicates rows added via the union, and are a perfect copy of the original fact table. The orange indicates the new column we’ve added for modified entitlements.

With this modified fact table, we’ll no longer need multiple joins. A single join in our VConn, with the appropriate policy, will now be sufficient to pass in all the info we need.

This policy first checks to see if a user is a superuser. If so, they get access to 1 entire copy of the dataset. If not, they’re subjected to the normal RLS rules.

So that’s how, but why?

If all you care about is getting the work done, read no further! If you’re curious about the query execution behind the scenes because you may want to further customize this solution, read on. It might seem like a bit of a convoluted approach at first glance. The simplest approach wouldn’t seem to require any data modification at all. Why not just write a policy which checks ISMEMBEROF(‘Superuser’) and, if true, returns the whole dataset?

The answer lies in join cardinality and join culling.

First, we’ll address join culling. There’s a tendency to assume that we could write a policy like the below, and use our base entitlements table.

We assume that if a user passes the ISMEMBEROF() check in our policy, the entitlements join will no longer happen. We’re not using the entitlements table for anything, so why bother joining it in? The way Tableau operates, however, means that once you’ve added the entitlement table to your policy, it will always be a part of your query, even if no columns are directly referenced in the policy. No matter what happens, the tables will join and the query will execute.

But why is that a problem? That answer comes from cardinality. If each row in your dataset can only be viewed by one person, and each person can only view one row, then you’ll actually be ok with this. Unfortunately, not many businesses are that simple. Most of the time, each user can view multiple rows, and each row can be viewed by multiple people. Take the simple example below, a 5-row entitlements table. It’s the same example from the beginning, but we’ve added one more user who can see the West region.

We now have 2 copies of “West” in the Entitlements columns of the Entitlements table. If we were to join this table to our fact table and query it, we’d end up doubling all the sales from the West. In a non-Superuser experience, however, this doesn’t matter. Tableau would first query the entitlements table to the appropriate user (let’s say Kelly, in this case) and then query the joined tables.

SELECT SUM(Sales) FROM sales JOIN entitlements ON sales.entitlement = entitlement.entitlement WHERE Person = 'Kelly'

The entitlements table would be filtered, the join would execute, and because there are now no duplicate values in the [entitlement.entitlement] column, no duplication occurs. Kelly sees the appropriate sales data. If, on the other hand, a Superuser logs in and queries, they’d receive the entire resulting table.

SELECT SUM(Sales) FROM sales JOIN entitlements ON sales.entitlement = entitlement.entitlement

In this case, there’s no WHERE clause, so they receive the unfiltered data. Because “West” appears twice in the [entitlement.entitlement] column, our sales in the West region get doubled. Of course, in practice, the impact will probably be much larger. There may be 5000 employees who can access the West region, and 3000 who can access the East. We’d have to do some silly math to try to reduce these numbers back to their de-duplicated state, and it would result in a lot of query overhead. Instead, we want to attempt to just query the raw, unduplicated fact table .

…and how does it work?

Really, a union is odd behavior to use here, because all we want to do is cull out the join. Because the join is unavoidable, however, we need to instead find a way to remove all duplication from the join. To do this, we unioned the fact table to itself. The duplication only happens when entitlements are joined together, so we need to make sure we don’t perform a many-to-many join. By materializing a single “Superuser” row in our entitlements table and creating a separate copy of the fact table that joins directly to it, we have effectively made a separate copy of the table for a superusers to query. The query we execute will be the same as above, but we’ve added a WHERE clause back on.

SELECT SUM(Sales) FROM sales JOIN entitlements ON sales.entitlement = entitlement.entitlement WHERE entitlement.entitlement = 'Superuser'

We know that ‘Superuser’ appears only once in our entitlements table (unlike the Region values, which may be repeated). As a result of this, we know that the fact table does not get duplicated. Our superusers see all of the data, but in its unduplicated glory!

[Multi-Value, Multi-Column] Row Level Security: Solving an Array of Use Cases

Virtual Connections have completely changed how Tableau connects to data, streamlining the processes of credential management and extract refreshes, while also reducing duplicate queries against your expensive databases. It also introduces the concept of centralized row-level security, or CRLS. Having a single point to create, audit, and edit your security policies allows for instant scaling of your data governance, and it also allows for better distribution of responsibilities.

In pre-VConn Tableau, the people building your datasources and workbooks were also the people responsible for building governance. This isn’t how most organizations work, however: data governance and dashboard buildouts are two totally separate functions. By separating VConn creation out from the analytic tasks, we let governance owners do what they do best, and let analysts confidently build, knowing that they don’t have to worry about security.

 Before VConns, governance was applied at every datasource or workbook. Even similar datasources required duplicate credentials, duplicate refreshes, and duplicate RLS policies.
With VConns, there’s a single point of governance application. This frees up backgrounders from duplicate refreshes, allows data stewards to set governance in a single place, and can save on costly duplicate queries against your data stores. 

All that said, this has brought an increased focus on Tableau’s existing row level security approaches. Many of the approaches detailed in the whitepaper rely on multiple joins, and VConns currently support only a single join. On top of that, people often have complex data models and rules. It’s not uncommon for data entitlements to be based on multiple columns, each of which have multiple values.

Consider a Regional Sales VP for certain categories. They may have access to their product categories across the whole country, but also have access to ALL sales within their regions. We need to evaluate two separate entitlements and check if either of them has been fulfilled. Traditionally, these may be kept in two separate entitlements tables. We have techniques for doing this in the whitepaper, but they require multiple joins, and VConns only supports a single entitlements table.

 Traditional RLS approaches would have you join both of these tables and do some calculation magic.

With VConns, however, we can combine these into a single entitlements table that represents all our entitlements, gives us an efficient query path to searching them, and provides a simple audit interface for our admins. Simply create one column for each entitlement (Category and Region, here) and populate them with pipe-separated arrays. You’ll have one row for each user which displays their entitlements.

 It’s easy to look at this table and find who can see what, and it also allows easy insertion of an ALL value instead of a complete list.

All that’s left is stitching this together with our fact table.

  1. Create a cross-join between your fact table and your entitlements table.
    • This is a scary first step, but just trust me for now. I know if we went on to evaluate this entire query, we’d end up with a massive dataset. The good news is that we’ll never actually execute the cross-join against the full tables, so for now, simply join on 1=1.
  2. Create a policy that checks for USERNAME() = [User] match.
    • This is the key to making the cross-join performant. This policy will act as a WHERE clause in our query, and (because we only have 1 row per user) it will filter our entitlements table down to a single row. Because of what’s referred to as “Filter Pushdown”, any decent database will perform this filter before joining the tables, so our cross-join will join our fact table to a 1-row entitlement table, creating no duplication at all.
    • Our data structure is now a non-duplicated copy of the fact table, but with the current user’s entitlements added onto every row as new columns. A small sample may look like this:
  3. Create a policy that checks entitlements.
    • First, we’ll check the Region entitlement. We need to check two things: is the entitlement ALL and, if not, does the Region on each row match the entitlement.
    • [Region Entitlement] = “ALL” OR (CONTAINS([Region Entitlement], [Region])
    • This calculation returns TRUE if the user is entitled to ALL or if the region in question is contained in their [Region Entitlement] array.
    • Repeat for each other entitlement you need! In this case, it’s just one more entitlement. Because we want to check either of these entitlements, we’ll use an OR statement, but if you want to check both you can use AND.

Voila! We’ve implemented governance against a single table, using only 1 entitlements table. It’s a flexible policy, allowing for ANDs and ORs, multi-value lists, and multiple entitlements. It’s easy to read, and, when combined with VConns, it’s easy to implement in a single place…and flow downstream to your entire server. No more worrying about searching out every workbook, no more downloading 500 workbooks to edit them, and no more managing multiple entitlements tables.

Handling governance centrally lets your BI Analysts do BI, and lets your Governance Analysts handle the governance. It allows you to centralize the security, but also all of the connection metadata, making administration a breeze. It lets your end users trust that the data is correct and secure, and reduces the nerves of the folks in your InfoSec department. Implementing complex RLS at scale has never been so easy!