ENCRYPTBYKEY (Transact-SQL)

Article
10/16/2024

S’applique à : SQL Server base de données Azure SQL Azure SQL Managed Instance Azure Synapse Analytics

Chiffre les données à l'aide d'une clé symétrique.

Notes

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

Syntaxe

  
EncryptByKey ( key_GUID , { 'cleartext' | @cleartext }  
    [, { add_authenticator | @add_authenticator }  
     , { authenticator | @authenticator } ] )

Arguments

key_GUID
GUID de la clé à utiliser pour chiffrer cleartext. uniqueidentifier.

'cleartext'
Données à chiffrer à l'aide de la clé.

@cleartext
Variable de type nvarchar, char, varchar, binary, varbinary ou nchar qui contient des données à chiffrer avec la clé.

add_authenticator
Indique si un authentificateur sera chiffré avec cleartext. Sa valeur doit être égale à 1 si vous utilisez un authentificateur. int.

@add_authenticator
Indique si un authentificateur sera chiffré avec cleartext. Sa valeur doit être égale à 1 si vous utilisez un authentificateur. int.

authenticator
Données à partir desquelles un authentificateur peut être dérivé. sysname.

@authenticator
Variable contenant les données à partir desquelles l'authentificateur sera dérivé.

Types de retour

varbinary d’une taille maximale de 8 000 octets.

Retourne NULL si la clé n'est pas ouverte, si elle n'existe pas ou s'il s'agit d'une clé RC4 déconseillée et que la base de données n'est pas au niveau de compatibilité 110 ou supérieur.

Retourne NULL si la valeur cleartext est NULL.

Remarques

EncryptByKey utilise une clé symétrique. Cette clé doit être ouverte. Si la clé symétrique est déjà ouverte dans la session en cours, il n'est pas nécessaire de l'ouvrir à nouveau dans le contexte de la requête.

L'authentificateur vous permet d'empêcher la substitution en valeurs entières des champs chiffrés. Par exemple, considérons la table suivante des données de salaires du personnel :

Employee_ID	Standard_Title	Base_Pay
345	Copy Room Assistant	Fskj%7^edhn00
697	Chief Financial Officer	M0x8900f56543
694	Data Entry Supervisor	Cvc97824%^34f

Sans même casser le code de chiffrement, un utilisateur malveillant peut déduire des informations importantes à partir du contexte dans lequel le texte chiffré est stocké. Comme à la fonction de Chief Financial Officer correspond un salaire plus élevé qu'à celle de Copy Room Assistant, la valeur chiffrée en M0x8900f56543 doit être supérieure à la valeur chiffrée en Fskj%7^edhn00. S'il en est ainsi, tout utilisateur disposant de l'autorisation ALTER sur la table peut donner une augmentation à la fonction Copy Room Assistant en remplaçant les données du champ Base_Pay par une copie des données stockées dans le champ Base_Pay de la fonction Chief Financial Officer. Cette attaque par substitution complète de valeur outrepasse complètement le chiffrement.

Vous pouvez déjouer les attaques par substitution de ce type en ajoutant des informations contextuelles au texte brut avant de le chiffrer. Ces informations contextuelles sont utilisées pour vérifier que les données en texte brut n'ont pas été déplacées.

Si un authentificateur est précisé lors du chiffrement des données, il faut utiliser le même pour déchiffrer les données à l'aide de DecryptByKey. Au moment du chiffrement, un hachage de l'authentificateur est chiffré avec le texte brut. Au moment du déchiffrement, vous devez passer le même authentificateur à DecryptByKey. Si les deux ne correspondent pas, le déchiffrement échoue. Cela indique que la valeur a été déplacée depuis qu'elle a été chiffrée. Nous recommandons l'utilisation d'une colonne contenant une valeur unique et constante comme authentificateur. Si la valeur de l'authentificateur change, vous risquez de perdre l'accès aux données.

Le chiffrement et le déchiffrement symétriques sont relativement rapides et adaptés à la manipulation de grandes quantités de données.

Important

L’utilisation des fonctions de chiffrement SQL Server conjointement au paramètre ANSI_PADDING OFF peut entraîner la perte de données en raison de conversions implicites. Pour plus d’informations sur ANSI_PADDING, consultez SET ANSI_PADDING (Transact-SQL).

Exemples

La fonctionnalité illustrée dans les exemples suivants utilise les clés et les certificats créés dans Guide pratique pour chiffrer une colonne de données.

R. Chiffrer une chaîne à l'aide d'une clé symétrique

L'exemple de code suivant ajoute une colonne à la table Employee, puis chiffre la valeur du numéro de sécurité sociale stockée dans la colonne NationalIDNumber.

USE AdventureWorks2022;  
GO  
  
-- Create a column in which to store the encrypted data.  
ALTER TABLE HumanResources.Employee  
    ADD EncryptedNationalIDNumber varbinary(128);   
GO  
  
-- Open the symmetric key with which to encrypt the data.  
OPEN SYMMETRIC KEY SSN_Key_01  
   DECRYPTION BY CERTIFICATE HumanResources037;  
  
-- Encrypt the value in column NationalIDNumber with symmetric key  
-- SSN_Key_01. Save the result in column EncryptedNationalIDNumber.  
UPDATE HumanResources.Employee  
SET EncryptedNationalIDNumber  
    = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);  
GO

B. Chiffrer un enregistrement avec une valeur d'authentification

USE AdventureWorks2022;  
  
-- Create a column in which to store the encrypted data.  
ALTER TABLE Sales.CreditCard   
    ADD CardNumber_Encrypted varbinary(128);   
GO  
  
-- Open the symmetric key with which to encrypt the data.  
OPEN SYMMETRIC KEY CreditCards_Key11  
    DECRYPTION BY CERTIFICATE Sales09;  
  
-- Encrypt the value in column CardNumber with symmetric   
-- key CreditCards_Key11.  
-- Save the result in column CardNumber_Encrypted.    
UPDATE Sales.CreditCard  
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11'),   
    CardNumber, 1, CONVERT( varbinary, CreditCardID) );  
GO

Voir aussi

DECRYPTBYKEY (Transact-SQL)
CREATE SYMMETRIC KEY (Transact-SQL)
ALTER SYMMETRIC KEY (Transact-SQL)
DROP SYMMETRIC KEY (Transact-SQL)
Hiérarchie de chiffrement
HASHBYTES (Transact-SQL)

Partage via