Data security has been one of the top concerns for Tableau enterprise adoption. Tableau handles data security by permission and row level security. Permission controls what workbooks/views an user can see. Row level security controls what data sets this user can see. For example APAC users see APAC sales, EMEA users see EMEA sales only while both APAC and EMEA users have the same permission to the same workbook.
Does Tableau row level security works with extracts? Yes. This blog provides everything you need to know to create row level security controls for extracts and live connections, includes a new approach leveraging V10 x-db join features.
Use case : To create one workbook that server users can see subset of the data based on their Region (Central, East, South and West) and segments (Consumer, Corporate and Home Office) they are assigned to.
Solution A – Workbook filter for Row Level Security by Group
- Create following 12 Tableau server groups (Central-Consumer, Central-Corporate, Central-HomeOffice, East-Consumer, East-Corporate, East-HomeOffice,….). Central-Consumer group has all the Central region users who are assigned to Consumer segment….
- Create calculated field
ISMEMBEROF(‘Central-Consumer’) AND [Region] = ‘Central’ AND [Segment] = ‘Consumer’ OR
ISMEMBEROF(‘Central-Coporate’) AND [Region] = ‘Central’ AND [Segment] = ‘Coporate’ OR
ISMEMBEROF(‘Central-HomeOffice’) AND [Region] = ‘Central’ AND [Segment] = ‘HomeOffice’ OR
ISMEMBEROF(‘West-Consumer’) AND [Region] = ‘West’ AND [Segment] = ‘Consumer’ OR
ISMEMBEROF(‘West-Coporate’) AND [Region] = ‘West’ AND [Segment] = ‘Coporate’ OR
ISMEMBEROF(‘West-HomeOffice’) AND [Region] = ‘West’ AND [Segment] = ‘HomeOffice’ OR
ISMEMBEROF(‘East-Consumer’) AND [Region] = ‘East’ AND [Segment] = ‘Consumer’ OR
ISMEMBEROF(‘East-Coporate’) AND [Region] = ‘East’ AND [Segment] = ‘Coporate’ OR
ISMEMBEROF(‘East-HomeOffice’) AND [Region] = ‘East’ AND [Segment] = ‘HomeOffice’ OR
ISMEMBEROF(‘South-Consumer’) AND [Region] = ‘South’ AND [Segment] = ‘Consumer’ OR
ISMEMBEROF(‘South-Coporate’) AND [Region] = ‘South’ AND [Segment] = ‘Coporate’ OR
ISMEMBEROF(‘South-HomeOffice’) AND [Region] = ‘South’ AND [Segment] = ‘HomeOffice’ - Add the calculated field to filter and select ‘true’
- After publish the workbook, set interactor permission to all the above 12 groups.
- Make sure Web Editing as No, Download as No.
That is all. ISMEMBEROF returns true if server current user is member of given group. ISMEMBEROF is the key function to use here. It works for both extracts and live connection.
Notice that the control is a workbook filter. If workbook is downloaded, filter can be changed so the row level security will not work anymore, which is why workbook permission has to set download permission as No.
The better solution is to use data source filter for ISMEMBEROF calculation instead of workbook filter
Solution B – Data Source Filter for Row Level Security by Group
- You have the groups and calculated field from Solution A step 1 and step 2
- Edit data source filters to include the calculated field and select ‘true’
- Publish the data sources and set connect only permission (no edit)
- After publish the workbook, set permission to all the above 12 groups. There is no need to put the above calculated field to workbook filter anymore since filter is at data source level now.
Published data sources are reusable, single source of truth, less loads to data sources and now you have governed row level security built-in.
The Solution B works extracts. The only thing is that it is little tricky during workbook development process where you will need to make local extract local copy to simulate the user behavior from Desktop, and replace data sources from local to server published data source before publish the workbook, you will need to copy & paste all calculations. Pls reference manual fast way or a hacky way.
The above approaches control user’s visibility of data sets by Tableau server groups. It assumes that you will manage the group members outside Tableau. When have too many data security groups to manage manaually, you can automate the group member creation by using Server REST API or your corp directory automation tool.
When group approach in Solution A & B can’t scale, the following USERNAME() approach will be another good option.
Solution C – Entitlement table x-db join for Row Level Security
Same use case but you want to add category as dimension for row level security in additional to Region and Segment. Now you will need 100+ groups just for row level security purpose which can be a lot to manage. We are going to use Tableau’s USERNAME() function which returns current server user name. It does not use group anymore but assume that you will have separate user entitlement table below.
| UserName | Region | Segment | Category |
| U123 | East | Comsumer | Furniture |
| U456 | East | Comsumer | Office Supplier |
This ser entitlement table can be Excel or separate database table. We can use V10’s cross database join feature for row level security:
- Create cross-db join between main datasource (like extract, MySQL) and use entitlement Excel
- Create calculated field
USERNAME() = [UserName] - If you use workbook filter, just add this calculated field into filter and set ‘true’ – the same as Solution A
- Or you use published data source, just edit data source filters to include the calculated field and select ‘true’ – the same as Solution B.
- You are done
The USERNAME() will return the server current user name. While [UserName] is the user name column of your use entitlement excel which can be a database table.
Please note: The current version of Tableau v10 does not support x-db joins between two extracts although it does support x-db joins between an extract and excel (or some selective database). So if your primary data source is an extract, your use entitlement table can’t be extract anymore.
In additional to ISMEMBEROF, the USERNAME() is another great Tableau server function for row level security. V10 x-db join feature extends USERNAME()’s use case a lot of more now since you can create your own use entitlement table outside your main database for agility and self-service.
When use entitlement table is in the same database as main FACT table, you may want to use database’ native join feature for row level security :
Solution D – Query Banding or Initial SQL for Row Level Security
For database (like TeraData) support query band, enter query banding:
- ProxyUser = B_<ProxyUser>
- TableauMode=<TableauMode>
- TableauApp=<TableauApp>
- Tableau Version=<TableauVersion>
- WorkbookName=Name of DataSource
For database( Vertica, Oracle, SQL Server, Sybase ASE, Redshift, and Greenplum, etc) support Initial SQL:
- [TableauServerUser] returns the current Tableau Server user’s username only.
- [TableauServerUserFull]
- [TableauApp]
- [WorkbookName}
As summary, ISMEMBEROF and USERNAME() are two Tableau functions for row level security:
- ISMEMBEROF returns true if server current user is member of given group. It needs server groups to be setup.
- USERNAME() returns server current user name. It needs entitlement table. V10 x-db joins allows the entitlement table to be outside main data source.
- Both can be implemented as Data Source Filter or workbook filter.
- Both work for extracts and live connections.
Although USERNAME() returns server current user name, it does not pass the current user name to live connected datasource outside Tableau server. In order to pass the server current user name to data source, you will have to use query banding or initial SQL depends on database you use. Query banding or initial SQL works only for live connections and does not work for extracts.
Do you still want to know more? Click here.