Building multitenant application – Part 2: Storing value into database session context from ASP.NET core web API

In the previous post about row level security in SQL server, I gave an example of reading a value from the database session context to feed into the security function for filtering data. In this post, I show examples of calling the sp_set_session_context store procedure from an ASP.NET core web API to store a value into the session context.

If a user logs into an SQL database directly, it’s straight forward to call the sp_set_session_context store procedure to set a value in the session. However, an end user typically does not authenticate directly against the database. Rather, the user logs into a web application which goes through an API for data. As such, we need a way to update the database session context via the web API.

In the application I’m working on, a user needs to authenticate before accessing any feature. Below example shows how I make a call to the database to set the session context on every successful authentication by hooking into the OnTokenValidated event.

 services.Configure<JwtBearerOptions>(JwtBearerDefaults.AuthenticationScheme, configureOptions =>
        {
            configureOptions.Events.OnTokenValidated = OnTokenValidated;
        });

 private static async Task OnTokenValidated(TokenValidatedContext ctx)
    {
        if (ctx.Principal is null)
        {
            throw new InvalidOperationException("User claims should not be null.");
        }
        var sessionContextRepository = ctx.HttpContext.RequestServices.GetRequiredService<ISessionContextRepository>();
        await sessionContextRepository.SetSessionContext("azureAdObjectId", value: ctx.Principal.GetObjectId());
    }

Below shows the relevant codes in the SessionContextRepository class

 public async Task SetSessionContext(string key, string value)
        {
            var connection = _budgetDbContext.Database.GetDbConnection();
            await connection.OpenAsync();
            var keyParameter = new SqlParameter("Key", key);
            var valueParameter = new SqlParameter("Value", value);
            var sqlCommand = new SqlCommand("EXEC sp_set_session_context @key = @Key, @value = @Value", (SqlConnection)connection);
            sqlCommand.Parameters.Add(keyParameter);
            sqlCommand.Parameters.Add(valueParameter);
            await sqlCommand.ExecuteNonQueryAsync();
        }

If your app does not need to depend on the user’s identity being available, you can also use the DbConnectionInterceptor to manipulate the session context whenever the app opens a connection. In the below example, I put the logic of calling the sp_set_session_context in a class that extends from DbConnectionInterceptor. I then register the class when setting up the database context.

  public class AppDbConnectionInterceptor : DbConnectionInterceptor
    {

        public AppDbConnectionInterceptor()
        {

        }

        public override async Task ConnectionOpenedAsync(DbConnection connection, ConnectionEndEventData eventData, CancellationToken cancellationToken = default)
        {
            var keyParameter = new SqlParameter("Key", "someKey");
            var valueParameter = new SqlParameter("Value", "someValue"); 
            var sqlCommand = new SqlCommand("EXEC sp_set_session_context @key = @Key, @value = @Value", (SqlConnection)connection);
            sqlCommand.Parameters.Add(keyParameter);
            sqlCommand.Parameters.Add(valueParameter); 
            await sqlCommand.ExecuteNonQueryAsync();
        }
    }

I register the interceptor by overriding the OnConfiguring method, as shown below:

public class AppDbContext : DbContext
{
    // --- codes omitted for brevity 
 
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.AddInterceptors(new AppDbConnectionInterceptor());
    }
}

Let’s recap. In the previous post, you see an example of how to implement row level security to filter out data at the database level. Sometimes, your filtering logic may depend on external metadata coming from the web API. For example, a logged in user may only see data belong to the user’s organization. In this case, you can leverage the database session context. In particular, you can call the sp_set_session_context from the web API when the app establishes a connection with the database, as shown in the examples above.

References

Row-Level Security – SQL Server | Microsoft Docs

Interceptors – EF Core | Microsoft Docs

Introduction to authentication for Single Page Apps on ASP.NET Core | Microsoft Docs

SESSION_CONTEXT (Transact-SQL) – SQL Server | Microsoft Docs

c# – Entity Framework – Setting session_context using IDbConnectionInterceptor – Stack Overflow