Dynamic RLS with AD Security Groups - best practices and app sharing?

[Vegard1985]

Hi.

I just wanted to post some of my own experiences on DRLS, and want to reach out to get some tips/ideas/best practices on how DRLS should work in practice, and how this should be distributed.

We currently run a BI landscape on HANA, where we sync roles with logic derived from AD - users will be replicated as user in HANA, a security group will be replicated as a role in HANA, and the role will be granted to the users. All we need to do is maintain any privileges/filters for the roles, normally a one-time job, and a very simple privilege revolving access to specific companies data.

Naturally, querying HANA works fine access-wise as long the query is run directly by the users themselves and directly connected to HANA (like Analysis for Office, and Design Studio (which is currently being phased out)). We have used PowerBI as a front-end tool for a couple years, and to handle data access, we have needed to adopt RLS, but access has been handled very manually. 

I'm currently looking at a dynamic RLS set up, and was hoping to get some thoughts on best practices of the distribution part. Our access/security-needs are very simple, where an AD security group has been set up for each company in the group.

• First I created a PowerBI report using AD content ("group", "user" tables) to create a final table with security group + userprinciplename + a query-merge with a manual list of company codes connected to these groups, and published this to a central workspace where we plan to set up "golden datasets" going forward. (In retrospect, I should've read up on the reuse of PowerBI datasets in other reports as a source, but my thought was to add this dataset to reports, and set up the DRLS part in the reports, as one would need to do.)
  Long story short, I was unable to use a PowerBI dataset as a source, because "you cannot explore live data and connect to another type of data source in the same file". Bummer..
• I then thought - let's create a dataflow that does the same. I created the dataflow, used the "group", "user" tables from AD, merged the queries, and also merged this with a manual input on company codes per security group. But refresh couldn't be done because this was a Computed Entity - it would need a Premium capacity..
• Ok, so I need to create this merge in each report then I guess.. At least I ended up creating a dataflow to just retrieve the "group", "user" and the manual table, no merging involved.

So, I'm now here:

1. A dataflow to get the AD "group", AD "user" and manual ('RLS') table is up and running and refreshed every two hours

2. In the PowerBI report (and need to do this for a bunch of other reports, but should only be a one-time job)

• I'm pulling in the AD group-table, merging this with the AD user-table as inner join (because I noticed the group-table included users that has been deactivated/deleted), and merging this with the 'RLS' table, to create an "AD Security Group" mastertable that holds all security groups, active users and the 'RLS' company code values
• I've disabled the load of the user table, it's not needed for this to be visible, only needed in the merge process.
• The 'RLS' table is also added to the model, which creates the necessary Many-to-one relationship link between the dataset and 'RLS' table, and ultimately the security group mastertable which is cross-filtered both ways.
• 
• One role is created like below, with a DAX-formula to first check if userprinciplename() is part of a specific group that can see all data (company code = *), if not, filters the "AD security group" mastertable with the user
• 
• This works fine as far as I can see, at least checking with PBI Desktop and using different users in the "View as" filters this like a charm.

Question 1: We need to create and use this logic for all reports where we want RLS in place?

Quesiton 2: If yes to 1, how do you handle the "reusability" of this logic, is creating a PowerBI dataflow and merging this in every report the only/best way to go? 

Question 3: DRLS covers the "data-access" part of reports. But are there any tips/tricks/best practices on how apps/reports are distributed in PBI service? We have 20-30 security groups, do I really need to add all these groups within the "Security" part of the dataset, and then again share/give access to the same groups for the App? Or could we teoretically just add a "all employees" group in the RLS-part of PBI service, and share the App with the applicable groups?

I hope you can shed some light on how you would create the best access-routine for reports and RLS 🙂

Thanks!

[kumarprabhu815]

Hey, Can you share the pbix file if you dont mind

[lbendlin]

It would have to be a Direct Query scenario (enable the preview feature)  but even then the performance will most likely be unsatisfactory for the actual report users.

[DCEQAO]

Sorry for my ignorance - newbie here...

Another kick at this topic re: best practises:    In my solution, I am proposing to use a security group based access control to secure sensitive data, much as the orignal poster has outlined, but with one significant difference - I wish to dynamically evaluate a user's membership in groups using a GraphAPI "https://graph.microsoft.com/v1.0/users/{user}/memberOf" instead of a table containing the users as original poster suggests.    The output from a join of the memberOf to the Groups table should yield the appropriate permissions.

Does any one have a method to include the GraphAPI data as a "table"  joined against a table in a tabular model using a live connection?  I could also change to a solution utilizing direct query if necessary, instead of a live connection.    

Is there some other way to surface the user's group membership in M or DAX that I have missed?

Comments and guidance would be most appreciated - I can't be the only one wanting to secure DRLS with security groups!

Thanks to all in advance!

[v-janeyg-msft]

Hi. @Vegard1985 

I don't know api very well, so I may not be able to help. Have you tried using dynamic rls? I think it can also meet your needs. Yes, use dq and rls, performance may be low.

Use userprincipalname:

Row-level security (RLS) with Power BI - Power BI | Microsoft Docs

Best Regards

Janey Guo

[v-janeyg-msft · Accepted solution]

Hi, @Vegard1985 

It’s my pleasure to answer for you.

According to your description, I think you have great ideas. If your reports needs to be distributed to users with different permissions to see different data, RLS can indeed be used, and it is inevitable.

In the recent update, it has been supported to connect powerbi dataset and other data sources at the same time.

LInk:Power BI December 2020 Feature Summary | Microsoft Power BI Blog | Microsoft Power BI

After setting up RLS, you can publish an app, add the "all employees" group you mentioned, and then they will see different content according to the permissions you set, and other users outside of RLS can access the report, but they can't see all the data.

Remember not to give the user  build permission of the dataset or edit permission of the report, otherwise RLS won't work.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jef]

Hello,

I just read this reply, well mostly the big red line. I am currently helping to start setting up a Self Service BI environment. Does that mean there is no way to use one model for multiple people with different access to data? 

If not, are there any upcoming projects to deal with this?

Kind regards

[lbendlin]

It depends on your capacity.  Shared or Premium or Embedded?

[Jef]

It is in premium capacity

[lbendlin]

Then you are good to proceed. Make sure to only share the app, and not the dataset or report.

[Vegard1985]

Hi, Janey - thanks for your input! 🙂 I'll be suer to double check the build permission!

[lbendlin]

"you cannot explore live data and connect to another type of data source in the same file"

Not any more! yipee!

Anyway, here's my take:

- RLS is based on either assigning users and distribution lists to roles in the Power BI service or (at the lowest granularity level) on identifying the user via USERPRINCIPALNAME() and then doing the group/permission mapping based on a reference table.  You will want to pick your approach based on your scenario but I would assume you go with the latter.

- RLS needs to flow in order to bite.  That means the RLS rules need to come from a dimension table at the far outskirts of your star/snowflake, and they better only point one way.  Having a bidirectional search setup wholly defeats the purpose of RLS

- Lastly, RLS is a lot of effort for not a lot of gain. It is highly appropriate for finance and sensitive data, but it is highly counterproductive for pretty much anything else. Only use it if you absolutely, positively must use it. In all other scenarios teach your users how to filter to their focus areas.

[Vegard1985]

"Not any more!" - is this in preview or something? Or in December version - I have PBI desktop November version 😐

Thanks for your input! I agree that RLS shouldn't be used unnecessarily. I don't plan on doing this for anything else than sensitive data, we don't need RLS to help employees "filter" correctly. Our group partly consists of competing companies, so anything that shows margin is off the table for employees in daughter companies - strict ref Competition Authorities.

Do you have any tips on my question 3 though, about PBI Service/app distribution?