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

Upgrading a Product to SQL Server 2016: Things to Check

SQL Server 2016 has been released recently. How do you check if your product is compatible with SQL Server 2016? Here’s the process I usually go through …

Research

I start by doing some research – looking at the discontinued, deprecated, breaking changes and behavioural changes listed by Microsoft and searching the application and database for occurrences. If any occurrences are found, I’ll note them down and include them in the next stage of the process which is testing.

Here are the lists published by Microsoft …

Discontinued Features

Deprecated Features

Breaking Changes

Behavior Changes

Upgrade Advisor

I will also run the SQL 2016 Upgrade Advisor on the database. This should identify stuff from the lists above, as well as potential improvements. Again, I’ll note any recommendations down for the testing stage. Here’s the link to the Upgrade Advisor

Hardware and Software Requirements

The last bit of my research is to check the new hardware and software Requirements for SQL 2016. I’ll check these against the 2014 requirements to determine what’s changed. The main changes I’ve found are:

  • Required .NET version is now 4.6
  • Minimum processor speed is now x64 processor: 1.4 GHz. So, no more x86

I will then update my product’s hardware and software requirements accordingly.

Tests

I’ll then create a test environment with my application running on top of a SQL Server 2016 database. Before I start my testing, I’ll double check the following:

  • SQL’s stats are up-to-date: EXEC sp_updatestats
  • SQL’s compatibility level is correct (should be 130): SELECT compatibility_level FROM sys.databases WHERE name = @dbname

Problem areas of the app will be noted down and taken to the next stage which is forming a plan the changes.

Testing Discontinued Features, Deprecated Features, Breaking Changes and Behavioural Changes

I’ll start by testing the areas of the app that I noted down from the research – to double check that the app is impacted and to determine how wide spread and severe the impact is.

Automated Testing

I’ll then run the product’s automated UI tests on the SQL Server 2016 database.

Performance Testing

I’ll then carry out performance tests in key areas to compare against our 2014 benchmarks. Usually, performance is better, but if an area is significantly slower, I’ll treat that has a problem that needs resolving.

Testing Potential Improvements

I’ll then go on to testing suggested improvements by the upgrade advisor. Any significant improvements will be noted down and included in the action plan in the next stage.

Action Plan

I’ll then form a plan from the test results. Changes will be logged in our issue management system, prioritised, scheduled and eventually implemented.
Compatibility is declared when all the changes have been completed in a release.

What process do you go through when testing your products against new versions of SQL Server?

Connect with me:RSSGitHubTwitterLinkedIn