Partager via

DECRYPTBYKEY (Transact-SQL)

S’applique à :SQL Serverbase de données Azure SQLAzure SQL Managed InstanceAzure Synapse Analytics

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

Conventions de la syntaxe Transact-SQL

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 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 } ] )

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 pendant 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. authenticator est sysname.

@authenticator

Variable contenant des données à partir desquelles un authentificateur est généré. Doit correspondre à la valeur fournie à ENCRYPTBYKEY. @authenticator est sysname.

Types de retour

varbinary d’une taille maximale de 8 000 octets. DECRYPTBYKEY retourne NULL si la clé symétrique utilisée pour le chiffrement des données n’est pas ouverte ou si le texte chiffré 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é immédiatement avant le déchiffrement du texte de chiffrement.

Le chiffrement symétrique et le déchiffrement fonctionnent généralement rapidement et fonctionnent bien pour les opérations impliquant de grands 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.

Exemples

R. Déchiffrer à 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échiffrer à 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;

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 n’est pas déchiffrée lorsqu’elle DECRYPTBYKEY est exécutée dans la master base de données ; 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!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

Contenu connexe

  • Last updated on