Security roles not working properly after Fall 2021 update

[SamKrygsheld]

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.

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.

Edit: Here is a link to an example pbix. Otherwise, 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.

[belvoir99]

@v-chuncz-msft - I can raise this as a separate Issue if you would like but I suspect they might be linked. Let me know if you do.

We have a long-running 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 can see from above 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. My colleague Tom is the best person to get in touch with via my email angus at oxfordpowerbi.co.uk. I can provide a mobile number for him. We are also raising an Azure support ticket this morning.

We have had to take down the service last night (about 10 hours ago). We will have to tell our users at 8am GMT (in 2 hours' time) 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.

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

[belvoir99]

@v-chuncz-msft 

see also this link https://community.powerbi.com/t5/Issues/Rls-not-working-on-embedded/idi-p/2147696

They think it might be the RLS code. Mine has the same structure. Can you pass that onto the person or team investigating?

If it is an update that caused the break, then after it is fixed (!), can MS review their procedures around updating code that will impact upon RLS? It's so much more important than any other area due to the risk of RLS failing and exposing data to other parties. The consequences could be quite severe. We were lucky to have picked it up before any of our customers otherwise we'd have to consider reporting a GDPR breach to the UK government. As it is, our commercial service is down at the moment, so I'm hoping this is a Priority 1 issue.

Some comms from you would be helpful, please!

[v-chuncz-msft]

@SamKrygsheld 

Relevant issues about RLS have been reported internally, so stay tuned.

[SamKrygsheld]

@v-chuncz-msft 

Any news on this?

[belvoir99]

@SamKrygsheld we have passed the issue to Microsoft and a team are now actively looking at it. That's all the news we have at the moment! 

[Sha_Kri]

For us it also seems that underlying RLS logic has changed in PowerBI recently so our RLS suddenly stopped working without any changes on our side. 

We had 2 tables X and Y with relationship many:many (NO option 'Apply security filter in both directions' checked). Both tables had the same rule set up in Roles. On 12/1/2021 users lost access to data and got the message:

'The user belongs to multiple roles that have security filters, which isn't supported when one of the roles has filters affecting table X with SecurityFilteringBehavior=Both relationships.'

Note that we DID NOT have option 'Apply security filter in both directions' checked.

Having a user present in several roles wasn't a problem before. 

So, we fixed the problem by removing RLS rules on table Y and left them on table X. Again, no option for 'apply security filter in both directions'.

So, the default behavior seems to be that if there is a many:many relationship for 2 tables, the security filter is applied implicitly.

[belvoir99]

@Sha_Kri I'm glad that you have found similar! Was the error message due to the latest Desktop update perhaps? Only the team that built this can really tell us what's happening. They have clearly changed/improved/broken something but have not documented it for the end users.

We changed our more complex DAX RLS query to something more simple but using a bi-directional relationship. Less efficient I suspect but works fine.

The MS team we have engaged with on this issue are struggling to see the problem. They are still asking for more information but haven't identified specifically the root cause or nature of the problem. Would you be able to engage with them also? It would add some weight to the issue. I can share their contact details if you message me directly.

[attel]

@Sha_Kri same here.

We set A, B, C, D 4 groups in one report. The report works perfectly for each group, however when a user have roles in both A and B, it does not work. The error message is: 

'The user belongs to multiple roles that have security filters, which isn't supported when one of the roles has filters affecting table X with SecurityFilteringBehavior=Both relationships.'

The error never happened before.