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

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

Anonymous — Tue, 22 Sep 2020 21:28:06 GMT

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.

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

Greg_Deckler — Wed, 23 Sep 2020 00:14:49 GMT

@dm-p @TomMartens

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

TomMartens — Wed, 23 Sep 2020 16:39:44 GMT

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-vs-javascript-filters

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