DECRYPTBYKEY (Transact-SQL)
適用対象: 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
関連項目
ENCRYPTBYKEY (Transact-SQL)
CREATE SYMMETRIC KEY (Transact-SQL)
ALTER SYMMETRIC KEY (Transact-SQL)
DROP SYMMETRIC KEY (Transact-SQL)
暗号化階層
暗号化アルゴリズムの選択