Row Level Security in Power BI

Today we are starting a new series on Power BI Row Level Security (RLS). Implementing RLS in Power BI can be a fascinating topic. Especially when you have design constraints, and cannot rely on a simple logic to apply RLS, or when your IT team cannot support your special security logic with customized AD groups. This is where a good understanding of modeling (Not DAX), and the Query Editor can become very handy.

Before we dive in, here are some pointers you can consider reading. As a starting point – Read Power BI Documentation here. Next stop: Kasper de Jong had written great posts here and here, with additional resources, that can help you to implement dynamic security.

This series will try to take you to the next level.

In today’s post, we will work on Kasper’s Power BI Report (Download it here), which was published on his Power BI Dynamic Security Cheat Sheet, and we’ll learn how to troubleshoot the security logic, and and further improve it.

Before we start, let’s download the report from here. The report implements RLS and ensure that the visual below, will only see the rows of the relevant logged-in user.

To review the rules, go to Modeling tab, and click Manager Roles. You will see that a single role was implemented with a filter on Users.

This rule is static. It will only work when “User” logs in. Kasper used the static rule for testing purposes, but it will not work if you publish it to the powerbi.com service. When other users will log in, they will not see any row in the visual above. To fix the rule, we should change the “User@poc.onmicrosoft.com” to “USERNAME()”.

And here is the modified DAX expression. Let’s click Save to continue.

To test our rule, we can now click View as Roles in Modeling tab, check the Other user box, and set User@poc.onmicrosoft.com in the text box. Next, let’s check the last checkbox – Role User 1, and click OK.

Note: By applying both check boxes, we instruct Power BI Desktop to apply our rule on the logged in user that is set in the Other User box. The term Other in that check box can be misleading in our example. We don’t test other rules, we test a user who is other than us.

Now, we can see the yellow notification bar, that shows us that our security logic is applied on User@poc.onmicrosoft.com

Extend the security beyond one table

Let’s create a new Table visual with Group, Product and SalesAmount from table Sales. You can follow the red arrows in this screenshot, if you are not sure how to create the table. Hey, did you find the problem? We are seeing rows from Sales that are not associated to User. Our guy belongs to Group A only. We shouldn’t allow him to see Sales data of other groups.

How to troubleshoot the Security filters

Let’s switch to Relationship view. Do you recall that we applied the DAX filter on table Users? You can imagine a flow that starts with the Users table. The user logs in and all the rows in Users that match the user’s email are filtered. Then all the UserGroup rows of the logged in user should be filtered. Next, Group should be filtered, and finally – Sales table.

So, let’s switch to Data view and check each of the tables, starting from Users, UserGroup, Group and finally Sales.

Switch to Data view (click the table icon in the left pane). Select the table Users in the right pane. Remember, we are still viewing the report as User@poc.onmicrosoft.com.

The table looks good. We can only see the one filtered row with our user email and Group A.

Let’s select the next table, UserGroup, which maps between the users and their corresponding groups. This table shows a single row with our user and his group. This is a good sign. It means that the relationship between Users and UserGroup works well.

Let’s move to the next table – Group. Do you see the problem?

Our user is assigned to group A, so we shouldn’t find here the other groups. The fact that we see three rows with Groups B and C suggests that our relationship between UserGroup and Group is not filtering the Group table.

Does it make sense to keep all the rows in Group unfiltered? Let’s check out.

Moving to the last table, Sales, we can see that our user can see all the rows. SECURITY ALERT!!!

By reviewing the tables from User to Sales, we have found out that the problem started in table Group. Our logged-in user can have access to all groups. we can determine that the relationship between UserGroup and Group is the root cause for the problem.

Let’s edit the suspicious relationship.

In the Edit relationship dialog, we can examine the Cardinality, Cross filter direction and Apply security checkbox. The relationship between UserGroup and Group is Many to one. This setting is correct. We can have the same group name in column Group for multiple users/rows, while the Group table must contain unique group values.

As the Security flow starts from the User table, and flows to UserGroup, and then to Group, we should ensure that the Cross filter direction is set to Both, and not to Single. By default, the filter flows from lookup tables (One) to fact tables (Many), and not the other way around (Many-to-One).

So, our Cross filter direction is correctly set to Both.

Finally, as we are troubleshooting the security filters, we should ensure that the bidirectional settings should be applied on the security flow. And here is our catch! The checkbox Apply security filter in both directions is not checked.

Problem found!!!

Let’s check the Apply security box, and click OK.

It works!!!

We can now see that our logged-in user can only see the Sales rows of Group A.

Conclusions

In this article, we learned how we can extend the security of our RLS implementation in Power BI by extending the security filter to work on more tables. We visualized a flow that starts from the users table and moves through the relationships to the other tables. Using the View as Roles, we tested the security logic, and examined each table in Data view to identify the tables who should be filtered. When we identified a “data breach” and found rows that shouldn’t be available for the tested user, we checked the relationship that led to that table, and fix it.

Hope you enjoyed this article. Stay tuned for the next post in this series, where we will apply RLS on Star Wars data, and increase the level of difficulty.

Featured Image was modified from its source. Original image here. Why ducks? Don’t ask me such questions 🙂