Embed BI, App owns data, dynamic RLS multiple filters

[MaartenInc]

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-us... 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.

[Anonymous · Accepted solution]

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...

Regards,

Xiaoxin Sheng