Forum Discussion
RLS using an if(switch)-statement and an or-statement
Hi,
I am trying to configure Row-Level Security for my model but it does not work the way I want it to.
My main table is the "Quotes" table with a lot of specific details about agreements for shipments on a certain connection. Two of those fields are "CountryFrom" and "CountryTo" which are the start and ending country of a quote. Next to this table I have a RLS table, including the email adresses of users and corresponding countries that the users are allowed to see. Note: users that are allowed to see all countries are NOT in this table, but they do need to be able to see the shipments of all countries.
The idea is that users can see all quotes where the starting OR ending country is within their set of allowed countries.
I tried the following:
- I duplicated the RLS table into "RLS UserCountryFrom" and "RLS UserCountryTo", which I connected to the fields "CountryFrom" and "CountryTo" of my Quote table (many to many)
- I built a Row-Level Security role with a filter on Quote with this code:
1 SWITCH(
2 CALCULATE( COUNTROWS('RLS UserCountryTo'),
3 FILTER('RLS UserCountryTo','RLS UserCountryTo'[Email]=USERPRINCIPALNAME())),
4 BLANK(), [CountryFrom] IN DISTINCT('fact Quote'[CountryFrom])
5 ,[CountryFrom] IN SELECTCOLUMNS(
6 FILTER('RLS UserCountryFrom','RLS UserCountryFrom'[Email] = USERPRINCIPALNAME())
7 ,"Country Code",'RLS UserCountryFrom'[Country Code])
8 ||
9 [CountryTo] IN SELECTCOLUMNS(
10 FILTER('RLS UserCountryTo','RLS UserCountryTo'[Email] = USERPRINCIPALNAME())
11 ,"Country Code",'RLS UserCountryTo'[Country Code]))
So if the email adress of a user does not correspond with a record in the RLS table, it will have access to all countries.
Otherwise, the CountryFrom or CountryTo should be in the set of allowed countries.
Assume that I have two users:
- User 1 is allowed to see all countries
- User 2 is only allowed to see UK
Then with the current setup: user 1 and 2 both can see all countries.
On the contrary, I have two remarkable findings:
1. If i only use the last part of the SWITCH function (lines 5-11) as filter on Quote: user 2 only sees UK. But user 1 sees nothing.
2. If I check the first equation of the SWITCH function (lines 2-3) as a measure: user 1 has value BLANK() and user 2 has value 1.
According to these findings one should say that my RLS formula should work, but obviously it won't. I have tried several things but none of them work, does someone know how to solve this problem?
Hi, Anonymous
I think you don't understand enough about the nature of RLS. Since your main table doesn't have an email column, you can only get row and column data through relational matching. If it doesn't match, you can get nothing, not get all.
So what you have to do is to build a new role to separate those who can see the whole report from those who can see some of the reports.
Below is my sample:
Copy the user table and rename it to distinguish from and to, and then establish a relationship with the main table respectively.
Set rls roles in desktop.
Deploy permissions in the service.
Note that you need to create two groups to avoid conflicts, put users who see all reports in one group and add them to All roles. Put the users who can only see some of them in another group, and then add them to From and To roles, both roles must be added, otherwise they will not see all of they can see.
I have tested this method and it works fine.
Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
4 Replies
Hi luuksommers,
Are you over-complicating this? Could you create 2 RLS roles, one for users in the company filter and one for users that can see everything?
I have a sample file here using a bridging table and 2 roles that works as you describe, let me know if you would like a copy.
Thank you
Anonymous
I think your logic is so twisted, If you can provide a sample with some dummy data, I can make a easy sample method for you.
Best Regards,
Community Support Team _ JaneyThanks for your reply!
I have created a sample with some dummy data. In this sample you see the 'fact Quote' table, with some details. The second table 'RLS User' one can see the table which I duplicated in my example. In this table you can see which users are allowed to see which countries. So in this sample the users should see the following quotes:
- User 1: all quotes
- User 2: 1, 4, 5, 6, 7 and 10
- User 3: 2, 3, 4, 6 and 9
QuoteID Price Lane CountryFrom CountryTo 1 20 GB-London---NL-Rotterdam GB NL 2 10 ES-Barcelona---FR-Paris ES FR 3 50 NL-Rotterdam---ES-Barcelona NL ES 4 5 GB-London---ES-Barcelona GB ES 5 25 IE-Dublin-GB-London IE GB 6 30 ES-Barcelona---IE-Dublin ES IE 7 40 NL-Rotterdam---GB-London NL GB 8 40 FR-Paris---NL-Rotterdam FR NL 9 20 ES-Barcelona---FR-Paris ES FR 10 10 IE-Dublin-GB-London IE GB UserID Email Country 2 [email protected] GB 2 [email protected] IE 3 [email protected] ES Hi, Anonymous
I think you don't understand enough about the nature of RLS. Since your main table doesn't have an email column, you can only get row and column data through relational matching. If it doesn't match, you can get nothing, not get all.
So what you have to do is to build a new role to separate those who can see the whole report from those who can see some of the reports.
Below is my sample:
Copy the user table and rename it to distinguish from and to, and then establish a relationship with the main table respectively.
Set rls roles in desktop.
Deploy permissions in the service.
Note that you need to create two groups to avoid conflicts, put users who see all reports in one group and add them to All roles. Put the users who can only see some of them in another group, and then add them to From and To roles, both roles must be added, otherwise they will not see all of they can see.
I have tested this method and it works fine.
Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
