Forum Discussion

JoseRamon's avatar
JoseRamon
Frequent Visitor
2 years ago

RLS problem: not adding permits when a user is assigned to different roles

This seems a very easy problem but is getting me crazy so much appreciatted if anyone can help…

 

I’ve got two tables named “Countries” and “Services”, one containing list of countries where we operate and the other containing overall list of services we provide, from service A to service Z.

 

I’ve got an “Invoices” table that contains invoice data for many countries and many services, but only one country and one service per row.  

 

I’ve set a 1:N relationship form Countries to Invoices and another 1:N relationship from “Services” to Invoices, with unidirectional filters

 

I need to configure for each country two different types of users

  • Type I    Users who can access Invoices issued to user's country but only for services A and K
  • Type II  Users who can access all Invoices issued to user's country no matter which service is invoiced

 

For users of Type I, I’ve created one COUNTRY_SERVICE rol for each country with security filter [Country]=”Country X” in Table “Countries” and security filter [service]=”A” || [service]=”K” in table “Services”

 

This COUNTRY_SERVICE roles work OK and if I add a user to many COUNTRY_SERVICE roles the permits are added so the user can access different countries and/or services. (Just as RLS documentation states)

 

Now for users of Type II I’ve defined ONE unique Role “ALL SERVICES” with security filters False() in Table “Countries” and no security filter in table “Services”. 

 

According to RLS documentation adding a user to both COUNTRY_SERVICE role and ALL_SERVICES role means the user should adquire the permits of both roles….…but that’s not the case.

 

What I’m finding is that such user

  1. Can actually see all rows of table “Services”. OK  🙂
  2. Can see only one row of table “Countries” with [Country] = “Country X”. 🙂
  3. Can see see only the rows of table  “Invoices” where [Country] = “Country X” and [service]=”A” || [service]=”K”.  ☹️

 

Any idea why a user that can see all rows of table "Services" cannot see rows with services different than “A” or “K” in table “Invoices”?

 

Thanks for your time

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi JoseRamon 

     

    That's because the user of type II's security filter is the false() in Table Country and empty in Service.

    The false() means that the user has no access, so it would make sense for security that in the absense of an expression to evaluate, it defaults to FALSE preventing someone from accessing the data just because they have no role.

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • JoseRamon's avatar
      JoseRamon
      Frequent Visitor
      2 years ago

      Thanks for your answer.

       

      Totally agreee that from a security perspective the behaviour you mention would de perfect and makes sense, but according to Microsoft documentation (see link below) RLS roles are additive.

       

      In fact when a user in my case is applied both roles, with filter False() for table "Countries" in one role and filter [Country]="Mexico" in the otrer role, the user sees only "Mexico" row in table "Countries" 🙂

       

      And for that user with no security filter for table "Services" in one role and filter [service]=”A” || [service]=”K” in the other role, user sees absolutely all raws in table "Services" 🙂

       

      So question is: Why that user sees only rows related to "Mexico" and services A and K in table "Invoices" and cannot see "Invoices" rows related to other services of Mexico?🤔

       

      https://learn.microsoft.com/en-us/power-bi/guidance/rls-guidance