Row Level Security In Tableau

Row level Security In Tableau :

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

Challenges to User/Developer-

The above problem can be resolved with the help of Row Level Security in Tableau.

This approach for securing data at the row level applies to data sources with live connections and extracts data sources whose tables are stored as multiple tables.

Solution

Approach-

Now, before we begin, this article consists of the following elements;

The Entitlements View, which is a denormalized view, having rows for all possible Usernames and their matching entitlements

Data View. Now, this could be a single denormalized View or multiple Tables that are JOINED together, modelled as a single View. There is no reason not to reduce it down to a single Table when taking Extracts.

JOIN Clauses between the Entitlements View and the Data View on every field which makes up part of an Entitlement

These views are in the same database or Schema.

At a higher level of it all, a basic breakdown of the process can be explained the following way;

Determine the Username

Get your Data Entitlements for said User, just that User

Filter your Data by those Entitlements

Now let’s get down to understanding what these complex terms mean, shall we?

Standard Row Level Security in Tableau

Technical Solution –

Entitlements View

For optimum performance (and it brings the only set of tables if conveyance into AN Extract), you’ll need to hitch all of those tables along into one Entitlement read. Yes, this can lead to a blown-up version of the Entitlements, it shouldn’t lead to the info table itself rebuke.

The JOINs (in our example) would be:

SELECT * FROM users_to_roles_map Ur INNER be part of roles_to_entitlements_map re ON Ur.role_id = re.role_id INNER be part of entitlements_view e ON re.entitlement_id = e.entitlement_id

Now you’ll have a read (again, store within the information but is most optimal) that has usernames and every one of the columns of claim data.

Filtering the Data by the Entitlements

The Standard Method of Row Level Security in Tableau uses a JOIN between the Data View and the Entitlements view. We recommend this because the same technique can be used with Live Connections and Multi-Table Extracts

JOINs for Deepest Granularity Method

Row Level Security in Tableau You can represent the Deepest Granularity form of this in SQL like the following (although VizQL will produce whatever complex queries it needs, this is the basic form):

SELECT * FROM data d INNER JOIN entitlements e ON d.attribute_a = e.attribute_a AND d.attribute_b = e.attribute_b AND … WHERE e.username = USERNAME()

For our example, the JOIN condition will be

ON d.region_id = e.region_id AND d.sub_region_id = e.sub_region_id AND d.country_id = d.country_id

We do this in Tableau in the Data Connection dialogue. Just set up an INNER JOIN and add the fields from each table on each side.

JOINs for Sparse Entitlements Method

The SQL for the Sparse Entitlements method is more complex. If you are using Custom SQL (only possible for Live Connections), it would look like this:

SELECT * FROM data d INNER JOIN entitlements e ON (e.region_id = d.region_id OR ISNULL (e.region_id) AND (e.sub_region_id = d.sub_region_id OR ISNULL(e.sub_region_id) AND (e.country_id = d.country_id OR ISNULL(e.country_id)

We can’t do that level of quality in Tableau’s be part of the dialogue, however, you’ll get a close to identical result by doing the subsequent (Major hat tip to Dan Cory at Tableau World Health Organization right away represented this method once conferred with this Row Level Security pattern.)

In the Tableau Desktop knowledge association pane, you’ll solely do straightforward equality be part of definitions (=, <>, <, >, <=, >=) and not write additionally complicated be part of clauses. However, you’ll outline be part of Calculations that permits for the subsequent pattern;

In the calculation editor that pops up, just put the integer value 1

Now do the same for the other table. This creates what is called a Cross JOIN (yes, theoretically this is a “data blow up”, but luckily in both a Live Connection and Multi-Table Extract scenario, it shouldn’t be processed that way, due to the filters that follow).

Now we go into a Sheet to define the Row Level Security filters we want to apply.

The first filter is the Username limit, which will reduce down the total rows of entitlements:

[Username]=USERNAME ()

Now you have the choice to do separate calculations for each of the levels in the hierarchy, or you can combine them all together. Those calculated views look like:

[region_id] = [region_id (Entitlements View)] OR ISNULL([region_id (Entitlements View)]

or a combined one for all levels:

([region_id] = [region_id (Entitlements View)] OR ISNULL([region_id (Entitlements View)])

AND

([sub_region_id] = [sub_region_id (Entitlements View)] OR ISNULL([sub_region_id (Entitlements View)])

AND

([country_id] = [country_id (Entitlements View)] OR ISNULL([country_id (Entitlements View)])

Place these calculated fields all as Data Source Filters and they will now give you the correct results based on the hierarchy you have defined. The magic is in the ISNULL calculations — this creates the effect of the NULL in the particular entitlement column to be matched up to ALL items in the other column.

All Access or Deepest Granularity: An intermediate solution

Advantages-

Row Level Security in Tableau is allows two users viewing the same dashboard to see only the data they are allowed to see (Restrict Access at the Data Row Level).

Conclusion-

When we are migrating from Qlik sense to Tableau above solution will be more effective, section access will be effective functionality in Qlik sense so it will be done easily via Row Level Security in Tableau.