Forum Discussion
Row Level Security using User Principal Name (UPN)
Hi,
I am trying to set up Row Level Security to restrict the data a user can view based on their e-mail address but I am having an issue.
Context:
Table A is what users have access to. This table contains data with account codes.
Table B contains accounts codes together with the user name and e-mail of the users assigned to that account code.
Because a user can be linked to multiple account codes, I have created a simple bridge table which contains unique account codes to create a relationship between Table A and Table B.
As a check to ensure Table A is correctly filtering account codes based on a users’ e-mail address. I added Table A and Table B to a test page, and then selected an e-mail address from Table B to see if the Table A correctly displayed only account codes linked to the selected e-mail address. This worked as expected.
I would now like to replicate this functionality into a role using row level security. I have tried to do this using User Principal Name (UPN) by creating a role with the following expression:
[E-mail Address] = userprincipalname()
This does not work after testing. I even added even USERPRINCIPALNAME() to a card to check if the e-mail address displayed correctly and it did.
I suspect I am missing something obvious and I would appreciate it if anyone could take a look.
Best regards,
9 Replies
Hi
Can you elaborate what you mean by row level security is not working. How are you testing this? You would create the measure under Modelling> Manage Roles. Be sure to pick the correct table this measure should filter against
You may find this useful
https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies
After publishing the report with the role Test with the filter [E-mail Address] = userprincipalname() did you add the users to the Test role in the Power BI service?
https://docs.microsoft.com/en-us/power-bi/service-admin-rls
One way that makes it easier is to add an active directory security group to the role. You can also share the report with the security group. That way, when a new employee comes on, if they get added to that security group, the report is already shared with them and RLS is applied.
One other note, for the long list of filters, instead of
[Group Company Code] = "DE14" || [Group Company Code] = "DE12" || [Group Company Code] = "DE11" || [Group Company Code] = "DE10" || [Group Company Code] = "DE09" || [Group Company Code] = "DE08" || [Group Company Code] = "DE07" || [Group Company Code] = "DE06" || [Group Company Code] = "DE05" || [Group Company Code] = "DE04" || [Group Company Code] = "DE03" || [Group Company Code] = "CH05" || [Group Company Code] = "CH03" || [Group Company Code] = "CH02" || [Group Company Code] = "AT03" || [Group Company Code] = "AT0220" || [Group Company Code] = "AT0210" || [Group Company Code] = "AT0200"
you can use the new IN function
[Group Company Code] IN {"AT0200", "AT0210", "AT0220", "AT03", "CH02", "CH03", "CH05", "DE03", "DE04", "DE05", "DE06", "DE07", "DE08", "DE09", "DE10", "DE11", "DE12", "DE14"}Hi both,
Apologies for the delayed response. Thanks for the tip re the IN function.
Because Table A and Table B have a relationship, what I mean by row level security not working is that when I publish the report to the Power Bi Service, or select View as Roles in Desktop mode, I expect Table A to display only data relevant to the e-mail address of the person who opened the report (via the userprincipalname expression). As noted in more detail in my first post, if I select an e-mail address from Table B in Desktop, Table A correctly displays data relevant to the selected e-mail address in Table B (because of the relationship between the tables).
One possible clue is that I have created this role against Table B (Security Mapping) rather than Table A (FPI & Accounts). I have done this because it is that table which has the e-mail address.
I hope this is clearer.
Best regards,
Did you ever this this resolved? I am wondering if RLS/View As Roles is buggy in the latest PBI Desktop release? I have a lot of experience with RLS and using UserPrincipalName() in roles. However, now, when I use my own user, the security role works fine. But as soon as I use View As Role with any value, it just returns all the data. This is obvious not how it is supposed to work.
Is anyone else have issues like this with the most recent release?
I ran into odd behavior if the person I view as was a member of the workspace. I think it's because the user can get to the dataset that RLS doesn't filter it down. Perhaps try it as a user you have just shared the report with?
