Intéressé par des cours d'informatique en ligne ?
Visitez mon nouveau site
https://www.yesik.it !
Pour des raisons de traçabilité ou de sécurité, il s'avère parfois nécessaire de garder une trace des modifications effectuées dans une base de données. Bien sûr, pour assurer la fiabilité de ces informations, il est important que cette journalisation soit automatisée. Nous allons voir ici comment c'est possible avec Apache Derby.
Sommaire
Préambule
Pour commencer, voici la table qui nous servira de support. Celle-ci pourrait servir dans une application de gestion clientèle. Son rôle étant d'associer des clients à leur ligne de crédit autorisé (noté de A à F):
sh$ ${DERBY_HOME}/bin/ij
ij> CONNECT 'jdbc:derby:SalesManagement;create=true';
ij> CREATE TABLE Client (
> ID int GENERATED ALWAYS AS IDENTITY PRIMARY KEY NOT NULL,
> Name varchar(30) UNIQUE NOT NULL,
> Credit char(1) NOT NULL DEFAULT 'F'
> );
0 rows inserted/updated/deleted
Note:
Toutes les sections sont indépendantes – et on suppose à chaque fois que l'on repart de la table vierge telle qu'elle vient d'être crée!
Journaliser les insertions
Dans cette section, nous allons nous intéresser à la journalisation des insertions dans la table. Nous ne nous préoccuperons pas d'enregistrer d'autres événements comme la modification d'un enregistrement ou sa suppression.
Nous allons examiner deux solutions. Toutes deux reposent sur l'ajout d'une colonne dans la table, destinée à mémoriser le nom de l'utilisateur ayant ajouté l'enregistrement.
Pour commencer, nous examinerons la solution qui consiste à utiliser une valeur par défaut pour mémoriser le nom de l'utilisateur. Puis, après avoir vu à quel point cette solution est fragile, nous examinerons une solution un peu plus robuste utilisant un trigger.
Avec une valeur par défaut
Intéressons nous donc tout d'abord à la solution qui consiste à utiliser le nom de l'utilisateur comme valeur par défaut:
ALTER TABLE Client ADD COLUMN Owner varchar(30) DEFAULT USER; GRANT SELECT,INSERT ON Client TO PUBLIC; -- Tout le monde peut lire et ajouter des enregistrements à la table
Cela semble résoudre notre problème. En effet, une insertion enregistrera le nom de son auteur:
ij> CONNECT 'jdbc:derby:SalesManagement;user=sylvain'; ij> INSERT INTO APP.Client ( Name, Credit) VALUES ('IBM', 'B'); ij> SELECT * FROM APP.Client; ID |NAME |&|OWNER --------------------------------------------------------------------------- 1 |IBM |B|SYLVAIN 1 row selected
Le problème de cette solution est qu'elle est extrêmement fragile. Il suffit à l'utilisateur de fournir un nom lors de la création de l'enregistrement pour que toute notre traçabilité parte à l'eau:
ij> CONNECT 'jdbc:derby:SalesManagement;user=sylvain'; ij> INSERT INTO APP.Client (Name, Credit, Owner) > VALUES ('UnTrustedClient', 'A', 'Dilbert'); -- Hin, hin, hin! ij> SELECT * FROM APP.Client; ID |NAME |&|OWNER --------------------------------------------------------------------------- 1 |IBM |B|SYLVAIN 2 |UnTrustedClient |A|Dilbert 2 row selected
Comme vous le constatez, cette solution est vraiment des plus fragiles. Il est donc temps de passer à quelque chose d'un brin plus robuste.
Avec un trigger
Nous allons maintenant utiliser un TRIGGER (Dans une base de données, un TRIGGER est une action déclenchée lors de la modification d'une table.) pour enregistrer le nom de l'utilisateur après chaque ajout.
Ici encore, nous allons repartir de la table comme définie en préambule. Et à nouveau, nous allons lui ajouter une colonne destinée à stocker le nom de l'utilisateur. Par contre, cette fois-ci, nous n'utiliserons plus une valeur par défaut pour remplir cette colonne:
ALTER TABLE Client ADD COLUMN Owner varchar(30) DEFAULT NULL; GRANT INSERT,SELECT ON TABLE Client TO PUBLIC;
En effet, c'est maintenant un TRIGGER qui sera chargé d'enregistrer le nom de l'utilisateur après une insertion:
CREATE TRIGGER LogClientOwner AFTER INSERT ON Client REFERENCING NEW_TABLE AS NEW UPDATE Client SET Client.Owner = USER WHERE Client.ID IN (SELECT ID FROM NEW);
Dorénavant, lors d'une insertion de données dans la table, on obtient le résultat souhaité – même si l'utilisateur fournit une valeur pour le champ Owner:
sh$ ${DERBY_HOME}/bin/ij
ij version 10.5
ij> CONNECT 'jdbc:derby:SalesManagement;user=sylvain';
ij> INSERT INTO APP.Client (Name, Credit)
> VALUES ('IBM', 'B'),
> ('GOOGLE', 'A');
2 rows inserted/updated/deleted
ij> SELECT * FROM APP.Client;
ID |NAME |&|OWNER
---------------------------------------------------------------------------
1 |IBM |B|SYLVAIN
2 |GOOGLE |A|SYLVAIN
2 rows selected
sh$ ${DERBY_HOME}/bin/ij
ij version 10.5
ij> CONNECT 'jdbc:derby:SalesManagement;user=sonia';
ij> INSERT INTO APP.Client (Name, Credit, Owner)
> VALUES ('BULL', 'F', 'Dilbert');
1 row inserted/updated/deleted
ij> SELECT * FROM APP.Client;
ID |NAME |&|OWNER
---------------------------------------------------------------------------
1 |IBM |B|SYLVAIN
2 |GOOGLE |A|SYLVAIN
3 |BULL |F|SONIA
3 rows selected
Piège:
Il est important aussi pour la fiabilité de ce système que les utilisateurs n'aient pas la permission en modification, sinon il est enfantin de contourner l'audit:
UPDATE Client SET Credit = 'A', Owner = 'Dilbert' WHERE ID = 3; -- Hin, hin, hin!
Journaliser les insertions, modifications et suppressions
Journaliser l'ensemble des évènements sur une table (INSERT, UPDATE et DELETE) se révèle un rien plus complexe: en effet, puisque plusieurs événements peuvent concerner le même enregistrement, il s'avère nécessaire d'ajouter une table annexe pour stocker le journal des modifications.
Néanmoins, une fois la table créée, la journalisation à proprement parlée n'est à nouveau qu'une question de trigger:
La table d'audit
La table d'audit nous servira à mémoriser les informations pour chaque événement: date, type d'événement, enregistrement cible et utilisateur à l'origine de l'événement. Bien entendu, les utilisateurs ne devront avoir aucun accès à cette table (en tous cas, pas en modification):
CREATE TABLE ClientLog ( ID int GENERATED ALWAYS AS IDENTITY PRIMARY KEY NOT NULL, Date timestamp DEFAULT CURRENT_TIMESTAMP, Event varchar(10) NOT NULL, -- INSERT, UPDATE ou DELETE RowId int NOT NULL, -- id of the *modified* row UserName varchar(30) NOT NULL -- user that caused the event ); GRANT SELECT, INSERT, DELETE, UPDATE ON Client TO PUBLIC; -- Tout le monde peut modifier la table client REVOKE ALL PRIVILEGES ON ClientLog FROM PUBLIC; -- Mais l'accès au journal est restreint
Les triggers
Reste à ajouter les triggers. Oui: les triggers. En effet, nous allons auditer plusieurs types d'évènements:
-- Journalise les insertions dans la table des clients CREATE TRIGGER LogInsertClient AFTER INSERT ON Client REFERENCING NEW_TABLE AS NEW INSERT INTO ClientLog(Event, UserName, RowId) SELECT T.*, NEW.ID FROM (VALUES('INSERT', USER)) AS T, NEW;
-- Journalise les modifications dans la table des clients CREATE TRIGGER LogUpdateClient AFTER UPDATE ON Client REFERENCING NEW_TABLE AS NEW INSERT INTO ClientLog(Event, UserName, RowId) SELECT T.*, NEW.ID FROM (VALUES('UPDATE', USER)) AS T, NEW;
-- Journalise les suppressions dans la table des clients CREATE TRIGGER LogDeleteClient AFTER DELETE ON Client REFERENCING OLD_TABLE AS OLD INSERT INTO ClientLog(Event, UserName, RowId) SELECT T.*, OLD.ID FROM (VALUES('DELETE', USER)) AS T, OLD;
Piège:
Toute la traçabilité des modifications repose ici sur l'identifiant des clients (colonne ID).
Il ne faut donc pas qu'un utilisateur puisse modifier cet identifiant. Heureusement, dans Derby, une colonne marquée GENERATED ALWAYS ne peut pas être modifiée:
ij> UPDATE Client SET ID = 1000 WHERE ID = 1; ERROR 42Z23: Attempt to modify an identity column 'ID'.
Cependant, méfiez-vous de cette faille potentielle si vous envisagez de porter cette solution vers un autre système de gestion de bases de données!
Tests
On peut maintenant s'assurer du fonctionnement de l'ensemble:
ij> CONNECT 'jdbc:derby:SalesManagement;user=sylvain';
ij> INSERT INTO APP.Client (Name, Credit)
> VALUES ('GOOGLE', 'A'), ('BULL', 'F');
2 rows inserted/updated/deleted
ij> UPDATE APP.Client SET Credit = 'E' WHERE Name = 'BULL';
1 row inserted/updated/deleted
ij> CONNECT 'jdbc:derby:SalesManagement;user=sonia'; ij> DELETE FROM APP.Client WHERE Name = 'GOOGLE'; 1 row inserted/updated/deleted ij> SELECT * FROM APP.Client; ID |NAME |& -------------------------------------------- 2 |BULL |E 1 row selected ij> SELECT * FROM APP.ClientLog; ERROR 42502: User 'SONIA' does not have SELECT permission on column 'ID' of table 'APP'.'CLIENTLOG'.
ij> CONNECT 'jdbc:derby:SalesManagement;user=APP'; ij> SELECT * FROM ClientLog; ID |DATE |EVENT |ROWID |USERNAME -------------------------------------------------------------------------------------------- 1 |2009-08-22 00:00:11.116 |INSERT |1 |SYLVAIN 2 |2009-08-22 00:00:11.116 |INSERT |2 |SYLVAIN 3 |2009-08-22 00:00:50.028 |UPDATE |2 |SYLVAIN 4 |2009-08-22 00:01:42.408 |DELETE |1 |SONIA 4 rows selected
Et voilà: nous avons maintenant un petit espion dans la base de données qui surveille toutes les modifications de la table Client et les enregistre pour qu'elles soient exploitées par l'administrateur. Utilisateurs gare à vous: Big Brother is watching you!
