Data Visualization with Tableau (36 Blogs) Become a Certified Professional
AWS Global Infrastructure

BI and Visualization

Topics Covered
  • Pentaho BI (9 Blogs)
  • Microsoft BI (5 Blogs)
  • Data Visualization with Tableau (34 Blogs)
  • BI and Data Visualization with Qlikview (1 Blogs)
SEE MORE

Row Level Security in Tableau : Implementation and Usage

Last updated on Jan 13,2021 3.1K Views

Today, organisations of all sizes suffer from a few common ailments such as compromise in confidentiality and time-management issues. How nice would it be to have a solution to show the viewers the information they need to see. Row Level Security in Tableau is that solution!

This article discusses all you need to get started with Row Level Security in Tableau  and the outline of it is as follows;

  • Prerequisites for Creating Row Level Security in Tableau
  • Introduction to Row Level Security
  • Ways to Implement Row Level Security
  • Hierarchial Relationships & Composite Keys
  • When is Row Level Security Built Into Your Database?

Prerequisites for Creating Row Level Security in Tableau

1. You need to create users and groups in Tableau Server;

2. You need to be connected to Tableau Server/Tableau Online because the Tableau Desktop will download the list of users and groups from there

3. Your User Filter should be on the same Data Source level according to the filter order in Tableau

NOTE: Embedded Data Sources are less secure and should be used only if Web Edit/Download will be disabled.

Now that you know what you need, let’s try to understand what this complex term actually means, shall we?

Introduction to Row Level Security 

Before understanding Row Level Security in Tableau, you need to understand some terminologies, namely, Entitlement and Entitlement Tables.

An Entitlement is basically a single unique combination of Attributes that the Data View will be filtered on. These Attributes must exist in Column(s) of the Data View. It could be a geographical hierarchy of a Region, Sub-Region, and Country, or any other combination you could think of.

An Entitlement Table is one combination of attributions that the data will be filtered on. For example, filter on Region, Sub-Region, and Country(as mentioned previously) is a single entitlement. Standard database design practices mean, you seldom have a single table that fits the criteria for the entitlements table. Most data aren’t usually mapped one-to-one to a single user; very often the data security is organized either by role, organization name, or both.

A basic breakdown of the process can be explained in the following steps:

  • Determine the Username
  • Get your Data Entitlements for said User, just that User
  • Filter your Data by those Entitlements

row level security process - row level security in tableau - edurekaWays To Implement Row Level Security in Tableau

There are 2 main options to implement Row-Level Security:

Using only Tableau

One way to implement Row Level Security is solely through Tableau. You can do so using:

  • Live/Extract connection
  • User Filter built and applied per data source

Let’s consider an example of implementing Row Level Security using only Tableau with user filters defined on Tableau Desktop. First of all, we create the users and groups required on Tableau Online.

Click on the Menu Bar >Server  > Create User Filter. A list of available fields appears before you to begin your user filter form.

Also, if you are not already signed to into Tableau Server/Online you may be prompted to do so at this stage.

row level security using only tableau - row level security in tableau -edurekaHere we have some georaphical data and we need to assign each user to their appropriate region in a fashion that allows only one user to map to a given row of data.

Your brand new user calculation appears under measures in the Data pane just like your other fields.

geographical data - row level security in tableau -edurekaIn the dialog box that pops up, select True and click on OK.

Using Hybrid Approach

Another way to implement Row Level Security is through a mixed approach where user information in Tableau Server or Tableau Online corresponds to data elements in the database.

  • Live/Extract connection
  • Leverage Data Server
  • Use Data Source Filters

So, in this example we connect to both the Orders with respective sales representatives. This contains actual data for visualization and the entitlement table which is the People’s Table. Data table gets joined to the entitlement table that conatins user names and key values. The table is an instance of having components in the database that associate users to specific data elements such as Users to Regions.

hybrid row level security - row level security in tableau -edureka♠NOTE: This is relatively rudimentary example which in real life isn’t always the case.

We use a Calculated Field to create a User Filter that leverages this data, creating the following formula that checks if the user name matches the sales representative field.

USERNAME() = [Username]

Once this Field is created, it can be dragged to the filter shelf or applied to multiple worksheets through embedded as a Data Source Filter.

Hierarchial Relationships & Composite Keys

Most organizations have a hierarchy, and often there is the requirement that those higher in the hierarchy can access the data of all of those who report to them but not vice versa. Let us, for instance, look at the following hierarchy to understand the concept better. We have 3 levels to consider:

  1. CEO(Level 3)
  2. Team Leads(Level 2)
  3. Employees(Level 1)

To represent various hierarchesl of the data, one effective technique is to have a row in the Entitlements View representing the most granular level of each security filter. You will probably need to construct one view out of many different tables that store different parts of the user entitlements.

To represent this in your Entitlements View for data by department and regions, the CEO will have a row for every department and region. Each Team Lead will in turn, only have a row for department that belong to their responsibility. Each Employee under a Team Lead will probably have none.

For hierarchically complicated entitlements tables you may need to use multiple fields to join them with the main table. This is known as a Composite Key.

It can be handled in 2 ways:

  • Create a Composite Key field in your Entitlements View and your Data Table. Then JOIN the two
  • JOIN the Entitlements View to the Data Table for every field that makes up the Composite Key

A simple example of hierachial filters is as follows:

IF ISMEMBEROF(‘level 3') THEN 1
ELSEIF ISMEMBEROF(‘Team Leads’) THEN
IF [Team Lead Username Field] = USERNAME() and [subdivision(entitlement)] = [subdivision(data table)] THEN 1 ELSE 0 END
ELSE 0 END

When is Row Level Security Built Into Your Database?

  • In Oracle, if you set up VPD for the database users, you can use Initial SQL in Tableau. This way you can make use of the existing security filtering
  • In post 2016 versions of SQL Server, you can set up Row Level Security in the database, based on the user. Tableau’s Impersonate-User-functionality will set the user correctly for you, while SQL Server does the filtering.

This brings us to the end of this article on Row Level Security in Tableau.

If you wish to learn Tableau, Edureka has a curated course on Tableau Training & Certification which covers various  like conditional formatting, scripting, linking charts, dashboard integration, Tableau integration with R and more. 

Got a question for us? Please mention it in the comments section and we will get back to you at the earliest.

 

Comments
0 Comments

Join the discussion

Browse Categories

webinar REGISTER FOR FREE WEBINAR
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP

Subscribe to our Newsletter, and get personalized recommendations.