Forum Discussion
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
- TomMartens
Super User
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 - Greg_Deckler
Community Champion