分享方式：

加密資料行

  • 文章

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics

本文描述如何使用 Transact-SQL，在 SQL Server 中透過對稱加密來加密資料行。 這有時候稱為資料行層級加密或資料格層級加密。

本文 Transact-SQL 程式碼範例使用 AdventureWorks2022 或 AdventureWorksDW2022 範例資料庫，從 Microsoft SQL Server Samples 和 Community Projects (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

下一步