RLS logic help!

[k3rz0rg]

Hi all,

I don't do RLS much so I need some help regarding the RLS logic.

I have an extracted table from usage metrics which has user principal names or emails they used to login in a column and I want from those emails, two people will be able to see all and rest of them will not.

I created a custom table with only those email addresses and if value from the userprincipal name matches with the login email (username()) then the report will show everything else nothing (or just the logged in persons info only).

The red marked columns are custom columns. Does anyone know how can I set the logic for the roles?

I added those role and role flag column to try if the logged in person is authorized (following corresponding user principal name) or role flag is 1 then the user will see all otherwise nothing.

I used role flag for this following a youtube video but didn't work and the column returns multiple 1s so maxx proved not to be a good idea.

//if the username() is 1 then the person is authorized to see all data else the person will only see his/her data in this case.

If(
   MAXX(
              Filter('Contacts_PermSource','Contacts_PermSource'[User Principal Name] = Username()),'Contacts_PermSource'[Role Flag]) = 0,
'Contacts_PermSource'[User Principal Name] = Username(),
1=1)

So I dropped this idea.

And also since that workspace has 30+ users, how can skip going through the trouble of adding the unauthorized users one by one?

Any help would be much appreciated.

Thanks,

k3.

[GilbertQ · Accepted solution]

Hi there

It sounds like you need Dynamic RLS, which makes it easier to manage

I suggest you read and try out this blog post from Kasper. There is a good working example which you can try and once you get that working, you can then apply it to your data.

https://www.kasperonbi.com/power-bi-desktop-dynamic-security-cheat-sheet/

[k3rz0rg]

Hi @GilbertQ ,

thanks for your response and tutorial and pardon my delayed response, i was out for 4th july vacation. I followed the similar procedure as the link suggested but when I test that rule with username offline (on desktop version while working on it), it works fine, once I publish it to the workspace and test it from there ( dataset > security > view/test as > username/email ) it does not filter and show all the data except one or two email addresses. I tried both username() and userprincipalname() and same outcome everytime. 

Initially I thought maybe because some of them workspace admins so I changed their role from admin to just member as well but no luck. I even tried making one of those email id holders login from his computer and he was able to see all the data where he was not supposed to.

Do you have any idea why is this happening? Is there any PBI server glitch or something?

[GilbertQ]

Hi there

If the users are members of the App Workspace V1, they are treated as Admins and will always see all the data.

You will need to remove the users from the App Workspace and share it via an App.

Or you can use the App Workspace V2 and assign the users a Viewer role.

[k3rz0rg]

Hi @GilbertQ ,

pardon my delayed response and yes I noticed that admin/member role issue with the older version of workspace  too and I believe, MS still need to work on the v2 as well. To avoid complication for the time being with such small report I created, I am just following the link you provided.

While I was working with dynamic RLS, I found the functions username() and userprincipalname() are the same (or at least giving me the same output in this case), do you know any differences between these two functions?

Thanks, 

K3.

[GilbertQ]

Hi there

Yes I do know the difference.

If you use USERNAME() and you are logged into a Domain controlled PC it will return DomainName\UserName

If you use USERPRINCIPALNAME() and you are logged into a Domain controlled PC it will return user@domain.com

When you are on the Power BI Service, it uses the USERPRINCIPALNAME() I would suggest using the USERPRINCIPALNAME()