How to implement Dynamic-Row Level Security in Power BI

Introduction:    

I recently had the need to implement some Row-level security (RLS) changes to a semantic model, my first thought was to do it using Power BI desktop as this is where I originally did it in the past, but now that wasn’t a good choice given that the development of this model is now done with Tabular Editor 3 so I took this as a good learning opportunity and wanted to share my experience.

               But before we jump into the details let’s start with a quick refresher of what RLS is and how it can be useful in our projects.

               RLS is an important element for data security. It must be used when we have the need to restrict data access for given users, the filters restrict the access at the row level, and we can define filters within roles. Let’s say that for instance we want to restrict certain sales representatives to only having access to their accounts so in this case we would be applying a filter logic that enforces a relationship between the “Salesperson” and “Accounts” entities.

               The first step that I took was to review the latest documentation regarding RLS available in Microsoft Learn and Tabular Editor 3, links available as references at the end of this article. I’d like to give a shout out to Kurt Buhler for all the details that he provides on the “Configuring Data Security” section in Tabular Editor 3 documentation.

               Once I leveled up my knowledge about RLS I felt ready to start implementing it and after multiple trial and errors these are the steps that I ended up implementing, so I wanted to share them in the hope that they can help you as a guideline.

               The following steps aren’t intended as a step-by-step guide given that your implementation may vary depending on your specific needs and current data modelling.

               I found that the best option was to use a combination of tools that I’ll describe in the next steps, your mileage may vary, and you might prefer a different combination of tools or rely more on one of them.

On Tabular Editor 3

1) I started the process within Tabular Editor 3 and one thing that I needed to do was to add a bridge table, to avoid a many to many relationship, for those not familiar with bridge tables is a table that contains one record per unique value in the key field between two tables that we can use to relate many-to-many dimensions.  I wrongly assumed that given that the many to many relationships are now supported in Power BI I would be able to implement RLS on them but that wasn’t the case, so this was one of my main takeaways.

2) After I added the new bridge table, I configured the relationships and removed the original many to many relationship.

3) Add new role “Dynamic-Row Level Security”

4) Set Model Permission Property to Read

5) Add new Table Permissions to the Role and add the table that will be filtered.

6) Add a DAX expression as a Filter.

'TableXYZ'[UserID]=MID(USERPRINCIPALNAME(),1,LEN(USERPRINCIPALNAME())-13)

7) Deploy to Power BI Service

On SQL Server Management Studio

1) Assign an Entra security groups to the role

2) Connect with SSMS to the PBI workspace where the dataset is hosted

3) Right click on the role and go to Properties/membership and add the required security groups

On Power BI service

1) Give the Security Groups access to the workspace as viewers

2) Add Build permissions at the dataset level (this is an optional step in my case I wanted the users to be able to connect to the model via XMLA endpoints)

3) Test access

3.1) Go to Model more options and select Security

3.2) Go to Role/More Options/Test as role

3.3) Select the report that should be used for testing, the report should be in the same workspace as the semantic model.

3.4) Go to View as… and select role and person to be tested.

And voilà, the information will be updated accordingly if RLS is required.

Conclusion:

·        Even though I needed to use different tools to implement RLS I was able to do it using Tabular as the main one while SSMS and PBI Service were great supporting tools.

·        I don’t feel comfortable giving users access at the workspace level, even if it is only viewers access, this goes again the principle of least privilege but it seems that there is no way around it and we need to adjust our implementation accordingly.

·        I would say that the main challenge that I faced was when I tried to test as role in Tabular Editor 3, I followed the steps in their documentation and after multiple failure attempts I gave up and tried in the Power BI service instead, I might have missed something but I would recommend you to consider Power BI service as an alternative if you face the same issues.

I hope you find this useful, and you can follow these guidelines if you have a similar need to implement RLS, if you do so please share your experience and thanks for reading!

References:

Setup or Modify RLS | Tabular Editor 3 Documentation

Row-level security (RLS) with Power BI - Microsoft Fabric | Microsoft Learn

Many-to-many relationship guidance - Power BI | Microsoft Learn