Introduction

What is RLS ?

RLS, or Row-Level Security, is a method of restricting data access in a database or business intelligence environment based on the characteristics of the user. Essentially, it allows administrators to control who can see what data within the same table or report. This is particularly valuable in environments where you have sensitive or private data that should only be accessible by certain users.

How is it different from RBAC ?

RLS in Power BI allows you to control access to rows of data based on the user viewing the report. This means you can create filters within roles defined in Power BI Desktop, and when users log into Power BI, they will only see data relevant to these filters.

RBAC, on the other hand, is broader and controls access at higher levels of the Power BI environment, not just at the data level. RBAC helps manage who has access to workspaces, reports, dashboards, and what kind of operations they can perform (like read, write, share, or delete).

How to enable RLS.

What you will need ?

The 'user table'

The users table is a table that maps all the users that will have access to the data, to the data they can see. It should be structured in the following way :

• user_id : the unique identifier of the user, that can be mapped back again an item of the PowerBI Authentication Payload.
• scope : the scope of the data available to the user. This is a string that will be used to filter the reporting data.

How to configure the RLS ?

The RLS is implemented by adding the user_table to your PowerBI dashboard and by (inner) joining it onto the reporting table. The user_table can then be used to filter down data from the reporting table the user has been granted access to. To do so, you will need to create a new role in your PowerBI dashboard, and add a filter to the reporting table that will filter down the data based on the scope of the user.

1. Ensure that your data model has the necessary columns to define RLS filters. Basically, you need a set of dimensions you want to apply RLS to, to be available in both the reporting table and the user_table. The user_table should also include a column acting as a primary mean of identification for the user (in the example, we will be using the email column).
2. Import the user_table into your PowerBI dashboard, and create a relationship between the user_table and the reporting tables. The join must be done on the scope column.
3. Create and manage role :
   1. In Power BI Desktop, click on the Model tab, then click on Manage Roles.
   2. Click on Create to create a new role.
   3. In the Manage roles dialog box, enter a name for the role, and then click on Add Table Filter.
   4. In the Table Filter DAX Expression dialog box, enter a DAX expression that filters the data based on the user's email address. This is done to filter the users table to the current dashboard'viewer. For example, if you want to filter the data based on the user's email address, you can use the following DAX expression:

    'user_table'[email] = USERPRINCIPALNAME()

   Copy to clipboard
   5. Click on OK to save the DAX expression.
   6. Click on Save to save the role.
   7. Click on Close to close the Manage roles dialog box.
   8. Publish the report to the Power BI service.

Example : implementing RLS at the school level

In this example, wee will be implementing RLS to restrict access to the data based on the user's email address, so that each user can only see the data for the school they are associated with.

Step 1 : Create the users table

The users table should have the following columns:

• email : the email address of the user
• school : the school the user is associated with

Step 2 : Implementing RLS in PowerBI

1. Import the users table into your PowerBI dashboard, and create a relationship between the users table and the reporting tables. The join must be done on the school column. This is the big deal : **if your reporting table DOES NOT contain a school column you won't be able to join the user_table table, and you won't be able to apply RLS to this table **.
2. Create a role :
   1. In Power BI Desktop, click on the Model tab, then click on Manage Roles.
   2. Click on Create to create a new role.
   3. In the Manage roles dialog box, enter a name for the role, and then click on Add Table Filter.
   4. In the Table Filter DAX Expression dialog box, enter a DAX expression that filters the data based on the user's email address. This is done to filter the users table to the current dashboard'viewer. For example, if you want to filter the data based on the user's email address, you can use the following DAX expression:

    user_table['email'] = USERPRINCIPALNAME()

   Copy to clipboard
   5. Click on OK to save the DAX expression.
   6. Click on Save to save the role.
   7. Click on Close to close the Manage roles dialog box.
   8. Publish the report to the Power BI service.

Why is it working ?

Here is what happing when a user is viewing the report, deep inside the the PowerBI engine :

1. The user logs with his email into Power BI and the report is displayed.
2. PowerBI set the USERPRINCIPALNAME() to the user's email.
3. The DAX expression in the role is evaluated, and the user_table is filtered to the user's email.
4. The tables joined on the user_table are now filtered to the user's email, and the user can only see the data for the school they are associated with.

A note about USERPRINCIPALNAME()

USERPRINCIPALNAME() is a DAX function that returns the currently authenticated user's email address. This function is only available in Power BI service, and not in Power BI Desktop. When you publish the report to the Power BI service, the USERPRINCIPALNAME() function will return the email address of the user viewing the report. This is useful for filtering the data based on the user's email address.

My customer is a fancy boï, he wants the RLS to work at school level, but also at class level. How can I do that ?

Since PowerBI doesn't handle multiple columns join (it's only been half a century since we walked on the moon after all), you will have to concatenate the columns you want to join on into a single column. This is done by creating a new column in all your reporting and user_table tables. Creating one column for multiple column can be done by simply concatenating the columns you want to join on. Of course, the concatenation order must be exactly the same in both your uer_table and your reporting table (otherwise the join won't work). If you wan't to show off how smart you are, you can do it in SQL BEFORE importing your data and use the hash of the concatenation as the join column instead of the concatenation itself (to save some tiny smööll bits of precious memory) (I did it and have no regrets !).