ENCRYPTBYKEY (Transact-SQL)

  • [アーティクル]

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics

対称キーを使用してデータを暗号化します。

Transact-SQL 構文表記規則

注意

この構文は、Azure Synapse Analytics のサーバーレス SQL プールでサポートされていません。

構文

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

Note

SQL Server 2014 (12.x) 以前のバージョンの Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。

引数

key_GUID
cleartext. uniqueidentifier の暗号化に使用する、キーの GUID です。

'cleartext'
キーを使用して暗号化するデータです。

@cleartext
キーで暗号化されるデータを含む nvarchar、char、varcharbinary、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 では対称キーが使用されます。 このキーは開いている必要があります。 対称キーが現在のセッションで既に開いている場合、クエリのコンテキストで再度開く必要はありません。

認証子を使用すると、暗号化されたフィールド全体が外部から置き換えられるのを防ぐことができます。 たとえば、次の給与データのテーブルについて考えてみます。

従業員 ID (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 に渡される必要があります。 2 つの認証子が一致しない場合、暗号化は解除できません。 これは、値が暗号化の後で移動されたことを示します。 一意で不変の値を含む列を認証子として使用することをお勧めします。 認証子の値が変更された場合、データへのアクセスが失われることがあります。

対称キーの暗号化と暗号化解除は比較的高速なので、データが大きい場合に適しています。

重要

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)