Forum Discussion
Helper IDynamic RLS with multiple tables
Actually, I had some time now. Here is original post, tweaked for User : UserAllowedCompany to have M:M relationship.
The filter from User will propogate as long as it travels through a single direction filter. So, either one --> many relationships from the one side OR single filter direction many --> many relationship from the left (A) side of A filters B.
As you've noted, it doesn't work nicely when traveling from the other direction, many --> one, like UserAllowedCompany (many) --> (one) Company.
To get this working without relying on bidirection filtering, you'll need to define an RLS filter rule on Company. Note that once that is working, it looks like the RLS filter will propogate throughout the rest of your model by leveraging the filter directions on (the rest showing are one --> many relationships).
Something like the following should work as the filter on Company:
CALCULATE( NOT ISEMPTY( UserAllowedCompany ), User[Id] = USERPRINCIPALNAME() )
Note that, even though you should also have the User[Id] = USERPRINCIPALNAME() filter on User, the RLS filters aren't evaluated in order, so you need to re-incorporate the underlying USERPRINCIPALNAME() filter any time you are defining a new RLS filter.
Here are some screenshots with a dummy model similar to yours in case helpful.
Super UserThe filter from User will propogate as long as it travels through one --> many relationships from the one side.
As you've noted, it doesn't work nicely when traveling from the other direction, many --> one, like UserAllowedCompany (many) --> (one) Company.
To get this working without relying on bidirection filtering, you'll need to define an RLS filter rule on Company. Note that once that is working, it looks like the RLS filter will propogate throughout the rest of your model through one --> many relationships.
Something like the following should work as the filter on Company:
CALCULATE(
NOT ISEMPTY( UserAllowedCompany ),
User[Id] = USERPRINCIPALNAME()
)Note that, even though you should also have the User[Id] = USERPRINCIPALNAME() filter on User, the RLS filters aren't evaluated in order, so you need to re-incorporate the underlying USERPRINCIPALNAME() filter any time you are defining a new RLS filter.
Here are some screenshots with a dummy model similar to yours in case helpful.
Helper IMarkLaf thank you so much!! My model has many to many from user to UserAlkowedCompany because the id between them is not the user Id but the Licensee-UserRoleId which can be assigned to many users. Does this make a difference as this is not working in my model. Thanks
- MarkLaf
It should still work similar to my solution. Just ensure that the M:M has filtering direction set where User filters UserAllowedCompany.
That said, I would recommend changing query to join the User Id onto UserAllowedCompany and then setting up a 1:M relationship between User and UserAllowedCompany as set up in the dummy model I showed.
If updating the query isn't possible or desireable, I can update my original post to include a M:M relationship between User and UserAllowedCompany. Just let me know
- MarkLaf
Actually, I had some time now. Here is original post, tweaked for User : UserAllowedCompany to have M:M relationship.
The filter from User will propogate as long as it travels through a single direction filter. So, either one --> many relationships from the one side OR single filter direction many --> many relationship from the left (A) side of A filters B.
As you've noted, it doesn't work nicely when traveling from the other direction, many --> one, like UserAllowedCompany (many) --> (one) Company.
To get this working without relying on bidirection filtering, you'll need to define an RLS filter rule on Company. Note that once that is working, it looks like the RLS filter will propogate throughout the rest of your model by leveraging the filter directions on (the rest showing are one --> many relationships).
Something like the following should work as the filter on Company:
CALCULATE( NOT ISEMPTY( UserAllowedCompany ), User[Id] = USERPRINCIPALNAME() )
Note that, even though you should also have the User[Id] = USERPRINCIPALNAME() filter on User, the RLS filters aren't evaluated in order, so you need to re-incorporate the underlying USERPRINCIPALNAME() filter any time you are defining a new RLS filter.
Here are some screenshots with a dummy model similar to yours in case helpful.