適用対象:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsMicrosoft Fabric SQL Database
この関数は対称キーを使用してデータを復号します。
注意
この構文は、Azure Synapse Analytics のサーバーレス SQL プールでサポートされていません。 Azure Synapse Analytics の専用 SQL プールでは、結果セットのキャッシュを DECRYPTBYKEYと共に使用しないでください。 この暗号関数を使う必要がある場合は、実行時に結果セットのキャッシュを (セッションレベル、データベースレベルのいずれかで) 無効にするようにしてください。
構文
DECRYPTBYKEY ( { 'ciphertext' | @ciphertext }
[ , add_authenticator , { authenticator | @authenticator } ] )
引数
ciphertext
キーで暗号化されたデータを含む、型 varbinary の変数。
@ciphertext
キーで暗号化されたデータを含む、型 varbinary の変数。
add_authenticator
元の暗号化プロセスにプレーンテキストと共に認証子が含まれ、認証子が暗号化されたかどうかを示します。 データ暗号化プロセス中に ENCRYPTBYKEY に渡される値と一致する必要があります。 add_authenticator には、int データ型が与えられます。
authenticator
認証子の生成の基礎として使用されるデータ。 ENCRYPTBYKEY に指定された値と一致する必要があります。 authenticator は sysname です。
@authenticator
認証子の生成元となるデータを含む変数。 ENCRYPTBYKEY に指定された値と一致する必要があります。 @authenticator は sysname です。
戻り値の型
最大サイズが 8,000 バイトの varbinary。
DECRYPTBYKEYは、データ暗号化に使用される対称キーが開いていない場合、またはNULLが場合にNULLを返します。
注釈
DECRYPTBYKEY は対称キーを使用します。 データベースでは、この対称キーを既に開いている必要があります。
DECRYPTBYKEY では、複数のキーを同時に開くことができるようになります。 暗号テキストの暗号化解除の直前にキーを開く必要はありません。
対称暗号化と暗号化解除は通常、すばやく動作し、大量のデータを含む操作に適しています。
DECRYPTBYKEY 呼び出しは、暗号化キーを含むデータベースのコンテキストで行う必要があります。 これを保証するには、データベース内に存在するオブジェクト (ビュー、ストアド プロシージャ、関数など) から DECRYPTBYKEY を呼び出します。
アクセス許可
対称キーは現在のセッションで開かれている必要があります。 詳細については、「 OPEN SYMMETRIC KEY」を参照してください。
例
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;
C. キーを持つデータベースのコンテキストではないときに復号化に失敗する
次の例は、キーが含まれるデータベースのコンテキストで DECRYPTBYKEY を実行する必要があること示しています。
DECRYPTBYKEY データベースでmasterが実行されると、行は暗号化解除されません。結果は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!Password#';
CREATE CERTIFICATE TestEncryptionCertificate
WITH SUBJECT = 'TestEncryption';
CREATE SYMMETRIC KEY TestEncryptSymmetricKey
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 AS VARBINARY (8000), @Val AS VARCHAR (30);
SELECT @Val = '000-123-4567';
OPEN SYMMETRIC KEY TestEncryptSymmetricKey DECRYPTION BY CERTIFICATE TestEncryptionCertificate;
SELECT @var = EncryptByKey(Key_GUID('TestEncryptSymmetricKey'), @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 TestEncryptSymmetricKey REMOVE PROVIDER KEY;
DROP CERTIFICATE TestEncryptionCertificate;
USE [master];
DROP DATABASE TestingDecryptByKey;
GO