How Do You Know Your Power BI Security Roles are Right?

You need insights from your data, but you don’t want the wrong people seeing data that’s confidential or outside their responsibilities. In the Power BI service, there’s a robust way of ensuring that the right people get the data that they need for their purposes: row-level security (RLS). It’s a set of filters that only gives people those rows that they need according to their role. These roles are creating in Power BI Desktop and mapped in Power BI Service to users, or better, security groups. I don’t want to talk about the technical aspects of setting up RLS. There’s plenty of resources online on how to set this up (I’ve learned a lot from Reza Rad, for example). Instead, I’d rather open up a discussion about auditing security in Power BI reports.

If you can ask, how do we know that we are improving, you should also be able to ask how do we know that the security roles are implemented correctly. Data culture is not just for the business, but for the reporting team as well. I haven’t seen much discussion of auditing security roles in Power BI circles, so I’m genuinely curious about how others tackle this issue. Does everyone simply work hard and hope for the best? Or do you restrict everything at the database level and use different apps for different groups instead? There may even be regulatory reasons which require you to restrict it at the database level. But even if you do restrict everything at the database level, you still need to validate that security as well.

Personally, I LOVE Power BI RLS (and folks on Twitter agree). It enables me to carve out the data for those who need it without the report creep which happens with maintaining dozens of siloed reports. To ensure that roles are correctly configured, however, I’ve had to do some significant work. Wrong row-level security can be not only embarrassing, but costly as well. It can expose an organization to legal liability or put the organization at a competitive disadvantage. Because security roles can be high stakes, they need a comprehensive suite of approaches to administer them. This is definitely an area for growth in the Power BI offering.

Here are some ways that I’ve reviewed Power BI security roles:

• an Excel workbook with each role, table, and DAX rule (Excel to the rescue!).
• Double checking in Power BI Desktop that each role is correct by comparing the rules for each role and table to the Excel workbook. Should we take screenshots of each rule?
• As report creator, testing as Role for each role in Power BI Desktop.
• As administrator testing as Role for each role in Power BI Service.
• Create measures specifically for RLS that are put onto a tab in the dataset containing the data (either a hidden tab or a tab on the original dataset). This makes Testing as Role quicker.
• Power BI reports that get data from Active Directory to show what users are in what groups, powered by unique naming conventions to show only Power BI security groups. This makes it easy for any team member to notice anomalies. Example: Red Team PBI role is mapped to Red Team PBI security group.
• Automatic testing with process automation. There are many possibilities with this tactic.

How would I like to verify role security in Power BI?

1. View as role in Power BI Service for anyone in a role where there are no filters on any tables. This would enable others who aren’t report authors to check security, making the roles transparent to business users who need to verify the correct roles but aren’t report creators or Power BI administrators.
2. Export security roles from Power BI Desktop. This would facilitate rapid review at the time of report creation and updating.
3. Administrators extract the security roles from Power BI Service. This would enable auditing of the security roles in place.

The good news is that I’ve figured out how to do #2. I can now export security roles for Power BI Desktop. I have template file called RLS localhost.pbit on GitHub. It works like this:

• Open a report in Power BI Desktop
• Open up DAX Studio (the latest version with External Tools makes this a breeze).
• Run a query to get the catalog_name and look at the localhost number at the bottom of DAX Studio.
• Open up RLS localhost.pbit and put in the localhost number and catalog_name.
• You’ll need to authenticate your access with your Office login.
• Wait for report to refresh.
• Most likely, you’ll want to enable native queries and ignore privacy in the settings to prevent having to ok each native query and to avoid the formula firewall error.

Security role report looks like this:

The report lets me see if the roles make sense and are consistent. On the page shown, there’s one table for Role Name, with the tables that are directly filtered by that role. If tables filtered is less than the max for roles, then conditional formatting shows the gap. The second table lists each table in the model, how many roles reference it, if it has a direct restriction or not, and what tables indirectly filter it. Date table is highlighted to show that it’s not filtered directly or indirectly. Region and Salesperson tables are both filtered directly by three roles each. Sales is filtered indirectly by Region and Salsesperson tables. The cards at the top of the page show number of roles, total tables, and how those tables are filtered.

The report documents security roles for 1-to-many relationships in Power BI models. Many-to-many relationships not supported. Bi-directional filtering and bi-directional security not supported. Inactive relationships not supported. I can finally export all of my security roles from this report, including a local timestamp of when they were extracted. The report uses a method described by Chris Webb: Analysing Power BI DMV Queries In Power BI Desktop. The report uses a live connection and can only be refreshed in Power BI Desktop.

What about you?

How do you review your security roles? How would you like to review them? Are there things you do that I haven’t mentioned? How can we help each other to be more transparent, more accountable, and more evidence based in our reporting practices?

Microsoft’s platform is the world’s most fluid & powerful data toolset.  Get the most out of it.

No one knows the Power BI ecosystem better than the folks who started the whole thing – us.

Let us guide your organization through the change required to become a data-oriented culture while squeezing every last drop* of value out of your Microsoft platform investment.

* – we reserve the right to substitute Olympic-sized swimming pools of value in place of “drops” at our discretion.