RLS with different levels of filters

[beatriz_verol]

I have a dashboard with three different levels of filters, such as: country, state and city, on each page.
I have a RLS setup with users for managers from cities, states and countries. A country manager should be able to see all information about his country, including the detailed information of states and cities. A city manager should see all details from his city, but also the “macro” data from his state and country, not being able to filter by any other city than his own. I’m failing on this step: on the city managers view, he can only see the "global" results (aka no filter) or his city´s.

All of my tables have city, state and country columns. It is possible to create a RLS setup with leveled permissions?

[v-jayw-msft · Accepted solution]

Hi @beatriz_verol ,

What is this "global" results in your table?

If it's the sum value of states and cities for corresponding country, you could consider to create a calculated column to get the value.

As below, If the table is filtered by city, the user still could see the global result for the country.

If I misunderstood your meaning, please share more details.

Best Regards,

Jay

[littlemojopuppy]

Hi @beatriz_verol 

I'm not really a fan of the solution I'm proposing, but maybe you could create a second table that summarizes the "global" macro data to satisfy the requirement.  I really don't like the idea of creating additional tables, but because of RLS I don't really see another workaround.  Hope this helps!