cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

How to do Dynamic RLS in PowerBI without manually entering roles?

My company is trying to do Dynamic RLS for PowerBI Embedded on behalf of our customers, but I am running into some major roadblocks while trying to secure the data.  The requirements/steps of what we are doing are:

 

  • Create reports on behalf of our customers, who in turn give access to the reports to their customers.
  • Our customers create users/logins via our product (which they share with their customers), and assign key-value pairs to said users to act as security.  Example: {"groupId": 123, "region": ["US-West", "US-Southwest"]}
  • When the end-user logs into our application, they should see the embedded report filtered appropriately: group_id = 123 AND region IN ('US-West', 'US-Southwest')
  • Since the data is dynamically generated, and since these key-value-pairs aren't known ahead of time (neither the number of them, the keys, the values, or the data types), we cannot hardcode or hand-enter in roles anywhere (unless there is a static role or two that can be re-used), nor can we re-publish frequently as roles are modified.
  • We are taking the "App Owns Data" approach to embedding PowerBI in our application.

All approaches to RLS I have seen thus far require manual entry of roles/members either into a PowerBI Desktop, PowerBI web, and/or a static table in PowerBI Desktop.  Are there any approaches that will work?  The only option I have seen so far is attempting to embed the key-value-pairs into the embed token's username field (which is limited to 256 ASCII chars only) and somehow parse it using a DAX expression.

2 REPLIES 2
Highlighted
Super User IX
Super User IX

Re: How to do Dynamic RLS in PowerBI without manually entering roles?

@dm-p @TomMartens 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IV
Super User IV

Re: How to do Dynamic RLS in PowerBI without manually entering roles?

Hey @matt_airkit ,

 

I consider this requirement not possible, due to this simple fact: RLS is based on roles with rules assigned.

 

Theses rules are an essential part of the data model and can not be changed during querying.

 

I thinks the only viable approach is to create something called "JSON script filters", here you will find some more description: https://docs.microsoft.com/en-us/power-bi/developer/embedded/embedded-row-level-security#using-rls-v...

 

Of course this is something that I would call: security by obscurity.

 

As this architecture is a more complex architecture, meaning there are three parties involved I would consider this approach:

  • Your customers has to provide you with information about email and the key/value pair
  • something (maybe an Azure function is executed triggerd by an Azure Logic App, the function executes a TMSL script and

voila a new role is created.

 

I have to admit, that I'm currently not sure if the data model has to be re-processed if a new role is added, my assumption: No!

 

Regards,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.