In this blog, I will be showing how you can implement row level security in Power BI using the traditional approach. I have already written a blog on Dynamic RLS which you can find here.
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.
When to implement traditional Row Level Security?
Traditional RLS is a very simple approach which basically does the same job as Dynamic RLS. The difference is in the implementation where in traditional approach roles need to be manually created in the pbix file itself and access must be managed from service unlike Dynamic rls. You can consider implementing traditional rls when you know that the number of users or conditions will hardly be increasing in the future. It is very easy to implement & maintain as long as the roles don’t increase drastically & even the users are limited
Blog structure:
1. Intro to the sample dataset
2. Creating roles
3. Adding members in Power BI Security
4. Sharing reports
Intro to the sample dataset:
For this blog, I’ll be using the same dataset as I had used in Dynamic RLS. The main change in this dataset is that we’ll be using only 1 table i.e. ‘sales_datasample’ It consists of sales data along with the country of the sales.
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.
For this example will be creating roles on the ‘COUNTRY’ column. Here’s how it is done:
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 column that will filter the data -> Add filter -> Select the ‘COUNTRY’ column -> Replace “value” with the country name e.g. USA or Australia -> Click on Save.
As I had mentioned earlier, in traditional rls multiple roles will need to be created according to the conditions. You can’t directly assign users to these roles from Power BI desktop. You need to assign them in Power BI service’s Security.
Adding Members in Power BI Security
Once the roles are successfully created, publish the report to Power BI service.
In Service, navigate to the Dataset you just published -> Go to it’s Security -> Under Row-Level Security -> Add email ids of the members to the roles that we had created in the previous step -> Click on Save.
Sharing Reports
Similar to my previous blog, 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 give 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 my other account:
As you can see, this account can only see France & Australia as I assigned the emails to roles of those countries.
Karan Nair
Team Lead – Data Analytics
Addend Analytics