Apr
25
2018

Row Level Security in Tableau

Data security is the main priority for organizations of every size and genre. In simple terms, we can say it’s a practice of keeping data protected from corruption and unauthorized access. The main focus of every organization is to ensure privacy while protecting personal or corporate data.

The first step in protecting your enterprise’s data privacy and security is to identify the types of information/data you want to protect and where that information is exposed in your organization. For example – If you have a Tableau dashboard which contain number of Tableau workbooks managed by individual users in various departments and they, in-turn, share workbooks between each other in a small group, we need to create a high end security for each department and for each user based on their role. These days’ security needs are more volatile – almost every day new users get added frequently, user privileges changes, organization structure changes etc. We need to define “something” dynamic inside tableau which can support both users with security constraints and users without security constraints.

We can achieve something similar if we can implement Row Level Security in tableau.

Row Level security is defined as the ability to control what data a user sees.

generic viagra woman Restrict Access at the Data Row Level

 

 

 

 

 

 

When you share workbooks with others by publishing them to Tableau Server by default, all users who have access to the workbooks can see all of the data shown in the views. You can override this behavior by applying a type of filter that allows you to specify which data “rows” any given person signed in to the server can see in the view.

When you publish your Tableau dashboard report, you want to allow each user to see only the data relevant to his or her Service Area/Service Line/Industry/Department /Job Level etc. Rather than creating a separate view for each user, you can apply a user filter that restricts access to the data based on users’ characteristics, such as their role.

follow Create a user filter and map users to values manually.

This method is convenient but high maintenance, and security can be tentative. viagra supplier us pharmacy It must be done per-workbook, and you must update the filter and republish as your user base changes. This procedure can be sufficient if you have a small and fairly static set of users or groups to manage, and only a small number of workbooks that need user filters. For example select the field you want to use for filtering the view ( viagra pharmacy london Server >  Create User Filter). I am using a service Area and Service Line as an example.

Drag the user Filter set () into the Filter shelf -> in the list on the left of the User Filter dialog box -> select a user (in this case rajeevpandey) -> select the individual members of the field that you want the selected users to be able to see.

 

 

Name User ID Dummy Service Area Dummy Service Line
Rajeev Pandey rajeevpandey E- commerce Movers and Packers

Create a dynamic filter using a security field in the data.

Using this method, you create a calculated field that automates the process of mapping users to data values. This method requires that the underlying data include the security information you want to use for filtering.

The most common way to do this is to use a reference (or “look-up”) table that contains this information. For example, if you want to filter a view so that only managers can see it, the underlying data must include user names and specify each user’s role.

Because filtering is defined at the data level and automated by the calculated field, this method is more secure than mapping users to data values manually.

Steps:

  1. Database must include the field that you want to use for filtering. Here our sole purpose is to restrict the user based on Service Area dynamically.

Let’s create a User Level access Excel file which should contain two sheets.

Excel Sheet 1 tab should contain four columns: Name, User ID, Dummy Service Area and Dummy Service Line. Data in the User ID field must match with the Tableau Server User ID .And, If we wanted to give Global access to the user then we will be adding a “Global access” under the Name column and username () Function under the User ID column as below.

Name User ID Dummy Service Area Dummy Service Area
Name 1 User ID 1 Service Area 1 Service Line 1
Name 2 User ID 2 Service Area 2 Service Line 2
Name 3 User ID 3 Service Area 3 Service Line 3
Global Access Username() Service Area 1 Service Line 1
Global Access Username() Service Area 2 Service Line 2
Global Access Username() Service Area 3 Service Line 3

Excel Sheet 2 tab (You can rename it to Global Access. We are creating this sheet for our own convenience so that we can remember which user needs a “Global access”. Note *: This sheet has nothing to do with data Row Level security. The only purpose is to track the Global access user) should contain three columns: Name, User ID, and Dummy Service Area. Make sure the Dummy service Area column for all the users should contain value as “All”.

Name User ID Dummy Service Area
Rajeev Pandey rajeevpandey All
Vikash vikash All
Aditya aditya All
  1. In Tableau Desktop, open the workbook you want to add user filtering to. On the Data Source page, add the reference table “User Level access” and then go the empty worksheet.
  2. Here we do a blend on “Dummy Service Area” and “Dummy Service Line” as per the below image.

 

 

  1. Create a calculated field – Updated UserID- where we are dynamically updating the User ID column value Username () with the Tableau Server User ID.

*Refer the screenshot of step 1 for getting an idea of Username () text which is available under User ID column.

 

 

 

 

 

 

 

Create a calculated Field Check which should count all the rows which contains “Global access” as Name.  if you notice, we have already updated the username() text to Username() function in the above mentioned calculated field (Updated UserID )and then we are counting the rows .Here, we are also using Fixed LOD, so we need not require to reference to any particular dimensions in the view for calculating the count of Global access rows.

   {Sum (if [Updated UserID] = USERNAME () THEN 1 ELSE 0 END)}

 

  1. Now create another calculated Field called “User Access”. The below calculated field will check two condition.

If you are part of Global access (referring to Name Column which contains value as Global access) then it will check the else condition i.e. USERNAME () = [Updated User ID]. It will show all the Service area values which are mapped to Global access and if a user is not a part of Global access (Count of check = Count of Global Access rows (Username ()) + Actual User ID) then it will restrict user to see value to that service area for which he/she belongs to.

 

Now drag “User Access” Field on the Quick filter shelf and select value as “show”. Right click on this Filter and select “All using this data source” option.This will restrict user to see based on the excel table “User Level access”.

Now what if you wanted to pass security restriction in multiple levels like Function, Service Area, Service Line, and Industry etc. Then we need to redefine the code as well as the reference table “User Level access”. Below mentioned approach will surely minimizes the amount of programming and security table / user group maintenance in tableau and achieves the appropriate level of security for the different users.

Let’s take a sample scenario where every user has different access and based on their access, they will see the charts /tables in tableau dashboard. Below example is showing the possible combinations of all the user restrictions. You can add “N” number of Columns based on your requirement like Business Unit, Service Line, Industry, Grant access during UAT etc and restrict the user for seeing the entire dashboard.

Pre- Requisites

  1. All the users (user ID) listed on the “User Level Access” table should be part of tableau server users group.
  2. User Level access” security table lists only users that are restricted so that anyone not in this table can see everything. In another words, if you have no restriction, you wouldn’t need to be part of excel security table
  3. There should be only one row for each user based on their role and restrictions. For example Username 2 can see the Federal data only in our dashboard.
  4. Blending should be done only on username.
Username Business Unit Service Area Possible Combinations
Username 1 Commercial Service Area 1 Restricted based on both SA & BU
Username 2 Federal All Restricted based on BU only
Username 3 All Service Area 2 Restricted based on SA only
Username 4 All All No restriction

Steps Involved:

1.  Create a calculated field that assigns the current user to the USERNAME() User ID.  This is the field that will link to the primary data set.

 

 

2. Connect your Tableau Desktop to the “User Level access” security table and perform a blending on Username ().This calculated Field needs to be included in  all the worksheet where you want to perform row level security on the dashboard

 

 

 

3. If the user access is restricted then what they are allowed to see is dictated by the “User Level access” security table and If the user is not in the restricted  security table then they will have “(All)” for everything.

4. Now let us create a scenario where user can only see the data in the dashboard based on their role. As you can see User1 can see data related to only three combination like Business unit, USDC and Service Area. He won’t be able to see or drill down based on Industry because he is not having access to industry. Similarly user2 is having its own restriction.

Users Business Unit USDC Indicator Service Area Industry
Username 1 Commercial USDC 1 Service Area 1 No access
Username 2 Federal No access Service Area 2 No access
Username 3 All(No restriction) USDC 3 Service Area 2 Industry 3
Username 4 All(No restriction) All(No restriction) All(No restriction) All(No restriction)

Below formula can be used for calculating the number of all possible combinations .We need to write If- Else condition for handling all the scenario’s2 (number of restricted columns)

5. In the above mentioned table, we have four columns based on which we need to restrict the user. For achieving this, we need to create 24 = 16 combinations. Please refer the below image for better understanding. You can find 16 different combinations based on user restriction. For example – A user can have global access (Row 1) or a user can be restricted to see only Industry (Row 2) or Users can be restricted to see only Service area (Row 3) or a user can be restricted to see only USDC indicator (Row 4) etc.

Screenshot 1: 

 

 

 

 

 

 

 

 

 

 

 

 

Screenshot 2: 

 

 

 

 

 

 

 

 

 

 

 

 

Screenshot 3: 

 

 

 

 

 

 

 

 

 

 

 

 

Note :* Mapped industry, Mapped Service area and USDC indicator are the actual fields which are coming from your database.

Now you need to create a calculated field which should fulfill all the conditions. Below attachment contains the If- Else calculation which you can directly use inside tableau. This calculation would use row level security within Tableau for circumstances where access to only specific rows of your data set are required.

IF USERNAME () = ATTR ([Excel sheet Tab name]. [Username])

THEN

// Below Calculation is derived from screenshot 1

IF ATTR ([Excel sheet Tab name]. [Operating Unit]) = ‘All’

AND

ATTR ([Excel sheet Tab name]. [USDC Indicator]) = ‘All’

AND

ATTR ([Excel sheet Tab name]. [Service Area]) = ‘All’

AND

ATTR ([Excel sheet Tab name]. [Industry]) = ‘All’ THEN 1     

   

ELSEIF ATTR ([Excel sheet Tab name]. [Operating Unit]) = ‘All’    AND

ATTR ([Excel sheet Tab name]. [USDC Indicator]) = ‘All’

AND

ATTR ([Excel sheet Tab name]. [Service Area]) = ‘All’

AND

ATTR ([Mapped Industry]) = ATTR ([Excel sheet Tab name]. [Industry]) THEN 1

 

ELSEIF ATTR ([Excel sheet Tab name]. [Operating Unit]) = ‘All’

AND

ATTR ([Excel sheet Tab name]. [USDC Indicator]) = ‘All’

AND

ATTR ([Mapped Service Area]) = ATTR ([Excel sheet Tab name]. [Service Area])

AND

ATTR ([Excel sheet Tab name]. [Industry]) = ‘All’ THEN 1

 

ELSEIF ATTR ([Excel sheet Tab name]. [Operating Unit]) = ‘All’

AND

ATTR ([USDC Indicator]) = ATTR ([Excel sheet Tab name]. [USDC Indicator])

AND

ATTR ([Excel sheet Tab name]. [Service Area]) = ‘All’

AND

ATTR ([Excel sheet Tab name]. [Industry]) = ‘All’ THEN 1

 

ELSEIF ATTR ([Operating Unit]) = ATTR ([Excel sheet Tab name]. [Operating Unit])

AND

ATTR ([Excel sheet Tab name]. [USDC Indicator]) = ‘All’

AND

ATTR ([Excel sheet Tab name]. [Service Area]) = ‘All’

AND

ATTR ([Excel sheet Tab name]. [Industry]) = ‘All’ THEN 1

 

// Below Calculation is derived from screenshot 2

ELSEIF ATTR ([Operating Unit]) = ATTR ([Excel sheet Tab name]. [Operating Unit])     

AND

ATTR ([USDC Indicator]) = ATTR ([Excel sheet Tab name]. [USDC Indicator])

AND

ATTR ([Excel sheet Tab name]. [Service Area]) = ‘All’

AND

ATTR ([Excel sheet Tab name]. [Industry]) = ‘All’ THEN 1

 

ELSEIF ATTR ([Operating Unit]) = ATTR ([Excel sheet Tab name]. [Operating Unit])    AND

ATTR ([Excel sheet Tab name]. [USDC Indicator]) = ‘All’

AND

ATTR ([Mapped Service Area]) = ATTR ([Excel sheet Tab name]. [Service Area])

AND

ATTR ([Excel sheet Tab name]. [Industry]) = ‘All’ THEN 1

 

ELSEIF ATTR ([Operating Unit]) = ATTR ([Excel sheet Tab name]. [Operating Unit])   

AND

ATTR ([Excel sheet Tab name]. [USDC Indicator]) = ‘All’

AND

ATTR ([Excel sheet Tab name]. [Service Area]) = ‘All’

AND

ATTR ([Mapped Industry]) = ATTR ([Excel sheet Tab name]. [Industry]) THEN 1

 

ELSEIF ATTR ([Excel sheet Tab name]. [Operating Unit]) = ‘All’

AND

ATTR ([USDC Indicator]) = ATTR ([Excel sheet Tab name]. [USDC Indicator])

AND

ATTR ([Mapped Service Area]) = ATTR ([Excel sheet Tab name]. [Service Area])

AND

ATTR ([Excel sheet Tab name]. [Industry]) = ‘All’ THEN 1

 

ELSEIF ATTR ([Excel sheet Tab name]. [Operating Unit]) = ‘All’

AND

ATTR ([USDC Indicator]) = ATTR ([Excel sheet Tab name]. [USDC Indicator])

AND

ATTR ([Excel sheet Tab name]. [Service Area]) = ‘All’

AND

ATTR ([Mapped Industry]) = ATTR ([Excel sheet Tab name].[Industry]) THEN 1

 

ELSEIF ATTR ([Excel sheet Tab name]. [Operating Unit]) = ‘All’

AND

ATTR ([Excel sheet Tab name]. [USDC Indicator]) = ‘All’

AND

ATTR ([Mapped Service Area]) = ATTR ([Excel sheet Tab name]. [Service Area])

AND

ATTR ([Mapped Industry]) = ATTR ([Excel sheet Tab name]. [Industry]) THEN 1

// Below Calculation is derived from screenshot 3

ELSEIF ATTR ([Operating Unit]) = ATTR ([Excel sheet Tab name]. [Operating Unit])

AND

ATTR ([USDC Indicator]) = ATTR ([Excel sheet Tab name]. [USDC Indicator])

AND

ATTR ([Mapped Service Area]) = ATTR ([Excel sheet Tab name]. [Service Area])

AND

ATTR ([Excel sheet Tab name]. [Industry]) = ‘All’ THEN 1

 

ELSEIF ATTR ([Operating Unit]) = ATTR ([Excel sheet Tab name]. [Operating Unit])

AND

ATTR ([USDC Indicator]) = ATTR ([Excel sheet Tab name]. [USDC Indicator])

AND

ATTR ([Excel sheet Tab name]. [Service Area]) = ‘All’

AND

ATTR ([Mapped Industry]) = ATTR ([Excel sheet Tab name]. [Industry]) THEN 1

 

ELSEIF ATTR ([Operating Unit]) = ATTR ([Excel sheet Tab name]. [Operating Unit])

AND

ATTR ([Excel sheet Tab name]. [USDC Indicator]) = ‘All’

AND

ATTR ([Mapped Service Area]) = ATTR ([Excel sheet Tab name]. [Service Area])

AND

ATTR ([Mapped Industry]) = ATTR ([Excel sheet Tab name]. [Industry]) THEN 1

 

ELSEIF ATTR ([Excel sheet Tab name]. [Operating Unit]) = ‘All’

AND

ATTR ([USDC Indicator]) = ATTR ([Excel sheet Tab name]. [USDC Indicator])

AND

ATTR ([Mapped Service Area]) = ATTR ([Excel sheet Tab name]. [Service Area])

AND

ATTR ([Mapped Industry]) = ATTR ([Excel sheet Tab name]. [Industry]) THEN 1

 

ELSEIF ATTR ([Operating Unit]) = ATTR ([Excel sheet Tab name]. [Operating Unit])

AND

ATTR ([USDC Indicator]) = ATTR ([Excel sheet Tab name]. [USDC Indicator])

AND

ATTR ([Mapped Service Area]) = ATTR ([Excel sheet Tab name]. [Service Area])

AND

ATTR ([Mapped Industry]) = ATTR ([Excel sheet Tab name]. [Industry]) THEN 1

ELSE 0 END

ELSEIF ISMEMBEROF (“Active Directory group (AD Group)”) THEN 1

ELSE 0 END

Note *: Tableau Server stores all user names in the Tableau Server identity store, which is managed by the Tableau Server Repository. If a Tableau Server is configured to use Active Directory for authentication, you must first import user identities from Active Directory to the identity store. When users sign in to Tableau Server, their credentials are passed to Active Directory, which is responsible for authenticating the user; Tableau Server does not perform this authentication. (By default, NTLM is used for authentication, but you can enable Kerberos or SAML for single sign-on functionality—however, in all these cases, authentication is left to Active Directory.) However, the Tableau user names stored in the identity store are associated with rights and permissions for Tableau Server. Therefore, after authentication is verified, Tableau Server manages user access (authorization) for Tableau resources.

Put the calculated Field into Quick Filter and select True as its value i.e. value as 1. Now, user should be able to see only those metrics which is related to their role/access and not the underlying data for other user.

The benefits of this dynamic method is:

You do not need to manually manage user access to the row level data in each individual worksheets. You just need to update user permission on excel sheet. Once a new user is added in excel sheet, Tableau filter will just inherit the changes.

About the Author: Rajeev Pandey

I am a multidisciplinary designer working in data visualization, interaction design and innovation. Expertise in developing Tableau, Web focus based visualization and reporting applications. I have a passion for analyzing, dissecting, and manipulating data sets as well as, building beautiful dashboard. Naturally talented in communicating between technology and business needs. Diverse and experienced in plenty of different domains .I am quick learner who can absorb new ideas and can communicate clearly and effectively.I love creativity and enjoy experimenting with various technologies.

6 Comments+ Add Comment

  • Thank you for submitting such wonderful content.very informative and useful for tableau learners.we are very happy to recieve such wonderful content.highly informative and one of the recommended sites for tableau learners.

    Tableau Training In Hyderabad.

  • Hi – This is something that I've been looking for. Do you have a sample workbook that has this implemented? I'm better at reverse engineering than following the step-by-step. Thanks.

    • Please try to follow the steps mentioned on the blog.Currently I havent added on Tableau public. 

  • Hi ,

    This information is very useful, but i have multiple users with some similar groups in this case the extract will create duplicate records.
    Any better way to handle the scenario.

    Thanks,
    Chaitanya

    • Never faced this issue as of now but I will try from my end and will get back to you

Leave a comment

You must be logged into post a comment.