Row Level Security - USERPRINCIPALNAME() Not Working?

[rodneyc8063]

Im exploring Power BI at the moment, and am a bit perplexed about dynamic row level security.

I found a blog (https://radacad.com/dynamic-row-level-security-with-power-bi-made-simple) with a very simple example.

I pretty much did everything exactly the same.

Created two tables, one for the user security, second for "sales". Created my roles, except instead of using USERNAME(), I just use the USERPRINCIPALNAME() function.

I test my roles in my desktop and I am seeing the expected results, my row gets filtered. So I then upload to the powerbi.com.

I right click on my uploaded dataset, and add a user to my security group.

I then create an "Embed Code" - With a web link which I send to my colleague. When I have them try and log in, for some reason its still showing all the data as if the row level security isnt even there?

I am not sure what I am missing, as I am assuming that everything should work as expected. I tested my roles in the desktop with "View as Role" and everything works just fine.

Its just when I get to the service, it doesnt seem to be working as expected?

Is there any other configuration I need to do within the service outside of adding someone to a security role?

Sorry if this is a bit vague, Im just a bit peeved that something seems so simple but I am some how still missing something

[GilbertQ]

Hi there

When you say you create an Embed Code, is this Embed code from creating an App or Publish to Web?

Also is this user a member of the App Workspace?
If the user is a member of the App workspace they will have rights to see all the data.

[rodneyc8063]

Hi @GilbertQ 

The embed code I am trying to use is the [Embed - Securely embed this report]

I shared that link with a colleague for testing the RLS

Funny thing, I believe I actually came across your blog here: https://www.fourmoo.com/2016/10/03/power-bi-dynamic-row-level-security-things-you-need-to-know-to-ge... while researching my issue 🙂

Also, just clarifying when you ask if the user is a member of the workspace do you mean the following?

I believe by default I did add him as a member in my workspace, but I later tried to change him to a VIEWER - But he was still able to see all the data.

As I mentioned, as I was doing more research I stumbled across your blog as well as this article: https://docs.microsoft.com/en-us/power-bi/service-admin-rls

Both of which mentions that I need to use this setting in the relationships tab

I am assuming that maybe my model is not working due to this, but when I tried to enable this option I hit the following error

[Table 'USER_SECURITY' is configured for row level security, introducing constraints on how security filters are specified. The setting for security filter behavior on relationship ' ' cannot be both]

To try and add a bit more context this is my model

This is the relationship

This is my data

I was hoping to start with as basic of an example of RLS as possible, hence I created this model based on the initial blog I posted.

I tried to also upload my PBIX file to here: https://www.dropbox.com/s/8xnqx8a3aq3qd1a/Row%20Level%20Security%20Test%20External.pbix?dl=0

Hopefully this helps my question to be a bit more clear?

Any insight and help is much appreciated

[GilbertQ]

Hi there

If you had to look at the RLS pattern the issue is that you need to have it set up slightly differently.

I can confirm that yes the Viewer Role in an App Workspace will confirm that it will keep the RLS.

If you can test and read this blog post from Kasper where you can play with the model and get it working, which will give you a great understanding of how it all works: https://www.kasperonbi.com/power-bi-desktop-dynamic-security-cheat-sheet/

[rodneyc8063]

Hi @GilbertQ 

Thanks for that post

I actually started my own sample, pretty much one to one and am starting to slowly understand a bit more about row level security.

Some interesting points I noticed, is that while testing row level security in the service, it appears that as the owner/creator of the dataset regardless if I set myself up in a role, I am still able to see all the data.

Another interseting point, I noticed is that with the sample from kaspersky, it looks like there is a "bridge" table, lack of a better word that helps to unify the users and the profiles together. Then I am able to use both directions, and apply row level security check box.

Im still a bit gray on how relationships work in Power BI, in terms of the direction (single or both direction) but I think Im getting a bit of a better handle on things now, thanks to your post

[GilbertQ]

Hi there

No worries, that is a great starting point and trying to learn it with your own data.

You are correct that if you are the person who uploads the PBIX file you are the Admin and can see all the data.

With regards to the cross-filter direction set to both, this allows for the security to flow between the tables, which then means that it will then correctly apply the security.