使用SQL Server行级安全性的分片多租户数据库

最新推荐文章于 2024-07-20 17:22:43 发布

寒冰屋

最新推荐文章于 2024-07-20 17:22:43 发布

阅读量799

点赞数

分类专栏：数据库文章标签：数据库 sql mysql

原文链接：https://www.codeproject.com/Articles/5318079/Sharded-Multi-Tenant-Database-using-SQL-Server-Row

版权

数据库专栏收录该内容

225 篇文章 13 订阅

订阅专栏

附录 1 - SUSER_ID()和字符串操作

附录 2 - SESSION_CONTEXT和APP_NAME

介绍

为多租户SaaS应用程序设计数据库有几种不同的设计模式。最流行的一种方法是在多个租户之间共享一个数据库（可以为所有租户使用一个数据库，也可以将租户拆分到多个数据库中，这样我们就可以水平扩展）。本文展示了创建分片多租户数据库的具体示例代码，其中租户在逻辑上使用行级安全谓词和每个租户的单独SQL用户进行隔离，隔离发生在数据库级别，无需依赖（或信任）应用级解决方案。

背景

拥有多租户数据库（多个租户之间共享的单个数据库）需要一些逻辑隔离，以确保一个租户无法访问其他租户的数据。一种常见的解决方案是在每个需要隔离的表中添加一个TenantID列，然后添加一些逻辑以确保一个租户无法看到其他租户的数据。（如果您想知道为什么我们不能只将TenantID列添加到“root”表并依赖子表的连接，请在此 StackOverflow线程中查看一些充分的理由）。

与其他数据库模式（例如为每个租户使用隔离数据库）相比，共享多租户数据库具有较低的数据库成本——正如您在此比较表中所见。但是，为所有租户使用单个数据库的主要限制是它无法扩展，因为您受到数据库/服务器限制的限制。这个问题的常见解决方案是使用共享数据库和隔离数据库之间的混合——它被称为数据库分片，基本上，这意味着根据分片标准（在我们的例子中将由TenantId分片标准）将您的数据分成不同的数据库——但是无需将每个租户保留在专用数据库中。

所以基本上，Catalog数据库（我们跟踪所有租户的地方）也应该跟踪每个Tenant的数据库位置：它应该有服务器名称（所以我们可以在多个服务器之间扩展），它应该有数据库名称，并且（非常重要）它应该有每个租户的SQL用户和密码。共享设计的一个重要方面是多个租户可以在同一台服务器上共享同一个数据库，因此租户之间具有强大的逻辑隔离非常重要，并且拥有单独的SQL用户和密码对于您将在下面看到的解决方案至关重要。

（上面的截图取自文章，Microsoft - Multi-tenant SaaS database tenancy patterns）。

在上面的屏幕截图中，每个数据库（目录、租户A-to-D、租户E-to-K等）应托管在不同的服务器上。但是，即使您开始/保持小规模，您仍然可以从这种架构中受益：您可以使用单个SQL服务器来为所有租户托管目录数据库和单个租户数据库。好处是您将在租户之间获得这种数据库级别的逻辑隔离（下面将详细介绍），而且将来您可以在不更改架构的情况下进行横向扩展。

创建Catalog 数据库

catalog 数据库包含所有租户的列表，以及他们所在的位置。

出于本文的目的，我将把[Password]列保留为纯文本，但最好对其进行加密和保护。

CREATE DATABASE [CatalogDB];
GO

USE [CatalogDB];

CREATE TABLE [Tenant] (
    [TenantID] [smallint] IDENTITY(1,1) NOT NULL 
       CONSTRAINT [PK_Tenant] PRIMARY KEY,
    [Subdomain] [nvarchar](256), -- how tenants are identified/resolved
    [Server] [nvarchar](256),
    [Database] [nvarchar](256),
    [UserId] [nvarchar](256),
    [Password] [nvarchar](256),
);

高级技巧（超出本文的范围）：catalog数据库可能是瓶颈（和单点故障），因此对于严肃的业务应用程序，对目录进行一些复制和缓存是个好主意。

关于分片方法的简要介绍

可以使用不同的方法在多个服务器之间分配租户。一种直观的方式就像“将前10个租户放在此服务器A中，然后仅在需要时提供新服务器B并将接下来的10个租户放在那里，等等”。

另一种方法是从几台服务器开始，然后在这些服务器上平均分配租户：假设您有3个服务器，分别称为A、B、C，您将Tenant1放入A，Tenant2放入B，Tenant3放入C，再将Tenant4放入A , Tenant5变成B等。所以基本上租户是按照(TenantId)%(NumberOfServers)来分配的。

如果您不想拥有单个目录（正如我之前所说，这既是瓶颈又是单点故障），您可以将目录分布在多个服务器上（就像租户的数据一样），只要您的请求可以被直接路由到正确的地方，这需要分片基于租户领域之类的东西。假设您有3个服务器A、B和C，在散列“mycustomer1.myapp.com”和取模3（因为我们有3个服务器）后，您得到数字0，因此您知道该客户应该存储在服务器A。然后在服务器A中，您不仅会找到“mycustomer1”和其他一些租户的租户数据，还会找到相应租户记录及其SQL用户名和密码的副本。所以没有单一的目录，而如果一个服务器出现故障，也不会影响到所有的租户。

创建共享多租户数据库

为了托管我们的前几个租户，我们将创建一个名为[TenantsA]的数据库。当我们变得更大时，我们可以将接下来的几个客户放入[TenantsB],[TenantsC]等中，它们应该都有相同的结构。

每个租户都有自己的SQL用户（如TenantId_1、TenantId_2等）来连接到他们的数据库，因此我们将创建一个函数，该函数CurrentTenantId()将在当前登录的SQL用户名为like 'TenantId_*'时返回TenantId。

CREATE DATABASE [TenantsA];
GO

USE [TenantsA];
GO

CREATE FUNCTION dbo.CurrentTenantId ()
    RETURNS smallint WITH SCHEMABINDING
AS BEGIN
    -- If user is not named like 'TenantId_*', then it's not a Tenant...
    IF SUBSTRING(SYSTEM_USER,1,9) <> 'TenantId_'
        RETURN NULL;
    RETURN CAST(SUBSTRING(SYSTEM_USER,10,9999) AS smallint);
END;
GO
GRANT EXECUTE ON [dbo].[CurrentTenantId] TO public;

然后我们创建将在租户之间共享的表（在我们的示例中为单个表）：

USE [TenantsA];
GO

CREATE TABLE [Person] (
  [PersonID] [int] NOT NULL IDENTITY(1,1)
      CONSTRAINT [PK_Person] PRIMARY KEY NONCLUSTERED, 
  [TenantID] [smallint] NOT NULL
      DEFAULT(dbo.CurrentTenantId()),
  [FullName] [nvarchar](256) NULL,
);

CREATE UNIQUE CLUSTERED INDEX [CIX_Person] ON [Person] ([TenantID], [PersonID]);

请注意，该[TenantID] 列有一个默认值，它使用该CurrentTenantId()函数，因此SaaS应用程序（在租户的上下文下运行）不需要显式指定TenantID。在本文后面，我们还将看到他们不能使用与自己的id不同的任何东西。

高级技巧（超出本文的范围）：多租户数据库也可以有一个[Tenant]表的副本，以及它自己的租户的记录副本，并且所有表（如[Person]）都可以从[TenantId]到[Tenant]表有一个外键，可能带有级联删除以允许轻松删除租户。

关于聚集索引的简短说明

如果你是一个细心的读者（并且对数据库有一点了解），你应该已经注意到上面的[Person]表有一个主键[PersonID]，但是它被定义为一个非聚集索引。

表的聚集索引（基本上描述了记录在磁盘中的物理排序方式）被明确定义为首先包含[TenantID]，然后是主键ID。通过使聚集索引以[TenantID]开头，这意味着任何过滤特定TenantID对象的查询都将非常快速地查找租户记录（如果它正在获取所有租户记录，或者如果它正在过滤某些附加条件）。

此设计基于这样的假设，即此表上的大多数查询都将包含作为过滤器的[TenantID]。这也是正确的，因为有这个安全谓词（如下）将确保所有租户始终按自己的查询表过滤TenantId。

虽然这可能不适用于所有可能的情况，但可以肯定的是，对于大多数多租户系统，大多数读取来自租户本身，它们始终会过滤自己的数据。

创建安全谓词函数

行级安全性允许我们根据任何标准过滤对行的访问。我们将创建一个谓词函数，它接受TenantId并决定（根据当前登录的SQL用户）用户是否有权访问TenantId。基本上，任何名为like "TenantId_X" (for any number X) 的SQL用户都只能访问查看/修改其[TenantId]=X中的记录。

USE [TenantsA];
GO

CREATE FUNCTION dbo.HasTenantAccess(@TenantId smallint)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    -- If user is named like 'TenantId_X' 
    -- it can only see/modify records where [TenantId]=X
    -- else it can see/modify all records
    RETURN SELECT 1 AS IsAllowed WHERE 
    dbo.CurrentTenantId() IS NULL
    OR @TenantId=dbo.CurrentTenantId();
GO

CREATE SECURITY POLICY dbo.TenantAccessPolicy;
GO

ALTER SECURITY POLICY dbo.TenantAccessPolicy ADD FILTER PREDICATE 
    dbo.HasTenantAccess([TenantId]) ON [dbo].[Person];

ALTER SECURITY POLICY dbo.TenantAccessPolicy ADD BLOCK PREDICATE 
    dbo.HasTenantAccess([TenantId]) ON [dbo].[Person];

过滤谓词（ADD FILTER PREDICATE）用于限制（过滤）读取操作，而块谓词（ADD BLOCK PREDICATE）用于限制（阻止）写入操作。

创建DDL触发器以强制执行安全谓词

安全策略TenantAccessPolicy可用于对任意数量的表应用过滤器。为确保始终过滤具有该[TenantId]列的任何表，我们可以创建一个DDL触发器，将安全谓词应用于任何新（或修改）表。下面的这个触发器捕获任何CREATE TABLE或 ALTER TABLE。

CREATE TRIGGER [trApplyTenantAccessPolicy] ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS 
AS
BEGIN
  SET NOCOUNT ON
  SET ANSI_PADDING ON
  DECLARE @data XML
  DECLARE @ObjectName varchar(500)
  DECLARE @SchemaName varchar(500)
  DECLARE @ObjectType varchar(500)
  DECLARE @EventType varchar(500)

  SET @data = EVENTDATA()
  SET @ObjectName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(500)')
  SET @SchemaName = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(500)')
  SET @ObjectType = @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(500)')
  SET @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(500)')
  if (@ObjectType='TABLE' AND (@EventType='CREATE_TABLE' OR @EventType='ALTER_TABLE')) BEGIN
    declare @object_id int
    SET @object_id=OBJECT_ID(@SchemaName+'.'+@ObjectName);
    IF EXISTS (SELECT * FROM sys.columns WHERE  object_id = @object_id AND name = 'TenantID') 
    AND NOT EXISTS (SELECT * FROM sys.security_predicates WHERE target_object_id=@object_id 
    AND predicate_definition='([dbo].[HasTenantAccess]([TenantId]))') BEGIN
        PRINT 'Applying TenantAccessPolicy/HasTenantAccess into '
            + @SchemaName+'.'+@ObjectName + '...'
        EXEC('ALTER SECURITY POLICY dbo.TenantAccessPolicy
            ADD FILTER PREDICATE dbo.HasTenantAccess([TenantId])
            ON [' + @SchemaName+'].['+@ObjectName+']');
        EXEC('ALTER SECURITY POLICY dbo.TenantAccessPolicy
            ADD BLOCK PREDICATE dbo.HasTenantAccess([TenantId])
            ON [' + @SchemaName+'].['+@ObjectName+']');
    END
  END
END

创建租户

每个租户基本上是[Tenant]表中的一条新记录，但它还需要创建一个单独的SQL用户（最好为每个租户设置不同的密码，这样如果一个租户的密码被泄露，攻击者就不应该能够联系到您的目录或其他租户）。

USE [CatalogDB];
INSERT INTO [Tenant] ([Subdomain]) VALUES ('customer1.myapp.com');
INSERT INTO [Tenant] ([Subdomain]) VALUES ('customer2.myapp.com');

-- For making this article simple, I'll run the next commands manually
-- but in a real production scenario you'll want to run this as dynamic SQL
-- based on the generated identity (SCOPE_IDENTITY())

-- Let's say the inserts above created TenantID=1 and TenantID=2
CREATE LOGIN [Tenant_1] WITH PASSWORD = '%UltraStrongPassword#!';
CREATE LOGIN [Tenant_2] WITH PASSWORD = '%aDifferentPasswordForEachTenantRight#!';

USE [TenantsA];

CREATE USER [TenantId_1] FOR LOGIN [TenantId_1];
CREATE USER [TenantId_2] FOR LOGIN [TenantId_2];
EXEC sp_addrolemember 'db_datareader', 'TenantId_1'
EXEC sp_addrolemember 'db_datawriter', 'TenantId_1'
EXEC sp_addrolemember 'db_datareader', 'TenantId_2'
EXEC sp_addrolemember 'db_datawriter', 'TenantId_2'

USE [CatalogDB]

UPDATE [Tenant] SET
  [Server]='(local)',
  [Database]='TenantsA',
  [UserId]='TenantId_1',
  [Password]='%UltraStrongPassword#!'
  WHERE [TenantID]=1;

UPDATE [Tenant] SET
  [Server]='(local)',
  [Database]='TenantsA',
  [UserId]='TenantId_1',
  [Password]='%aDifferentPasswordForEachTenantRight#!' -- Right?!
  WHERE [TenantID]=2;

最终测试

让我们创建一个新表以确保新表将自动应用安全策略（过滤器和阻止谓词）。

USE [TenantsA];
GO

CREATE TABLE [Order] (
  [OrderID] [int] NOT NULL IDENTITY(1,1)
      CONSTRAINT [PK_Order] PRIMARY KEY NONCLUSTERED, 
  [TenantID] [smallint] NOT NULL
      DEFAULT(dbo.CurrentTenantId()),
  [Amount] [decimal](15,2) NULL,
);

CREATE UNIQUE CLUSTERED INDEX [CIX_Order] ON [Order] ([TenantID], [OrderID]);

让我们为每个租户添加一些记录

Invoke-Sqlcmd -Database "TenantsA" -Username "TenantId_1" 
-Password "%UltraStrongPassword#!" -Query @"
    INSERT INTO [Person] (FullName) VALUES ('Rick Drizin');
    INSERT INTO [Person] (FullName) VALUES ('Mickey Mouse');
    INSERT INTO [Order] (Amount) VALUES (100);
"@

Invoke-Sqlcmd -Database "TenantsA" -Username "TenantId_2" 
-Password "%aDifferentPasswordForEachTenantRight#!" -Query @"
    INSERT INTO [Person] (FullName) VALUES ('Donald Duck');
    INSERT INTO [Order] (Amount) VALUES (900);
"@

现在让我们检查记录是否自动获取了租户ID，并且结果是根据当前租户（当前用户）过滤的：

最后，让我们检查一下租户不能显式添加或修改来自其他租户的记录：

不是很酷吗？我希望你和我写这篇文章时一样喜欢这篇文章！

附录 1 - SUSER_ID()和字符串操作

出于本文的目的，我决定采用直观简单的设计，即所有共享表都包含该[TenantID]列，所有SQL用户都命名为like 'Tenant_*'，为了提取当前租户的ID，有这个CurrentTenantId()函数根据当前SQL用户提取ID。

这种设计的一个问题是该CurrentTenantId()函数执行字符串操作（子字符串和强制转换），效率不高。需要考虑的重要一点是该函数是不确定的（我们可以运行SELECT OBJECTPROPERTY(OBJECT_ID('[dbo].[CurrentTenantId]'), 'IsDeterministic') 验证），这意味着该函数被执行多次（例如，对于每一行测试/返回/加入/等）。这是因为SYSTEM_USER（我们用来获取当前用户）也是不确定的。

我知道这种字符串操作对整个过程没有太大影响，因为这是一个内存操作（不涉及I/O）。但是如果我们想避免这种开销，我们可以做一些小的改变：

每个SQL用户在表sys.server_principals中都有一条记录，并且有一个唯一的id principal_id。我们可以存储这个principal_id，而不是存储TenantID 。因此，在像[Person]和[Order]这样的表中没有[TenantID]列，而是有一个名为[TenantPrincipalID]的列，这是要在谓词中测试的列。

此列的默认值为SUSER_ID()，它返回这个principal_id。

HasTenantAccess() 函数也将使用SUSER_ID，并且会是这样的：

CREATE FUNCTION dbo.CurrentTenantId ()
   RETURNS smallint WITH SCHEMABINDING 
AS BEGIN

  -- If user is dbo, then it's not a Tenant... 
  IF IS_MEMBER('db_owner')=1 RETURN NULL;

  RETURN SUSER_ID();
END; 
GO 

GRANT EXECUTE ON [dbo].[CurrentTenantId] TO public;

请注意， SUSER_ID()也是非确定性的（类似于SYSTEM_USER和所有其他安全功能），因此CurrentTenantId()仍会为每一行调用。

此SUSER_ID解决方案的一个缺点是，当与非租户用户连接时（例如，用于故障排除或维护），过滤租户会变得有点棘手：

USE [TenantsA];

-- instead of this 
-- SELECT * FROM [Order] WHERE [TenantID]=2; 

 -- we would need this
SELECT * FROM [Order] WHERE [TenantPrincipalID]=SUSER_ID('TenantId_2');

另一个缺点是，如果我们移动租户（跨服务器），我们将不得不相应地更新[TenantPrincipalID]，因为每个服务器都会为其SQL用户提供不同的ID。

更糟糕的是：如果我们使用新服务器中的备份为某些租户恢复数据库，我们会拥有数据库[TenantPrincipalID]，但我们不知道每个租户各自[TenantId]的数据库。

因此，在我们创建新租户后立即将其存储[PrincipalID]在Catalog中非常重要：

USE [TenantsA];
SET @PrincipalID=SUSER_ID('TenantId_2')

USE [CatalogDB];
UPDATE [Tenant] SET [PrincipalID]=@PrincipalID WHERE [TenantID]=2

附录 2 - SESSION_CONTEXT和APP_NAME

还有许多其他关于行级安全性的文章使用SESSION_CONTEXT（或临时表，它们类似但在我看来更容易维护）来设置当前ID。这个解决方案的问题是它需要在数据层中进行一些调整（当连接打开时，您需要设置上下文）。这不仅在每次调用时增加了到数据库的额外往返，而且还增加了出现缺陷的可能性（如果代码没有正确设置租户，您会看到未过滤的结果吗？）。最后，使用SESSION_CONTEXT也会增加一些开销（如果不是字符串操作，至少你会得到I/O开销，这更糟）。

许多文章中使用的另一个流行的解决方案建议使用 APP_NAME(这是您可以在连接字符串中传递的东西) 而不是SYSTEM_USER或SUSER()。该APP_NAME函数不会增加I/O开销（就像SESSION_CONTEXT那样），但它仍然是一个非确定性函数，它仍然需要将varchar强制转换为int。在最好的情况下，我们会避免使用SUBSTRING，但如果我们只是在没有“TenantId_”前缀（如“CREATE LOGIN [1]...”）的情况下命名租户用户，也可以避免这种情况。

如果出于任何原因您希望（或必须）为所有租户共享一个SQL用户，那么使用 APP_NAME可能是一个很好的解决方法。在我看来，拥有个人用户会增加一层额外的安全性，如果每个租户都有自己的用户，那么使用APP_NAME代替SYSTEM_USER/SUSER没有意义，因为前者是由数据层提供的，而后者是由SQL本身提供的，所以更安全。

总而言之，拥有单个用户的租户允许我们直接在数据库层进行隔离，而无需信任开发人员或代码。

https://www.codeproject.com/Articles/5318079/Sharded-Multi-Tenant-Database-using-SQL-Server-Row

寒冰屋

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
使用SQL Server行级安全性的分片多租户数据库

本文展示了使用分片SQL数据库u的多租户SaaS的数据库级可扩展架构的具体示例代码。它展示了Catalog数据库的创建、共享多租户数据库的创建、如何设计具有TenantId列的表（以及如何设计聚集索引以获得最佳性能）、如何使用行级安全谓词函数自动过滤基于SQL用户的记录，以及如何创建DDL触发器以强制共享表将始终应用安全谓词，从而确保开发人员的错误永远不会将租户数据暴露给其他租户。
复制链接

扫一扫

专栏目录