cancel
Showing results for 
Search instead for 
Did you mean: 
jirineoral

Different approach to Dynamic Row Level Security

There were already written few blog posts both about Row Level Security. I would like to add one more about this topic. I use this pattern for several years in SQL Server Analysis Services. Biggest advantage of this approach is, that you don’t have to fight with table relationships, which can be sometimes tricky to make work correctly.
To follow with steps, you can download sample file, which uses Adventure Works Sample Data.

File can be downloaded here: https://drive.google.com/file/d/0B9ZohZ1CALKZOFZBRG9YZERWakk/view?usp=sharing

Introduction

Let’s have a look at the report and scenario. Adventure Works sales products, that are grouped to categories. Common requirement is to limit data in report, so category manager would see just his/her category. So if I want to grant access just to “Bikes” I could write DAX row filter

DAX formula:

[Category] = “Bikes”

This could work for few categories. But if you have 40. Role membership would be hard to maintain. Same thing can be handled by one role using configuration table.

01.jpg

Solution

For purpose of blog post, I will enter data manually using “Enter Data” option. You can replace data in table with real user names and email addresses. In real life I use for this configuration SQL Server Master Data Services, but any table would do.

02.jpg 

Table security can stay unrelated

03.jpg

 

What I’ll need is to create calculated column to DimProduct table. Let’s call it Security_Category using function lookupvalue searching in security configuration table.

DAX formula

Security_Category = LOOKUPVALUE(security_mapping[CATEGORY],security_mapping[CATEGORY],[Category],security_mapping[USER],"domain\username")

first column is the value I want to return, second is searched column, third value I want to find, fourth second column to be searched, fifth value.

Arguments 4,5 are here to restrict access to particular user if we had multiple users in same configuration table.

04.jpg

DAX filter needs to be evaluated as true value. I can check if anything was found by function ISBLANK function

DAX formula

Security_Category = ISBLANK(LOOKUPVALUE(security_mapping[CATEGORY],security_mapping[CATEGORY],[Category],security_mapping[USER],"domain\username"))

05.jpg

Formula returns now true, where records weren’t found. I need it oppositely and can switch logical value using function NOT

DAX formula

Security_Category = NOT(ISBLANK(LOOKUPVALUE(security_mapping[CATEGORY],security_mapping[CATEGORY],[Category],security_mapping[USER],"domain\username")))

06.jpg

As next step I can create new role Dynamic Security in Power BI desktop and apply Row Filter on DimProduct table referencing calculated column with formula from previous step.

07.jpg

I can check what if visible to role members using View as role Dynamic Security.

08.jpg

09.jpg

To make it really dynamic, I will replace static value “domain\username” with function USERNAME() in formula.

DAX formula:

Security_Category = NOT(ISBLANK(LOOKUPVALUE(security_mapping[CATEGORY],security_mapping[CATEGORY],[Category],security_mapping[USER],USERNAME())))

Function USERNAME behaves differently in desktop where it returns username in format “domain\username” and when published to powerbi.com where it returns email address username@domain.com

Therefore before publihing replace column USER with column EMAIL in DAX formula

Security_Category = NOT(ISBLANK(LOOKUPVALUE(security_mapping[CATEGORY],security_mapping[CATEGORY],[Category],security_mapping[EMAIL],USERNAME())))

Conclusion

In this blog post I covered how to implement dynamic security in Power BI without relationships in data model using DAX formulas. Let me know if you have any questions or comments

Jiri

Comments

Hi Markus, 

thanks for pointing this out. I forgot to mention one last step. Username() function can't be used in calculated column. So you should rather put the final formula into role row level filter directly.

Paste this expression into role definition and it will work

NOT(ISBLANK(LOOKUPVALUE(security_mapping[CATEGORY],security_mapping[CATEGORY],[Category],security_mapping[USER],USERNAME())))

When publishing to PowerBI.com

 NOT(ISBLANK(LOOKUPVALUE(security_mapping[CATEGORY],security_mapping[CATEGORY],[Category],security_mapping[EMAIL],USERNAME())))

Thanks

Jiri

Thank you for your soon reply, Jiri. 🙂

Yes, it works.
I just need to figure out in detail the pros and cons of your approach compared to the approach by means of enabled cross-filtering in both directions.
For example:
How does the each approach need to be modified in order to for example filter by Category and Region, meaning two columns.

However, you helped me a lot. 🙂

@jirineoral. Sorry for the spam. Have few updates. Let me summarize my approch.

 

Data Source: I have 2 entities in On-Prem SQL Server

  1. Customer (ID, CustomerName, Revenue, Area, Subsidiary, Country)
  2. ManagerPermission (Alias, Email, Country)

Report: Created Report in Power Bi Desktop with "Import" option. Create a new role called "DynamicRLS" with expression on "Customer" entiry.

 

NOT(ISBLANK(LOOKUPVALUE(ManagerPermission[Country],ManagerPermission[Country],[Country],ManagerPermission[Email],USERNAME())))

Published Report to online, but not able to see any data in report as other user. Then I added an security group explicitly to Role (Dataset > Security > Row-Level Security > DynamicRLS). Now, able to view report w/ filtered data, as expected. Thank you for your post, Very helpful.