Row-Level Security Basics in Tableau

Data

Row-Level Security Basics in Tableau

by Grant Eisenmenger
//

In this blog, we will run through fundamental concepts and building blocks for Row-Level Security (RLS) solutions. This will include:

  • USERNAME() and Entitlements Tables
  • ISMEMBEROF() and Permissions Groups
  • Implementation Patterns
  • Some Simple Examples
  • Version Considerations
  • Mistakes to Avoid

If you are questioning if you need RLS or you just want a high-level overview, check out our first blog in the series, Do I Need Row-Level Security (RLS) in Tableau?

Our use of RLS can be segmented into two basic types:

  1. Customizing data on Tableau Server/Online based upon an individual user
  2. Customizing data on Tableau Server/Online based upon a consistent group of users
    • The users may change within the group, but the group itself is static.

For #1, think about trying to provide multiple account executives with data for only their relevant accounts. This type of security typically relies on the USERNAME() function and Entitlements Tables mentioned below.

For #2, think about providing all data for the South Region to members of the South Region group. This type of security typically relies on the ISMEMBEROF() function and maintaining User Groups on Tableau Server/Online.

While we are introducing these two types of RLS as distinct options here, they are often combined in real implementations. One of our examples in the next blog post will contain an instance that does just this.

User Functions

User Functions are built into Tableau and allow us to pull information into calculated fields about a User logged into Tableau Server/Online. You can check out Carly Capitula’s blog, which outlines a full list of our options.

USERNAME()

The Username function will return the username of the current user. This is the Tableau Server or Tableau Online username if the user is signed in. If the user is not signed in, it is the local or network username for the Tableau Desktop user. This function is case-sensitive.

The USERNAME() function is often used in tandem with an Entitlements table to develop a solution that is more robust and scalable.

Entitlements Tables

An Entitlements table is a table of data that describes people or groups and what they are allowed to see. For example, think about your favorite paper supply company. Regional managers should see their store’s data but not other stores’, a manager higher up the chain might see multiple regions, and the Assistant to the Regional Manager might be barred from seeing any data. The Entitlements table reflects these “permissions” in such a way that we can tie into our other data. Here is an example of a “deepest granularity” Entitlements table for a sales hierarchy in South America:

ISMEMBEROF()

The ISMEMBEROF() function returns true if the logged-in person currently using Tableau is a member of the group that matches the string. If the person is not signed in or not a member of the group, it returns false. This function is case-sensitive. Here’s an example:

Permissions Groups

Using the ISMEMBEROF() function requires a group to be created on Tableau Server/Online and users be added to the group. Additionally, the members of the group will need to be maintained either from the Tableau Server user interface or REST API calls to the server. AD groups can be synced to Tableau Server. Oftentimes, we see security provided around server groups in simpler cases where security can be defined by groups only, and/or an Entitlements table would be difficult to create or maintain in the shorter term.

Overall, The USERNAME() function is more dynamic than ISMEMBEROF(), since ISMEMBEROF() must test against a static literal string that is typed into a calculation. Both functions can be used to filter data based on a user’s attribute or group membership, like this:

A General Pattern of Implementation

Once we have an Entitlements table that describes these permissions, we can join it to a “Fact” table that includes the data we are reporting on, like sales. This join will look different depending on the method we use—we will talk through options in the next post—but if you are unsure what type of join to do, play it safe at the beginning and use and INNER join.

After we join the tables, we need to provide logic for how the data should be limited for a user visiting the dashboard; this will be done by filtering on a calculation that references one of the user functions we mentioned above. While we set these up in the order of 1a, 1b, Tableau will generally execute them as 1b first, then 1a, so that we do not generate a huge table in the intermediate steps:

Below is a diagram of the typical implementation pattern we recommend to provide the best security around your data. The Noodle refers to Tableau’s new Relationships data model, available in version 2020.2 and later:

Some Simple Examples

With the USERNAME() Function

Here is a very simple example of a way to set up RLS in Tableau with an Entitlements table using the USERNAME() function. Let’s say the original data table looks like this:

And our Entitlements table looks like this:

(1a) These tables are set up with an INNER JOIN on Country Code.

(1b) The developer has set up a data source filter, equal to TRUE, based on a calculation that says:

When I log in as User 3, who is a national manager for Brazil, Tableau will first use the logic above to filter the Entitlements table down to this:

Then INNER JOIN the filtered Entitlements table to my data table, resulting in data that looks like this:

With the ISMEMBEROF() Function

Here is a very simple example of a way to set up RLS in Tableau only using ISMEMBEROF(), without an Entitlements table. Let’s say the original data table looks like this:

And User3 is placed in the “BR” group on Tableau Server.

(1b) The developer has set up a data source filter, equal to TRUE, based on a calculation that says:

When I log in as User 3, who is a national manager for Brazil, Tableau will first use the logic above to filter the data down to this:

Considerations Around Tableau Versions

The points above can also help a lot around providing optimal performance around RLS; we will talk more about performance in the next blog.

Mistakes to Avoid

Always use a Data Source Filter for any RLS filtering; never use an Extract Filter. An Extract Filter will return an extract tailored for the user who created the extract and will not adjust to other users.

Additionally, the one data source set up to avoid in newer Tableau versions (2020.2+) is this:

Taking a “Logical” Extract with a physical join of the Entitlements table will either take the extract with the developer’s permissions only or blow up the row count of the extract.

Now that we know the basics around RLS, our next blog will move into describing some simple solutions that can help you deploy RLS for your use case! If you’re looking for support or guidance around RLS in the meantime, let us know. We’d love to start the conversation and help set you up for success.

More About the Author

Grant Eisenmenger

Analytics Consultant
Options for Row-Level Security in Tableau Now that you know you need Row-Level Security (RLS) and you are familiar with the basic concepts and building blocks, we can take a ...
Row-Level Security Basics in Tableau In this blog, we will run through fundamental concepts and building blocks for Row-Level Security (RLS) solutions. This will include: ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072