通过

加密数据列

  • 项目

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric SQL 数据库

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics

本文介绍如何使用 Transact-SQL，在 SQL Server 中通过对称加密对数据列进行加密。 这有时称为列级加密或单元级加密。

本文中的 Transact-SQL 代码示例使用 AdventureWorks2022 或 AdventureWorksDW2022 示例数据库，可从 Microsoft SQL Server 示例和社区项目主页下载它。

安全性

权限

下面的权限是执行以下步骤所必需的：

  • 数据库的 CONTROL 权限。
  • 数据库的 CREATE CERTIFICATE 权限。 只有 Windows 登录名、SQL Server 登录名和应用程序角色才能拥有证书。 其他组和角色不能拥有证书。
  • 表的 ALTER 权限。
  • 针对密钥的某些权限，并且必须未被拒绝授予 VIEW DEFINITION 权限。

创建数据库主密钥

要使用下面的示例，必须具有数据库主密钥。 如果数据库尚不具有数据库主密钥，请创建一个。 若要创建一个主密钥，请连接到数据库，然后运行以下脚本。 请确保使用复杂密码。

将以下示例复制并粘贴到连接到 AdventureWorks 示例数据库的查询窗口中。 选择“执行”。

CREATE MASTER KEY ENCRYPTION BY   
PASSWORD = '<complex password>';

请始终备份数据库主密钥。 有关数据库主密钥的详细信息，请参阅 CREATE MASTER KEY (Transact-SQL)。

示例：通过对称加密和验证器进行加密

  1. 在 “对象资源管理器” 中，连接到 数据库引擎的实例。

  2. 在标准栏上，选择“新建查询” 。

  3. 将以下示例复制并粘贴到连接到 AdventureWorks 示例数据库的查询窗口中。 选择“执行”。

    CREATE CERTIFICATE Sales09  
   WITH SUBJECT = 'Customer Credit Card Numbers';  
GO  

CREATE SYMMETRIC KEY CreditCards_Key11  
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE Sales09;  
GO  

-- Create a column in which to store the encrypted data.  
ALTER TABLE Sales.CreditCard   
    ADD CardNumber_Encrypted varbinary(160);   
GO  

-- Open the symmetric key with which to encrypt the data.  
OPEN SYMMETRIC KEY CreditCards_Key11  
   DECRYPTION BY CERTIFICATE Sales09;  

-- Encrypt the value in column CardNumber using the  
-- symmetric key CreditCards_Key11.  
-- Save the result in column CardNumber_Encrypted.    
UPDATE Sales.CreditCard  
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')  
    , CardNumber, 1, HASHBYTES('SHA2_256', CONVERT( varbinary  
    , CreditCardID)));  
GO  

-- Verify the encryption.  
-- First, open the symmetric key with which to decrypt the data.  

OPEN SYMMETRIC KEY CreditCards_Key11  
   DECRYPTION BY CERTIFICATE Sales09;  
GO  

-- Now list the original card number, the encrypted card number,  
-- and the decrypted ciphertext. If the decryption worked,  
-- the original number will match the decrypted number.  

SELECT CardNumber, CardNumber_Encrypted   
    AS 'Encrypted card number', CONVERT(nvarchar,  
    DecryptByKey(CardNumber_Encrypted, 1 ,   
    HASHBYTES('SHA2_256', CONVERT(varbinary, CreditCardID))))  
    AS 'Decrypted card number' FROM Sales.CreditCard;  
GO

通过简单对称加密进行加密

  1. 在 “对象资源管理器” 中，连接到 数据库引擎的实例。

  2. 在标准栏上，选择“新建查询” 。

  3. 将以下示例复制并粘贴到连接到 AdventureWorks 示例数据库的查询窗口中。 选择“执行”。

     CREATE CERTIFICATE HumanResources037  
   WITH SUBJECT = 'Employee Social Security Numbers';  
GO  

CREATE SYMMETRIC KEY SSN_Key_01  
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE HumanResources037;  
GO  

USE [AdventureWorks2022];  
GO  

-- Create a column in which to store the encrypted data.  
ALTER TABLE HumanResources.Employee  
    ADD EncryptedNationalIDNumber varbinary(128);   
GO  

-- Open the symmetric key with which to encrypt the data.  
OPEN SYMMETRIC KEY SSN_Key_01  
   DECRYPTION BY CERTIFICATE HumanResources037;  

-- Encrypt the value in column NationalIDNumber with symmetric   
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.  
UPDATE HumanResources.Employee  
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);  
GO  

-- Verify the encryption.  
-- First, open the symmetric key with which to decrypt the data.  
OPEN SYMMETRIC KEY SSN_Key_01  
   DECRYPTION BY CERTIFICATE HumanResources037;  
GO  

-- Now list the original ID, the encrypted ID, and the   
-- decrypted ciphertext. If the decryption worked, the original  
-- and the decrypted ID will match.  
SELECT NationalIDNumber, EncryptedNationalIDNumber   
    AS 'Encrypted ID Number',  
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))   
    AS 'Decrypted ID Number'  
    FROM HumanResources.Employee;  
GO

后续步骤