How to Apply a Hierarchical Multi-Tenant Filter with Row-Level Security

[grcontin]

I am building a hierarchical multi-tenant project and need to apply RLS (Row-Level Security) to my queries. I have tried doing this using a global filter and, in a more desperate attempt, even composing the generated query via an interceptor. However, I am not very confident about the best path to follow. To better illustrate, my hierarchical multi-tenant structure has the following entities:

public sealed class Tenant
{
    public int Id { get; init; }
    public IReadOnlyCollection<Unit> Units => _units;
    public IReadOnlyCollection<User> Users => _users;
    public string Name { get; private set; }
    //...

    private readonly HashSet<Unit> _units = new();
    private readonly HashSet<User> _users = new();
}

public sealed class Unit
{
    public int Id { get; init; }
    public int TenantId { get; private set; }
    public IReadOnlyCollection<Department> Departments => _departments;
    public string Name { get; private set; }
    //...

    private readonly HashSet<Department> _departments = new();
}


public sealed class Department
{
    public int Id { get; init; }
    public int UnitId { get; private set; }
    public IReadOnlyCollection<Team> Teams => _teams;
    public string Name { get; private set; }
    //...

    private readonly HashSet<Teams> _teams = new();
}

public sealed class Team
{
    public int Id { get; init; }
    public int DepartmentId { get; private set; }
    public string Name { get; private set; }
    //...
}


public sealed class User
{
    public int Id { get; init; }
    public int TenantId { get; private set; }
    public string Username { get; private set; }
    public IReadOnlyCollection<UserGrant> Grants => _grants;
    //...
	
    private readonly HashSet<UserGrant> _grants = new();
}

public sealed class UserGrant
{
    public int Id { get; init; }
    public int UserId { get; private set; }
    public int UnitId { get; private set; }
    public int DepartmentId { get; private set; }
    public int TeamId { get; private set; }
}

internal sealed class MultiTenantDbContext : DbContext
{
        public DbSet<Tenant> Tenants { get; private set; }
        public DbSet<Unit> Units { get; private set; }
        public DbSet<Department> Departments { get; private set; }
        public DbSet<Team> Teams { get; private set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Tenant>()
                .HasKey(t => t.Id);

            modelBuilder.Entity<Unit>()
                .HasKey(u => u.Id);

            modelBuilder.Entity<Unit>()
                .HasOne(u => u.Tenant)
                .WithMany(t => t.Units)
                .HasForeignKey(u => u.TenantId);
				
			modelBuilder.Entity<Tenant>()
                .HasMany(t => t.Users)
                .WithOne(u => u.Tenant)
                .HasForeignKey(u => u.TenantId);

            modelBuilder.Entity<Department>()
                .HasKey(d => d.Id);

            modelBuilder.Entity<Department>()
                .HasOne(d => d.Unit)
                .WithMany(u => u.Departments)
                .HasForeignKey(d => d.UnitId);

            modelBuilder.Entity<Team>()
                .HasKey(t => t.Id);

            modelBuilder.Entity<Team>()
                .HasOne(t => t.Department)
                .WithMany(d => d.Teams)
                .HasForeignKey(t => t.DepartmentId);
				
				modelBuilder.Entity<User>()
                .HasKey(u => u.Id);

            modelBuilder.Entity<User>()
                .HasOne(u => u.Tenant)
                .WithMany(t => t.Users)
                .HasForeignKey(u => u.TenantId);

            modelBuilder.Entity<UserGrant>()
                .HasKey(ug => ug.Id);

            modelBuilder.Entity<UserGrant>()
                .HasOne(ug => ug.User)
                .WithMany(u => u.Grants)
                .HasForeignKey(ug => ug.UserId);

            modelBuilder.Entity<UserGrant>()
                .HasOne(ug => ug.Unit)
                .WithMany()
                .HasForeignKey(ug => ug.UnitId);

            modelBuilder.Entity<UserGrant>()
                .HasOne(ug => ug.Department)
                .WithMany()
                .HasForeignKey(ug => ug.DepartmentId);

            modelBuilder.Entity<UserGrant>()
                .HasOne(ug => ug.Team)
                .WithMany()
                .HasForeignKey(ug => ug.TeamId);
        }
    }
}

My filter would need to apply an INNER JOIN with User and UserGrants and ensure that no user from one department or team within the same tenant can access information about a user from another department or team, for example.

Another thing I would like to know is if there is a way to avoid having User and UserGrants replicated and mapped across every bounded context of the application. Ideally, these would be mapped in a single place where I could apply the filter under the appropriate conditions.

EF Core version: 8.0.11
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 8.0
Operating system:
IDE: Visual Studio 2022 17.4

[roji]

What hasn't worked for you with global query filters?

[grcontin]

I couldn't apply this filter configuration because it involves some JOINS that I need to perform. Additionally, I need to apply some AND conditions in the ON clauses of these JOINS, and, in the end, a WHERE clause that also checks the user's ID. This filter needs to be applied whenever the user is authenticated. It's a very complex filter, not a simple one.
Here is an example of the filter I need to apply:

INNER JOIN Users ON Tenants.Id = Users.TenantId  
INNER JOIN UserGrants ON Users.Id = UserGrants.UserId  
AND UserGrants.UnitId = Units.Id  
AND UserGrants.DepartmentId = Departments.Id  
AND UserGrants.TeamId = Teams.Id  
WHERE Users.Id = @Id  

This filter sometimes changes depending on which table I am trying to read specific information from, so it ends up being very complex. That’s why I’m a bit lost on how to proceed.