Embed BI, App owns data, dynamic RLS multiple filters

MaartenInc — Tue, 19 Jul 2022 10:00:05 GMT

Hello,

I'm a little stuck on implementing Dynamic RLS with multiple filters. For our scenario, I'd like to generate two reports.

To comprehend our security, a user is associated with a organizational unit (companycode, domaincode). A user also has one or more roles.

Our data model only has one table "ActiveAccidents". Which is a list of (you guessed it) active accidents. The table has fields CompanyCode, ReporterName and InvolveeName.

I'd like to create a report with dynamic RLS security, which filters the data:

On the user's CompanyCode
Checks whether the user has the role "CustomSQLRoleA"

I'd also like to create a report with dynamic RLS security, which filters the data:

On the user's CompanyCode
On the data where the user's email occurs in ReporterName or InvolveeName

Settings this up in Power BI desktop is quit straightforward.

But when generating the Embed Token. We always get following error message back:

request:

{ "datasets": [ { "id": "3a96d9f8-...-11bba1f611a2" } ], "reports": [ { "id": "d5c6f76b-...-8672d80a93ba" } ], "targetWorkspaces": [ { "id": "beca21a6-...-17f65322eb31" } ], "identities": [ { "username": "m.demo@demo.be", "datasets": [ "3a96d9f8-...-11bba1f611a2" ], "roles": [ "EMAIL" ] }, { "username": "Demo0", "datasets": [ "3a96d9f8-...-11bba1f611a2" ], "roles": [ "CompanyCode" ] }, { "username": "Demo", "datasets": [ "3a96d9f8-...-11bba1f611a2" ], "roles": [ "SystemCode" ] }, { "username": "Demo", "datasets": [ "3a96d9f8-...-11bba1f611a2" ], "roles": [ "DomainCode" ] }, { "username": "CustomSQLRoleA", "datasets": [ "3a96d9f8-...-11bba1f611a2" ], "roles": [ "DatabaseRole" ] } ] }

response:

Status: BadRequest (400) Response: { "error": { "code": "InvalidRequest", "message": "Multiple identities provided for dataset 3a96d9f8-...-11bba1f611a2" } }

This error message somewhat makes sense to us. But we have no idea how to approach it differently.

Our approach matches with what is suggested on https://docs.microsoft.com/en-us/power-bi/developer/embedded/embedded-row-level-security#applying-user-and-role-to-an-embed-token or https://docs.microsoft.com/en-us/power-bi/developer/embedded/cloud-rls#dynamic-security or https://radacad.com/dynamic-row-level-security-with-power-bi-made-simple

The limitations also don't mention that what we are trying wouldn't be supported.

Re: Embed BI, App owns data, dynamic RLS multiple filters

Anonymous — Fri, 22 Jul 2022 03:32:15 GMT

HI @MaartenInc,

In fact, the role part allows you to use a list of values, you do not need to duplicate defined different roles with the same usernames. I think the error should be related to these duplicate usernames.

{ "datasets": [{ "id": "3a96d9f8-...-11bba1f611a2" }], "reports": [{ "id": "d5c6f76b-...-8672d80a93ba" }], "targetWorkspaces": [{ "id": "beca21a6-...-17f65322eb31" }], "identities": [{ "username": "m.demo@demo.be", "datasets": [ "3a96d9f8-...-11bba1f611a2" ], "roles": [ "EMAIL" ] }, { "username": "Demo", "roles": ["SystemCode", "DomainCode", "CompanyCode"], "datasets": [ "3a96d9f8-...-11bba1f611a2" ] }, { "username": "CustomSQLRoleA", "datasets": [ "3a96d9f8-...-11bba1f611a2" ], "roles": [ "DatabaseRole" ] } ] }

Using row-level security with embedded content in Power BI embedded analytics - Power BI | Microsoft Docs

Regards,

Xiaoxin Sheng

topic Re: Embed BI, App owns data, dynamic RLS multiple filters in Developer

Embed BI, App owns data, dynamic RLS multiple filters

Re: Embed BI, App owns data, dynamic RLS multiple filters