Row level security with multiple roles

[PallaviKGVG]

Hi,

I have a report built and pushed on to power bi service. When i am embeding the report using Azure AD, i want to get a report filtered with the clientid and locationid which can be dynamic value.

Currently, i am getting the report which filtered with client id where i am using row level security role as 'Dispensary' and filter as clientid=USERNAME() on mange roles. Since, where i use to generate token on webapplication by passing username as client id and roles as ["Dispensary"] but , now i have another condition comes in where i have to get a report based on codition that is locationid and clientid . 

 How can i achieve this?

Thanks in advance.

Pallavi K

[v-jiascu-msft]

Hi Pallavi,

It seems your embedded mode is "App Owns Data". Please refer to developer/embedded-row-level-security.

1. I would suggest you create two roles based on locationid and clientid. One role is also good if the two fields can be combined.

{
    "accessLevel": "View",
    "identities": [
        {
            "username": "EffectiveIdentity",
            "roles": [ "Role1", "Role2" ],
            "datasets": [ "fe0a1aeb-f6a4-4b27-a2d3-b5df3bb28bdc" ]
        }
    ]
}

2. If not for the sake of security, I would suggest you use slicers to make the two fields dynamic. 

Best Regards,

Dale

[PallaviKGVG]

Thank you v-jiascu-msft.

Currently, for generatingtoken with single role i am passing the post request body as 

$clientid = "2";

{
"accessLevel": "View",
"identities": [
{
"username": $clientid ,
"roles": [ "Client" ],
"datasets": [ "9b61d620-e6ac-41fc-b2c9-3f1d89241a03" ]
}
]
}

Another field that comes in now is 

$location = "3";//Can be multiple location under client

Note: I may have multiple location under single client [under client 2 i may have location id 2,3,4 etc] and i have 2 different table called Clients and Locations. 

In Bi desktop i have Client role created as client_id = USERNAME(); (I have duplicated client id column as string)

How i can pass the location for username property of identities, where it takes single value?

What can be done at the power bi desktop to create the roles for both clients and locations ?

Thanks and regards

Pallavi

[v-jiascu-msft]

Hi @PallaviKGVG,

Could you please mark the proper answer as a solution?

Best Regards,
Dale

[PallaviKGVG]

Hi @v-jiascu-msft,

The issue is no yet solved.

I need to get a report as such where client_id = 2 and location_id =1  (Single client may have different locations). When i want to render a report , where the location_id is only 1. 

reffer image for the relationship.

So, How i can achive that?

The client and locationid can be dynamic. As 'username' in identities takes only single value where can i pass location_id like just 1?.

How my role level security created at the desktop?

In the above solution, when i create the role as such, i wil get a report for that particular client and all locations under that client. But, in this case i need a single location that is under client, i mean to say that location id and client id is send to the api at the time of embedding in the webapplication.

something like this ?

$clientid = "2";

{
"accessLevel": "View",
"identities": [
{
"username": $clientid ,$location_id
"roles": [ "Client","Location" ],
"datasets": [ "9b61d620-e6ac-41fc-b2c9-3f1d89241a03" ]
}
]
}

If their is something to solve this issue is very helpfull.

Thank you

Pallavi

[PallaviKGVG]

Hi, any updates on the above issue

Thank you

Pallavi

[v-jiascu-msft]

Hi Pallavi,

Do you do this for security? The RLS roles in this scenario are PRE-defined. We just bind them with users. So there isn't something like "client_id = 2 and location_id =1" when we apply RLS. There is only Role1 or Role2. The rules of Role1 is client_id = 2 and location_id =1". If you want some data of where the location_id is only 1, there will be a role, let's call it Role 3, with rules "location_id = 1". Please give it a try.

Best Regards,

Dale

[PallaviKGVG]

Thank you Dale,

I understand the solution(The rules of Role1 is client_id = 2 and location_id =1". If you want some data of where the location_id is only 1, there will be a role, let's call it Role 3, with rules "location_id = 1"). 

But, i cannot hardcode the location id and client id in role(like "location_id = 1" and client_id = 2), where i am embeding the report into web application, i won't be knowing which client and location user logged in to application(Location and client id is dynamic always).

When i logged in to web application and when the reports rendered through power bi rest api,  i should be seeing only respective client and respective location report data.

I have rised this question with respect to report embed on webapplication.

Thank you

Pallavi

[Anonymous]

Was there any solution to this. I am having an extremely similar issue in my company right now.

[v-jiascu-msft]

Hi Pallavi,

Let's make the scenario clearer first. 

Is it the "App Owns Data"? If so, there is only one PBI account. Let's assume it as "Admin". 

So PBI will always get requests from "Admin". How can it know which client_id it is? That's why the roles are here. PBI Service doesn't need to know who logged in. If the application request contents of "role1", the PBI Service will return the contents of "role1". 

How could it be that "Location and client id is dynamic always"? Client 1 has location 1 this time while client 1 will have location 2 next time?

Best Regards,

Dale

[v-jiascu-msft]

Hi Pallavi,

We can set many rules for one role at the same time. So we can only create one role for your scenario. Please refer to the snapshot below.

Best Regards,

Dale