Security roles not functioning the same after Fall 2021 update

[SamKrygsheld]

I also posted this as an Idea in Issues, but I'm also posting here to see if I can attach an example in the comments. Here's the original message. (Edit: It does not seem possible to attach a dashboard to a post or comment. Has this always been the case?)

I have been using row-level security to pass a user's role to the dashboard, allowing me to restrict visibility of elements in different ways throughout the dashboard based on the user's role. Since the Fall 2021 update, PowerBI seems to ignore the row-level security of a table in certain situations, specifically within calculated columns.

If it's possible to attach a dashboard, I can upload an example, but here's how to duplicate it:

1. Import or create a table that you want to filter. This can be anything. I'll refer to this table as FilterTable.
2. Create a UserRoles table using Enter Data. Name the column "Role" and enter "3 - Admin", "2 - Manager", "1 - User".
3. Create Row-Level Security roles, hard-coding each to its corresponding line in UserRoles (ie. for Admin role, set the DAX expression to [Role = "3 - Admin"].
4. Create a measure: [_UserRole] = MAX(UserRoles[Role])
5. Create a calculated column in FilterTable: [Viewable] = IF([_UserRole] = "3 - Admin", 1, 0)
6. Create a Card visual displaying [_UserRole]. It will currently display "3 - Admin".
7. Create a Table with data from FilterTable, and include both [Viewable] and [_UserRole]. These will display "1" and "3 - Admin", respectively.

After all that is set up, go to "Modeling" -> "View As" and set your role to User. You will see that [_UserRoles] will correctly change to "1 - User", but the [Viewable] calculated column will incorrectly stay mapped to 1 for each row in FilterTable. I was previously using this logic to filter the table to [Viewable] = 1, but now all the rows are showing up to everyone. My understanding is that this is because the [Viewable] calculated column is now ignoring the Row-Level Security on the UserRoles table.

Edit: After further testing, I am now sure Security Roles are not working properly. Putting measures in the table directly will show the correct value (in compliance with RLS), but using them inside a calculated columns allows you to see data you shouldn't. This could allow users to view data they should not be allowed to see.

[SamKrygsheld]

I have been using row-level security to pass a user's role to the dashboard, allowing me to restrict visibility of elements in different ways throughout the dashboard based on the user's role. Since the Fall 2021 update, PowerBI seems to ignore the row-level security of a table in certain situations, specifically within calculated columns.

If it's possible to attach a dashboard, I can upload an example, but here's how to duplicate it:

1. Import or create a table that you want to filter. This can be anything. I'll refer to this table as FilterTable.
2. Create a UserRoles table using Enter Data. Name the column "Role" and enter "3 - Admin", "2 - Manager", "1 - User".
3. Create Row-Level Security roles, hard-coding each to its corresponding line in UserRoles (ie. for Admin role, set the DAX expression to [Role = "3 - Admin"].
4. Create a measure: [_UserRole] = MAX(UserRoles[Role])
5. Create a calculated column in FilterTable: [Viewable] = IF([_UserRole] = "3 - Admin", 1, 0)
6. Create a Card visual displaying [_UserRole]. It will currently display "3 - Admin".
7. Create a Table with data from FilterTable, and include both [Viewable] and [_UserRole]. These will display "1" and "3 - Admin", respectively.

After all that is set up, go to "Modeling" -> "View As" and set your role to User. You will see that [_UserRoles] will correctly change to "1 - User", but the [Viewable] calculated column will incorrectly stay mapped to 1 for each row in FilterTable. I was previously using this logic to filter the table to [Viewable] = 1, but now all the rows are showing up to everyone. My understanding is that this is because the [Viewable] calculated column is now ignoring the Row-Level Security on the UserRoles table.

Edit: After further testing, I am now sure Security Roles are not working properly. Putting measures in the table directly will show the correct value (in compliance with RLS), but using them inside a calculated columns allows you to see data you shouldn't. This could allow users to view data they should not be allowed to see.

[Anonymous]

@SamKrygsheld 

You can save it in an online doc and share the link, make sure everyone with the link can access. Thanks.

Paul

[SamKrygsheld]

Alright, here's a link: https://1drv.ms/u/s!AgTnvCMB7gUGg85-nj4Yeb1ZYDQIBw?e=9XrAyM

[Anonymous]

@SamKrygsheld 

I tried but not able to create a proper model with provided steps, it is appreciated if you could just create a short sample pbix without sensitive information.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[SamKrygsheld]

I have a pbix ready. Do you have a secure file upload or another way I can get it to you? I don't see an option to attach it to these posts.

[belvoir99]

@SamKrygsheld 

We have an active Power BI Embedded service with hundreds of users. This afternoon a user complained that they could see other users' data - that's never happened before. The PBIX file has not been updated, and an inspection of the website code has not revealed any changes (and no changes have been made today).

I have reinstalled a version of the PBIX from July and the problem persists, so I'm fairly certain it's not the recent update of the Desktop software. Evidence is starting to point to MS code running Embedded in the Service.

I saw on one of your posts that Microsoft are investigating. They are welcome to get in touch with me (if they read this), although to be honest I don't think we can tell them much as we haven't changed anything today.

We have had to take down the service this evening. We will have to tell our users at 8am GMT tomorrow that there is no service. This would be the first time since we began 3+ years ago. I am hoping that Microsoft are urgently looking into this.

Please let me know if you find out anything else, thanks! And thanks for posting.

BTW, i don't use calculated columns in the RLS model part of the PBIX.