DECRYPTBYKEY (Transact-SQL)

[アーティクル]
04/26/2023

適用対象:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

この関数は対称キーを使用してデータを復号します。

注意

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

Azure Synapse Analytics の専用 SQL プールでは、結果セットのキャッシュを DECRYPTBYKEY と組み合わせて使用しないでください。この暗号関数を使う必要がある場合は、実行時に結果セットのキャッシュを (セッションレベル、データベースレベルのいずれかで) 無効にするようにしてください。

構文

  
DecryptByKey ( { 'ciphertext' | @ciphertext }   
    [ , add_authenticator, { authenticator | @authenticator } ] )

Note

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

引数

ciphertext
キーで暗号化されたデータを含む、型 varbinary の変数。

@ciphertext
キーで暗号化されたデータを含む、型 varbinary の変数。

add_authenticator
元の暗号化プロセスにプレーンテキストと共に認証子が含まれ、認証子が暗号化されたかどうかを示します。データ暗号化プロセス中、ENCRYPTBYKEY (Transact-SQL) に渡された値に一致する必要があります。 add_authenticator には、int データ型が与えられます。

authenticator
認証子の生成の基礎として使用されるデータ。 ENCRYPTBYKEY (Transact-SQL) に与えられた値と一致する必要があります。 authenticator には、sysname データ型が与えられます。

@authenticator
認証子の生成元となるデータを含む変数。 ENCRYPTBYKEY (Transact-SQL) に与えられた値と一致する必要があります。 @authenticator には、sysname データ型が与えられます。

戻り値の型

最大サイズが 8,000 バイトの varbinary。 DECRYPTBYKEY は、データの暗号化に使用する対称キーが開いていない場合か、ciphertext が NULL の場合、NULL を返します。

注釈

DECRYPTBYKEY は対称キーを使用します。データベースでは、この対称キーを既に開いている必要があります。 DECRYPTBYKEY では、複数のキーを同時に開いておくことができます。暗号化テキストの復号する直前にキーを開く必要はありません。

対称暗号化/復号は通常、比較的すぐに動作します。大量のデータが含まれる操作で効率的に動作します。

DECRYPTBYKEY 呼び出しは、暗号化キーを含むデータベースのコンテキストで行う必要があります。これを保証するには、データベース内に存在するオブジェクト (ビュー、ストアドプロシージャ、関数など) から DECRYPTBYKEY を呼び出します。

アクセス許可

対称キーは現在のセッションで開かれている必要があります。詳細については、「OPEN SYMMETRIC KEY (Transact-SQL)」を参照してください。

例

A. 対称キーを使用して暗号化解除する

この例では、対称キーで暗号化テキストを復号します。

-- 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, EncryptedNationalID   
    AS 'Encrypted ID Number',  
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalID))   
    AS 'Decrypted ID Number'  
    FROM HumanResources.Employee;  
GO

B. 対称キーと認証ハッシュを使用して暗号化解除する

この例では、認証子と共に、最初に暗号化されたデータを復号します。

-- 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('SHA1', CONVERT(varbinary, CreditCardID))))   
    AS 'Decrypted card number' FROM Sales.CreditCard;  
GO

C. キーを持つデータベースのコンテキストではないときに復号化に失敗する

次の例は、キーが含まれるデータベースのコンテキストで DECRYPTBYKEY を実行する必要があること示しています。 DECRYPTBYKEY がマスターデータベースで実行されると、行は復号化されず、結果は NULL になります。

-- Create the database
CREATE DATABASE TestingDecryptByKey
GO

USE [TestingDecryptByKey]

-- Create the table and view
CREATE TABLE TestingDecryptByKey.dbo.Test(val VARBINARY(8000) NOT NULL);
GO
CREATE VIEW dbo.TestView AS SELECT CAST(DecryptByKey(val) AS VARCHAR(30)) AS DecryptedVal FROM TestingDecryptByKey.dbo.Test;
GO

-- Create the key, and certificate
USE TestingDecryptByKey;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ItIsreallyLong1AndSecured!Passsword#';
CREATE CERTIFICATE TestEncryptionCertificate WITH SUBJECT = 'TestEncryption';
CREATE SYMMETRIC KEY TestEncryptSymmmetricKey WITH ALGORITHM = AES_256, IDENTITY_VALUE = 'It is place for test',
KEY_SOURCE = 'It is source for test' ENCRYPTION BY CERTIFICATE TestEncryptionCertificate;

-- Insert rows into the table
DECLARE @var VARBINARY(8000), @Val VARCHAR(30);
SELECT @Val = '000-123-4567';
OPEN SYMMETRIC KEY TestEncryptSymmmetricKey DECRYPTION BY CERTIFICATE TestEncryptionCertificate;
SELECT @var = EncryptByKey(Key_GUID('TestEncryptSymmmetricKey'), @Val);
SELECT CAST(DecryptByKey(@var) AS VARCHAR(30)), @Val;
INSERT INTO dbo.Test VALUES(@var);
GO

-- Switch to master
USE [Master];
GO

-- Results show the date inserted
SELECT DecryptedVal FROM TestingDecryptByKey.dbo.TestView;

-- Results are NULL because we are not in the context of the TestingDecryptByKey Database
SELECT CAST(DecryptByKey(val) AS VARCHAR(30)) AS DecryptedVal FROM TestingDecryptByKey.dbo.Test;
GO

-- Clean up resources
USE TestingDecryptByKey;

DROP SYMMETRIC KEY TestEncryptSymmmetricKey REMOVE PROVIDER KEY;
DROP CERTIFICATE TestEncryptionCertificate;

Use [Master]
DROP DATABASE TestingDecryptByKey;
GO