SQL Server et protection des données
Par Arian Papillon le dimanche 10 avril 2022, 14:51 - Lien permanent
La sécurité des données est une préoccupation de plus en plus fréquente depuis la législation pour la protection des données personnelles (RGPD) et à cause de l'actualité qui montre que les fuites de données sont de plus en plus fréquentes.
Je suis sollicité de plus en plus fréquemment pour des projets de ce type : chiffrement, masquage, anonymisation, ...
Nous allons voir ici quelques unes des solutions offertes par le moteur de bases de données et astuces pour les mettre en oeuvre.
Identification des données sensibles à protéger
Les versions récentes de SSMS offrent des fonctionnalités de découverte et classification des données sensibles. Pour la découverte automatique, cela fonctionne par défaut avec des noms de colonnes libellés en anglais (mais il est possible de configurer son propre fichier json de configuration. Ensuite, on pourra produire des rapports ou interroger la liste des classifications avec la vue système sys.sensitivity_classifications (SQL Server 2019).
Masquage de données
Pour le masquage de données, le "dynamic data masking" offre la possibilité de masquer les données aux utilisateurs non explicitement autorisés. Notez qu'il ne s'agit pas là d'une vraie solution de sécurité : un utilisateur un peu malin peut trouver des solutions pour contourner ce masquage s'il peut construire sa propre requête SELECT. Voir à ce sujet ma vidéo ici :
Pseudonymisation ou anonymisation
Pour l'anonymisation, il n'y a pas vraiment de solution intégrée à SQL Server qui permette de faire du "Static Data Masking", technique qui consiste à écraser les données personnelles réelles par des données anonymisées. On peut se pencher des solutions tierces comme "Data Masker" de l'éditeur Regdgate, c'est un produit puissant mais son prix est plutôt élevé.
Reste la possibilité d'effectuer cela par des jobs SQL, voici une astuce pour générer des données pseudonymisées, ici pour les noms de famille, la même technique peut être répliquée pour toutes sortes de colonnes :
-- Je crée d'abord une table de noms de famille pour la pseudonymisation
-- J'y insère un grand nombre de noms réalistes, à partir d'un fichier,
-- on peut trouver cela assez facilement à partir de fichiers de l'INSEE par exemple
-- Je vous mets un backup à disposition avec 1048958 noms issus de l'INSEE
-- ici : http://blog.datafly.pro/public/Documents/Pseudo.bak
CREATE TABLE [LASTNAMES](
[ID] [int] IDENTITY NOT NULL,
[LASTNAME] [varchar](30) NULL,
CONSTRAINT [PK_LASTNAMES] PRIMARY KEY CLUSTERED
(
[ID] ASC
))
GO
-- Création d'une vue
CREATE VIEW [dbo].[V_RANDOMLASTNAME] AS
SELECT TOP (1) LASTNAME AS LASTNAME FROM LASTNAMES
WHERE ID = FLOOR(RAND()*1048958)+1 -- Ajuster la valeur en fonction du nombre de noms dans la table
GO
-- Création d'une fonction
CREATE FUNCTION [dbo].[F_GET_RANDOMLASTNAME] ()
RETURNS VARCHAR(30)
AS
BEGIN
RETURN (SELECT LASTNAME FROM V_RANDOMLASTNAME)
END;
GO
-- Exemple : pseudonymisation de tous mes clients inactifs
UPDATE Clients SET Nom = dbo.F_GET_RANDOMLASTNAME()
WHERE Actif = 0
Chiffrement
Le chiffrement des données est une fonctionnalité prise en charge nativement par SQL Server. Mais la contrainte souvent évoquée est qu'on ne veut pas engager de lourdes modifications de code dans les applications clientes. Les différentes options de chiffrement de données sont les suivantes :
Nous ne parlerons pas ici de Always Encrypted, technologie de chiffrement de bout en bout entre le client et le serveur, dont la mise en oeuvre n'est pas vraiment transparente pour les applications si elles n'ont pas été étudiées dès le départ pour utiliser cette fonctionnalité.
Le chiffrement transparent (ou TDE - Transparent Data Encryption) se limite uniquement à se protéger du vol de fichiers de données et de sauvegarde : le chiffrement est fait au niveau du stockage, tous les utilisateurs qui ont accès à l'instance et à la base de données accèdent aux données en clair. C'est par contre simple à mettre en oeuvre et parfaitement transparent pour les applications clientes qui ne voient pas la différence.
Le chiffrement de colonnes : le chiffrement dans SQL Server s'appuie sur une hiérarchie de clés et de certificats. Par exemple, les données sont chiffrées par une clé symétrique, elle même protégée et chiffrée par un certificat (chiffrement asysmétrique), le certificat étant lui même protégé par la clé maitre de la base de données (database master key), cette dernière sécurisée par la clé de l'instance (service master key).
Dans cet exemple, pour chiffrer ou déchiffrer, l'utilisateur doit avoir les permissions CONTROL sur le certificat (mais interdiction de modification) et REFERENCE sur la clé symétrique.
En principe, l’accès à une colonne chiffrée se fait :
- En lecture, ouverture de la clé protégée (OPEN … KEY … DECRYPTION BY CERTIFICATE …) puis lecture avec une fonction de déchiffrement (Decryptbykey)
- En écriture, ouverture de la clé protégée (OPEN … KEY … DECRYPTION BY CERTIFICATE …) puis écriture avec une fonction de chiffrement (Encryptbykey)
Pour éviter les modifications que cela impose au code applicatif, c'est notre but, une astuce consite à utiliser la fonction DECRYPTBYKEYAUTOCERT, qui permet d’éviter ces étapes avec une ouverture automatique du certificat pour la lecture. Pour l’écriture, il est par contre indispensable d’ouvrir la clé avec une instruction OPEN KEY, ce qui sera fait par l’intermédiaire de triggers INSTEAD OF.
Pour chaque table où l'on veut chiffrer une des colonnes, il faudra effectuer les opérations suivantes :
- La table source contenant la colonne sensible sera renommée avec le suffixe _encrypt
- Une nouvelle colonne binaire y est ajoutée pour contenir les valeurs chiffrées
- Cette colonne est alimentée en données chiffrées à partir de la colonne en clair
- La colonne en clair est ensuite supprimée de la table.
Les objets suivants devront être créés :
- Une vue, portant le nom de la table source : elle renvoie les données déchiffrées de la table _encrypt contenant la colonne chiffrée, en utilisant la fonction DECRYPTBYKEYAUTOCERT
- Deux triggers INSTEAD OF sur portant sur cette vue, qui permettent d’assurer l’insertion et la mise à jour dans la table chiffrée.
Notez que les triggers INSTEAD OF sont incompatibles avec les syntaxes UPDATE FROM avec jointure, ce qui peut poser des problèmes pour certaines requêtes de mise à jour : à tester en profondeur, donc. A prendre en compte aussi que les colonnes que vous chiffrez ne doivent pas servir à des recherches ni être indexées.
Pour générer le code nécessaire, je vous propose d'expérimenter la procédure stockée suivante, destiner à faciliter les opérations pour peu que l'on ne chiffre qu'une seule colonne de la table (mais n'hésitez pas à l'adapter à votre contexte).
/* Générateur de code SQL pour chiffrement d'une colonne -----------------------------
- Renommage
- Ajout de la colonne chiffrée
- Alimentation
- Vue
- Trigger Instead of Insert
- Trigger Instead of Update
- Code de supression de colonne en commentaire
Exemple d'utilisation :
DECLARE @pschema sysname, @ptable sysname, @pcolumn sysname, @pcertificate sysname, @psimkey sysname
SET @ptable = 'creditcards' -- nom de la table
SET @pschema = 'dbo' -- schéma de la table
SET @pcolumn = 'creditcardnumber'
SET @pcertificate = 'Col_Encrypt_Cert'
SET @psimkey = 'Col_Encrypt_Key'
EXEC SP_EncryptionScriptGeneration
@table = @ptable,
@schema = @pschema,
@column = @pcolumn,
@certificate = @pcertificate,
@simkey = @psimkey;
----------------------------------------------------------------------------*/
CREATE PROCEDURE SP_EncryptionScriptGeneration @table sysname, @schema sysname, @column sysname,
@certificate sysname, @simkey sysname
AS
IF OBJECT_ID('tempdb..#script') IS NOT NULL BEGIN
DROP TABLE #script;
END;
CREATE TABLE #script (Id INT IDENTITY PRIMARY KEY, command NVARCHAR(MAX));
INSERT #script VALUES ('-- Table : '+@schema+'.'+@table);
INSERT #script VALUES ('-------------------------------');
-- Renommage
INSERT #script VALUES ('-- Renommage --');
INSERT #script
VALUES ('EXEC sp_rename '''+@schema+'.'+@table+''', '''+@table+'_encrypt''');
-- Calculs
-- Calcul taille de la colonne
DECLARE @max_length SMALLINT, @is_nullable BIT, @column_id INT, @columntype NVARCHAR(MAX);
DECLARE @UsesAuth TINYINT=0, @BlockSize INT=16;
DECLARE @cipherlen INT;
-- Extraction informations de colonne
SELECT @max_length=SC.max_length, @is_nullable=SC.is_nullable, @column_id=SC.column_id,
@columntype=t.name
-- varchar and others
+CASE
WHEN t.name IN ('char', 'varchar', 'binary', 'varbinary', 'datetimeoffset',
'datetime2', 'time') THEN
'('+CASE SC.max_length WHEN -1 THEN 'max' ELSE LTRIM(STR(SC.max_length))END
+')' ELSE '' END
+CASE
WHEN t.name IN ('nchar', 'nvarchar') THEN
'('+CASE SC.max_length
WHEN -1 THEN
'max' ELSE LTRIM(STR(SC.max_length / 2))END+')' ELSE '' END
-- numeric
+CASE
WHEN t.name IN ('decimal', 'numeric') THEN
'('+LTRIM(STR(SC.precision))+','+LTRIM(STR(SC.scale))+')' ELSE '' END
+CASE
WHEN t.name IN ('float') AND SC.precision<>53 THEN
'('+LTRIM(STR(SC.precision))+')' ELSE '' END
+CASE
WHEN t.name IN ('real') AND SC.precision<>24 THEN
'('+LTRIM(STR(SC.precision))+')' ELSE '' END
FROM sys.columns SC
JOIN sys.types AS t
ON SC.user_type_id=t.user_type_id
WHERE SC.object_id=OBJECT_ID(@schema+'.'+@table)AND SC.name=@column;
SELECT @cipherlen= ((FLOOR((8+@max_length+ (@UsesAuth * 20)) / @BlockSize)+2) * @BlockSize) +20;
-- Extraction clé primaire
--DROP TABLE IF EXISTS #pk;
IF OBJECT_ID('tempdb..#pk') IS NOT NULL BEGIN
DROP TABLE #pk;
END;
SELECT ic.key_ordinal, col.[name]
INTO #pk
FROM sys.tables tab
INNER JOIN sys.indexes pk
ON tab.object_id=pk.object_id AND pk.is_primary_key=1
INNER JOIN sys.index_columns ic
ON ic.object_id=pk.object_id AND ic.index_id=pk.index_id
INNER JOIN sys.columns col
ON pk.object_id=col.object_id AND col.column_id=ic.column_id
WHERE tab.object_id=OBJECT_ID(@schema+'.'+@table)
ORDER BY ic.key_ordinal;
-- Ajout de colonne et alimentation
INSERT #script VALUES ('-- Ajout colonne --');
INSERT #script
VALUES ('ALTER TABLE '+@schema+'.'+@table+'_encrypt ADD '+@column+'_encrypt VARBINARY('
+CAST(@cipherlen AS VARCHAR(4))+')');
INSERT #script VALUES ('GO');
INSERT #script VALUES ('-- Alimentation --');
INSERT #script
VALUES ('OPEN SYMMETRIC KEY '+@simkey+' DECRYPTION BY CERTIFICATE '+@certificate);
INSERT #script
VALUES ('UPDATE '+@schema+'.'+@table+'_encrypt SET '+@column+'_encrypt = ENCRYPTBYKEY(KEY_GUID('''
+@simkey+'''),'+@column+')');
INSERT #script VALUES ('CLOSE SYMMETRIC KEY '+@simkey);
-- NonNullable Column
IF @is_nullable=0
INSERT #script
VALUES ('ALTER TABLE '+@schema+'.'+@table+'_encrypt ALTER COLUMN '+@column+'_encrypt VARBINARY('
+CAST(@cipherlen AS VARCHAR(4))+') NOT NULL');
INSERT #script VALUES ('GO');
-- Create view
INSERT #script VALUES ('-- Create View --');
DECLARE @command NVARCHAR(MAX), @command2 NVARCHAR(MAX);
SELECT @command=N'CREATE VIEW '+@schema+N'.'+@table+N' AS SELECT '
+CAST((SELECT CASE
WHEN column_id=@column_id THEN
'CAST(DECRYPTBYKEYAUTOCERT(CERT_ID('''+@certificate
+'''),NULL,'+@column+'_encrypt) AS '+@columntype+') AS '
+@column ELSE SC.name END+', '
FROM sys.columns SC
--JOIN sys.types AS t
-- ON SC.user_type_id = t.user_type_id
WHERE SC.object_id=OBJECT_ID(@schema+'.'+@table)
ORDER BY column_id
FOR XML PATH('')) AS NVARCHAR(MAX));
SET @command=LEFT(@command, LEN(@command)-1)+N' FROM '+@schema+N'.'+@table+N'_encrypt';
INSERT #script VALUES (@command);
INSERT #script VALUES ('GO');
-- Create insert trigger
INSERT #script VALUES ('-- Create Insert Trigger --');
SET @command=N'CREATE TRIGGER '+@schema+N'.TR_INS_IO_ENCRYPT_'+@table+N' ON '+@schema+N'.'+@table
+N' INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON; '+N'OPEN SYMMETRIC KEY '+@simkey
+N' DECRYPTION BY CERTIFICATE '+@certificate+N'; ';
SET @command=@command+N'INSERT INTO '+@schema+N'.'+@table+N'_encrypt (';
SELECT @command2=CAST((SELECT CASE
WHEN SC.is_identity=1 THEN
''
WHEN SC.column_id=@column_id THEN
SC.name+'_encrypt' ELSE SC.name END
+CASE WHEN SC.is_identity=0 THEN ', ' ELSE '' END
FROM sys.columns SC
WHERE SC.object_id=OBJECT_ID(@schema+'.'+@table)
ORDER BY column_id
FOR XML PATH('')) AS NVARCHAR(MAX));
SET @command2=LEFT(@command2, LEN(@command2)-1)+N') ';
SET @command=@command+@command2;
SELECT @command2=N'SELECT '
+CAST((SELECT CASE
WHEN SC.is_identity=1 THEN
''
WHEN SC.column_id=@column_id THEN
'ENCRYPTBYKEY(KEY_GUID('''+@simkey+'''), '+@column+')' ELSE
SC.name END+CASE WHEN SC.is_identity=0 THEN ', ' ELSE '' END
FROM sys.columns SC
WHERE SC.object_id=OBJECT_ID(@schema+'.'+@table)
ORDER BY column_id
FOR XML PATH('')) AS NVARCHAR(MAX));
SET @command2=LEFT(@command2, LEN(@command2)-1)+N' FROM inserted; ';
SET @command=@command+@command2;
SET @command=@command+N'CLOSE SYMMETRIC KEY '+@simkey+N'; END;';
INSERT #script VALUES (@command);
INSERT #script VALUES ('GO');
-- Create update trigger
INSERT #script VALUES ('-- Create Update Trigger --');
SET @command=N'CREATE TRIGGER '+@schema+N'.TR_UPD_IO_ENCRYPT_'+@table+N' ON '+@schema+N'.'+@table
+N' INSTEAD OF UPDATE AS BEGIN SET NOCOUNT ON; ';
IF OBJECTPROPERTY(OBJECT_ID(@schema+'.'+@table), 'TableHasPrimaryKey')=1 BEGIN
SET @command=@command+N'IF UPDATE ';
DECLARE @key sysname, @cpt INT=1;
DECLARE PK CURSOR FOR SELECT name FROM #pk ORDER BY key_ordinal;
OPEN PK;
FETCH NEXT FROM PK
INTO @key;
WHILE @@FETCH_STATUS=0 BEGIN
IF @cpt>1 SET @command=@command+N' OR UPDATE';
SET @command=@command+N'('+@key+N')';
SET @cpt=@cpt+1;
FETCH NEXT FROM PK
INTO @key;
END;
CLOSE PK;
DEALLOCATE PK;
SET @command=@command
+N' BEGIN RAISERROR(N''You can not update the primary key, Transaction has been failed'', 16, 1); RETURN; END; ';
END;
SET @command=@command+N'OPEN SYMMETRIC KEY '+@simkey+N' DECRYPTION BY CERTIFICATE '+@certificate
+N'; ';
SET @command=@command+N'UPDATE '+@schema+N'.'+@table+N'_encrypt SET ';
SELECT @command2=CAST((SELECT CASE
WHEN SC.is_identity=1 THEN
''
WHEN SC.column_id=@column_id THEN
SC.name+'_encrypt=ENCRYPTBYKEY(KEY_GUID('''+@simkey+'''), I.'
+SC.name+')' ELSE SC.name+'=I.'+SC.name END
+CASE WHEN SC.is_identity=0 THEN ', ' ELSE '' END
FROM sys.columns SC
WHERE SC.object_id=OBJECT_ID(@schema+'.'+@table)
ORDER BY column_id
FOR XML PATH('')) AS NVARCHAR(MAX));
SET @command2=LEFT(@command2, LEN(@command2)-1);
SET @command=@command+@command2;
SET @command=@command+N' FROM inserted I INNER JOIN '+@schema+N'.'+@table+N'_encrypt E ON ';
DECLARE PK CURSOR FOR SELECT name FROM #pk ORDER BY key_ordinal;
OPEN PK;
SET @cpt=1;
FETCH NEXT FROM PK
INTO @key;
WHILE @@FETCH_STATUS=0 BEGIN
IF @cpt>1 SET @command=@command+N' AND ';
SET @command=@command+N'I.'+@key+N'=E.'+@key;
SET @cpt=@cpt+1;
FETCH NEXT FROM PK
INTO @key;
END;
CLOSE PK;
DEALLOCATE PK;
SET @command=@command+N' CLOSE SYMMETRIC KEY '+@simkey+N'; END;';
INSERT #script VALUES (@command);
INSERT #script VALUES ('GO');
-- Cleanup
INSERT #script VALUES ('/* -- Cleanup : Drop Column --');
INSERT #script
VALUES ('ALTER TABLE '+@schema+'.'+@table+'_encrypt DROP COLUMN '+@column);
INSERT #script
VALUES ('ALTER TABLE '+@schema+'.'+@table+'_encrypt REBUILD ');
INSERT #script VALUES ('-------------------*/');
INSERT #script VALUES ('');
SELECT * FROM #script;
GO
A vous de jouer pour sécuriser vos données au mieux et éviter les vols et les accès non autorisés...!