There are multiple ways to set up RLS in Power BI. The traditional way is to set static roles & assign email ids to those roles individually. Though this approach is very easy to implement, it can become a nightmare to maintain if the number of roles becomes high. Another way & most efficient way is to set up a single role in Power BI & dynamically control the access rights by connecting it to a source table which will have all the details about the data to be filtered. This approach is also referred to as Dynamic Row Level Security or simply Dynamic RLS.
What is Row Level Security?
With Row Level Security, you can restrict the data that the end-user consumes. RLS enables you to create roles & assign users to those roles so that they get access to only the data that is relevant to them. For example, you can ensure that workers access only those data rows that are pertinent to their department. Another example is to restrict sales manager’s data access to only the data relevant to their location.
Why Dynamic Row Level Security?
One issue in following the traditional approach is that it requires to manage the RLS manually from Power BI & its becomes very difficult to maintain if the number of roles are very high.
Dynamic RLS solves all these issues. This approach allows you to create a single role in Power BI Development whose access rights can be managed from a simple excel sheet or from a table in a database.
Blog structure:
- Intro to the sample dataset
- Creating roles
- Adding members in Power BI Security
- Sharing reports
Intro to the sample dataset:
For this blog, I have created a very simple dataset consisting of 2 tables coming from an excel sheet.
You can download the pbix file from here.
The two tables are
1. ‘sales_data_sample’: consists of sales data along with the country of the sales
2. ‘rls_access’: has a column for country & a corresponding email id of the person who must have access to a particular country’s data
Table ‘rls_access’ & ‘sales_data_sample’ are connected to each other via column ‘COUNTRY’ present in both the tables. Make sure that the table with email id filters the table that contains the sales data.
Note: If the relationship demands the Cross filter direction to be both (bidirectional cross filtering), then make sure that you have enabled the ‘Apply security filter in both directions’ option for the RLS to work properly (See image below).
I have also created a few visualiations to get an easy view of the whole dataset.
Creating roles in Power BI
Once the dataset is ready, the next step is to create roles in Power BI.
Before creating roles let me introduce you to 2 DAX functions: Username() & UserPrincipalName().
These functions do the same job i.e. they return the domain name or the system name according to the environment its called in.
To understand how this function works, create a measure with any one of these functions & put it in a card. You will notice that these functions return the pc name if viewed on desktop & return the account id when published in the Power BI service. This account id in the Power BI service will help us match with the email id defined in the ‘rls_access’ table.
To create roles,
From ‘Modeling’ ribbon -> select ‘Manage roles’ -> In ‘Manage roles’ window, Select Create -> Give it a readable name -> Select the table which has the email ids -> Add filter -> Select the ‘EMAIL’ column -> Replace “value” with the ‘Username()’ function -> Click on Save.
The role we created will compare ‘rls_access’ s Email column to the output returned by Username(), which if satisfied will show the data filtered according to the email id. This simple line of code will help us to dynamically add RLS to our Power BI report.
Adding Members in Power BI Security
Once the role is successfully created, publish the report to the Power BI consulting services.
In Service, navigate to the Dataset you just published -> Go to its Security -> Under Row-Level Security -> Add email ids of all the members with whom you want to share the report -> Click on Save.
Note: Adding the members here doesn’t guarantee that the data will be visible to that id. The same id must also be present in the underlying dataset.
Sharing Reports
Now the last thing left is to share the report with other users so they can actually see the data that is assigned to them. There are multiple ways to share the reports in Power BI. In this example, we will be sharing the whole workspace with the users.
In Power BI Service, Go to the workspace -> Select Access -> Add email ids of users that the report need to be shared with -> Set the type to ‘Viewer’ -> Click on Add.
Note: Make sure that you only give ‘read only’ permissions while added users to the workspace. For this example, I have given users the ‘Viewer’ permission.
Now, Let’s test if the RLS is working.
Here’s a snip of the data I see, when I log in & see the shared report with the CS account:
As you can see, cs@addendanalytics.com can only see the countries that was assigned to it in our dataset.
Summary
Now you know how you can set up Dynamic RLS in Power BI. The main advantage of this method is that you don’t need to set up a new role every time there’s a requirement for it.
As this can be handled in the dataset itself, you can host this information in any of the cloud services such as SharePoint, Onedrive or in a database & manage it directly from there. Make sure that the data model is set up correctly so that the RLS works without any issues.
Karan Nair
Team Lead – Data Analytics
Addend Analytics