Chiffrer une colonne de données

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics

Cet article explique comment chiffrer une colonne de données à l’aide du chiffrement symétrique dans SQL Server à l’aide de Transact-SQL. On parle parfois de chiffrement au niveau colonne ou au niveau cellule.

Cet article requiert l'échantillon de base de données AdventureWorks, que vous pouvez télécharger à partir de la page d'accueil des AdventureWorks2022échantillons et Projects de communautés Microsoft SQL Server.

Sécurité

Autorisations

Les autorisations suivantes sont nécessaires pour effectuer les étapes ci-dessous :

  • Une autorisation CONTROL au niveau de la base de données.
  • Une autorisation CREATE CERTIFICATE au niveau de la base de données. Les connexions Windows, les connexions SQL Server et les rôles d’application sont les seuls à pouvoir posséder des certificats. Les groupes et les rôles ne peuvent pas posséder de certificats.
  • Autorisation ALTER sur la table.
  • Autorisation sur la clé, et l’autorisation VIEW DEFINITION ne doit pas lui avoir été refusée.

Créer une clé principale de base de données

Pour pouvoir utiliser les exemples suivants, vous devez disposer d’une clé principale de base de données. Si votre base de données n’a pas encore de clé principale de base de données, créez-en une. Pour en créer une, connectez-vous à votre base de données et exécutez le script suivant. Veillez à utiliser un mot de passe complexe.

Copiez et collez l’exemple suivant dans la fenêtre de requête qui est connectée à l’exemple de base de données AdventureWorks. Sélectionnez Exécuter.

CREATE MASTER KEY ENCRYPTION BY   
PASSWORD = '<complex password>';

Sauvegardez toujours votre clé principale de base de données. Pour plus d’informations sur les clés principales de base de données, consultez CREATE MASTER KEY (Transact-SQL).

Exemple : Chiffrer avec le chiffrement symétrique et l’authentificateur

  1. Dans l' Explorateur d'objets, connectez-vous à une instance du Moteur de base de données.

  2. Dans la barre d’outils standard, sélectionnez Nouvelle requête.

  3. Copiez et collez l’exemple suivant dans la fenêtre de requête qui est connectée à l’exemple de base de données AdventureWorks. Sélectionnez Exécuter.

    CREATE CERTIFICATE Sales09  
   WITH SUBJECT = 'Customer Credit Card Numbers';  
GO  

CREATE SYMMETRIC KEY CreditCards_Key11  
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE Sales09;  
GO  

-- Create a column in which to store the encrypted data.  
ALTER TABLE Sales.CreditCard   
    ADD CardNumber_Encrypted varbinary(160);   
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 using the  
-- symmetric key CreditCards_Key11.  
-- Save the result in column CardNumber_Encrypted.    
UPDATE Sales.CreditCard  
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')  
    , CardNumber, 1, HASHBYTES('SHA2_256', CONVERT( varbinary  
    , CreditCardID)));  
GO  

-- Verify the encryption.  
-- 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('SHA2_256', CONVERT(varbinary, CreditCardID))))  
    AS 'Decrypted card number' FROM Sales.CreditCard;  
GO

Chiffrer avec le chiffrement symétrique simple

  1. Dans l' Explorateur d'objets, connectez-vous à une instance du Moteur de base de données.

  2. Dans la barre d’outils standard, sélectionnez Nouvelle requête.

  3. Copiez et collez l’exemple suivant dans la fenêtre de requête qui est connectée à l’exemple de base de données AdventureWorks. Sélectionnez Exécuter.

     CREATE CERTIFICATE HumanResources037  
   WITH SUBJECT = 'Employee Social Security Numbers';  
GO  

CREATE SYMMETRIC KEY SSN_Key_01  
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE HumanResources037;  
GO  

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  

-- Verify the encryption.  
-- 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, EncryptedNationalIDNumber   
    AS 'Encrypted ID Number',  
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))   
    AS 'Decrypted ID Number'  
    FROM HumanResources.Employee;  
GO

Étapes suivantes