DECRYPTBYKEY (Transact-SQL)
適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure 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)
暗号化階層
暗号化アルゴリズムの選択
フィードバック
https://aka.ms/ContentUserFeedback」を参照してください。
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「フィードバックの送信と表示