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.
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.
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.
All that’s left is stitching this together with our fact table.
- 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.
- 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:
- 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!
10 thoughts on “[Multi-Value, Multi-Column] Row Level Security: Solving an Array of Use Cases”
Hello, I stumbled upon this article while searching for solutions to my security issues. I had not considered creating delimited lists for each of the secured columns by user. You might be onto something here.
However, I am left confused by your instruction steps. I don’t understand how you would create a cross join before or as part of the virtual connection creation. You also ask to create two separate policies, which is not possible on a single table.
Can you elaborate on these steps further?
Sure thing, sorry for the pieces that weren’t clear.
For the cross-join, Tableau doesn’t explicitly allow cross joins here, so we’ll use an old SQL trick of joining on 1=1. Because every row matches every row, this will result in a cross join. You’re not doing it in the VConn creation, you’re doing it as part of the policy itself. That’s listed under step 1.
You’re right on the two policies point, my apologies. Logically they exist as two policies, but really they should be a single policy. Simply use an AND between them to ensure they’re both enforced. For example, USERNAME()=[User] AND CONTAINS([Region Entitlement], [Region])
I’ll fix that when I’m at my computer!
Thank you. This does clear up a little. I’m still unclear on the cross join. I understand what it is and how to create one in SQL, but is my initial data set in the virtual connection a custom sql script which is the cross join, or are you performing the cross join in the virtual connection somewhere. The phrase “as a part of the policy itself” is confusing me.
Is your policy condition “1=1 and username()=user and contains(field, field entitlement)…”?
I think it would be helpful to see where the components of this solution fit into the virtual connection more clearly. I am sorry to be so dense. 🙂
I had the same question as Richard. Where should I put “1=1” in the Virtual connection now?
Unfortunately as of now, the only option is to create a column in each table that is just 1. Whether you do that using custom SQL (like the below) or actually materialize it into your table on the back-end is up to you. Once the column exists on both tables, you can just join the two columns together as though they were a normal join clause.
Depending on your dialect, something like this would work.
SELECT 1 AS joincolumn * FROM table1
Thanks for the quick reply. I did the trick you said, just added an extra column with value “1” as a cross join key to the both fact table and the entitlement table. The trick works, and the USERNAME() = [USER] works as well. Thanks a lot!
However, while trying to use CONTAINS(), the system will generate error message now. error: “INVALID_ARGUMENT”
screen shot: https://ibb.co/9TXTPyd
Please kindly let me know if you see anything wrong. 🙂
I wish I had an immediate answer here…the invalid argument isn’t complaining about your function’s syntax, it’s just a poorly-worded error message. I’d get a support case in as quickly as possible so they can look at logs. In the interim, I’ll see if I can repro your issue, but I’ve never seen that before in this use case.
So that the place I put the USERNAME() and CONTAINS() were correct, right? If it’s convenient for you, please kindly post your testing screenshot while you have time. So that I could make sure that I didn’t mis-understand your instructions by any chance.
I found that when I set the fact table to “Live” then the issue would gone and could work perfectly. However, when I switched the fact table to be “Extract” in the VConn then the problem shows. Therefore, there might some bugs in the Tableau. Just FYI.