Tableau Q&A - Row-Level Security

5
Apr 2013

Our business intelligence consultants are constantly engaged in tackling challenge after challenge. The individual capabilities of each are impressive enough, but what they accomplish together is what makes them such an effective team. We’ve decided to share some of the solutions they’ve created together in a new Tableau Q&A blog series.

Challenge

A question regarding Tableau row level security:

“A client has a team of 200 sales people. Each salesperson, when they log into Tableau, must only be permitted to see their own data.”

“The sales persons are split into groups of about 30 with a dedicated sales manager who will have permissions to see their data.” 

“In addition, 10 people in the organization will have unrestricted access to all sales rep data.”

“Row level security, as I understand it, would have a table in SQL that holds a row of data for each salesperson/manager combination as well as a line of data for each salesperson/company-wide viewer. This table would then be linked to sales order lines on each salesperson, creating a data set that tableau can connect to and apply a 'USERNAME()=' filter.”

“In this client’s case, this would cause a huge amount of duplicate rows. They are concerned this will impact performance. Does anyone have any other suggestions as to how I could implement security in this kind of environment?”

Team Discussion: How We Got to the Solution

  1. “So maybe you join in some form of code, say "Security Group 1," to your particular row and then create a calculated field that maps the username to a security group. Lastly, a calculated field comparing the two OR username() = <Your SuperUsers>.”
  2. “This typically depends on the type of connection - extract or live? If live, some databases have alternative options such as SQL Server's impersonation. With live connections in general, you would want to have your fact table with separate security tables that have a record for every user and the dimension members to which they have access. Then you'll join the security and fact tables with a filter restriction on the user so you're only bringing back his rows. With Extracts, you can use user filters otherwise you will need to extract the join detailed above which would blow up your data set as you described.”
  3. “One thing to consider though is that the maintenance of the sales team now lays in the Tableau workbooks calculated field, so if there are going to be a bunch of different workbooks requiring this security this may become an issue.

RE: “You can now apply filters directly to data sources in v8, so there may be a way to abstract that to data server and keep the workbook level edits to a minimum.”

Solution

“I ended up creating a fixed row size lookup table which used multiple columns for each level of management within the company hierarchy. I then used a case statement and username() in Tableau to enforce the security rules.”

Comments

April 6, 2013

Joe Mako

v8 Blending may be another option

I think it is really great that there are many ways to accomplish things in Tableau.

Another option, using the traditional username-region table, is taking advantage of blending in v8.

http://public.tableausoftware.com/views/RowLevelSecurity_0/usingv8blending is an example that uses a conditional filter (we currently cannot filter directly on a Boolean).

This might not work in all situations, can you come up with a situation where this v8 blend filter does not enable the analysis you want?

April 17, 2013

Tamas

Joe, if you have billions of

Joe, if you have billions of rows, then blending is not an option.

Interworks, with most of the currently used databases you are able to execute stored functions which could manage the data security. If I would implement a row level security like this (I would not), then I would put the filtering logic into a stored function to:

a) hide the detailed logic from the users
b) in case of changes change the logic in one place only
c) integrate with other policies like Oracle VPD.
d) I can use my SQL92 hierarchical queries to traverse across hiearchies and match the values with any of the levels instead of this case/when stuff

Also, if I would implement something like this I would definitely obtain these security permissions from ActiveDirectory/LDAP servers instead of flattened database tables. With a result cache enabled stored function, which connects to the same AD which authenticates the users, and uses the AD's group structure as base data is the cleanest solution in my opinion.

On the other hand, any kind of row level security implemented in BI application level will fail from that point when you have deal with the Desktop users. From that, you will need VPD, on connect context settings, etc. To be honest Tableau's solutions is a nice, but quite lightweight implementation which could be pretty useful in big enterprise deployments.

What I think is necessary for supporting row level security / VPD in databases are:

  • a connection time executed code which could initialize row/column level security in database side (e.g. VPD contexts)
  • End to end user authentication, pass tableau user credentials to database level (like for SQL Server impersonation, but also for Oracle, Teradata, etc.)

I am not a tableau expert, last year I have used for a few weeks, so maybe these features are already implemented. Are they?

April 18, 2013

Koushik Mandala

This looks like a work around

This looks like a work around rather than a solution. If we need to add another level of security we have to go back and change the DDL of the table followed by Parameter changes.

April 18, 2013

Koushik Mandala

This looks like a work around

This looks like a work around rather than a solution. If we need to add another level of security we have to go back and change the DDL of the table followed by Parameter changes.

Search