Different approach to Dynamic Row Level Security

by jirineoral Regular Visitor on ‎10-19-2016 02:29 PM

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

Attachment
Comments
by Markus_Hanisch Visitor
on ‎11-14-2016 04:33 PM

I'm currently preparing a slide deck on Dynamic RLS and came across your blog post.
I tested your solution. Whenever I want to replace the string value "domain\user" with the USERNAME() or USERPRINCIPALNAME() function, I get the following error:
"The CUSTOMDATA function and the USERNAME function are not supported in calculated columns. These functions must only be used in measures or in 'AllowedRowsExpression'."

 

If I use the string it works perfectly fine. Can you double-check this behavior in your model?
DynamicRLS_LOOKUPVAUE_DataModel.jpgData model with relationships and "security_mapping" table and calculated column "Security"


jirineoral wrote:

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




by jirineoral Regular Visitor
on ‎11-14-2016 11:32 PM

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

by Markus_Hanisch Visitor
on ‎11-15-2016 01:11 AM

Thank you for your soon reply, Jiri. :-)

by jirineoral Regular Visitor
on ‎11-15-2016 01:18 AM

Your wellcome Markus, 

does it work for you now?

 

Thanks


Jiri

by Markus_Hanisch Visitor
on ‎11-15-2016 01:23 AM

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. :-)

by jirineoral Regular Visitor
on ‎11-15-2016 01:49 AM

The biggest advantage is that, you don't have to bother with relationships at all and you don't have to change your data model. You are filtering data, that are already related in your data model referencing unrelated mapping table. In past I never came across a situation where I couldn't use this unrelated method, I came across several situations where in order to relate data it would require change in data model. And I didn't want to change data model just for security Smiley Happy

Jiri

by jjuelke Regular Visitor
on ‎11-27-2016 08:12 AM

greate tip!

Just for clarifcation how to get this to work...you have to define a role-filter on table [Product] as fllow:

 

[Category] =

LOOKUPVALUE(security_mapping[CATEGORY]

,security_mapping[CATEGORY],[Category]

,security_mapping[USER],USERNAME()

)

 

no calculcated column and no ISBLANK()... needed, really simple and can be extended to multiple security columns as needed

by ypradeep23 Frequent Visitor
on ‎12-23-2016 02:40 AM

I am trying this approch with Direct Query mode. Looks like "LOOKUPVALUE" is not supported in it. And getting errors while defining Measure.

 

Is there any work-around for Direct Query mode. ?

by jirineoral Regular Visitor
on ‎12-23-2016 04:14 AM

@ypradeep23 if you use direct query mode, you need to define row level restrictions rather in data source then on report level. 

Jiri

by ypradeep23 Frequent Visitor
on ‎12-24-2016 04:19 AM

I tried this approch by Improting data from On-Prem SQL Server. It is working in 'Power BI-Desktop'. But some how it is working as expected when publish to Online. Report owner (my alias) is able to see all data & other users not able to see any data. Not sure where it went wrong. Smiley Sad

by ypradeep23 Frequent Visitor
on ‎12-24-2016 04:58 AM

@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.

by mscampos Regular Visitor
on ‎02-17-2017 11:16 AM
I did not have success to implement it. For some reason the report don't respect my role, it show all data.
by jirineoral Regular Visitor
on ‎02-19-2017 12:28 PM

@mscampos where it doesn't work? Desktop or service? If service and you published to shared workspace rather than "my workspace" it ignores roles for administrators of that shared workspace. Admins see everything

Jiri

by mscampos Regular Visitor
on ‎02-20-2017 04:27 AM

@jirineoral Thanks for reply.

 

When I use the mode "VIEW AS ROLE" in desktop it works fine.
When I publish it in "MY WORKSPACE" it ignores my roles.

Had I configure something after publish it?

by jirineoral Regular Visitor
on ‎02-20-2017 08:07 AM

@mscampos

After publishing to service, you need yet to set up membership for roles. But not sure if it will restrict your access if you're the publisher. I tested it using second account

 

Jiri

by Markus_Hanisch Visitor
on ‎02-20-2017 12:38 PM

@mscampos

As @jirineoral already stated you are most probably the admin / publisher of your data set. Therefore, RLS settings won't have any effect on your user. I tried and tested the above mentioned procedure with another user. And it worked just fine.

 

by mscampos Regular Visitor
on ‎02-22-2017 05:44 AM

@jirineoral@Markus_Hanisch thank you guys! It worked!!
The rls don't apply for the "owner", I tried with other user and it worked.

by Brysonds Frequent Visitor
on ‎04-19-2017 01:10 PM

Hi Jiri,

 

Very interesting approach! I haven't had a chance to test yet, but will this model work if the same person belongs to multiple categories AND if multiple people belong to the same category?

 

Example:

 

Example Table.JPG

 

by jirineoral Regular Visitor
on ‎04-20-2017 12:48 AM

Hi @Brysonds,

yes it works, that's how I use it

 

Jiri

by S-F Visitor
on ‎05-08-2017 12:14 PM

I understand the logic but when I try to use the USERNAME() I get an error because it says the function can't be used in a calculated column. Any help would be greatly appreciated.

 

ERROR:

CUSTOMDATA, USERNAME and USERPRINCIPALNAME functions are not supported in calculated tables/columns. These functions may only be used in Measures or in the AllowedRowsExpression.

by jirineoral Regular Visitor
on ‎05-14-2017 10:59 PM

hi @S-F

this was already addressed on ‎11-15-2016 08:32 AM. Please read previous comments


Jiri

by msudulag Regular Visitor
on ‎07-21-2017 10:24 AM

Hi Jiri,

 

This is nice post!!

 

I have tables users,roles,application, each application having different roles ,each user havinf different roles here

i want to display if user is Admin role for that Particular application he has to see all others data also.if is not Admin he can see only his data .

 

I am taking all tables into my model and creating relation after that how i have to map in Manage roles Dynamically

Can you please help me on this.

 

Regards

Mallikarjun

by jirineoral Regular Visitor
on ‎07-25-2017 01:49 AM

Hi Mallikarjun,

There is no DAX function to check role membership as far as I'm aware

Maybe use some PowerShell script to list members?

 

Jiri

by Markus_Hanisch Visitor
on ‎07-25-2017 08:58 AM

Understanding the Power BI admin role this link helps you to understand the admin role and the PowerShell script to get the object ID of the role as well as assign it to a user.

Then have a closer look at this PowerShell script:

Get-​Msol​Role​Member

Get-​Msol​Role​Member

 

I hope this helps.

by onlinetimesheet Visitor
on ‎11-09-2017 11:56 PM

Very helpful post thanks for sharing a knowledge with us

by mahmoud_sameer Visitor
‎02-14-2018 11:58 PM - edited ‎02-15-2018 12:06 AM

HI Jiri,

 

Thanks a lot for this post and it is very useful and interesting.

I tried the solution and worked , but I got error when the user has access to more than one category due to the lookup function returns multiple values, above there is some one asked about the same situation and you answer with that it will work, so can you tell me how to solve this problem because Iam stucking in this problem .

 

Regards

Mahmoud

by mahmoud_sameer Visitor
on ‎02-15-2018 03:03 AM

HI Jiri,

 

Thanks, I did it , I had a mistake in one of the steps.

 

Thaaaaaaanks

by jirineoral Regular Visitor
on ‎02-15-2018 04:00 AM

Hi @mahmoud_sameer

you're wellcome Smiley Happy I love problems that are resolved before I can respond to them Smiley Very Happy

 

Jiri

by paruchuri447 Regular Visitor
on ‎03-05-2018 10:39 PM

I have implemented dynamic row level security and added table filters in manage roles as below. the below is created in Employee table. 

 

CONTAINS(
Users,
Users[Email ID],
USERNAME(),
Users[state],
[state]
)

 

Users Table:

 

Account ID,       Email ID,                           State

123                    krishan.1@gmail.com       MD,PA,OH

144                    k.2@gmail.com                 

 

Employee Table:

 

Account ID,    Employee phone number, Employee state, Employee City

123                 1234567890                       MD                     Glenbernie

144                  4567777774                      NY                       Jefferson

 

If i use above DAX expression when i logged in as krishan.1@gmail.com it is restricting but when i logged in with k.2@gmail.com the report is showing blank because there is no data in users table. 

 

If there is no records for state in users table then it has to show everything for that user not other user data.

 

How can i do this.

 

Please help.

 

Thanks,

Paruchuri

by jirineoral Regular Visitor
on ‎03-14-2018 01:35 AM

Hi @paruchuri447

add unrestricted users to unrestricted roles

 

Jiri

by sgselvait Visitor
on ‎04-16-2018 05:59 PM

Hi. Is it possible to mask the Bike category(category will be "Restricted") instead of filtering "Bike"?