ABP.io with row level security on a SQL Server database with EF

[drewwilliams1982]

We're using tenant isolation via the global EF filter, but want to add RLS on the database too. I've implemented this and it appears to be working, but I am fearful that I've missed finer implementation details.

The working prototype I have uses a EF DBCommandInterceptor and prepends the tenant on every command:

private void Prepend(DbCommand command)
{
    if (command.CommandType != System.Data.CommandType.Text) return;

    command.CommandText = "EXEC sp_set_session_context N'TenantId', @__rls_tenantId;\n" + command.CommandText;

    var p = command.CreateParameter();
    p.ParameterName = "@__rls_tenantId";
    p.Value = (object?)currentTenant.Id ?? DBNull.Value;
    command.Parameters.Add(p);
}

The HOST problem: The main issue I encountered was with my original implementation where I was using a connection based interceptor. This led to a single bug (which I was lucky to find during dev) where the framework occasionally drops into NULL tenant (through CurrentTenant.ChangeTenant) for some host-level data. We don't use a NULL host and have gone to lengths to remove it as much as possible, but seeing the framework dip into NULL tenant mode whilst grabbing settings (for example), means this still happens beyond my control. The issue is that if this is the first db op, then the whole connection is setup with tenantId = NULL on the session context. The command interceptor solves this as it's set per command, which requires the Current Tenant to be correct at the time of the command execution.

My concern is that I've missed some other nuanced condition with my implementation. I feel lucky to have hit the above condition, but I don't know if there's anything else I might have missed.

Has anyone else implemented RLS directly on the database in conjunction with the EF global filter? What issues have you hit?

[maliming]

Hi,

Your implementation looks reasonable and the switch from a connection-level interceptor to a command-level one is the right call — it avoids the session context being "locked in" for the lifetime of a connection.

That said, this isn't really an ABP-specific concern. The sp_set_session_context + DbCommandInterceptor pattern is a general EF Core topic, and the edge cases you're worried about (command types, interceptor coverage, etc.) are not tied to ABP internals. You might get better feedback from the EF Core community — the efcore GitHub repo or the EF Core discussions would be a better place to find others who have implemented RLS this way.

Thanks

[drewwilliams1982]

Thanks for your reply. Sorry if I didn't make it clear, but it's specifically ABP internals that I'm asking about here.

The example I gave about needing to use a command level interceptor was because of ABP internals and was a surprise to me. I'm trying to seek guidance or experience of using a command interceptor for RLS specifically with ABP to determine if there are any other internals of ABP that are incompatible.

[rodri-oliveira-dev]

Hi @drewwilliams1982,

I think your concern is valid, particularly because ABP introduces tenant-scoping and Unit of Work behavior on top of EF Core.

Switching to a DbCommandInterceptor makes sense for the scenario you described. However, I would pay attention to a few ABP-specific interactions before relying on this implementation for tenant isolation in production.

1. Tenant changes within an active Unit of Work

ABP's ICurrentTenant.Change() changes the ambient tenant context, and nested scopes restore the previous tenant when disposed.

However, ABP also manages DbContext instances and database connections through its Unit of Work infrastructure.

This creates an important scenario to test:

1. Start a Unit of Work under tenant A.

2. Execute a database command.

3. Enter CurrentTenant.Change(null) or switch to tenant B.

4. Execute another command within the same Unit of Work.

5. Return to tenant A and execute another command.

Your interceptor should set the session context for every command, but I would also verify how the DbContext and connection are reused throughout these transitions.

In particular, switching the ambient tenant should not be treated as equivalent to creating a new DbContext or Unit of Work.

For operations intentionally crossing tenant boundaries, separate Units of Work may provide clearer isolation boundaries.

2. Host context and data filters are different concerns

ABP distinguishes between:

using (CurrentTenant.Change(null))
{
    // Execute in host context.
}

And:

using (_dataFilter.Disable<IMultiTenant>())
{
    // Disable ABP's multi-tenant query filter.
}

These operations are not equivalent.

A null tenant is part of ABP's normal execution model, even if your application primarily operates with tenant-specific data.

I would therefore define an explicit RLS policy for the null context rather than attempting to replace it with the previous tenant.

For tenant-owned tables, my preferred default would be:

• A valid tenant context permits only rows belonging to that tenant.

• A missing tenant context does not automatically grant unrestricted access.

• Any legitimate cross-tenant administrative operation follows an explicitly authorized path.

Be particularly careful with a predicate that interprets SESSION_CONTEXT(N'TenantId') IS NULL as permission to access every tenant.

3. Interceptor coverage

Your current implementation skips commands whose CommandType is not Text.

That may be intentional, but I would verify the complete database access surface.

For EF Core, ensure your implementation handles the synchronous and asynchronous variants of:

• ReaderExecuting

• ScalarExecuting

• NonQueryExecuting

Also consider:

• ABP modules using their own DbContexts.

• Background workers and distributed event handlers.

• Stored procedures and raw ADO.NET/Dapper operations.

• Database migrations or maintenance tasks that intentionally run outside normal tenant-scoped access.

EF Core interceptors only cover commands executed through the EF Core pipeline where they are registered.

The SQL Server RLS policy still applies to other database access paths, but those paths must also establish the correct session context.

4. SQL Server connection reuse and MARS

Two SQL Server details are worth testing.

First, avoid setting the session context as read_only = 1 if the same logical connection needs to switch between tenants.

Second, check whether Multiple Active Result Sets (MARS) is enabled.

Microsoft documents specific visibility and concurrency behavior for SESSION_CONTEXT when multiple MARS batches are active.

If your design depends on modifying the session context between commands, I would test those conditions explicitly.

5. RLS must protect writes as well as reads

One additional consideration: SQL Server distinguishes between filter predicates and block predicates.

A filter predicate can prevent a tenant from reading another tenant's rows, but it does not necessarily prevent inserting a row associated with a different tenant.

I would include appropriate AFTER INSERT and AFTER UPDATE block predicates where required.

Also, since SQL Server allows callers to set their own session context, this architecture is valuable as defense in depth against accidental query-filter omissions, but it should not be treated as a separate authentication boundary against arbitrary SQL execution.

Suggested integration tests

Before considering the implementation production-ready, I would validate at least these scenarios:

Scenario | Expected result -- | -- Tenant A reads tenant B's records | No records returned Tenant A inserts a record for tenant B | Operation rejected Tenant A switches to B within a Unit of Work | Only B's records visible while B is active Tenant context returns to A | Only A's records visible ABP temporarily switches to host context | No unintended access to tenant-owned data ABP multi-tenant filter is disabled | SQL Server RLS still restricts access Background worker executes without a tenant | No unintended access Connections are reused across requests | No tenant context leakage

I would prioritize integration tests against SQL Server rather than relying solely on EF Core unit tests, because the most important guarantees here depend on connection and session behavior.

Conclusion

I don't see an inherent architectural incompatibility between ABP multi-tenancy and SQL Server RLS.

However, the combination introduces two independently managed contexts:

1. ABP's ambient ICurrentTenant and Unit of Work.

2. SQL Server's connection-scoped SESSION_CONTEXT.

Your interceptor is effectively responsible for keeping these contexts synchronized.

That synchronization, particularly during nested tenant changes and connection reuse, is what I would consider the main risk to validate.

Relevant references:

• ABP — Multi-Tenancy

• ABP — Unit of Work

• EF Core — Interceptors

• SQL Server — SESSION_CONTEXT

• SQL Server — Row-Level Security

I would be interested to know whether your implementation allows tenant changes within the same Unit of Work, or whether you have already established separate Unit of Work boundaries for those operations. That distinction could help narrow down the remaining risks.

Hi @drewwilliams1982,

I think your concern is valid, particularly because ABP introduces tenant-scoping and Unit of Work behavior on top of EF Core.

Switching to a DbCommandInterceptor makes sense for the scenario you described. However, I would pay attention to a few ABP-specific interactions before relying on this implementation for tenant isolation in production.

1. Tenant changes within an active Unit of Work

ABP's ICurrentTenant.Change() changes the ambient tenant context, and nested scopes restore the previous tenant when disposed.

However, ABP also manages DbContext instances and database connections through its Unit of Work infrastructure.

This creates an important scenario to test:

1. Start a Unit of Work under tenant A.
2. Execute a database command.
3. Enter CurrentTenant.Change(null) or switch to tenant B.
4. Execute another command within the same Unit of Work.
5. Return to tenant A and execute another command.

Your interceptor should set the session context for every command, but I would also verify how the DbContext and connection are reused throughout these transitions.

In particular, switching the ambient tenant should not be treated as equivalent to creating a new DbContext or Unit of Work.

For operations intentionally crossing tenant boundaries, separate Units of Work may provide clearer isolation boundaries.

2. Host context and data filters are different concerns

ABP distinguishes between:

using (CurrentTenant.Change(null))
{
    // Execute in host context.
}

And:

using (_dataFilter.Disable<IMultiTenant>())
{
    // Disable ABP's multi-tenant query filter.
}

These operations are not equivalent.

A null tenant is part of ABP's normal execution model, even if your application primarily operates with tenant-specific data.

I would therefore define an explicit RLS policy for the null context rather than attempting to replace it with the previous tenant.

For tenant-owned tables, my preferred default would be:

• A valid tenant context permits only rows belonging to that tenant.
• A missing tenant context does not automatically grant unrestricted access.
• Any legitimate cross-tenant administrative operation follows an explicitly authorized path.

Be particularly careful with a predicate that interprets SESSION_CONTEXT(N'TenantId') IS NULL as permission to access every tenant.

3. Interceptor coverage

Your current implementation skips commands whose CommandType is not Text.

That may be intentional, but I would verify the complete database access surface.

For EF Core, ensure your implementation handles the synchronous and asynchronous variants of:

• ReaderExecuting
• ScalarExecuting
• NonQueryExecuting

Also consider:

• ABP modules using their own DbContexts.
• Background workers and distributed event handlers.
• Stored procedures and raw ADO.NET/Dapper operations.
• Database migrations or maintenance tasks that intentionally run outside normal tenant-scoped access.

EF Core interceptors only cover commands executed through the EF Core pipeline where they are registered.

The SQL Server RLS policy still applies to other database access paths, but those paths must also establish the correct session context.

4. SQL Server connection reuse and MARS

Two SQL Server details are worth testing.

First, avoid setting the session context as read_only = 1 if the same logical connection needs to switch between tenants.

Second, check whether Multiple Active Result Sets (MARS) is enabled.

Microsoft documents specific visibility and concurrency behavior for SESSION_CONTEXT when multiple MARS batches are active.

If your design depends on modifying the session context between commands, I would test those conditions explicitly.

5. RLS must protect writes as well as reads

One additional consideration: SQL Server distinguishes between filter predicates and block predicates.

A filter predicate can prevent a tenant from reading another tenant's rows, but it does not necessarily prevent inserting a row associated with a different tenant.

I would include appropriate AFTER INSERT and AFTER UPDATE block predicates where required.

Also, since SQL Server allows callers to set their own session context, this architecture is valuable as defense in depth against accidental query-filter omissions, but it should not be treated as a separate authentication boundary against arbitrary SQL execution.

Suggested integration tests

Before considering the implementation production-ready, I would validate at least these scenarios:

Scenario	Expected result
Tenant A reads tenant B's records	No records returned
Tenant A inserts a record for tenant B	Operation rejected
Tenant A switches to B within a Unit of Work	Only B's records visible while B is active
Tenant context returns to A	Only A's records visible
ABP temporarily switches to host context	No unintended access to tenant-owned data
ABP multi-tenant filter is disabled	SQL Server RLS still restricts access
Background worker executes without a tenant	No unintended access
Connections are reused across requests	No tenant context leakage

I would prioritize integration tests against SQL Server rather than relying solely on EF Core unit tests, because the most important guarantees here depend on connection and session behavior.

Conclusion

I don't see an inherent architectural incompatibility between ABP multi-tenancy and SQL Server RLS.

However, the combination introduces two independently managed contexts:

1. ABP's ambient ICurrentTenant and Unit of Work.
2. SQL Server's connection-scoped SESSION_CONTEXT.

Your interceptor is effectively responsible for keeping these contexts synchronized.

That synchronization, particularly during nested tenant changes and connection reuse, is what I would consider the main risk to validate.

Relevant references:

• [ABP — Multi-Tenancy](https://abp.io/docs/latest/framework/architecture/multi-tenancy)
• [ABP — Unit of Work](https://abp.io/docs/latest/framework/architecture/domain-driven-design/unit-of-work)
• [EF Core — Interceptors](https://learn.microsoft.com/en-us/ef/core/logging-events-diagnostics/interceptors)
• [SQL Server — SESSION_CONTEXT](https://learn.microsoft.com/en-us/sql/t-sql/functions/session-context-transact-sql)
• [SQL Server — Row-Level Security](https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security)

I would be interested to know whether your implementation allows tenant changes within the same Unit of Work, or whether you have already established separate Unit of Work boundaries for those operations. That distinction could help narrow down the remaining risks.