I recently created this simple Power BI desktop file that allows you to try out dynamic security with the new security relationship feature as described in this blog post.
The model contains the following tables:
- Sales – contains the metrics, the fact table
- Group – is the dimension table that groups a set of users. This can be anything in the real world, a department, a geography, productgroup, etc
- User – the users with their username connecting to the reports
- UserGroup – This ties individual users to the “group”, this usually is a M:M relationship where many users belong to multiple groups.
All relationships are set to single directional filtering except the one between Group and UserGroup. This allows the filter from the User table to filter the groups down to only those groups where the current user has access to, in turn filtering out the rows in the sales table for just those groups. I also enabled the Apply security filter in both directions property:
This property only shows up when you have the preview feature called “Enable cross filtering in both directions for DirectQuery” enabled that you can find in the options menu.
The report contains this a simple tablix that shows the products, sales and groups including the username and the userprinciplename (using the corresponding DAX functions) that is connecting to the model:
As you can see both return slightly different values, the userprinciplename function return the User-Principal-Name attribute from AD which is a UPN that is an Internet-style login name for a user based on the Internet standard RFC 822. Username returns the SAMAccountname which is a logon name used to support clients and servers from previous version of Windows.
If you open the role management, there is one role in there that filters the username table using the earlier mentioned DAX USERNAME() function:
This allows you to test this inside of Power BI desktop or you can replace the username() function with any hardcoded string as well to try out how the security works. Here is a subset of the data as being secured by the role:
Hope this helps you get start started faster with dynamic RLS. You can download the file here: http://www.kasperonbi.com/uploads/security.zip .
Thanks for this great blog post, as I was trying to figure this out myself. I would possibly suggest one key piece missing, which is included in the Bi-Directional relationship to “Apply security Filter in both directions” needs to be enabled!
Hi,
Ive applied the Bi-Directional relationship to “Apply security Filter in both directions” needs to be enabled! however my grand total value isnt corrent. Any ideas?
Thanks for blog , we have slightly different scenario , fact table contians salesman username and he should see only his data and his manager should see his and all his salesman data. how to achieve this hirarchical RLS
Hi Surenda
Not sure if you’ve already figured this out but if not here’s a blog post which explains how to set it up with a hierarchy – http://angryanalyticsblog.azurewebsites.net/index.php/2016/12/27/dynamic-rls-via-hierarchy-in-power-bi/
Kasper, imagine I am an ISV and I want to group the data from all my clients in a unique Data Warehouse, and then I apply RLS to distribute the content to the client. The tables from the data source have the same structure for all clients.
My first idea was to include a column identifying the client in all tables, and apply the RLS for all of them in a static way.
I cannot apply the RLS only on the Fact Tables because the values on the Dimensions are different for the clients, and they would be visible on Filters, for example.
Is this really the only solution for my problem? It is quite complicated doing the way I am doing because I have over 30 tables in the model and over 40 clients.
I wish there was a way of doing this dinamically.
Thanks in advance
Hi Kasper
I have tried to implement DRLS in the PBI Desktop File for sample ADW files but have hit a roadblock similar to at work where we currently do not have PBI Service, hence distribute PBI Desktop files to users via email or shared paths. The requirement is for the DRLS to take effect automatically when the user opens the PBI Desktop files from their local machines.
In PBI Desktop file the problem is that every time any user opens the file, it opens in the default “None” role mode which means all data is visible to all users. This severely compromises data security.
Only after the selecting the view as roles – “User”, does the DLRS come into effect. Why does the PBI file not open with a User role for any user. How can this be overcome and can the “User” be set as the default role or delete the “None” role.
Please note we do not have PBI Service and this is similar to SSAS OLAP and TAB security using excel cubes which we implemented successfully.
Thanks
Uday
Hi Uday, that will never work. Power BI desktop will not respect security, the user opening it is the admin and can see all the roles. You can only use this with a server component (PBI desktop is free).
Kasper
Hi Kasper,
I have a scenario where I have implemented location wise dynamic rls security so when the user logins it fetches them particular country he is part of it.Dax -Country[CountryKey] = LOOKUPVALUE(
‘Security'[CountryKey],
‘Security'[EmailAddress],USERPRINCIPALNAME(),
‘Security'[CountryKey],Country[CountryKey]
)
Now client says I need to block “FRANCE&AUSTRALIA” do we need to add any “all except” function in above DAX or is there any other way to switch on/off access for this scenario.
Note-im working on Direct query
Thanks
nag
you mean you want to block both countries? In that case you should update the ‘Security'[CountryKey] column to contain both. AlleExcept does something completely different.
Hi Kasper,
To add more to the topic I have a scenario where we need to implement dynamic rls organizational level report so for using path function achieved in import but I need to achieve in DQ so I’ve created 9 levels in vertica db and pulled it off to PBI.
and created roles like email=userprinicipalname()||[L1mgr]=userprinicipalname()||[L2mgr]=userprinicipalname()||till 9 levels
2)now I have a scenario like if any strange numbers found in ex us I need to block fr particular our sales reps.
3)They need to restrict current year / last year fr few sales reps.
Hw can we achieve 2 & 3d we have the premium capacity with 15k employees.
Hi kasper,
Finally, after long testing, UPN role works well with import&DQ and now 2&3 options need to be achieved dynamically.
Thank you,
kng
Hi – great post, this has helped me so much!
What value is the ‘Users’ table adding here? Can’t you just apply the same DAX filter to the ‘UserGroup’ table and get the same result?
Also, is there a simple way to implement hierarchical subgroup / OR filtering in PBI? So in your context if users belong to a group and (optionally) to a subgroup?
How would this work if there were overlapping groups? So would it be possible within the same model to restrict one viewer’s reports to a group (say country) and another viewer’s to a type (say toothbrush sales, or whatever, but across all countries)?
Thanks!
You could but the performance will be better if you use the smaller dimension table.
You could add a role for each group, the data is always additive so if you can see data from one role but not through another the allowed role wins. Unfortunately there is no solution for using AD groups, you always need to have the username, the majority of users have tons of groups and it would be too expensive to iterate over all of them in the engine. You could write a sync tool that syncs all users from a group to a table in the model.
I have a requirement where a user can belong to multiple roles. Each roles filter on a different dimension table that is tied to a central fact table.
I’ve been able to implement it using CONTAINS or LOOKUPVALUE but the performance is taking a hit with that method.
If I use this method and create a set of intermediate tables for each role with relationships to it’s respective dimension, I end up getting the error below
“The user belongs to multiple roles “AAA” ,”BBB”, “CCC” that have security filters, which isn’t supported when one of the roles has filters affecting table ‘FactTableName’ with SecurityFilteringBehaviour=Both relationships.”
Hey Kasper, thanks for the post! I am able to download the sample file and reproduce the desired results. However when I publish to the Power BI Service all rows are returned.
I have validated the Users field is equal to the Username() and in my AD setup Userprinciplename() as well, however the filter is not happening. Something seems to change when publishing to the service.
I’ve done lots of DRLS in SSAS and the Enterprise Gateway with success, but for some reason, and unfortunately this requirement is non SSAS.
Any ideas?
In the Power BI service you are likely the owner of the dataset\workspace and thus an admin that sees everything. The only way to test is to use the “view as” functionality : https://docs.microsoft.com/en-us/power-bi/service-admin-rls#validating-the-role-within-the-power-bi-service
Hi Kasper,
Thanks for the post, this is very helpful.
I have same issue as some of users had i Power BI service.
My dynamic RLS is working fine in desktop, when I test with specific email.
But it is not working for users in Power BI service.
I have added user emails to roles in Power BI Service. They are read only members (sometimes they are just app users).
But still DRLS is not kicking in?
Looks like I might be missing some additional steps in Power BI service. Can you please let me know.
I found the reason why Dynamic RLS was not working. I have multiple fact tables in the model. I have one role set for each of the fact table. So I had same users assigned to more than one role.
When I have a user assigned to one of the roles then it will work. Then the user sees all data in other fact tables.
So have anyone setup multiple fact tables and multiple roles and same user assigned to more than one role in the model?
Appreciate your help on this.
Does this work for guest accounts in AAD? Guest users need to log into reports in power bi that uses RLS on an on prem SSAS tabular. Is this possible?
Thanks,
Dan
Hi Kasper,
I have implemented the same RLS in Power BI Desktop when I view in Desktop the UserPrincipalName() works fine however when I published it to the Service it not filtering based on RLS in the table, please help us ASAP. Thanks
Regards
Sohail
I suspect that you have configured the app workspace so that members have edit permissions, the RLS roles will not be applied to them. Users will be able to see all of the data. If they can edit they can also updated the dataset. https://docs.microsoft.com/en-us/power-bi/service-admin-rls#using-rls-with-app-workspaces-in-power-bi
Thanks Kasper, so should I add them as Viewer/Member access only please Advise. Thanks
exactly.
Hi Kasper,
We have external user so once I create UserPrincipalName() RLS in Desktop and than release it as app it the RLS should work even for external users i guess please advise. Thanks
Sohail
yes it will, the username might not look like you expect it to. Please check it.
Hi Kasper,
Thank you for sharing your knowledge.
I’ve been using some of the ideas you proposed on your post 🙂
As the reports I did where Proof of Concepts, it was fine to “hard code” the tables User – Group (I created them in the CDS).
However, now that we want to scale the solution up, questions regarding the sourcing have been brought up.
What do you think is the best practice? Source the tables (User – Group) from the application (SAP, Navision, CRM, etc) or create them in an external environment (Common Data Model) and pull data from there?
Thank you!
It really doesn’t matter as long as you can get the data out the way you want it. Probably using SAP or CRM it might be difficult.
Hi Kasper,
I would like know what if the user want to see all the groups that are available? It is not feasible to add row one by one for all the groups and it will be tedious also. So whats the simplest way to do that? So I would to achieve the followings.
1. If A,B,C and D are the groups that are available. What should I do to give access to all the groups.
2. If I have given access to A,B and D groups to the user in securtity table, what should I do to give access to only those groups who have been given access in the security table. Not like giving access to all the groups like above.
Please help me out on this…
Hi Kasper,
Thank you for this post. I have the hierarchical RLS set-up and it works just fine – when a user logs in he/she only sees his/her own data and data of users rolling up to him. However, upon login, the default view in the Power BI dashboard is just blank/zero. I was wondering if it’s possible to configure the default view where upon login, the filters are automatically set to the user’s own data but still able to select others’ data when user changes the filters?
Hi Kasper,
Thank you.
We have a scenario where different reports demand different RLS mehod.
Is there a way to set RLS method based on a report name or any other parameter that the report can send?