DECRYPTBYKEY (Transact-SQL)

Article
04/26/2023

S’applique à :SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

Cette fonction utilise une clé symétrique pour déchiffrer les données.

Notes

Cette syntaxe n’est pas prise en charge par le pool SQL serverless dans Azure Synapse Analytics.

Pour les pools SQL dédiés dans Azure Synapse Analytics, la mise en cache du jeu de résultats ne doit pas être utilisée conjointement avec DECRYPTBYKEY. Si cette fonction de chiffrement doit être utilisée, vérifiez que la mise en cache du jeu de résultats est désactivée (au niveau de la session ou au niveau de la base de données) au moment de l’exécution.

Syntaxe

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

Remarque

Pour afficher la syntaxe Transact-SQL pour SQL Server 2014 (12.x) et versions antérieures, consultez la Documentation sur les versions antérieures.

Arguments

ciphertext
Variable de type varbinary contenant des données chiffrées avec la clé.

@ciphertext
Variable de type varbinary contenant des données chiffrées avec la clé.

add_authenticator
Indique si le processus de chiffrement d’origine comprend et chiffre un authentificateur avec le texte en clair. Doit correspondre à la valeur passée à ENCRYPTBYKEY (Transact-SQL) durant le processus de chiffrement des données. add_authenticator a le type de données int.

authenticator
Données utilisées comme base pour la génération de l’authentificateur. Doit correspondre à la valeur fournie à ENCRYPTBYKEY (Transact-SQL). authenticator a le type de données sysname.

@authenticator
Variable contenant des données à partir desquelles un authentificateur est généré. Doit correspondre à la valeur fournie à ENCRYPTBYKEY (Transact-SQL). @authenticator a le type de données sysname.

Types de retour

varbinary d’une taille maximale de 8 000 octets. DECRYPTBYKEY retourne NULL si la clé symétrique utilisée pour chiffrer les données n’est pas ouverte ou si ciphertext est NULL.

Remarques

DECRYPTBYKEY utilise une clé symétrique. Cette clé symétrique doit déjà être ouverte dans la base de données. DECRYPTBYKEY autorise l’ouverture de plusieurs clés en même temps. Vous n’avez pas besoin d’ouvrir la clé tout de suite avant de déchiffrer le texte chiffré.

Le chiffrement et le déchiffrement symétriques fonctionnent assez rapidement, et donnent de bons résultats pour les opérations impliquant de gros volumes de données.

L’appel DECRYPTBYKEY doit se produire dans le contexte de la base de données contenant la clé de chiffrement. Vérifiez cela en appelant DECRYPTBYKEY à partir d’un objet (par exemple une vue, une procédure stockée ou une fonction) qui réside dans la base de données.

Autorisations

La clé symétrique doit déjà être ouverte dans la session active. Pour plus d’informations, consultez OPEN SYMMETRIC KEY (Transact-SQL).

Exemples

R. Déchiffrement à l'aide d'une clé symétrique

Cet exemple déchiffre le texte chiffré avec une clé symétrique.

-- 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. Déchiffrement à l'aide d'une clé symétrique et d'un hachage d'authentification

Cet exemple déchiffre les données chiffrées à l’origine avec un authentificateur.

-- 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. Impossible de déchiffrer hors du contexte de base de données avec la clé

L’exemple suivant montre que DECRYPTBYKEY doit être exécutée dans le contexte de la base de données qui contient la clé. La ligne ne sera pas déchiffrée quand DECRYPTBYKEY est exécutée dans la base de données MASTER ; le résultat est 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

Voir aussi

ENCRYPTBYKEY (Transact-SQL)
CREATE SYMMETRIC KEY (Transact-SQL)
ALTER SYMMETRIC KEY (Transact-SQL)
DROP SYMMETRIC KEY (Transact-SQL)
Hiérarchie de chiffrement
Choisir un algorithme de chiffrement