Row Level Security design for key value attributes

Question

Background

I have a set of objects, called Protected Entities. These Protected Entities, have custom defined attributes attached to them (managed from an application (.NET)). Within the application we've also defined a set of Roles a user (or group of users) might have.

This will be running on Azure SQL by the way

What I want

Now I want to define a query, which I want to use with Row Level Security, to allow for application defined filtering on these protected entities.

Use case:

User1 with the role Regional User has only access to the protected entities where all the attributes of those entities are matching with the filtering defined on the Regional User role.

User2 with the role Regional User and Field 3 Expert role has only access to the protected entities matching the filtering on Regional User role OR matching the filtering on Field 3 expert role.

Why RLS? Because I want to building a guarantee that doing aggregate calculations and or other database operations are only done on the entities you have access to (needed requirement).

Example Dataset

Protected Entities

ID	Protected Entity Name
1	First Entity
2	Second Entity

Entities attributes (key value)

ID Entity	Key	Value of attribute
1	MyField	My Val
1	MyField1	My Val 2
2	MyField	Some other val

Roles

ID	Name
R1	Regional User
R2	Field 3 Expert
R3	Global Admin

Role filter table

(Which role has access to which protected entity based on the attribute values (AND filter)

Role ID	Attribute Key	Access when value is
R1	MyField1	MyRegional Value
R1	MyField2	Only when this second filter is also matching
R2	MyField3	Specific Field 3 value

What's the question?

I'm trying to explore 2 things. This example above is about applying an AND filter on a role. The next option I'm exploring in putting a filter object between Role and it's filtering, allowing for AND + OR combination of filtering. Where AND are inside a filter, the OR accross those filters.

I find it difficult to think on how to query this well. I started with the following query, however, I'm not sure how to approach it well. Especially with a performant query (i'm first going for a filtered view, before trying to translate it to a security predicate).

I know I need a count on how many filters I expect and how many filters are matched per role and filter.

DECLARE @username varchar(50);

SET @username = 'priority-nl';

SELECT tempRoleCount.AmountOfFiltersExpected, af.RoleId, af.SecurityFilterId, pa.EntityId, COUNT(pa.AttributeValue) AS a
FROM dbo.AttributeFilters af
LEFT JOIN (
    SELECT RoleId, SecurityFilterId, COUNT(1) AS AmountOfFiltersExpected FROM dbo.AttributeFilters
    GROUP BY RoleId, SecurityFilterId
) AS tempRoleCount ON (tempRoleCount.RoleId = af.RoleId AND tempRoleCount.SecurityFilterId = af.SecurityFilterId)
LEFT JOIN dbo.ProtectedEntityAttributes pa ON (af.AttributeKey = pa.AttributeKey AND af.ValueToFilter = pa.AttributeValue)
WHERE NOT(EntityId IS NULL)
GROUP BY af.RoleId, af.SecurityFilterId, pa.EntityId

Example SQL database code setup

https://gist.github.com/meghuizen/8021572216fe50958f2fca25e92fa6b0