Chiffrer une colonne de données

Cette rubrique explique comment chiffrer une colonne de données à l'aide du chiffrement symétrique dans SQL Server 2012 avec Transact-SQL.

Dans cette rubrique

• Avant de commencer :

  Sécurité

• Pour chiffrer une colonne de données à l'aide de Transact-SQL

Avant de commencer

Sécurité

Autorisations

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

• Autorisation CONTROL sur la base de données.

• Autorisation CREATE CERTIFICATE sur 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.

[Top]

Utilisation de Transact-SQL

Pour chiffrer une colonne de données à l'aide d'un chiffrement symétrique simple

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

2. Dans la barre d'outils standard, cliquez sur Nouvelle requête.

3. Copiez et collez l'exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter.

   USE AdventureWorks2012;
   --If there is no master key, create one now. 
   IF NOT EXISTS 
       (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
       CREATE MASTER KEY ENCRYPTION BY 
       PASSWORD = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
   GO
   
   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(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 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('SHA1', 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('SHA1', CONVERT(varbinary, CreditCardID))))
       AS 'Decrypted card number' FROM Sales.CreditCard;
   GO
   

Pour chiffrer une colonne de données à l'aide du chiffrement symétrique qui inclut un authentificateur

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

2. Dans la barre d'outils standard, cliquez sur Nouvelle requête.

3. Copiez et collez l'exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter.

   USE AdventureWorks2012;
   GO
   
   --If there is no master key, create one now. 
   IF NOT EXISTS 
       (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
       CREATE MASTER KEY ENCRYPTION BY 
       PASSWORD = '23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj'
   GO
   
   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 [AdventureWorks2012];
   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
   

Pour plus d'informations, consultez les documents suivants :

• CREATE CERTIFICATE (Transact-SQL)

• CREATE SYMMETRIC KEY (Transact-SQL)

• ALTER TABLE (Transact-SQL)

• OPEN SYMMETRIC KEY (Transact-SQL)

[Top]