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

[Anonymous]

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.

[TomMartens]

Hey @Anonymous ,

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

[Greg_Deckler]

@dm-p @TomMartens