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. The structure of the code will be deliberately simple and focused on the key bits that are needed for 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):

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

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:

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

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:

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

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 SQL’s session context in a key called TenantId.

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

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:

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:

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

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

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 …

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

Now, we’ll bring in EF core using nuget. We’ll need Microsoft.EntityFrameworkCore and Microsoft.EntityFrameworkCore.SqlServer.

Let’s turn our attention to the EF core data context. Here’s a basic class that maps our tenants and products:

public class DataContext : DbContext
{
    public DbSet<Tenant> Tenants { get; set; }
    public DbSet<Product> Products { get; set; }

    private string connectionString;

    public DataContext(DbContextOptions<DataContext> options,
        IHttpContextAccessor httpContextAccessor,
        IConfiguration configuration) : base(options)
    {
        this.connectionString = configuration["ConnectionStrings:DefaultConnection"];
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Tenant>().ToTable("Tenant");
        modelBuilder.Entity<Product>().ToTable("Product");
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        connection = new SqlConnection(this.connectionString);

        optionsBuilder.UseSqlServer(connection);

        base.OnConfiguring(optionsBuilder);
    }
}

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.

public class APIKey
{
    private readonly RequestDelegate next;
    public APIKey(RequestDelegate next)
    {
        this.next = next;
    }

    public async Task Invoke(HttpContext context)
    {
        var apiKey = context.Request.Headers["X-API-Key"].FirstOrDefault();
        if (string.IsNullOrEmpty(apiKey))
        {
            context.Response.StatusCode = 400;
            await context.Response.WriteAsync("Invalid API key");
            return;
        }
        Guid apiKeyGuid;
        if (!Guid.TryParse(apiKey, out apiKeyGuid))
        {
            context.Response.StatusCode = 400;
            await context.Response.WriteAsync("Invalid API key");
            return;
        }
        context.Items["APIKey"] = apiKeyGuid;
        await next.Invoke(context);
    }
}

public static class APIKeyExtension
{
    public static IApplicationBuilder UseAPIKey(this IApplicationBuilder app)
    {
        app.UseMiddleware<APIKey>();
        return app;
    }
}

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 inDataContext.

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

public void ConfigureServices(IServiceCollection services)
{
    ...
    services.AddSingleton<IHttpContextAccessor, HttpContextAccessor>(); // we need this to access HttpContext in DataContext    services.AddDbContext<DataContext>(ServiceLifetime.Scoped);}
public void Configure(
    IApplicationBuilder app,
    IHostingEnvironment env)
{
    app.useAPIKey();    ....
}

Let’s go back to DataContext. We can set the API key in our constructor:

private Guid apiKey;
public DataContext(DbContextOptions<DataContext> options,
    IHttpContextAccessor httpContextAccessor,
    IConfiguration configuration) : base(options)
{
    this.connectionString = configuration["ConnectionStrings:DefaultConnection"];
    if (httpContextAccessor.HttpContext != null)    {        this.apiKey = (Guid)httpContextAccessor.HttpContext.Items["APIKey"];    }}

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

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    connection = new SqlConnection(this.connectionString);
    connection.StateChange += (sender, e) =>        {            if (e.CurrentState == ConnectionState.Open)            {                var tenantId = GetTenantId();                var cmd = _connection.CreateCommand();                cmd.CommandText = @"exec sp_set_session_context @key=N'TenantId', @value=@TenantId";                cmd.Parameters.AddWithValue("@TenantId", tenantId);                cmd.ExecuteNonQuery();            }        };
    optionsBuilder.UseSqlServer(connection);

    base.OnConfiguring(optionsBuilder);
}

public Guid GetTenantId(){    var tenant = Tenants.Where(t => t.APIKey == this.apiKey).FirstOrDefault();    if (tenant == null)    {        throw new Exception("Unknown tenant");    }    return tenant.TenantId;}

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

[Route("api/[controller]")]
[ApiController]
public class ProductsController : ControllerBase
{
    private DataContext dataContext;
    public ProductsController(DataContext dataContext)
    {
        this.dataContext = dataContext;
    }

    [HttpGet]
    public IActionResult GetAll()
    {
        return Ok(dataContext.Products.ToList());
    }
}

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 add an action method to get a product by its id:

[HttpGet("{productId}", Name = "ProductGet")]
public IActionResult GetAll(Guid productId)
{
    var product = dataContext.Products.Where(p => p.ProductId == productId).FirstOrDefault();
    if (product == null) return NotFound();

    return Ok(product);
}

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

[HttpPost]
public IActionResult Post([FromBody]Product product)
{
    product.ProductId = Guid.NewGuid();
    dataContext.Products.Add(product);
    dataContext.SaveChanges();

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

… 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'):

[HttpPost]
public IActionResult Post([FromBody]Product product)
{
    product.ProductId = Guid.NewGuid();
    product.TenantId = dataContext.GetTenantId();
    dataContext.Products.Add(product);
    dataContext.SaveChanges();

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

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:

[HttpPut("{productId}")]
public IActionResult Put(Guid productId, [FromBody]Product product)
{
    var existingProduct = dataContext.Products.Where(p => p.ProductId == productId).FirstOrDefault();
    if (existingProduct == null) return NotFound();

    existingProduct.ProductName = product.ProductName;
    existingProduct.UnitPrice = product.UnitPrice;
    existingProduct.UnitsInStock = product.UnitsInStock;
    existingProduct.UnitsOnOrder = product.UnitsOnOrder;
    existingProduct.ReorderLevel = product.ReorderLevel;
    existingProduct.Discontinued = product.Discontinued;

    dataContext.SaveChanges();

    return Ok(existingProduct);
}

[HttpDelete("{productId}")]
public IActionResult Delete(Guid productId, [FromBody]Product product)
{
    var existingProduct = dataContext.Products.Where(p => p.ProductId == productId).FirstOrDefault();
    if (existingProduct == null) return NotFound();

    dataContext.Products.Remove(existingProduct);

    dataContext.SaveChanges();

    return NoContent();
}

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.

The full code for this can be downloaded from GitHub: https://github.com/carlrip/asp-net-core-multi-tenant-rls

Comments

Vicent November 15, 2017

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.

Carl December 11, 2017

Thanks for the comment Vicent – this is a great point and solution!

Vikas December 4, 2017

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?

Carl December 11, 2017

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

Dev Raj Gautam December 6, 2017

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.

Carl December 11, 2017

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!

Anand February 1, 2018

Nice explanation, can you explain how to implement in ASP.NET Web API not in ASP.NET Core Web API.

Harry March 6, 2018

Hi Carl,

First of all thanks for sharing your knowledge I have read almost all your posts and have learned a lot!

I have a question regarding the technique you are using to achieve to set sp_set_session_context.

Since your are crating a new connection object inside the db contact class. How will this get disposed and closed if my db context is using Scoped lifetimes?

Cheers, Harry

Carl March 6, 2018

Great question Harry! Yes, I think we’ll have a memory leak in the above DataContext class. However, if we remove the Connection_StateChange event handler in Connection_StateChange when the connection is closed I think it will dispose ok.

private void Connection_StateChange(object sender, System.Data.StateChangeEventArgs e)
{
    if (e.CurrentState == ConnectionState.Open)
    {
        var cmd = connection.CreateCommand();
        cmd.CommandText = @"exec sp_set_session_context @key=N'TenantId', @value=@TenantId";
        cmd.Parameters.AddWithValue("@TenantId", tenantId);
        cmd.ExecuteNonQuery();
    }
    else if (e.CurrentState == ConnectionState.Closed)
	{
	    connection.StateChange -= Connection_StateChange;
	}
}

Shawn November 5, 2019

Hey Carl,

Thanks for the post. When I try to implement this using EF Core 2.2, it closes the connection before I run any LINQ queries, causing my session_context value to be lost and not being used for RLS.

I tried to add connection.Open() before UseSqlServer(connection), but that’s a big no as EF Core no longer closes the connections.

I appreciate any thought you have on this.

Thanks, Shawn

Maxim Markelow March 30, 2018

Hi! In case of multitenant application, is it better to pass tenant id to DAO explicitly and filter by this id? Becase such implicit solution will be broken, if we will change database or ORM. Also we mix business and data access layer. And this approach is often considered as a bad practice. But it is high performance. And what about more sophisticated cases? For example, access for specific entity depands on user rights. Can you advice how fetch data with such restriction without filtering entities in application memory and with pagination support?

Carl March 31, 2018

Thanks for the question Maxim. I think you are asking a couple of questions here:

• Should we be doing the filtering outside of database and data access layers? If the app needs to work on different types of databases, this is going to be a challenge! Unless your db is very small, I always do the filtering in database queries – pulling all the data into memory and then doing the filtering in memory won’t scale very well
• Should we use RLS to implement data authorisation rules? – shouldn’t we implement these rules in the business layer? From an architectural point of view you can argue that. However, this won’t scale very well if you are dealing with lots of data. The other benefit of using RLS in the database is that the risk of data leaks reduced because the logic is centralised in database filter predicates rather than having to do this all over your code

Mark Arnold September 11, 2018

Thanks Carl. Very timely article for my team. One question though, are you confident that under load the API won’t in some way reuse connections (from a pool for instance) such that the wrong tenantId winds up in the session_context?

Carl September 13, 2018

Cheers Mark. I’m fairly sure this will be fine under load. I’ve used this approach for a web app that gets lots of traffic and it has no issues.