RLS Many users linked to many companies

[mlanda]

I need to be able to apply row level security and I have the following structure: 

With the following permissions:

And this works to filter the reports by user,  if userX belongs to companyY, but we need this to work with user X being able to see companyY and companyZ, how do you do this?

Its also worth mentioning that I got this working by following this video:
https://www.youtube.com/watch?v=ywNx7Cn1aZU

Which comes from microsoft but it says its now outdated, so I assume that there is a better way, can someone point me to the right resource? Thank you!

[Greg_Deckler · Accepted solution]

Looks like you need to make your User to UserTenant relationship bi-directional or at least have the User table filter the UserTenant table. Then if a user is in more than one tenant it should work. Alternatively, have your RLS rule based upon UserTenant instead of User.

[mlanda]

Doing both changes worked, thank you!