Creating a Multi-Tenant ASP.NET Core Web API with SQL Server RLS

In this post we’re going to leverage SQL Server Row Level Security (RLS), Entity Framework Core and ASP.NET Core to create a multi-tenant database with a multi-tenant web API …

First, let’s create a database containing a couple of tables – one to hold our tenants and one to hold some real data (products in this example):

We’ll add some test data in there as well. We’ll have 2 tenants – one with 3 products and the other one with 2 products:

Before we move on to RLS, let’s create a “super user” that will have access to data in all the tenants (this is useful for debugging). While we are at it, we’ll create a “normal user” that our web API will use to access data:

Now on to the RLS. First we need to create a predicate function that is going to apply a filter when the product table is accessed for the given tenant. The tenant for a given row is passed into the function. The tenant that the API is operatoring for is stored in SESSION_CONTEXT('TenantId') – we’ll come on to how this is set in the web API soon. We also need to make sure our super user has access to all the rows in the table

Now we can bind our predicate function to our product table. The FILTER predicate ensures we only read data for a given tenant and the BLOCK predicate ensures we only write data for a given tenant:

So, let’s give this a quick test, connecting as normaluser:


We should get following results back (3 rows for the first query and 2 for the 2nd query):

If you connect as superuser, SELECT * FROM dbo.Product, you should get all 5 rows.

Cool, we’re done with our database. Let’s move on to our web API …

Let’s create a new ASP.NET Core Web Application in Visual Studio, choosing the Web API project template.

First let’s create our model classes to hold our tenants and products …

Now, we’ll bring in EF core using nuget (I’m using the stable 1.1.2 packages). We’ll need Microsoft.EntityFrameworkCore and Microsoft.EntityFrameworkCore.SqlServer.

Time to create our EF core data context. Here’s a basic class that maps our tenants and products:

We’re not done with DataContext yet – we need to set SESSION_CONTEXT('TenantId') for every query that EF core does … we’ll come back to this after we’ve got the tenant from the API.

So, let’s create some middleware that will inspect the API key from the request to determine the tenant:


Please note the highlighted line in the above code where we place the tenant in a dictionary in the HttpContext. This is how we are going to get the tenant in DataContext.

Before we go back to DataContext, let’s go and do all our registrations in Startup:

Time now to go back to DataContext.
We can set the tenant in our constructor:

We can then set SESSION_CONTEXT(‘TenantId’) every time a connection is opened before any queries are run by EF core.

Ok, we’re done with DataContext now. Let’s create a simple API now to get some products:

Let’s check this is working in Postman with our 2 API keys. We should get 3 products for the 1st key and 2 products for the 2nd key:

Cool, we are good. Let’s quickly add an action method to get a product by its id:

Now let’s create the action method in our API to post new products:


… and let’s give it a try:

… it blows up!

We obviously have a problem because we are not submitting the tenant id in the product.

I lied when I said we were done with our database! Let’s add a default value for the tenant id that picks up SESSION_CONTEXT(‘TenantId’):

Now, when you post a product in Postman, we should be ok:

To finish off our controller we’ll quickly implement a PUT and DELETE:

Pretty straightforward and they both work. If you try to PUT and DELETE products that belong to a tenant that your API key is not for then you get a 404 – just as we want.

The great thing about this approach is that there is no reference to the tenant in our controller code – the multi-tenancy is taken care for us lower down in the stack which reduces our development costs and risk of leaking data.

Connect with me:RSSGitHubTwitterLinkedIn

HttpContext in ASP.NET Core

HttpContext is largely the same in asp.net core as it’s always been. However, one difference is that it is not automatically as available everywhere in your code base as it used to be …

If you are in a controller then you are okay – HttpContext is a property of ControllerBase:

If you are in middleware then you are okay – HttpContext is passed in as a parameter:

However, if you are in the domain layer, the you need to do some work …

First you need to register IHttpContextAccessor for dependency injection in the apps Startup class:

In your domain layer, you then need to bring in Microsoft.AspNetCore.Http via nuget and use dependency injection to get a reference to IHttpContextAccessor which in tern gives you a reference to HttpContext

Connect with me:RSSGitHubTwitterLinkedIn