如何用Dapper和SQL RLS创建一个多租户ASP.NET Core Web API

在这篇文章中，我们将通过利用Dapper和SQL Server行级安全（RLS）创建一个多租户ASP.NET Core网络API。

创建数据库

首先，让我们创建一个包含几个表的数据库。一个表将存储我们的租户，一个表将存储一些真正的数据（本例中的产品）。下面是创建数据库和表的SQL脚本：

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

Tenant 表包含一个APIKey 字段，我们稍后将使用它来将 HTTP 请求映射到租户。

Product 表中的数据使用TenantId 字段分配给租户。

让我们在表中添加一些测试数据：

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

该数据库有两个租户--一个有三个产品，另一个有两个产品。

在进入RLS之前，让我们创建一个超级用户，它可以访问所有租户的数据（这对调试很有用）。我们还将创建一个普通用户，我们的网络API将使用该用户来访问数据：

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

创建行级安全

SQL Server RLS允许将谓词放在表上。当表被查询时，SQL Server会自动应用RLS谓词。

我们将使用一个RLS谓词，这样查询就会返回指定租户的数据。下面是这个谓词的SQL脚本：

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

Product 表最终会在查询过程中对每条记录使用这个谓词，并传入租户ID。

SESSION_CONTEXT 是一个SQL连接的键值存储。当网络API处理一个HTTP请求时，我们最终会把用户的租户ID放入 。SESSION_CONTEXT('TenantId')

如果传入的租户ID与会话上下文中的租户ID相匹配，则该谓词返回1 。如果用户是一个超级用户，该谓词也会返回1 。超级用户对于调试是很有用的，因为它可以访问所有的数据。

现在我们可以将谓词绑定到Product 表。FILTER 谓词确保我们只读取指定租户的数据，BLOCK 谓词确保我们只写指定租户的数据：

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

因此，让我们进行一次快速测试，连接为normaluser 。首先，我们将使用租户1：

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

现在让我们试试租户2。

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

我们可以看到，同样的SQL查询为连接上下文中指定的租户提供数据。很好!

创建ASP.NET Core网络API

网络API将被刻意简化，重点是实现多租户的关键部分。

下面是如何处理一个请求的概述：

• 请求将在其HTTP头中有一个API密钥。
• 自定义ASP.NET Core中间件将从HTTP请求中提取API密钥，并创建一个SQL连接，在其上下文中设置TenantId 。
• API控制器将使用中间件打开的连接。
• SQL Server RLS将启动并只提供相关租户的数据。

在我们写任何代码之前，我们需要为Dapper 和System.Data.SqlClient 添加依赖性。

创建租户中间件

租户中间件是网络API的一个关键部分。所以，让我们从这个开始：

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

以下是这个中间件的关键点：

• 如果中间件在请求中找到一个API密钥，它就会打开一个SQL连接。
• 打开连接后，立即执行一个查询，在连接上下文中设置租户ID。
• 然后，连接和租户对象被放在HTTP上下文中。这就是API控制器如何获得对这些对象的访问。
• 当连接仍然开放时，请求管道中的下一个中间件被调用。

我们需要在Startup 类中注册这个中间件。该中间件需要在请求管道中的API控制器之前出现：

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

中间件需要访问HTTP上下文，因此，我们需要在Startup 类中添加该服务：

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

创建模型

我们需要几个简单的类来保存数据：

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

创建控制器

控制器是非常直接的：

[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");
  }
}

我们从HTTP上下文中获取连接，然后进行dapper查询。不幸的是，我们不能在构造函数中获得连接，因为HTTP上下文是不可用的。

如果我们运行Web API并使用API密钥进行请求，我们就能得到相关的租户产品。

让我们创建一个动作方法来获取单个产品：

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

让我们测试一下，尝试访问一个不属于该API密钥的租户的产品：

我们得到一个404，这就是我们想要的。

我们将实现的最后一个动作方法将发布一个产品：

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

注意，在产品插入数据库之前，我们在产品上设置了正确的租户ID。

让我们试一试。

它起作用了!

这篇文章的代码可以在GitHub上找到：github.com/carlrip/asp…

总结

这种方法的好处是，我们不在dapper查询中明确应用过滤器--安全问题都是在SQL Server下游处理的。这降低了我们的开发成本和数据泄露的风险。其他应用程序可以访问同一个数据库，并应用同样的多租户逻辑。