cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

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.
  • PBIRLS.png
  • 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
  • PBIRole.png
  • 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?

PBIService_Role.png

 

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

Thanks!

 

 

 

1 ACCEPTED SOLUTION
Microsoft
Microsoft

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.

View solution in original post

4 REPLIES 4
Microsoft
Microsoft

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.

View solution in original post

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

Super User III
Super User III

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

"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?

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors