Dynamic Security in Power BI

UPDATE: the feature has changed since its introduction. For more information, see the update at the bottom of the post.

In case you haven’t noticed, Power BI added a new feature in preview mode: row level security (RLS). This blog post describes how you  can use this feature to implement dynamic security. In other words, the data being displayed depends on the user that’s viewing it. I will not talk about live connections to Analysis Services, there are plenty of resources on how to implement dynamic dimension security in SSAS (for example the excellent Expert Cube Development).

Let’s start with a very basic data set:

powerbisecurity_01

I included the email address of the user, as well as a (fictional) domain login. I loaded the data into a Power BI Desktop model. For testing purposes, I tried to add a calculated column that would show my username, just to see if it would display an email address or a domain login. Alas, this is not possible.

powerbisecurity_02

So I needed to create a measure instead. This measure just counts the number of rows where the email address matches the USERNAME() function. I created a similar measure for the domain login.

powerbisecurity_03

When I threw everything on a canvas, I got the following result:

powerbisecurity_04

Apparently, in PBI the domain is used, not the email. Good to know if row level security is implemented in PBI someday (note: currently it’s only available in the Power BI Service).

OK, now let’s upload this report to the Power BI Service so we can implement the RLS. When the report is opened, it seems  that my carefully crafted measures are ignored. Maybe Power BI ignores the USERNAME() function as long as there is no RLS defined.

powerbisecurity_05

Now, let’s add the RLS by clicking on the three dots at the data source and choosing Security. As a member, I added the distribution list of the Microsoft Competence Center of my company. This works because we are using Office 365 for mail. Currently it’s not possible to use Active Directory Security Groups, unfortunately.

powerbisecurity_06

Then I defined a simple rule that just checks if the email equals the USERNAME() function.

powerbisecurity_07

You can test your role by clicking on the three dots and choosing Test data as role.

powerbisecurity_08

Now my measures seem to be working fine, as well as the rule.

powerbisecurity_09

At the top, you can choose between roles or you can specify a specific user to test the security.

powerbisecurity_09bis

When I choose my own account, I still see all of the data (and the measures go totally crazy. It appears I’m all people at once). This is probably because I’m the owner of the data set and thus some sort of god-like admin. It’s the same in Analysis Services: if you’re an administrator you can always see all of the data, even if roles are denying you access to it.

powerbisecurity_10

When I choose another user, you can see that the security is correctly applied.

powerbisecurity_11

When someone is chosen that has access (because they are part of the distribution list) but whose email is not in the email column, the table is just empty. When someone is chosen that doesn’t have access, the visual gives an error.

powerbisecurity_12

You can only test reports though, dashboards are not listed. This makes it a bit harder to test them, since you’d have to actually log in as that person to see what the results are. I quickly created an easy dashboard with two tiles: one tile is a live tile displaying the entire report, the other a simple card displaying the value for MeasureA.

This is what I see (as owner of the data):

powerbisecurity_13

(expertly photoshopped in Paint to make it a bit smaller)

I kindly asked Davy to log in into Power BI and sent me a screenshot of what he saw. The result:

powerbisecurity_14

So RLS works on live tiles (as I would expect), but also on regular tiles. Thanks again Davy for the assistance.

That’s it for this blog post. RLS is still in preview, so it’s possible some things can change along the way. Currently there are some limitations (AD groups being one of them) and I hope they are resolved soon.

UPDATE:
Row level security in Power BI is now in general availability. There are some changes however: you have to define the roles and their filters inside Power BI Desktop instead of in the service. Adding members to a role still remains in the service. This separation makes sure that you can republish Power BI Desktop files without losing the defined security.

Koen Verbeeck

Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at element61, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

3 thoughts on “Dynamic Security in Power BI

Leave a Reply

Your email address will not be published. Required fields are marked *