Row Level Security - Multiple Filter Criteria

[asm495]

I am trying to use RLS to filter some data according to a table which stores users territory information. The territory information is a table ("UserTerritories") consisting of both a Country and a Territory. Both of these fields can be wildcarded with a * to indicate they have access to either all countries or all territories within a country.

Example "UserTerritories" information table shown here. This would indicate that the first user (User 100) has access to all of Germany plus the West and North territories in Spain. User 200 has access to the East and Paris territories in France. User 300 has access to all data.

Username	Country	Territory
100	Germany	*
100	Spain	West
100	Spain	North
200	France	East
200	France	Paris
300	*	*

Example "Account" data to be filtered using RLS:

Account	Country	Territory
A001	Germany	West
A002	Germany	Berlin
A003	Spain	West
A004	Spain	North
A005	Spain	South
A006	France	North
A007	France	East
A008	France	West
A009	France	Paris

I have been able to use SELECTCOLUMNS in my RLS rules (as shown below) to be able to filter either country or territory but this treats each of these fields individually rather than as a combination. What this means is that if a user's territory rules has a * for one of the territory fields then it would include all territories for any of the countries they have access to and not just the one specific country.

var UserCountrySet = SELECTCOLUMNS(
  FILTER(
    'UserTerritories',
    [Username] = USERPRINCIPALNAME()
  ),
  "Country", [Country]
)

var UserTerritorySet = SELECTCOLUMNS(
  FILTER(
    'UserTerritories',
    [Username] = USERPRINCIPALNAME()
  ),
  "Territory", [Territory]
)

return
  ("*" IN UserCountrySet || [Country] IN UserCountrySet) &&
  ("*" IN UserTerritorySet || [Territory] IN UserTerritorySet)

Can anyone provide any guidance how I can filter the list of Accounts by seeing if it matches ANY of the row entries (i.e. the Country & Territory combination) in my "UserTerritories" table please? Any help greatly appreciated.

Thanks!

[OwenAuger · Accepted solution]

Hi @asm495 

There are different ways of writing the condition.

I would recommend something like this (to be placed as a RLS filter on the Account table):

VAR CurrentUser =
    USERPRINCIPALNAME ()

VAR CurrentCountryTerritory =
    -- Construct 4 row table containing the 4 combinations:
    -- ( Country, Territory ), ( Country, * ), ( *, Territory ), ( *, * )
    VAR CurrentCountry = 
        SELECTCOLUMNS ( { "*", Account[Country] }, "@Country",[Value] )
    VAR CurrentTerritory =
        SELECTCOLUMNS ( { "*", Account[Territory] }, "@Territory",[Value] )
    RETURN
        CROSSJOIN ( CurrentCountry, CurrentTerritory )


VAR Include =
    -- This returns TRUE only if at least one of the combinations in the variable CurrentCountryTerritory
    -- appears in the 'UserTerritories' table for the current user
    CALCULATE (
        NOT ISEMPTY ( 'UserTerritories' ),
        TREATAS ( CurrentCountryTerritory, 'UserTerritories'[Country], 'UserTerritories'[Territory] ),
        'UserTerritories'[Username] = CurrentUser
    )

RETURN
    Include

The logic is to:

1. For each row of Account table, there are four possible Country/Territory combinations that could exist in 'UserTerritories' that would allow that row of Account to be vislble.
2. Those four combinations are:
   1. ( Country, Territory )
   2. ( Country, * )
   3. ( *, Territory )
   4. ( *, * )
3. If any of those is present in 'UserTerritories' when filtered on the current user, then return TRUE, i.e. the current row of Account will be visible.

Does this work for you?

Regards,

Owen

[asm495]

Thank you so much. This was the perfect solution and did exactly what I needed! Very much appreciated.