Datafly.pro

Voici l'exemple complet :

Nous avons une table Customers avec une colonne Password que nous souhaitons chiffrer. (On évitera bien sûr de chiffrer des colonnes clés ou indexées, à ce propos voir mon précédent article.) Nous souhaitons que ce chiffrement n'occasionne aucune modification à mes applications clientes... (en dehors des écarts éventuels de performances)

Préparation des données d'exemple

Préparons les données de l'exemple :

CREATE TABLE [dbo].[Customers](
[ContactID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[Password] [nvarchar](50) NOT NULL
) ON [PRIMARY]

Insérons quelques données...

INSERT INTO [dbo].[Customers]([FirstName], [LastName], [Password])
SELECT N'Gustavo', N'Achong', N'PwdAchong0' UNION ALL
SELECT N'Catherine', N'Abel', N'PwdAbel0' UNION ALL
SELECT N'Kim', N'Abercrombie', N'PwdAbercrombie0' UNION ALL
SELECT N'Humberto', N'Acevedo', N'PwdAcevedo0' UNION ALL
SELECT N'Pilar', N'Ackerman', N'PwdAckerman0' 

Mise en place du chiffrement

Préparons le chiffrement : Je choisis de chiffrer mes données avec une clé symétrique, le plus performant. Je sécurise cette clé symétrique avec une clé asymétrique.

-- création de la master key : sécurise le chiffrement de la base
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd' 
-- création d'une clé asymétrique pour sécuriser ma clé symétrique de chiffrement
CREATE ASYMMETRIC KEY AKey
WITH ALGORITHM = RSA_2048 ; 
GO
-- création de ma clé symétrique de chiffrement
CREATE SYMMETRIC KEY SKey
WITH ALGORITHM = DES ENCRYPTION BY ASYMMETRIC KEY AKey ; 

Chiffrement des données

Pour chiffrer nos données existantes et rendre le chiffrement transparent pour les applications, nous allons devoir :

• renommer ma table
• y ajouter une nouvelle colonne pour le password chiffré
• remplir cette colonne chiffrée et vider celle non chiffrée
• poser des vues et des triggers instead off pour ne pas perturber les applications

-- renommage de la table
exec sp_rename 'dbo.Customers','Encr_Customers'
-- ajout de la colonne chiffrée
ALTER TABLE dbo.Encr_Customers ADD Encr_Password varbinary(256) NULL
-- insertion des données chiffrées et suppression des données non chiffrées
OPEN SYMMETRIC KEY SKey DECRYPTION BY ASYMMETRIC KEY AKey ;
UPDATE Encr_Customers
SET  Encr_Password = ENCRYPTBYKEY(KEY_GUID('SKey'), [Password]) ;
CLOSE SYMMETRIC KEY SKey ;
UPDATE Encr_Customers SET Password = NULL;
GO

Déchiffrer ses données

Maintenant, pour lire nos données en clair, nous pouvons utiliser la syntaxe suivante :

OPEN SYMMETRIC KEY SKey DECRYPTION BY ASYMMETRIC KEY AKey ;
SELECT  [ContactID]
      , [FirstName]
      , [LastName]
      , CONVERT(NVARCHAR, DecryptByKey(Encr_Password)) as Password
FROM    dbo.Encr_Customers;
CLOSE SYMMETRIC KEY SKey ;

L'inconvénient de cette méthode est qu'il faut ouvrir la clé asymétrique avant de déchiffrer.

Une autre méthode est disponible : la fonction DecryptByKeyAutoAsymKey permet d'ouvrir automatiquement la clé asymétrique (pour peu qu'on en ait la permission) et de s'affranchir de ce problème... A noter qu'il existe aussi une fonction DecryptByKeyAutoCert pour la sécurisation par certificat.

SELECT  [ContactID]
      , [FirstName]
      , [LastName]
      , CONVERT(NVARCHAR, DecryptByKeyAutoAsymKey(AsymKey_ID('AKey'), NULL, Encr_Password)) AS Password
FROM    dbo.Encr_Customers

Rendre le chiffrement transparent

Cette solution va nous permettre de créer une vue nommée Customers à partir de cette requête. Elle sera utilisée de manière transparente par les applications en lieu et place de la table Customers d'origine :

CREATE VIEW dbo.Customers AS ... etc...

Pour l'insertion, la mise à jour et la suppression, des triggers INSTEAD OF posés sur la vue Customers mettre à jour de façon transparente la table chiffrée sous-jacente Encr_Customers :

-- Triggers INSTEAD OF
CREATE TRIGGER [dbo].[TR_Insert_Customers] ON [dbo].[Customers]
  INSTEAD OF INSERT
AS
  BEGIN
    SET NOCOUNT ON
    OPEN SYMMETRIC KEY SKey DECRYPTION BY ASYMMETRIC KEY AKey ;
    INSERT  INTO Encr_Customers (FirstName, LastName, Encr_Password)
            SELECT  [FirstName]
                  , [LastName]
                  , ENCRYPTBYKEY(KEY_GUID('SKey'),
                                 [Password]) AS Encr_Password
            FROM    inserted
    CLOSE SYMMETRIC KEY SKey
  END
GO

CREATE TRIGGER [dbo].[TR_Update_Customers] ON [dbo].[Customers]
  INSTEAD OF UPDATE
AS
  BEGIN
    SET NOCOUNT ON
    IF (UPDATE(ContactID)) -- If ContactID is updated
    BEGIN
        RAISERROR (N'You can not edit ContactID, Transaction has been failed', 16, 1)
        RETURN
    END
    OPEN SYMMETRIC KEY SKey DECRYPTION BY ASYMMETRIC KEY AKey ;
    UPDATE Encr_Customers 
    SET [FirstName] = I.FirstName,
    [LastName]=I.LastName,
    Encr_Password = ENCRYPTBYKEY(KEY_GUID('SKey'),I.Password) 
    FROM  inserted I INNER JOIN Encr_Customers E
    ON I.ContactID = E.ContactID
    CLOSE SYMMETRIC KEY SKey
  END
GO

CREATE TRIGGER [dbo].[TR_Delete_Customers] ON [dbo].[Customers]
  INSTEAD OF DELETE
AS
  BEGIN
    SET NOCOUNT ON
    DELETE Encr_Customers FROM Encr_Customers e, deleted d where e.contactId=d.ContactID
  END
GO

Gérer les permissions

Comment donner l'accès aux données chiffrées à nos utilisateurs ? Via les permissions :

• Leur donner les accès à la vue
• Leur donner la permission CONTROL sur la clé asymétrique (nécessaire pour chiffrer et déchiffrer) et VIEW DEFINITION sur la clé symétrique
• Pour éviter que la clé asymétrique puisse être supprimée ou modifiée, interdire la modification des clés asymétriques aux utilisateurs (DENY)

-- Permissions
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON [dbo].[Customers] TO [toto]
GRANT CONTROL ON ASYMMETRIC KEY::[AKey] TO [toto]
GRANT VIEW DEFINITION ON SYMMETRIC KEY::[SKey] TO [toto]
DENY ALTER ANY ASYMMETRIC KEY TO public

Rétablir l'accès sur une autre instance

Un dernier point : pour pouvoir restaurer la base sur un autre serveur et lire les colonnes chiffrées, vous pouvez régénérer la master key :

-- Après restore
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'P@ssw0rd'
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'P@ssw0rd'

Bon chiffrement (et n'oubliez pas d'être prudents : planifier, bien sauvegarder vos clés et vos données, faire les tests à fond... : les données que vous ne sauriez plus déchiffrer seraient perdues !) .