ENCRYPTBYKEY (Transact-SQL)

發行項
12/22/2023

適用於：SQL Server Azure SQL Database Azure SQL 受控執行個體 Azure Synapse Analytics

使用對稱金鑰加密資料。

注意

Azure Synapse Analytics 的無伺服器 SQL 集區不支援此語法。

Syntax

  
EncryptByKey ( key_GUID , { 'cleartext' | @cleartext }  
    [, { add_authenticator | @add_authenticator }  
     , { authenticator | @authenticator } ] )

注意

若要檢視 SQL Server 2014 （12.x）和舊版的 Transact-SQL 語法，請參閱舊版檔。

引數

key_GUID
為用於加密 cleartext 之金鑰的 GUID。 uniqueidentifier。

'cleartext'
這是要利用金鑰加密的資料。

@cleartext
為類型是 nvarchar、char、varchar、binary、varbinary 或 nchar 的變數，其中包含要以金鑰加密的資料。

add_authenticator
指出驗證器是否要與 cleartext 一起加密。在使用驗證器時，必須是 1。 int.

@add_authenticator
指出驗證器是否要與 cleartext 一起加密。在使用驗證器時，必須是 1。 int.

authenticator
這是要衍生驗證器的資料。 sysname.

@authenticator
這是含有要衍生驗證器之資料的變數。

傳回型別

varbinary，大小上限為 8,000 位元組。

如果索引鍵不是開啟、不存在、或索引鍵是已被取代的 RC4 索引鍵，且資料庫不在相容性層級 110 以上的話，則傳回 NULL。

如果 cleartext 值為 NULL，則傳回 NULL。

備註

EncryptByKey 會使用對稱金鑰。因此必須開啟此金鑰。如果目前的工作階段已經開啟對稱金鑰，即無須在查詢內容中再開啟一次。

驗證器可以幫助您防止代換已加密之欄位的全值。例如，請參考下面的薪資資料表。

Employee_ID	Standard_Title	Base_Pay
345	影印室助手	Fskj%7^edhn00
697	財務長	M0x8900f56543
694	排版設計總監	Cvc97824%^34f

惡意使用者不必破解加密，就可以從儲存加密文字的內容，推斷出大量資訊。由於財務長的薪水比影印室助理高，因而可推論加密為 M0x8900f56543 的值必然大於加密為 Fskj%7^edhn00 的值。若是如此，則只要是對資料表具有 ALTER 權限的使用者，都可以將影印室助理的 Base_Pay 欄位資料換成財務長的 Base_Pay 欄位資料，為影印室助理加薪。這種全值替換攻擊完全跳過加密。

若要阻止全值替換攻擊，可以在對純文字進行加密之前，先加入內容資訊。這個內容資訊是用來確認純文字資料沒有被移動。

如果驗證器參數是在資料加密時指定，若要利用 DecryptByKey 將資料解密，必須使用同一個驗證器。在加密時，驗證器的雜湊會與純文字一起加密。在解密時，必須將同一個驗證器傳遞給 DecryptByKey。如果兩者不相符，解密會失敗。這表示值在加密後有被移動過。我們建議您使用包含唯一且不變之值的資料行當做驗證器。如果驗證器的值變更，您可能會遺失資料的存取權。

對稱加密和解密相當快速，而且很適合處理大量資料。

重要

使用 SQL Server 加密函數搭配 ANSI_PADDING OFF 設定一起使用，可能會因為隱含轉換而造成資料遺失。如需 ANSI_PADDING 的詳細資訊，請參閱 SET ANSI_PADDING (Transact-SQL)。

範例

下列範例所示範的功能，必須倚賴如何：加密資料行中所建立的金鑰和憑證。

A. 使用對稱金鑰加密字串

下列範例會在 Employee 資料表加入一個資料行，並將資料行 NationalIDNumber 中所儲存的社會保險號碼值加密。

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

B. 加密記錄與驗證值

USE AdventureWorks2022;  
  
-- Create a column in which to store the encrypted data.  
ALTER TABLE Sales.CreditCard   
    ADD CardNumber_Encrypted varbinary(128);   
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 with symmetric   
-- key CreditCards_Key11.  
-- Save the result in column CardNumber_Encrypted.    
UPDATE Sales.CreditCard  
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11'),   
    CardNumber, 1, CONVERT( varbinary, CreditCardID) );  
GO

另請參閱

DECRYPTBYKEY (Transact-SQL)
CREATE SYMMETRIC KEY (Transact-SQL)
ALTER SYMMETRIC KEY (Transact-SQL)
DROP SYMMETRIC KEY (Transact-SQL)
加密階層
 HASHBYTES (Transact-SQL)