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();
        }