Optimizing Permission-Based Filtering for Entities in Azure SQL Server

Question

I'm working with Azure SQL Server (2019) and need to design an efficient way to filter entities based on user permissions.

Scenario:

• Each entity is associated with one or more areas.
• Each user is exposed to multiple areas.
• A user can view an entity only if they are exposed to all of the entity’s areas.
• User permissions (exposed areas) come from an external source and are provided as a parameter (not stored in the database).

Example:

• Entity A belongs to Area 1 and Area 2.
• A user must have access to both Area 1 and Area 2 to see Entity A.

Attempts and Issues

1. Bitwise Representation:

   • Encode areas as bits and store a combined areaBits for each entity.
   • Query using: entities.areaBits & userExposedAreaBits = entities.areaBits.
   • Issue: Bitwise operations do not leverage indexes efficiently, leading to slow queries.
   • Tried precomputing permutations of area combinations, but this scales poorly (exponential growth).

2. Join with Relationship Table (EntityAreas):

   • Store (EntityId, AreaId) pairs in a mapping table.
   • Filter entities by checking if the user has access to all required areas.
   • Issue: The query becomes complex and struggles with performance at scale.

Additional Considerations

• Millions of entities.
• 20-30 areas, but they grow slowly (~2-3 new areas per year).
• 99% of entities belong to a single area, but multi-area entities must be handled.
• User permissions are passed as a parameter (e.g., comma-separated list, table-valued parameter, etc.).

Question

What is the best way to model and implement the main query to efficiently filter entities while ensuring a good execution plan? Looking for a solution that scales well with large datasets.