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.

Recommended reading for building great ASP.NET core web APIs:

Connect with me:RSSGitHubTwitterLinkedIn
Share this:Share on RedditTweet about this on TwitterShare on LinkedInShare on Google+

6 thoughts on “Creating a Multi-Tenant ASP.NET Core Web API with SQL Server RLS”

  1. You’ll have problems with this if EF Core does some connection pooling, as I think it does in v2…
    Instead you can use the new feature called “Global query filters” that allow to do all this without having to tweak the database.

  2. Great Article! From the code above I understand that the products would be listed per tenant based on the api key being passed. However I am curious about the fact that if we have a superuser and that person was to login in, how would the tenant finder work in such a scenario?

    1. Thanks for the comment Vikas!

      The superuser wouldn’t be used by the app (unless there is a feature that needs access to all the tenant data). I find the superuser really useful whilst developing and debugging

  3. this is the most convincing article i have read concept clear! :) thanks. How would we implement user wise security in this principal say there is a user of tenant a and i only want him to edit or delete the records he inputted.

    1. Thanks for the kind comment!

      Good question on the only allowing certain users to change certain records. I would look into using the BLOCK PREDICATE and instead of using the same predicate function as the FILTER PREDICATE, I would associate it with a new function, dbo.UserAccessPredicate() say, that checks that the user is allowed to edit / delete the record. In addition to this I would pass the user id or role id from the code into SQL in another SESSION_CONTEXT() variable – the rule in dbo.UserAccessPredicate() would do a check against this …

      Hope this helps!

Leave a Reply

Your email address will not be published. Required fields are marked *