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

June 30, 2020

dotnet

In this post, we are going to create a multi-tenant ASP.NET Core web API by leveraging Dapper and SQL Server Row Level Security (RLS).

Creating the database

First, let’s create a database containing a couple of tables. One table will store our tenants, and one will store some real data (products in this example). Here are the SQL scripts that create the database and tables:

CREATE DATABASE ProductDb
GO
USE ProductDb
GO
CREATE TABLE dbo.Tenant
(
  TenantId uniqueidentifier NOT NULL,
  APIKey uniqueidentifier NOT NULL,
  TenantName nvarchar(200) NOT NULL,
  CONSTRAINT PK_Tenant PRIMARY KEY CLUSTERED (TenantId ASC)
)
GO
CREATE TABLE dbo.Product
(
  ProductID uniqueidentifier NOT NULL,
  TenantId uniqueidentifier NOT NULL,
  ProductName nvarchar(50) NOT NULL,
  UnitPrice money NULL,
  UnitsInStock smallint NULL,
  UnitsOnOrder smallint NULL,
  ReorderLevel smallint NULL,
  Discontinued bit NOT NULL,
  CONSTRAINT PK_Product PRIMARY KEY CLUSTERED (ProductID ASC),
  CONSTRAINT FK_Product_Tenant FOREIGN KEY (TenantId) REFERENCES dbo.Tenant(TenantId)
)
GO

The Tenant table contains an APIKey field, which we will use later to map an HTTP request to a tenant.

Data in the Product table is allocated to a tenant using the TenantId field.

Let’s add some test data into the tables:

INSERT INTO dbo.Tenant(TenantId, APIKey, TenantName)
VALUES('6CB8DE43-2043-4415-B267-7FFFA2EB5AC0', '98D92BF3-0620-4CC4-9C7D-78BC94DD4C55','tenant 1')
INSERT INTO dbo.Tenant(TenantId, APIKey, TenantName)
VALUES('25EA09EF-E24E-494B-911F-F63CE9ED8458', '081FF61A-E688-4DC2-84E7-6CC8FFED4D69','tenant 2')
GO

INSERT dbo.Product(ProductID, TenantId, ProductName, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
VALUES ('E897FF55-8F3D-4154-B582-8D37D116347F', '6CB8DE43-2043-4415-B267-7FFFA2EB5AC0', N'Chai', 18.0000, 39, 0, 10, 0)
GO
INSERT dbo.Product(ProductID, TenantId, ProductName, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
VALUES ('F5506F2A-5148-44FE-9225-AC75108AA30F', '6CB8DE43-2043-4415-B267-7FFFA2EB5AC0', N'Chang', 19.0000, 17, 40, 25, 0)
GO
INSERT dbo.Product(ProductID, TenantId, ProductName, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
VALUES ('775EDB92-32BE-4D46-ABBB-921BC1860514', '6CB8DE43-2043-4415-B267-7FFFA2EB5AC0', N'Aniseed Syrup', 10.0000, 13, 70, 25, 0)
GO
INSERT dbo.Product(ProductID, TenantId, ProductName, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
VALUES ('ED3D0EE0-3D02-460A-9659-58C34CCC663F', '25EA09EF-E24E-494B-911F-F63CE9ED8458', N'Chang', 16.0000, 3, 12, 13, 0)
GO
INSERT dbo.Product(ProductID, TenantId, ProductName, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
VALUES ('CED038B1-DF1C-4111-9E48-E386A1A7063A', '25EA09EF-E24E-494B-911F-F63CE9ED8458', N'Pavlova', 17.4500, 29, 0, 10, 0)
GO

The database has two tenants - one with three products and the other with two products.

Before moving on to RLS, let’s create a super user that will have access to data in all the tenants (this is useful for debugging). We’ll also create a normal user that our web API will use to access data:

CREATE LOGIN superuserlogin WITH PASSWORD = 'p@ssw0rd'
GO
CREATE USER superuser FOR LOGIN [superuserlogin]
GO
EXEC sp_addrolemember N'db_owner', N'superuser'
GO
CREATE LOGIN [normaluserlogin] WITH PASSWORD = 'p@ssw0rd'
GO
CREATE USER [normaluser] FOR LOGIN [normaluserlogin]
GO
EXEC sp_addrolemember N'db_datareader', N'normaluser'
EXEC sp_addrolemember N'db_datawriter', N'normaluser'
GO

Creating the row-level security

SQL Server RLS allows predicates to be put on a table. SQL Server then automatically applies the RLS predicate when the table is queried.

We are going to use a RLS predicate so that a query returns data from a specified tenant. Here’s the SQL script for the predicate:

CREATE FUNCTION dbo.TenantAccessPredicate (@TenantId uniqueidentifier)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS TenantAccessPredicateResult
  WHERE (@TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS uniqueidentifier))
      OR
      (DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('superuser'))
GO

The Product table will eventually use this predicate on each row during a query, passing in the tenant id.

SESSION_CONTEXT is a key-value store for a SQL connection. We will eventually put the user’s tenant id into SESSION_CONTEXT('TenantId') when the web API handles an HTTP request.

The predicate returns 1 if the tenant id passed in matches the one in the session context. The predicate also returns 1 if the user is a super user. The super user is useful for debugging because it gives access to all the data.

Now we can bind the predicate to the 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:

CREATE SECURITY POLICY dbo.TenantAccessPolicy
ADD FILTER PREDICATE dbo.TenantAccessPredicate(TenantId) ON dbo.Product,
ADD BLOCK PREDICATE dbo.TenantAccessPredicate(TenantId) ON dbo.Product
GO

So, let’s give this a quick test, connecting as normaluser. First we’ll use tenant 1:

EXEC sp_set_session_context @key=N'TenantId', @value='6CB8DE43-2043-4415-B267-7FFFA2EB5AC0'
GO
SELECT * FROM dbo.Product
GO

Now let’s try tenant 2:

EXEC sp_set_session_context @key=N'TenantId', @value='25EA09EF-E24E-494B-911F-F63CE9ED8458'
GO
SELECT * FROM dbo.Product
GO

We can see that the same SQL query gives data for the tenant specified in the connection context. Neat!

Creating the ASP.NET Core web API

The web API will be deliberately simple, focusing on implementing key parts for multi-tenancy.

Here’s an overview of how a request will be handled:

The request will have an API key in its HTTP header.
Custom ASP.NET Core middleware will pull the API key out of the HTTP request and create a SQL connection with TenantId set in its context.
The API controller will use the connection that the middleware opened.
SQL Server RLS will kick in and only give data for the relevant tenant.

Before we write any code, we need to add dependencies for Dapper and System.Data.SqlClient.

Creating tenant middleware

The tenant middleware is a key part of the web API. So, let’s start with this:

public class TenantMiddleware
{
  private readonly RequestDelegate next;

  public TenantMiddleware(RequestDelegate next)
  {
    this.next = next;
  }

  public async Task Invoke(HttpContext context, IConfiguration configuration)
  {
    context.Items["TenantConnection"] = null;
    context.Items["Tenant"] = null;
    var apiKey = context.Request.Headers["X-API-Key"].FirstOrDefault();
    if (string.IsNullOrEmpty(apiKey))
    {
      return;
    }
    Guid apiKeyGuid;
    if (!Guid.TryParse(apiKey, out apiKeyGuid))
    {
      return;
    }
    using (var connection = new SqlConnection(configuration["ConnectionStrings:DefaultConnection"]))
    {
      await connection.OpenAsync();
      var tenant = await SetTenant(connection, apiKeyGuid);
      context.Items["TenantConnection"] = connection;
      context.Items["Tenant"] = tenant;
      await next.Invoke(context);
    }
  }

  private async Task<Tenant> SetTenant(SqlConnection connection, Guid apiKey)
  {
    var tenant = await connection.QueryFirstOrDefaultAsync<Tenant>("SELECT * FROM Tenant WHERE APIKey = @APIKey", new { APIKey = apiKey });
    await connection.ExecuteAsync("EXEC dbo.sp_set_session_context @key = N'TenantId', @value = @value", new { value = tenant.TenantId });
    return tenant;
  }
}

public static class TenantMiddlewareExtension
{
  public static IApplicationBuilder UseTenant(this IApplicationBuilder app)
  {
    app.UseMiddleware<TenantMiddleware>();
    return app;
  }
}

Here are the key points for this middleware:

If the middleware finds an API key in the request, it opens a SQL connection.
Immediately after opening the connection, a query is executed to set the tenant id in the connection context.
The connection and the tenant objects are then put inside the HTTP context. This is how the API controller will gain access to these.
The next middleware in the request pipeline is invoked while the connection is still open.

We need to register this middleware in the Startup class. The middleware needs to come before the API controller in the request pipeline.

public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
    app.UseTenant();    ...
    app.UseEndpoints(endpoints =>
    {
        endpoints.MapControllers();
    });
}

The middleware needs access to HTTP context, so, we need to add that service in the Startup class:

public void ConfigureServices(IServiceCollection services)
{
  services.AddSingleton<IHttpContextAccessor, HttpContextAccessor>();  ...
}

Creating models

We need a couple of simple classes to hold the data:

public class Tenant
{
  public Guid TenantId { get; set; }
  public Guid APIKey { get; set; }
  public string TenantName { get; set; }
}

public class Product
{
  public Guid ProductId { get; set; }
  public Guid TenantId { get; set; }
  public string ProductName { get; set; }
  public Decimal UnitPrice { get; set; }
  public Int16 UnitsInStock { get; set; }
  public Int16 UnitsOnOrder { get; set; }
  public Int16 ReorderLevel { get; set; }
  public bool Discontinued { get; set; }
}

Creating the controller

The controller is very straight forward:

[Route("api/[controller]")]
[ApiController]
public class ProductsController: ControllerBase
{
  [HttpGet]
  public async Task<IEnumerable<Product>> GetAll()
  {
    var connection = (SqlConnection)HttpContext.Items["TenantConnection"];
    return await connection.QueryAsync<Product>("SELECT * FROM Product");
  }
}

We get the connection from the HTTP context and then make the dapper query. Unfortunately, we can’t get the connection in the constructor because the HTTP context isn’t available their.

If we run the web API and make a request with an API key, we get the relevant tenant products:

Let’s create an action method for getting a single product:

[HttpGet("{productId}", Name = "ProductGet")]
public async Task<ActionResult<Product>> GetById(Guid productId)
{
  var connection = (SqlConnection)HttpContext.Items["TenantConnection"];
  var product = await connection.QueryFirstOrDefaultAsync<Product>("SELECT * FROM Product WHERE ProductId = @ProductId", new { ProductId = productId });
  if (product == null) return NotFound();

  return Ok(product);
}

Let’s test this by trying to access a product that doesn’t belong to the tenant for the API key:

We get a 404, which is what we want.

The last action method we will implement will post a product:

[HttpPost]
public async Task<ActionResult<Product>> Post([FromBody]Product product)
{
  var connection = (SqlConnection)HttpContext.Items["TenantConnection"];
  var tenant = (Tenant)HttpContext.Items["Tenant"];
  product.ProductId = Guid.NewGuid();
  product.TenantId = tenant.TenantId;
  await connection.ExecuteAsync(@"INSERT INTO Product(ProductID, TenantId, ProductName, UnitPrice, UnitsInStock, ReorderLevel, Discontinued)
                                  VALUES(@ProductID, @TenantId, @ProductName, @UnitPrice, @UnitsInStock, @ReorderLevel, @Discontinued)",
                                  product);

  var url = Url.Link("ProductGet", new { productId = product.ProductId });
  return Created(url, product);
}

Notice that we set the correct tenant id on the product before it is inserted into the database.

Let’s give this a try:

It works!

The code for this post can be found in GitHub at https://github.com/carlrip/asp-net-core-multi-tenant-dapper-rls.

Wrap up

The great thing about this approach is that we don’t explicitly apply a filter in the dapper queries - the security is all handled downstream in SQL Server. This reduces our development costs and risk of leaking data. Other applications can access the same database and have the same multi-tenancy logic applied.

If you to learn about using React with ASP.NET Core you might find my book useful:

ASP.NET Core 5 and React

Find out more

You might find some of my other posts interesting:

Why React with ASP.NET Core?

What's new in ASP.NET Core 3 for React SPAs?

ASP.NET Core Logging with Serilog and SQL Server

Scalable and Performant ASP.NET Core Web APIs: Asynchronous Code

Integration Testing on ASP.NET Core Web API controllers with a SQL backend