![\"twitter\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" "\\"Share")
<\/a><\/a>![\"linkedin\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" "\\"Share")
<\/a>![\"mail\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" "\\"Share")
<\/a>C’est un sujet d’actualit\u00e9, d’ailleurs d\u00e9but mars l’ANSSI a r\u00e9\u00e9valu\u00e9<\/a> son estimation de la menace cyber au regard des \u00e9v\u00e8nements en Ukraine, et invit\u00e9 les entreprises fran\u00e7aises \u00e0 renforcer leur posture de s\u00e9curit\u00e9. <\/p>\n Le vol de donn\u00e9es est un des risques auxquels toutes les entreprises s’exposent, et ce risque existe tout autant \u00e0 l’int\u00e9rieur qu’\u00e0 l’ext\u00e9rieur d’une organisation. Brevets, r\u00e9sultats de recherche, donn\u00e9es financi\u00e8res, donn\u00e9es personnelles et RGPD… Chiffrer les donn\u00e9es sensibles, ou m\u00eame toutes les donn\u00e9es tant qu’\u00e0 faire, peut \u00eatre une r\u00e9ponse rassurante mais elle entraine son lot de co\u00fbts cach\u00e9s : options ou outils payants, impact sur la performance, la volum\u00e9trie, la manageabilit\u00e9 et et le maintien en conditions op\u00e9rationnelles. <\/p>\n Les questions \u00e0 se poser sont principalement : contre quoi dois-je me prot\u00e9ger ? quelles sont mes donn\u00e9es \u00e0 caract\u00e8re sensible ? <\/p>\n Nous allons voir dans cet article (assez long finalement), l’\u00e9ventail de solutions de chiffrement propos\u00e9es pour les bases de donn\u00e9es de type MySQL \/ MariaDB. <\/p>\n On peut distinguer deux grandes mani\u00e8res d’adresser la question du chiffrement de donn\u00e9es avec MySQL:<\/p>\n Le chiffrement Data-at-Rest <\/em>:<\/strong> Que ce soit InnoDB Data-at-rest ou TDE, seul le tablespace est chiffr\u00e9. Lorsqu’il est lu depuis le disque, MySQL le d\u00e9chiffre et ensuite son contenu est visible en clair : On comprend donc que l’effet de TDE est limit\u00e9 : il faudra en plus songer \u00e0 g\u00e9rer le droit d’en conna\u00eetre des utilisateurs, chiffrer la communication entre le client et le serveur (id\u00e9alement via un tunnel sinon gare aux probl\u00e8mes de performance<\/a>), et aussi chiffrer les extractions, les dumps, etc… A ce niveau seuls les backups physiques sont compatibles avec TDE, les fichiers de dump type mysqldump \/ mysqlpump seront lisibles en clair. <\/p>\n L’avantage de TDE est, comme son nom l’indique, de rester transparent vis-\u00e0-vis des applications. Nul besoin de modifier le code applicatif pour d\u00e9crypter les donn\u00e9es, c’est MySQL qui s’en charge. La granularit\u00e9 en revanche sera le tablespace. Si votre d\u00e9ploiement est au format innodb_file_per_table=1<\/a> qui est le cas par d\u00e9faut depuis la version 5.6.6, chaque table peut \u00eatre chiffr\u00e9e ou non s\u00e9par\u00e9ment. <\/p>\n TDE utilise une infrastructure de chiffrement compos\u00e9e d’une InnoDB MASTER KEY, qui elle-m\u00eame chiffre des TABLESPACE KEYS permettant d’encrypter les fichiers sur disque. Il existe \u00e9galement une BINLOG KEY qui permet de chiffrer le contenu des journaux binaires. Ces cl\u00e9s doivent \u00eatre g\u00e9r\u00e9es : stock\u00e9es, sauvegard\u00e9es, et doivent \u00eatre chang\u00e9es r\u00e9guli\u00e8rement comme pr\u00e9conis\u00e9 par les normes de s\u00e9curit\u00e9 type PCI-DSS. Nous allons voir plus loin sp\u00e9cifiquement comment le faire. <\/p>\n Le chiffrement applicatif : <\/strong> Parmi les inconv\u00e9nients principaux, le manque de transparence vis-\u00e0-vis de l’application. Le code devra \u00eatre modifi\u00e9 pour manipuler la ou les cl\u00e9s de chiffrement, ce qui exclue la solution pour nombre de progiciels qui ne l’ont pas pr\u00e9vu. L’impact sur la performance et la volum\u00e9trie est \u00e9galement tr\u00e8s dissuasif, comme nous allons le voir plus loin. <\/p>\n Dans le cas de TDE, les MASTER KEY et BINLOG KEY sont stock\u00e9es dans un keyring<\/a>, qui peut \u00eatre soit port\u00e9 par une infrastructure KMS type Oracle OKV<\/a>, Amazon KMS<\/a>, Hashicorp Vault<\/a>, etc… ou un keyring local sous la forme d’un fichier crypt\u00e9 (\u00e9dition Enterprise) ou non (\u00e9dition communautaire):<\/p>\n Dans l’exemple nous allons utiliser un keyring local crypt\u00e9. Dans un premier temps il faut cr\u00e9er un r\u00e9pertoire sous lequel notre keyring sera stock\u00e9 : <\/p>\n Puis modifier le fichier de configuration de MySQL pour d\u00e9clarer le keyring:<\/p>\n Noter le premier probl\u00e8me ici, la n\u00e9cessit\u00e9 de stocker le mot de passe de d\u00e9chiffrement du keyring dans la config de MySQL, ce qui fait porter tout le poids de la s\u00e9curisation sur les droits et comptes autoris\u00e9s \u00e0 lire le fichier (\u00e9videmment il faut d\u00e9j\u00e0 pouvoir acc\u00e9der \u00e0 la machine locale). Dans les impl\u00e9mentations de production, on lui pr\u00e9f\u00e8rera nettement un KMS qui est un outil fait pour \u00e7a. <\/p>\n On red\u00e9marre le service MySQL pour pouvoir v\u00e9rifier si le plugin est bien disponible et pr\u00eat \u00e0 \u00eatre utilis\u00e9:<\/p>\n Il ne reste plus qu’\u00e0 tester le chiffrement d’une table et voir ce que \u00e7a donne sur disque:<\/p>\n OK, les donn\u00e9es sont bien lues en clair dans le client MySQL, voyons dans le fichier IBD:<\/p>\n Le fichier est bien chiffr\u00e9 correctement, les donn\u00e9es sont illisibles. <\/p>\n Nous avons vu que le chiffrement applicatif consiste \u00e0 chiffrer une ou plusieurs colonnes \u00e9ligibles, non la table ou la base en entier. Deux cas sont possibles: Dans les 2 cas, il faudra commencer par installer les fonctions n\u00e9cessaires de g\u00e9n\u00e9ration de cl\u00e9s, chiffrement, d\u00e9chiffrement etc… livr\u00e9es dans les plugins keyring_udf.so et openssl_udf.so :<\/p>\n Dans le cas d’un chiffrement sym\u00e9trique:<\/strong> D\u00e9j\u00e0 premi\u00e8re remarque, notre colonne char(16) est devenue un varbinary(32) au passage, mais on le verra plus loin cela n’aura que peu d’effets sur la volum\u00e9trie. Voyons ce que r\u00e9v\u00e8lent nos donn\u00e9es avec et sans cl\u00e9 de chiffrement:<\/p>\n On voit bien le manque de transparence de cette solution pour l’applicatif, qui devra modifier tous ses acc\u00e8s en lecture ou DML aux donn\u00e9es pour passer la cl\u00e9 de chiffrement. <\/p>\n Regardons le contenu du fichier sur disque, ou dans un mysqldump, pour voir si notre colonne reste bien illisible de bout en bout:<\/p>\n Dans le cas d’un chiffrement asym\u00e9trique:<\/strong> Les donn\u00e9es sont correctement crypt\u00e9es:<\/p>\n La colonne peut \u00eatre ensuite d\u00e9chiffr\u00e9e avec la cl\u00e9 publique. Peu importe l’ordre ici il ne s’agit pas d’un \u00e9change de cl\u00e9 type RSA entre Alice et Bob, le but est simplement d’\u00e9viter d’utiliser la m\u00eame cl\u00e9 pour faire les deux op\u00e9rations. <\/p>\n A noter que cette fois le type de la colonne credit_card<\/em> est pass\u00e9e de char(16) \u00e0 blob directement, ce qui aura un impact non n\u00e9gligeable sur la volum\u00e9trie, comme on le verra plus loin.<\/p>\n Enfin, pour \u00e9viter d’avoir \u00e0 passer la cl\u00e9 d’ouverture du keyring partout dans le code, il est possible de la centraliser dans une fonction : <\/p>\n Mais l\u00e0 encore le probl\u00e8me de stocker le secret pour d\u00e9verrouiller le keyring est seulement d\u00e9plac\u00e9 ailleurs… <\/p>\n Jusqu’ici, nous nous sommes int\u00e9ress\u00e9 aux fichiers de donn\u00e9es, mais des informations sensibles peuvent se trouver aussi dans d’autres fichiers, comme les fichiers de sauvegardes, les fichiers de log (log d’erreur, log de requ\u00eates lentes), les journaux binaires …<\/p>\n Les journaux binaires:<\/strong> Comme pour TDE, le chiffrement de journaux binaires est bas\u00e9 sur une hi\u00e9rarchie de cl\u00e9s avec une BINLOG MASTER KEY qui chiffre les binlogs, ce qui n\u00e9cessite la mise en place d’un keyring et d’activer l’option binlog_encryption<\/em>:<\/p>\n Le dernier binlog g\u00e9n\u00e9r\u00e9 est bien encrypt\u00e9. La lecture d\u2019un binlog n\u2019est plus possible directement via mysqlbinlog:<\/p>\n Pour pouvoir le d\u00e9coder, il faudra forcer son interpr\u00e9tation par le serveur : il devra lire le binlog, le d\u00e9coder et renvoyer le r\u00e9sultat, en d\u00e9tournant l’utilisation de l’option –read-on-remote-server<\/em> sur le serveur local:<\/p>\n Le probl\u00e8me \u00e9vident que cela pose, c’est qu’on ne pourra lire le contenu d’un binlog sans avoir un serveur d\u00e9marr\u00e9 \u00e0 c\u00f4t\u00e9, avec le keyring charg\u00e9 en m\u00e9moire. mysqlbinlog dans ce cas devient beaucoup moins souple d’utilisation lorsque les journaux binaires sont chiffr\u00e9s. <\/p>\n Autres fichiers:<\/strong> La plupart des normes conseillent voire imposent la rotation r\u00e9guli\u00e8re des cl\u00e9s. Dans le cas de TDE, il faudra effectuer la rotation \u00e0 la fois sur la INNODB MASTER KEY pour les fichiers de donn\u00e9es et la BINLOG MASTER KEY pour les journaux binaires. <\/p>\n Pour des raisons \u00e9videntes de performance, la rotation de la master key r\u00e9encrypte simplement la cl\u00e9 dans l\u2019ent\u00eate de chaque tablespace, elle ne rechiffre pas tout le tablespace. La rotation de la binlog master key quant \u00e0 elle permet de r\u00e9encrypter le fichier de mot de passe associ\u00e9 \u00e0 chaque binlog. Elle ne r\u00e9encrypte pas tous les binlogs. <\/p>\n En performance:<\/strong> Le chiffrement applicatif est nettement plus concern\u00e9 par les impacts, notamment via l’utilisation de cl\u00e9s asym\u00e9triques, car il n\u00e9cessite un d\u00e9chiffrement \u00e0 chaque appel, par chaque client, donc l\u2019impact sur le serveur est \u00e0 balancer avec le volume de donn\u00e9es \u00e0 d\u00e9chiffrer. Exemple avec d\u00e9chiffrement asym\u00e9trique d\u20191 ou 2 colonnes, et comparaison avec la m\u00eame table sans chiffrement: Une remarque aussi sur les indexes dans le cas du chiffrement applicatif, c’est la valeur chiffr\u00e9e qui est index\u00e9e, donc l’index ne pourra jamais \u00eatre utilis\u00e9 pour r\u00e9soudre un pr\u00e9dicat :<\/p>\n Avec TDE en revanche, l’index se base sur la valeur d\u00e9crypt\u00e9e donc ce la ne posera pas de probl\u00e8me :<\/p>\n En volum\u00e9trie:<\/strong> Dans le cas de TDE, nous avons vu plus haut que les donn\u00e9es d’un dump sont en clair, et qu’il n’existe pas d’outil natif pour lier la MASTER KEY au fichier dump g\u00e9n\u00e9r\u00e9. En revanche dans le cas du chiffrement applicatif, nous avons vu que la donn\u00e9e est chiffr\u00e9e de bout en bout, donc dans ce cas le dump est prot\u00e9g\u00e9. <\/p>\n Pour ce qui est des backups physiques (MySQL Enterprise Backup, MariaBackup), il faudra utiliser une option pour sp\u00e9cifier de crypter le backup et d’embarquer le keyring avec. Par d\u00e9faut, cette option –encrypted-password <\/em>attend un mot de passe sur la ligne de commande, ce qui n’est pas l’id\u00e9al, le mieux (le moins pire, en r\u00e9alit\u00e9) \u00e9tant de cr\u00e9er une section [mysqlbackup]<\/em> dans le fichier de configuration de mysqld et de renseigner la cl\u00e9 dedans:<\/p>\n Attention si une rotation de cl\u00e9 intervient pendant le backup ou entre 2 backups, les backups ant\u00e9rieurs seront rendus inutilisables par la suite, donc il vaut mieux planifier les rotations de cl\u00e9s juste avant de lancer un backup full…<\/p>\n Le top du chiffrement reste encore aujourd’hui pour MySQL seulement un voeu pieux: le chiffrement c\u00f4t\u00e9 client permet de prot\u00e9ger les donn\u00e9es de bout en bout, tout en rendant l’acc\u00e8s \u00e0 ces donn\u00e9es transparent pour l’application. Le meilleur des mondes en quelque sorte: Si on fait le bilan des avantages et inconv\u00e9nients de chaque solution, crit\u00e8re par crit\u00e8re: Si votre objectif est de tout crypter sans vous poser de question, alors TDE semble la bonne approche mais elle ne suffira pas. Il faudra lui adjoindre une bonne politique de gestion des privil\u00e8ges en appliquant le need to know<\/em>, un chiffrement des connexions entre client et serveur, et une protection des fichiers type dump, et journaux binaires. <\/p>\n Si vous savez pr\u00e9cis\u00e9ment quelles colonnes chiffrer, qu’elles ne sont pas nombreuses, vous pouvez adopter une approche \u00e0 base de chiffrage applicatif \u00e0 cl\u00e9 sym\u00e9trique pour limiter la casse en termes de performance et volum\u00e9trie. Tout en ayant \u00e0 l’esprit qu’il y aura un co\u00fbt en termes d’adaptation c\u00f4t\u00e9 applicatif. <\/p>\n Et pour monter une solution vraiment s\u00e9rieuse, vous ne pourrez pas vous passer d’un KMS, car sans cela, il faudra toujours stocker une cl\u00e9 en clair dans un fichier quelque part. Ce serait comme de mettre un lecteur d’empreinte digitale \u00e0 l’entr\u00e9e et laisser la porte du fond ouverte… <\/p>\n En attendant le chiffrement c\u00f4t\u00e9 client pour les bases on-prem<\/em>, on pourra se pencher du c\u00f4t\u00e9 de GCP Cloud SQL <\/a> qui a d\u00e9velopp\u00e9 sa propre couche cliente capable de prendre en charge une telle fonctionnalit\u00e9. Raison de plus pour passer sur GCP<\/a> ?<\/p>\n A bient\u00f4t ! C’est un sujet d’actualit\u00e9, d’ailleurs d\u00e9but mars l’ANSSI a r\u00e9\u00e9valu\u00e9 son estimation de la menace cyber au regard des \u00e9v\u00e8nements en Ukraine, et invit\u00e9 les entreprises fran\u00e7aises \u00e0 renforcer leur posture de s\u00e9curit\u00e9. Le vol de donn\u00e9es est un des… Continuer la lecture Solutions principales en th\u00e9orie:<\/h2>\n
\nLe principe est de chiffrer les donn\u00e9es sur disque, pour se pr\u00e9munir du vol de fichiers, \u00e0 travers la solution InnoDB Data-at-rest Encryption<\/a> en \u00e9dition communautaire, ou Transparent Data Encryption<\/a> (aka<\/em> TDE<\/strong>) en \u00e9dition Enterprise. La fonctionnalit\u00e9 existe depuis plusieurs ann\u00e9es, mais je recommande de migrer au minimum vers les derni\u00e8res versions de MySQL et MariaDB pour \u00e9viter les \u00e9cueils des premi\u00e8res impl\u00e9mentations.<\/p>\n
\n– Depuis le serveur MySQL lui-m\u00eame.
\n– Sur le r\u00e9seau entre le client et le serveur.
\n– Dans l’application. <\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/03\/tde1-1024x590.png\")
<\/p>\n
\nIl consiste \u00e0 ne chiffrer que les colonnes qui sont \u00e9ligibles. Plus chirurgical, mais ce ne sera pas la solution adapt\u00e9e s’il faut chiffrer toute la base. Dans ce cas, la colonne est stock\u00e9e chiffr\u00e9e sur disque, et ne pourra \u00eatre d\u00e9chiffr\u00e9e qu’en fournissant la cl\u00e9 qui a permis l’encryption. Sans ce s\u00e9same, un fouillis de chiffres et lettres illisibles.
\nCette solution couvre donc plus de cas de figure que TDE car sans cl\u00e9, les donn\u00e9es restent chiffr\u00e9es de bout en bout. Il suffit donc de ne donner la cl\u00e9 qu’aux applications \/ personnes autoris\u00e9es:<\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/03\/column1-1024x602.png\")
<\/p>\nTransparent Data Encryption:<\/h2>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/03\/tde2-1024x622.png\")
<\/p>\n\r\n$ mkdir \/var\/lib\/mysql\/mysql_keyring_encrfile\r\n$ chown -R mysql:mysql \/var\/lib\/mysql\/mysql_keyring_encrfile\r\n$ chmod 750 \/var\/lib\/mysql\/mysql_keyring_encrfile\r\n<\/pre>\n
\r\n$ vi \/etc\/mysql\/mysql.conf.d\/mysqld.cnf\r\n(...)\r\n# KMIP KEYRING_ENCRYPTED_FILE\r\nearly-plugin-load=keyring_encrypted_file.so\r\nkeyring_encrypted_file_data=\/var\/lib\/mysql\/mysql_keyring_encrfile\/keyring-encrypted\r\nkeyring_encrypted_file_password=************************\r\n<\/pre>\n
\r\n$ chmod og-rwx \/etc\/mysql\/mysql.conf.d\/mysqld.cnf\r\n$ systemctl restart mysql\r\n<\/pre>\n
\r\n$ mysql --login-path=local \\\r\n\t--execute="select PLUGIN_NAME, PLUGIN_VERSION, PLUGIN_STATUS \r\n-> from information_schema.plugins \r\n-> where PLUGIN_NAME = 'keyring_encrypted_file\u2019;"\r\n\r\n+------------------------+----------------+---------------+\r\n| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_STATUS |\r\n+------------------------+----------------+---------------+\r\n| keyring_encrypted_file | 1.0 | ACTIVE |\r\n+------------------------+----------------+---------------+\r\n<\/pre>\n
\r\nmysql> alter table payment_method_encrypted ENCRYPTION='Y' ;\r\nQuery OK, 122 rows affected (0.06 sec)\r\nRecords: 122 Duplicates: 0 Warnings: 0\r\n\r\nmysql> select * from payment_method_encrypted ;\r\n+-------------+-----------------+---------------------+-----+\r\n| customer_id | credit_card | expiration_date | CCV |\r\n+-------------+-----------------+---------------------+-----+\r\n| 103 | 586792408103868 | 2020-09-13 19:39:13 | 62 |\r\n| 112 | 191648252486855 | 2020-09-13 19:39:13 | 13 |\r\n| 114 | 775979657448809 | 2020-09-13 19:39:13 | 56 |\r\n| 119 | 927510594881615 | 2020-09-13 19:39:13 | 78 |\r\n| 121 | 226470357949352 | 2020-09-13 19:39:13 | 36 |\r\n(...)\r\n<\/pre>\n
\r\n$ strings payment_method_encrypted.ibd | more\r\ncffb97d4-1474-11ec-b390-00163e74e536\r\nT&xNn\r\nU\\v4\r\n>xS{& \r\nsMPrP\r\nBz|&_;Rl\r\n#?d:\r\n,i&xr_\r\n'B' y\r\n(...)\r\n<\/pre>\nChiffrement applicatif avec cl\u00e9s sym\u00e9triques \/ asym\u00e9triques:<\/h2>\n
\n– Chiffrement sym\u00e9trique<\/em> : on chiffre et d\u00e9chiffre avec la m\u00eame cl\u00e9. Moins s\u00e9curis\u00e9, mais l’impact en performance est ma\u00eetris\u00e9.
\n– Chiffrement asym\u00e9trique<\/em> : le bon vieux principe des cl\u00e9s priv\u00e9es \/ publiques<\/a>, plus s\u00e9curis\u00e9, par contre avec un impact en performance \/ volum\u00e9trie qui fait r\u00e9fl\u00e9chir (voir plus loin). <\/p>\n\r\nINSTALL PLUGIN keyring_udf SONAME 'keyring_udf.so';\r\nCREATE FUNCTION keyring_key_generate RETURNS INTEGER SONAME 'keyring_udf.so';\r\nCREATE FUNCTION keyring_key_fetch RETURNS STRING SONAME 'keyring_udf.so';\r\nCREATE FUNCTION keyring_key_length_fetch RETURNS INTEGER SONAME 'keyring_udf.so';\r\nCREATE FUNCTION keyring_key_type_fetch RETURNS STRING SONAME 'keyring_udf.so';\r\nCREATE FUNCTION keyring_key_store RETURNS INTEGER SONAME 'keyring_udf.so';\r\nCREATE FUNCTION keyring_key_remove RETURNS INTEGER SONAME 'keyring_udf.so\u2019;\r\n--\r\nCREATE FUNCTION asymmetric_decrypt RETURNS STRING SONAME 'openssl_udf.so';\r\nCREATE FUNCTION asymmetric_derive RETURNS STRING SONAME 'openssl_udf.so';\r\nCREATE FUNCTION asymmetric_encrypt RETURNS STRING SONAME 'openssl_udf.so';\r\nCREATE FUNCTION asymmetric_sign RETURNS STRING SONAME 'openssl_udf.so';\r\nCREATE FUNCTION asymmetric_verify RETURNS INTEGER SONAME 'openssl_udf.so';\r\nCREATE FUNCTION create_asymmetric_priv_key RETURNS STRING SONAME 'openssl_udf.so';\r\nCREATE FUNCTION create_asymmetric_pub_key RETURNS STRING SONAME 'openssl_udf.so';\r\nCREATE FUNCTION create_dh_parameters RETURNS STRING SONAME 'openssl_udf.so';\r\nCREATE FUNCTION create_digest RETURNS STRING SONAME 'openssl_udf.so';\r\n<\/pre>\n
\nOn ne peut pas utiliser de keyring ni local ni sous forme de KMS, donc il faut trouver le moyen de stocker la cl\u00e9 quelque part, encore un probl\u00e8me de plus \u00e0 g\u00e9rer… On utilisera aes_encrypt()<\/em> \/ aes_decrypt()<\/em> pour chiffrer \/ d\u00e9chiffrer dans notre exemple:<\/p>\n\r\nmysql> desc payment_method ;\r\n+-----------------+----------+------+-----+---------+-------+\r\n| Field | Type | Null | Key | Default | Extra |\r\n+-----------------+----------+------+-----+---------+-------+\r\n| customer_id | int | NO | PRI | NULL | |\r\n| credit_card | char(16) | NO | PRI | NULL | |\r\n| expiration_date | datetime | NO | | NULL | |\r\n| CCV | smallint | NO | | NULL | |\r\n+-----------------+----------+------+-----+---------+-------+\r\n\r\nmysql> -- -- Stocker la cl\u00e9 qq part !!\r\nmysql> set @key_str = sha2('***************************************',512) ;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> set @init_vector = random_bytes(16) ;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> create table payment_method_cconly_encrypted_sym \r\n\tas select customer_id \r\n\t, aes_encrypt(credit_card,@key_str,@init_vector) as credit_card\r\n\t, expiration_date \r\n\t,CCV \r\n\tfrom payment_method;\r\nQuery OK, 122 rows affected, 122 warnings (0.03 sec)\r\nRecords: 122 Duplicates: 0 Warnings: 122\r\n\r\nmysql> desc payment_method_cconly_encrypted_sym ;\r\n+-----------------+---------------+------+-----+---------+-------+\r\n| Field | Type | Null | Key | Default | Extra |\r\n+-----------------+---------------+------+-----+---------+-------+\r\n| customer_id | int | NO | | NULL | |\r\n| credit_card | varbinary(32) | YES | | NULL | |\r\n| expiration_date | datetime | NO | | NULL | |\r\n| CCV | smallint | NO | | NULL | |\r\n+-----------------+---------------+------+-----+---------+-------+\r\n<\/pre>\n\r\nmysql> select * from payment_method_cconly_encrypted_sym ;\r\n+-------------+------------------------------------+---------------------+-----+\r\n| customer_id | credit_card | expiration_date | CCV |\r\n+-------------+------------------------------------+---------------------+-----+\r\n| 103 | 0xA7D64A3EF3E7D099552B964722DFA851 | 2020-09-13 19:39:13 | 62 |\r\n| 112 | 0xFB07124853DFD549FA813DE8702C7993 | 2020-09-13 19:39:13 | 13 |\r\n| 114 | 0xC3462527596C47371FEF7EDB29CF5E8F | 2020-09-13 19:39:13 | 56 |\r\n(...)\r\n\r\nmysql> select customer_id\r\n\t, cast(aes_decrypt(credit_card, @key_str, @init_vector) as char(16))\r\n\t, expiration_date\r\n\t, CCV from payment_method_cconly_encrypted_sym;\r\n+-------------+--------------------------------------------------------------------+---------------------+-----+\r\n| customer_id | cast(aes_decrypt(credit_card, @key_str, @init_vector) as char(16)) | expiration_date | CCV |\r\n+-------------+--------------------------------------------------------------------+---------------------+-----+\r\n| 103 | 58679240810386 | 2020-09-13 19:39:13 | 62 |\r\n| 112 | 191648252486855 | 2020-09-13 19:39:13 | 13 |\r\n| 114 | 775979657448809 | 2020-09-13 19:39:13 | 56 |\r\n| 119 | 927510594881615 | 2020-09-13 19:39:13 | 78 |\r\n| 121 | 226470357949352 | 2020-09-13 19:39:13 | 36 |\r\n(...)\r\n<\/pre>\n
\r\n$ strings \/var\/lib\/mysql\/classicmodels\/payment_method_cconly_encrypted_sym.ibd | more\r\ninfimum\r\nsupremum\r\n_Zu;\r\nf2>Zt\r\n^pzvK\r\n?!'0uz9\r\n\\rKrU+\r\npn<g\r\nE3k&\r\nT#}(H0\r\nNd(M\r\nZ!\\jad\r\n(\u2026)\r\n\r\n$ mysqldump --login-path=local classicmodels > classicmodels.dmp\r\n$ more classicmodels.dmp\r\n(\u2026)\r\n\/*!40000 ALTER TABLE `payment_method_cconly_encrypted_sym` DISABLE KEYS *\/;\r\nINSERT INTO `payment_method_cconly_encrypted_sym` VALUES (103,_binary '\ufffd\\\ufffdJ>\\\ufffd\\\ufffd\u0419U+\ufffdG\\"\u07e8Q','2020-09-13 19:39:13',62),(112,_binary '\ufffdHS\\\ufffd\\\ufffdI\ufffd\ufffd=\\\ufffdp,y\ufffd','2020-09-13 19:39:13',13),(114,\r\n_binary '\\\ufffdF%\\'YlG7\\\ufffd~\\\ufffd)\\\ufffd^\ufffd','2020-09-13 19:39:13',56),(\u2026)\r\n<\/pre>\n
\nPour le chiffrement asym\u00e9trique, nous pouvons utiliser soit une cl\u00e9 comme pour l’exemple pr\u00e9c\u00e9dent, soit un keyring, ce qui semble \u00eatre un plus mais comme il faut quand m\u00eame pouvoir retenir la valeur de la cl\u00e9 qui permet d’ouvrir le keyring, le probl\u00e8me est simplement d\u00e9plac\u00e9. Ci-dessous nous cr\u00e9ons la paire de cl\u00e9s priv\u00e9e \/ publique, et chiffrons la table avec la cl\u00e9 publique. <\/p>\n\r\nmysql> SELECT keyring_key_store('*************************************', \r\n-> 'SECRET', create_asymmetric_priv_key('RSA', 2048)) as res;\r\n+----------+\r\n| res\t |\r\n+----------+\r\n| 1 |\r\n+----------+\r\nmysql> set @ppk = keyring_key_fetch('*************************************') ;\r\nmysql> set @pubkey = CREATE_ASYMMETRIC_PUB_KEY('RSA',@ppk) ;\r\nmysql> create table payment_method_cconly_encrypted_asym_keyr\r\n -> as\r\n -> select customer_id\r\n -> ,asymmetric_encrypt('RSA',credit_card,@ppk) as credit_card\r\n -> ,expiration_date\r\n -> ,CCV\r\n -> from payment_method ;\r\nQuery OK, 122 rows affected (0.24 sec)\r\nRecords: 122 Duplicates: 0 Warnings: 0\r\n<\/pre>\n\r\nmysql> select * from payment_method_cconly_encrypted_asym limit 3 \\G\r\n*************************** 1. row ***************************\r\n customer_id: 103\r\n credit_card: 0x7CE7DE6D248AECB4136499F8886C45EFB4C9FA2916BFAB4C4947AE2296A26A48DBF5963CCF4543751D48AFB41A6643CE0BE8A42DE39AFE591428C721E8A2B413224A1CB5D2CFE690FB5FB56D683BEC435A4065192900849114E8E42CE9C884BC597A94DCDB48FD7D460833147AABBC88D1077EB4BE3A48751462E5E12007E76C\r\nexpiration_date: 2020-09-13 19:39:13\r\n CCV: 62\r\n*************************** 2. row ***************************\r\n customer_id: 112\r\n credit_card: 0x65C0361273A511E367ABEF1BB84A5CEB3AEE0E85C6243AF2BC8E8D096DFA073058CF59A972DEF3BE94257EADDC8D237941167F263E77FC637BF0522A0FB987CC605D6680BF7592BC4488ECE48A476E62018043C26ADF8EB0DC50200C03D56B06A7681E06DB9DC891ED97C5C035863A17A3B9C8BD1ACEF4A2D634354EDE84135D\r\nexpiration_date: 2020-09-13 19:39:13\r\n CCV: 13\r\n*************************** 3. row ***************************\r\n customer_id: 114\r\n credit_card: 0x34CA32F38C722E8AF1BCAA9C7D60412678C4959702577705FFB98FA346A6BF897A3558CA13BD94B4B3BC039DAB036642EBE4660813E1F2FD366DC654C4334BCDB8AC0C9B641B0F1C9F5C3D576C791107F3C20C1CE3427768B386EA5741E338141E60A858B6B4EB883DB614150607BBA299938AA0A584FC09AE896F952E7B853B\r\nexpiration_date: 2020-09-13 19:39:13\r\n CCV: 56\r\n3 rows in set (0.00 sec)\r\n<\/pre>\n
\r\nmysql> desc payment_method_cconly_encrypted_asym ;\r\n+-----------------+----------+------+-----+---------+-------+\r\n| Field | Type | Null | Key | Default | Extra |\r\n+-----------------+----------+------+-----+---------+-------+\r\n| customer_id | int | NO | | NULL | |\r\n| credit_card | blob | NO | | NULL | |\r\n| expiration_date | datetime | NO | | NULL | |\r\n| CCV | smallint | NO | | NULL | |\r\n+-----------------+----------+------+-----+---------+-------+\r\n4 rows in set (0.00 sec)\r\n\r\nmysql> select customer_id\r\n -> , cast(asymmetric_decrypt('RSA',credit_card, @pubkey) as char(16)) as credit_card\r\n -> , expiration_date\r\n -> , CCV from payment_method_cconly_encrypted_asym;\r\n+-------------+-----------------+---------------------+-----+\r\n| customer_id | credit_card | expiration_date | CCV |\r\n+-------------+-----------------+---------------------+-----+\r\n| 103 | 58679240810386 | 2020-09-13 19:39:13 | 62 |\r\n| 112 | 191648252486855 | 2020-09-13 19:39:13 | 13 |\r\n| 114 | 775979657448809 | 2020-09-13 19:39:13 | 56 |\r\n(...)\r\n<\/pre>\n\r\nmysql> CREATE DEFINER = 'root'@'localhost' FUNCTION `appdecrypt`() \r\n -> RETURNS TEXT(500) deterministic SQL SECURITY DEFINER \r\n -> RETURN RTRIM(CREATE_ASYMMETRIC_PUB_KEY('RSA',\r\n -> keyring_key_fetch('****************************************')));\r\n\r\nmysql> grant execute on function appdecrypt to 'appuser'@'localhost' ;\r\nmysql> flush privileges ;\r\n\r\n$ mysql --user=appuser --socket=\/var\/run\/mysqld\/mysqld.sock \\\r\n --password=****** classicmodels\r\n\r\nmysql> set @pubkey = appdecrypt() ;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> select customer_id , cast(asymmetric_decrypt('RSA',credit_card,@pubkey) as char(16)) \r\n-> as credit_card ,expiration_date ,CCV from payment_method_cconly_encrypted_asym_keyr limit 1 ;\r\n+-------------+-----------------+---------------------+-----+\r\n| customer_id | credit_card | expiration_date | CCV |\r\n+-------------+-----------------+---------------------+-----+\r\n| 103 | 58679240810386 | 2020-09-13 19:39:13 | 62 |\r\n+-------------+-----------------+---------------------+-----+\r\n<\/pre>\nAutres sources de donn\u00e9es \u00e0 chiffrer:<\/h2>\n
\nLes journaux binaires peuvent contenir soit les requ\u00eates de modification en mode STATEMENT <\/em>ou MIXED<\/em>, ou les lignes au format base64 qui peuvent \u00eatre d\u00e9cod\u00e9es par l’option –decode-rows<\/em> de mysqlbinlog<\/em>, donc ils doivent \u00eatre consid\u00e9r\u00e9s comme une source potentielle \u00e0 prot\u00e9ger. C’est chose possible mais seulement depuis les versions r\u00e9centes <\/a>de MySQL. <\/p>\n\r\n$ vi \/etc\/mysql\/mysql.conf.d\/mysqld.cnf \r\n(...)\r\nearly-plugin-load=keyring_encrypted_file.so\r\nkeyring_encrypted_file_data=\/var\/lib\/mysql\/mysql_keyring_encrfile\/keyring-encrypted\r\nkeyring_encrypted_file_password=*********\r\nbinlog_encryption=on\r\n\r\n$ systemctl restart mysql.service\r\n<\/pre>\n
\r\nmysql> show binary logs ;\r\n+---------------+-----------+-----------+\r\n| Log_name | File_size | Encrypted |\r\n+---------------+-----------+-----------+\r\n| binlog.000001 | 504 | No |\r\n| binlog.000002 | 154604 | No |\r\n| binlog.000003 | 179 | No |\r\n(\u2026)\r\n| binlog.000021 | 64159 | Yes |\r\n+---------------+-----------+-----------+\r\n<\/pre>\n
\r\n$ mysqlbinlog binlog.000021 | more\r\nERROR: Reading encrypted log files directly is not supported.\r\n# The proper term is pseudo_replica_mode, but we use this compatibility alias\r\n# to make the statement usable on server versions 8.0.24 and older.\r\n\/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*\/;\r\n\/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*\/;\r\nDELIMITER \/*!*\/;\r\nSET @@SESSION.GTID_NEXT= 'AUTOMATIC' \/* added by mysqlbinlog *\/ \/*!*\/;\r\nDELIMITER ;\r\n# End of log file\r\n<\/pre>\n
\r\n$ mysqlbinlog --login-path=local \\\r\n --read-from-remote-server binlog.000021\r\n(\u2026)\r\n# at 319\r\n#210915 8:10:48 server id 1 end_log_pos 604 CRC32 0x9e86fd59 \t\r\nQuery\tthread_id=9\texec_time=0\terror_code=0\r\nuse `classicmodels`\/*!*\/;\r\nSET TIMESTAMP=1631693448\/*!*\/;\r\n\/*!80013 SET @@session.sql_require_primary_key=0*\/\/*!*\/;\r\nCREATE TABLE `payment_method_tde_plus_encr` (\r\n `customer_id` int NOT NULL,\r\n `credit_card` varbinary(32) DEFAULT NULL,\r\n `expiration_date` datetime NOT NULL,\r\n `CCV` smallint NOT NULL(\u2026)\r\n<\/pre>\n
\nPour les autres fichiers, il n’existe rien de natif pour chiffrer, donc s’ils sont consid\u00e9r\u00e9s comme cibles potentielles, il faudra trouver d’autres moyens de les prot\u00e9ger. <\/p>\nGestion des cl\u00e9s:<\/h2>\n
\r\nALTER INSTANCE ROTATE MASTER KEY;\r\nALTER INSTANCE ROTATE BINLOG MASTER KEY; \r\n<\/pre>\n
Impact sur la performance et la volum\u00e9trie:<\/h2>\n
\nConcernant TDE, l’impact sera r\u00e9duit en lecture tant que le buffer pool InnoDB est suffisamment bien dimensionn\u00e9 pour \u00e9viter au maximum de faire des lectures disques. En revanche, sur un workload plut\u00f4t transactionnel, attention aux effets de bord sur les checkpoints et les transactions. Il n’y a que peu de benches publi\u00e9s montrant un r\u00e9el impact \u00e0 l’heure actuelle, on peut citer toutefois des travaux de recherche de 2 \u00e9tudiants de l’universit\u00e9 de Lule\u00e5 en Su\u00e8de<\/a>:
\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/03\/lulea1-1024x342.png\")
\nsource: Th\u00e8se 2019 Lule\u00e5 University of Technology (Feidias Moulianitakis, Konstantinos Asimakopoulos)<\/em><\/p>\n
\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/03\/column2-1024x418.png\")
<\/p>\n\r\n-- Elapsed : 0.03s\r\nmysql> explain select count(1) from TESTLOAD \r\n -> where password like '%c72c%' \\G\r\n*************************** 1. row ***************************\r\n id: 1\r\n select_type: SIMPLE\r\n table: TESTLOAD\r\n partitions: NULL\r\n type: index\r\npossible_keys: NULL\r\n key: IX_PASSWD\r\n key_len: 258\r\n ref: NULL\r\n rows: 40962\r\n filtered: 11.11\r\n Extra: Using where; Using index\r\n1 row in set, 1 warning (0.00 sec)\r\n\r\n-- Elapsed : 1.82s\r\nmysql> explain select count(1) from TESTLOAD_ENCRYPTED \r\n -> where cast(asymmetric_decrypt('RSA',password,@pubkey) \r\n -> as char(256)) like '%c72c%'\\G\r\n*************************** 1. row ***************************\r\n id: 1\r\n select_type: SIMPLE\r\n table: TESTLOAD_ENCRYPTED\r\n partitions: NULL\r\n type: ALL\r\npossible_keys: NULL\r\n key: NULL\r\n key_len: NULL\r\n ref: NULL\r\n rows: 39624\r\n filtered: 100.00\r\n Extra: Using where\r\n1 row in set, 1 warning (0.00 sec)\r\n<\/pre>\n\r\n-- Elapsed : 0.029s\r\nmysql> explain select count(1) from TESTLOAD_TDE \r\n -> where password like '%c72c%' \\G\r\n*************************** 1. row ***************************\r\n id: 1\r\n select_type: SIMPLE\r\n table: TESTLOAD_TDE\r\n partitions: NULL\r\n type: index\r\npossible_keys: NULL\r\n key: IX_PASSWD\r\n key_len: 258\r\n ref: NULL\r\n rows: 41031\r\n filtered: 11.11\r\n Extra: Using where; Using index\r\n1 row in set, 1 warning (0.00 sec)\r\n<\/pre>\n
\nQuant \u00e0 l’impact sur la volum\u00e9trie, c’est aussi l’encryption avec cl\u00e9s asym\u00e9triques qui sera la plus impactante, en raison de la conversion de type vers blob:
\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/03\/encrypt_volpb.png\")
\nLa compression n’est plus du tout efficace et elle ne permet plus de faire gagner de la place. Il faudra donc pr\u00e9voir de la capacit\u00e9 disque en plus en fonction de la quantit\u00e9 de donn\u00e9es \u00e0 chiffrer…<\/p>\nImpact sur les sauvegardes:<\/h2>\n
\r\n$ cat \/etc\/mysql\/mysql.conf.d\/mysqld.cnf \r\n(...)\r\n[mysqlbackup]\r\nencrypt-password=*********\r\n\r\n$ mysqlbackup --defaults-file=\/etc\/mysql\/mysql.conf.d\/mysqld.cnf \\\r\n\t--login-path=local --backup-image=\/var\/lib\/mysql\/backups\/mysql.20210914.mbi \\\r\n\t--backup-dir=\/root\/backup-tmp backup-to-image\r\n(...)\r\n210914 12:00:37 MAIN INFO: The server's active keyring is 'keyring_encrypted_file'.\r\n210914 12:00:37 MAIN INFO: The keyring file is located at '\/var\/lib\/mysql\/mysql_keyring_encrfile\/keyring-encrypted', size 613.\r\n210914 12:00:37 MAIN INFO: The keyring file is copied to '\/root\/backup-tmp\/meta\/keyring_kef'.\r\n210914 12:00:37 MAIN INFO: Initialized keyring with 4 keys.\r\n(...)\r\n210914 12:00:37 MAIN INFO: This backup includes a keyring.\r\n(...)\r\n210914 12:00:38 MAIN INFO: Full Image Backup operation completed successfully.\r\n210914 12:00:38 MAIN INFO: Backup image created successfully.\r\n210914 12:00:38 MAIN INFO: Image Path = \/var\/lib\/mysql\/backups\/mysql.20210914.mbi\r\n210914 12:00:38 MAIN INFO: MySQL binlog position: filename binlog.000021, position 412\r\n(\u2026)\r\n<\/pre>\n
La solution ultime : chiffrement c\u00f4t\u00e9 client:<\/h2>\n
\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/03\/CSE1-1024x604.png\")
\nDans ce cas, c’est \u00e0 la charge de la couche cliente de chiffrer les donn\u00e9es lorsqu’elles sont inject\u00e9es en base. C’est un peu comme un chiffrement applicatif, sauf que l’intelligence du d\u00e9chiffrement r\u00e9side dans la couche cliente MySQL, et pas directement dans l’application, ce qui implique que l’on n’a plus de code \u00e0 modifier. Malheureusement, ce syst\u00e8me n’existe pas encore pour MySQL. Pour les concurrents on peut citer quelques exemples comme Always Encrypted sur SQL Server<\/a>, ou Client-Side FLE sur MongoDB<\/a>. Attention tout comme le chiffrement aplicatif, la granularit\u00e9 s’applique \u00e0 la colonne, donc il reste peu commode pour chiffrer toute une base. <\/p>\nTableau Bilan et conclusion:<\/h2>\n
\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/03\/encryption_table-1024x621.png\")
\nOn l’aura compris, il n’existe donc pas (encore) la solution universelle, chacune ayant ses avantages et inconv\u00e9nients qu’il faudra bien mesurer avant de se lancer dans une telle aventure. <\/p>\n
\n~David<\/p>\n<\/a><\/a><\/a>","protected":false},"excerpt":{"rendered":"