教程:在使用随机加密且已启用 enclave 的列上创建和使用索引

适用于:Windows Azure SQL 数据库上的 SQL Server 2019 (15.x) 及更高版本

本教程将讲解如何对启用了安全区域的列创建和使用索引,并使用带安全区域的 Always Encrypted所支持的随机加密。 它将显示:

  • 如何在有权访问保护列的密钥(列主密钥和列加密密钥)时创建索引。
  • 如何在无权访问保护列的密钥时创建索引。

先决条件

安装最新版本的 SQL Server Management Studio (SSMS)。

在执行本教程中的以下步骤之前,请确保已完成开始使用具有安全 enclave 的 Always Encrypted 教程之一。

步骤 1:在数据库中启用加速数据库恢复 (ADR)

注意

此步骤仅适用于 SQL Server。 如果使用 Azure SQL 数据库或 Azure SQL 托管实例,请跳过此步骤。 ADR 始终在 Azure SQL 数据库和 Azure SQL 托管实例中启用。

Microsoft 强烈建议,先在数据库中启用 ADR,再在使用随机加密且已启用 enclave 的列上创建首个索引。 请参阅含安全 enclave 的 Always Encrypted 中的数据库恢复部分。

  1. 关闭在上一教程中使用的任何 SSMS 实例。 关闭 SSMS 时,将会关闭你已打开的数据库连接,这是启用 ADR 的必需操作。

  2. 打开 SSMS 的新实例,并以 sysadmin 身份连接到你的 SQL Server 实例,不要为数据库连接启用 Always Encrypted。

    1. 启动 SSMS。
    2. 在“连接到服务器”对话框中,指定服务器名称,选择身份验证方法,并指定凭据。
    3. 选择“选项 >>”,并选择“Always Encrypted”选项卡。
    4. 务必取消选中“启用 Always Encrypted (列加密)”复选框。
    5. 选择“连接”。
  3. 打开新查询窗口,并执行以下语句来启用 ADR。

    ALTER DATABASE ContosoHR SET ACCELERATED_DATABASE_RECOVERY = ON;
    

步骤 2:在不使用角色分隔的情况下创建和测试索引

这一步将在加密列上创建和测试索引。 你将是一个同时具有以下两个角色的用户:管理数据库的 DBA 和有权访问保护数据的密钥的数据所有者。

  1. 打开新 SSMS 实例,并连接到已为数据库连接启用 Always Encrypted 的 SQL Server 实例。

    1. 启动新 SSMS 实例。
    2. 在“连接到服务器”对话框中,指定服务器名称,选择身份验证方法,并指定凭据。
    3. 选择“选项 >>”,并选择“Always Encrypted”选项卡。
    4. 选中“启用 Always Encrypted(列加密)”和“启用 Secure Enclaves”复选框
    5. 如果要对数据库使用证明,请选择 enclave 证明协议的值,该值代表你使用的证明服务(主机保护者服务或 Microsoft Azure 证明),并且填写 enclave 证明 URL。 否则,请选择“无”。
    6. 选择“连接”。
    7. 如果系统提示启用 Always Encrypted 查询的参数化,请选择“启用”。
  2. 如果系统没有提示启用 Always Encrypted 参数化,请验证它是否已启用。

    1. 在 SSMS 的主菜单中,选择“工具”。
    2. 选择选项...。
    3. 导航到 查询执行>SQL Server高级。
    4. 务必选中“启用 Always Encrypted 参数化”。
    5. 选择“确定”。
  3. 打开查询窗口,并执行下面的语句,以加密“Employees”表中的“LastName”列。 后面的步骤将介绍如何在此列上创建并使用索引。

    ALTER TABLE [HR].[Employees]
    ALTER COLUMN [LastName] [nvarchar](50) COLLATE Latin1_General_BIN2 
    ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL;
    GO   
    ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
    GO
    
  4. 在“LastName”列上创建索引。 由于你是在启用了 Always Encrypted 的情况下连接到数据库,因此 SSMS 内部的客户端驱动程序会透明地将 CEK1(列加密密钥,用于保护 LastName 列)提供给 enclave,而创建该索引需要此密钥。

    CREATE INDEX IX_LastName ON [HR].[Employees] ([LastName])
    INCLUDE ([EmployeeID], [FirstName], [SSN], [Salary]);
    GO
    
  5. 对“LastName”列运行丰富查询,并验证 SQL Server 是否在执行查询时使用索引。

    1. 在同一或新查询窗口中,确保工具栏上的“包括实时查询统计信息”按钮处于启用状态。

    2. 执行下面的查询。

      DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%';
      SELECT * FROM [HR].[Employees] WHERE [LastName] LIKE @LastNamePrefix;
      GO
      
    3. 在“实时查询统计信息”选项卡中,观察查询是否使用索引。

步骤 3:在使用角色分隔的情况下创建索引

在这一步中,你将假装为两个不同的用户,在加密列上创建索引。 一个用户是需要创建索引但无权访问密钥的 DBA。 另一个用户是有权访问密钥的数据所有者。

  1. 使用未启用 Always Encrypted 的 SSMS 实例,执行以下语句以删除 LastName 列上的索引。

    DROP INDEX IX_LastName ON [HR].[Employees]; 
    GO
    
  2. 以数据所有者(或有权访问密钥的应用程序)身份,使用 CEK1 填充 enclave 内的缓存。

    注意

    除非你在步骤 2:在不使用角色分隔的情况下创建和测试索引之后重启了 SQL Server 实例,否则此步骤是多余的,因为 CEK1 已存在于缓存中。 之所以添加它是为了展示数据所有者如何向 enclave 提供密钥(如果 enclave 中尚无的话)。

    1. 在已启用 Always Encrypted 的 SSMS 实例中,在查询窗口中执行下面的语句。 此语句将所有已启用 enclave 的列加密密钥都发送到 enclave。 有关详细信息,请参阅 sp_enclave_send_keys。

      EXEC sp_enclave_send_keys;
      GO
      
    2. 作为执行上述存储过程的替代方法,可以运行一个针对 LastName 列使用 enclave 的 DML 查询。 这只会使用 CEK1 填充 enclave。

      DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%';
      SELECT * FROM [HR].[Employees] WHERE [LastName] LIKE @LastNamePrefix;
      GO
      
  3. 以 DBA 身份创建索引。

    1. 在未启用 Always Encrypted 的 SSMS 实例中,在查询窗口中执行下面的语句。

      CREATE INDEX IX_LastName ON [HR].[Employees] ([LastName])
      INCLUDE ([EmployeeID], [FirstName], [SSN], [Salary]);
      GO
      
  4. 以数据所有者身份对“LastName”列运行丰富查询,并验证 SQL Server 是否在执行查询时使用索引。

    1. 在已启用 Always Encrypted 的 SSMS 实例中,选择现有查询窗口或打开新查询窗口,并确保工具栏上的“包括实时查询统计信息”按钮处于启用状态。

    2. 执行下面的查询。

      DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%';
      SELECT * FROM [HR].[Employees] WHERE [LastName] LIKE @LastNamePrefix;
      GO
      
    3. 在“实时查询统计信息”选项卡中,观察查询是否使用索引。