Issue in Row Level Security in the Browser

[swapnilsd08]

I have assigned RLS for a column in the Power BI Desktop, and when I do "View As Role" and select that newly created Role then the data gets filtered and is displayed correctly.

But the same change is not being reflected when I open the dashboard in the browser through app.powerbi.com. I have also added Users and Groups in the "Security" part of Dataset and clicked "Test as role" on the Role that I created in Power BI Desktop.

What could be the issue? What should have been done?

Please help!

Thanks,

Swapnil

[swapnilsd08]

Hey Mike,

This works, Thanks!

I have one more question regarding the same. How do I implement RLS to multiple columns in a same table? i.e. currently it is for Regional_Head, I also want it to be for Directors and Managers in a way that if a manger logs in, he/she is able to see data accordingly. And how to grant an Admin access to a user whose name is not there in the table?

Do I have to create separate Roles for me to check if a username is in any of those 3 columns? Or is there a way to check if Username in Column1 or Username in Column2 or Username in Column3 or Username in{hard-coded name}?

Thanks,
Swapnil

[GilbertQ]

hi @swapnilsd08

Below is my blog post which goes into a few details when setting up RLS to be aware of.

I think the issue you are currently having is that because you created and published the Power BI File, you are effectively an Admin, so not matter what you do you will always see everything.

Try with another user who has got the RLS applied and confirm that they can see only their data?

I have done this in the past and it took me a while to figure it out.

Power BI – Dynamic Row Level Security – Things you need to know to get it working!

[swapnilsd08]

Hi @guavaq,

This is a great blog post. Thanks, it definitely is a missing link that I was looking for. 

I have one more question. If I have created 4 different pages in a dashboard and have created "Roles" pertaining to each page, i.e. Admins for data/visuals on Page 1 might be different from the same on Page 2.  Also, each page points to a different table.

My Manage Roles looks something like this: 5 different roles, out of which "Admin" role has different set of users for each Page, i.e. each Table here would have a different set of users for "Admin".

So, how does it work in the browser while we have to add "Members" in "Security" on right-clicking the dataset? Because there I find only 5 Roles that I created in the Model in Power BI Desktop. So, do I have to collectively add all the "Admin" users for all the pages under this one "Admin" bucket?

Is RLS dashboard specific and not Page specific?

Thanks,

Swanill

[GilbertQ]

Hi @swapnilsd08

Glad the blog post helped.

To answer your question, the RLS applies across the datasets which then means it also applies to the reports and dashboards.

If I understand you correctly then if your users only have access to a particular table, which is also in a particular sheet, then they should see no data on the other sheets.

Ideally with RLS you want to have one dataset, and then the users only see their view of the world. But it can be used in many other ways also.

[mtaubman]

Swapnil,

Try using this formula:

LEFT(USERPRINCIPALNAME(), FIND("@", USERPRINCIPALNAME())-1)

You can create a measure by right-clicking on a dataset and selecting "Create measure...".  In the formula bar, give the measure a name and type the above expression, like:

UsernameOnly = LEFT(USERPRINCIPALNAME(), FIND("@", USERPRINCIPALNAME())-1)

Then display it somewhere on the report.  The reason to do this is so you can see what the expression evaluates to, and if it matches what you think is in your [Region_Head] column.  This will help you debug problems with your RLS.  When you are satisfied it works, you can remove it.

Good luck!

-Mike

[swapnilsd08]

Hi Mike,

You are correct, the format on both the places are different. Also, I have abc\swapnil.desai as Windows name and so I intend to remove the first 4 characters in order for it to match with the name in Region_Head column (which only contains username 'swapnil.desai')

How do I create a measure so that I can display the result of your DAX formula in your RLS expression? Also, why is it necessary?

It is difficult for me to get hold of a strainght forward way to implement RLS based on Windows Authentication with SQL Server in Power BI. Can you help me with an example or template if you have on this?

Thanks,
Swapnil

[mtaubman]

That's odd, Swapnil.  When I display USERNAME on the desktop, I get the domain\usermane format, but when I display is in the service I get the username@domain format.

Are you seeing the same format in both places?

You shouls also create a measure so you can display the result of your DAX formula in your RLS expression.  As written above, it looks like it would just return the USERNAME with the first 4 characters missing.  I don't think that's what you intend.

Regards,

Mike

[swapnilsd08]

Mike,

I checked by creating a measure User=Username() and it returned domain\username. 

For eg, my windows login in domain\swapnil.desai and after creating that Region_Head role (as per the above DAX), I was able to filter out the data only for me when I tested via "View role as". But when I tried it on app.powerbi.com, it gave me blank data.

Also, there is a column called Region_Head in the table that has "usernames" of all the users excluding the domain names. So, which formula do you suggest to use for RLS pertaining to Windows login for domain\username?

Thanks,

Swapnil

[mtaubman]

Swapnil,

I believe USERNAME() returns in username@domain format in the service. Do you display the USERNAME() and the results of your RLS function anywhere on the report?  You might want to do that to confirm what values you are really getting. 

Also, are you sure about that Region_Head formula?  It looks like it might not always return what you expect.  You could also use the FIND function to locate the separator within the username.

Regards,

Mike

[swapnilsd08]

Hi Herbert,

I am using SQL Server 2016 as my datasource. I have used "Import" as "Data Connectivity Mode" and wrote a SQL statement to get the data.

Then, for RLS I went to "Modeling -> Manage Roles" and created a role and used the below DAX expression as I want to apply RLS to all the Regional Heads in order for them to see only their data (rows in the table):

[Region_Head] = RIGHT(USERNAME(),LEN(USERNAME())-LEN(LEFT(USERNAME(),4)))

Names are in format domain\username and therefore I have used this DAX expression to fetch just the username.

Now, when I click on "View As Roles" and select the above created role, I can see the report being filtered by it and displays data as per RLS. 

Moreover, I have also added these members in the security tab of the dataset in app.powerbi.com and the moment I click on "Test as role", the data shows nothings but "Blank".

Let me know what needs to be done.

Thanks,

Swapnil 

[v-haibl-msft]

@swapnilsd08

What is your data source and how do you configure RLS for your column? Could you please provide some details about it?

I just tested with RLS, it worked properly when the user open the dashboard in the browser through app.powerbi.com.

Best Regards,

Herbert