分享方式:


加密資料行

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

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

本文 Transact-SQL 程式碼範例使用 AdventureWorks2022AdventureWorksDW2022 範例資料庫,從 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  
    

下一步