RLS using an if(switch)-statement and an or-statement

[Anonymous]

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?

[v-janeyg-msft]

@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 _ Janey

[Anonymous]

Thanks 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	b@mail.com	GB
2	b@mail.com	IE
3	c@mail.com	ES

[v-janeyg-msft · Accepted solution]

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

[jsaunders_zero9]

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