Creating a Multi-Tenant ASP.NET Core Web API with Dapper and SQL 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. 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 …

#asp.net #.net #api #dapper #sql

carlrippon.com

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