L'idée derrière l'audit de base de données est de savoir qui a accédé aux tables de votre base de données et quand, ainsi que les modifications qui y ont été apportées. Il s'agit non seulement d'une exigence minimale standard pour toute application d'entreprise, mais également d'une exigence légale pour de nombreux domaines tels que la banque et la cybersécurité. Les journaux d'audit de base de données sont essentiels pour investiguer sur toutes sortes de problèmes d'application tels que les accès non autorisés, les modifications de configuration et bien d'autres choses encore.
Dans le blog d'aujourd'hui, nous allons ajouter un log à la base de données MySQL Sakila Sample Database afin d’auditer la table Location. C'est une table-clé car la base de données représente les processus métiers d'un magasin de location de DVD.
Créer une table afin d’enregistrer le journal d’audit
Idéalement, il est préférable d'avoir une table de d'audit pour chaque table auditée. Voici l'instruction DDL pour créer la table de journal d'audit pour la table de location :
create table rental_audit_log( id int NOT NULL AUTO_INCREMENT, rental_id int NOT NULL, old_values varchar(255), new_values varchar(255), done_by varchar(255) NOT NULL, done_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) );
Dans Navicat, vous pouvez également utiliser le Concepteur de tables pour spécifier tous les champs et attributs sans avoir à écrire une instruction DDL :
Créer les triggers d’audit
Nous devrons créer 3 déclencheurs pour insérer des enregistrements dans la table rental_audit_log, un pour chaque type d'instruction DML effectuée sur la table de location (INSERT, UPDATE et DELETE).
Trigger AFTER INSERT
Les instructions INSERT sur la table de location seront interceptées par rental_insert_audit_trigger. Nous allons le faire se déclencher APRÈS les opérations d'insertion et fournir toutes les nouvelles données sous forme de JSON_OBJECT. Dans Navicat, tous ces détails peuvent être fournis dans l'onglet Déclencheurs du Concepteur de tables :
Après avoir ajouté une nouvelle ligne à la table de location, nous pouvons également voir un nouvel enregistrement dans le rental_audit_log :
Trigger AFTER UPDATE
Les instructions UPDATE sur la table de location seront enregistrées par le rental_update_audit_trigger de la façon suivante :
Désormais, chaque fois qu'un enregistrement de location est mis à jour, rental_update_audit_trigger est exécuté et une ligne rental_audit_log sera créée pour capturer à la fois l'ancienne et la nouvelle valeur de l'enregistrement. Dans ce cas, nous pouvons voir que l'utilisateur robg a modifié la date de location de "2005-05-25 17:17:04" à "2005-05-31 19:47:04" :
Trigger AFTER DELETE
Pour traquer les instructions DELETE sur la table de location, nous allons créer location_delete_audit_trigger:
Dans ce cas, seule la colonne old_values est définie puisqu'il n'y a pas de nouvel état d'enregistrement. Ainsi la colonne new_values est vide dans la ligne rental_audit_log qui sera générée :
Ici, nous pouvons voir que l'utilisateur fsmith a supprimé l'enregistrement 1114 de la table location le 2023-03-22 à 08:46:07.
Réflexion finale sur les journaux d’audit en utilisant la méthode des triggers
Dans le blog d'aujourd'hui, nous avons ajouté un log à la base de données MySQL Sakila Sample Database pour auditer la table Location. Notre tableau de log comprenait les champs les plus courants audités. Certaines organisations en incluent d'autres, comme le type d'opération DML, tandis que certaines incluent uniquement les champs modifiés. C'est vraiment à chaque organisation de voir ce qui lui convient le mieux.
