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.