加密数据列

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

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

本文中的 Transact-SQL 代码示例使用 AdventureWorks2022AdventureWorksDW2022 示例数据库,可从 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  
    

后续步骤