L’ « audit trail », littéralement « piste d’audit » est un audit destiné à pister les événements qui se passe dans un système. Dans une base de données, il est, la plupart du temps, destiné à vérifier ce qui s’est passé, notamment sur le plan des valeurs avant ou après la modification. Il peut à la fois servir pour la sécurité (qui à fait quoi ?), comme sur le plan fonctionnel (pourquoi cette valeur ?, À quelle date un tel changement ?…).
En dehors des outils lourds comme CDC (Change Data Capture) et Change Tracking (tous deux destiné à savoir quoi, donc fonctionnel) ou Database Audit (destiné à savoir qui, donc sécurité), voici une méthode basée sur un déclencheur et une seule et unique table, facile et rapide à mettre en Å“uvre et qui permet de tracer qui et quoi…
la principe est simple : chaque table reçoit un unique déclencheur lancé sur tous les événements de mise à jour (INSERT, UPDATE, DELETE) et concatène les images avant et après des données (tables inserted et deleted) puis les transforme en un unique document XML, quelque soit le nombre de lignes. Il est alors facile de stocker cette information das une ligne d’une table, accompagnée de métadonnées, notamment, date et heure de survenance, application cliente, compte de connexion, utilisateur SQL, machine hôte, login windows, etc…
Il ne reste plus qu’à lire la table de trace pour savoir qui à fait quoi et quand ! Comme les données sont sous forme XML, il est possible des les interroger par des requêtes SQL mêlant XQuery et XPath. Une astuce consistant à publier certaines vues pour les demandes de contrôle les plus fréquentes (en général quelques tables).
Les objets sont créé dans un schéma SQL de nom S_ADT et la table à scruter est la table S_ADT.T_MAJ.
Voici l’ensemble du code pour se faire…
1 – LA TABLE
Pour recueillir les données tracées
CREATE TABLE T_MAJ
(MAJ_ID BIGINT IDENTITY PRIMARY KEY,
MAJ_DHU DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
MAJ_DHL DATETIME2 DEFAULT SYSDATETIME() NOT NULL,
MAJ_CNX sysname DEFAULT SYSTEM_USER,
MAJ_USR sysname DEFAULT USER,
MAJ_APPLICATION NVARCHAR(128),
MAJ_HOST NVARCHAR(128),
MAJ_WINLOGIN NVARCHAR(128),
MAJ_WINDOMAIN NVARCHAR(128),
MAJ_SCHEMA NVARCHAR(128),
MAJ_OBJECT NVARCHAR(128),
MAJ_TYPE CHAR(1),
MAJ_DATA XML);
GO
2 – LE TRIGGER
Exemple de création de trigger sur une table pour tracer tous les événements
ON ???
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON;
DECLARE @S sysname, @O sysname,
@A NVARCHAR(128), @H NVARCHAR(128), @L NVARCHAR(128), @D NVARCHAR(128);
SELECT @S = s.name, @O = o.name
FROM sys.objects AS o
JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
JOIN sys.objects AS t
ON t.parent_object_id = o.object_id
WHERE t.object_id = @@PROCID;
SELECT @A program_name, @H host_name, @L login_name, @D domain_name
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;
-- cas d'insertion
IF NOT EXISTS(SELECT * FROM deleted)
BEGIN
INSERT INTO S_ADT.T_MAJ (MAJ_SCHEMA, MAJ_OBJECT, MAJ_TYPE, MAJ_DATA,
MAJ_APPLICATION, MAJ_HOST, MAJ_WINLOGIN, MAJ_WINDOMAIN)
SELECT @S, @O, 'I',
CAST((SELECT * FROM inserted FOR XML AUTO, ELEMENTS, ROOT('insert'), BINARY BASE64) AS XML),
@A, @H, @L, @D;
RETURN;
END;
-- cas de la suppression
BEGIN
IF NOT EXISTS(SELECT * FROM inserted)
INSERT INTO S_ADT.T_MAJ (MAJ_SCHEMA, MAJ_OBJECT, MAJ_TYPE, MAJ_DATA,
MAJ_APPLICATION, MAJ_HOST, MAJ_WINLOGIN, MAJ_WINDOMAIN)
SELECT @S, @O, 'D',
CAST((SELECT * FROM deleted FOR XML AUTO, ELEMENTS, ROOT('delete'), BINARY BASE64) AS XML),
@A, @H, @L, @D;
RETURN;
END;
-- cas de la modification
INSERT INTO S_ADT.T_MAJ (MAJ_SCHEMA, MAJ_OBJECT, MAJ_TYPE, MAJ_DATA,
MAJ_APPLICATION, MAJ_HOST, MAJ_WINLOGIN, MAJ_WINDOMAIN)
SELECT @S, @O, 'I',
CAST((SELECT *
FROM (SELECT 'OLD' AS _old_, * FROM deleted
UNION ALL
SELECT 'NEW' AS _new_, * FROM inserted) AS _auditTrail_
FOR XML AUTO, ELEMENTS, ROOT('update'), BINARY BASE64) AS XML),
@A, @H, @L, @D;
GO
Pour vous faciliter la tâche, quelques scripts complémentaires…
3 – Un batch de création des triggers sur toutes les tables…
'CREATE TRIGGER E_auditTrail_~1
ON ~2
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON;
DECLARE @S sysname, @O sysname,
@A NVARCHAR(128), @H NVARCHAR(128), @L NVARCHAR(128), @D NVARCHAR(128);
SELECT @S = s.name, @O = o.name
FROM sys.objects AS o
JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
JOIN sys.objects AS t
ON t.parent_object_id = o.object_id
WHERE t.object_id = @@PROCID;
SELECT @A program_name, @H host_name, @L login_name, @D domain_name
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;
IF NOT EXISTS(SELECT * FROM deleted) --> cas d''insertion
BEGIN
INSERT INTO S_ADT.T_MAJ (MAJ_SCHEMA, MAJ_OBJECT, MAJ_TYPE, MAJ_DATA,
MAJ_APPLICATION, MAJ_HOST, MAJ_WINLOGIN, MAJ_WINDOMAIN)
SELECT @S, @O, ''I'',
CAST((SELECT * FROM inserted FOR XML AUTO, ELEMENTS, ROOT(''insert''), BINARY BASE64) AS XML),
@A, @H, @L, @D;
RETURN;
END;
BEGIN
IF NOT EXISTS(SELECT * FROM inserted) --> cas de la suppression
INSERT INTO S_ADT.T_MAJ (MAJ_SCHEMA, MAJ_OBJECT, MAJ_TYPE, MAJ_DATA,
MAJ_APPLICATION, MAJ_HOST, MAJ_WINLOGIN, MAJ_WINDOMAIN)
SELECT @S, @O, ''D'',
CAST((SELECT * FROM deleted FOR XML AUTO, ELEMENTS, ROOT(''delete''), BINARY BASE64) AS XML),
@A, @H, @L, @D;
RETURN;
END;
-- cas de la modif
INSERT INTO S_ADT.T_MAJ (MAJ_SCHEMA, MAJ_OBJECT, MAJ_TYPE, MAJ_DATA,
MAJ_APPLICATION, MAJ_HOST, MAJ_WINLOGIN, MAJ_WINDOMAIN)
SELECT @S, @O, ''I'',
CAST((SELECT *
FROM (SELECT ''OLD'' AS _old_, * FROM deleted
UNION ALL
SELECT ''NEW'' AS _new_, * FROM inserted) AS _auditTrail_
FOR XML AUTO, ELEMENTS, ROOT(''update''), BINARY BASE64) AS XML),
@A, @H, @L, @D;'
DECLARE @T NVARCHAR(261), @N VARCHAR(38), @CMD NVARCHAR(MAX);
DECLARE C CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS T,
REPLACE(REPLACE(CAST(NEWID() AS VARCHAR(38)), '-', '_'), '-', '_') AS N
FROM INFORMATION_SCHEMA.TABLES AS T
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_SCHEMA 'S_ADT'
AND NOT EXISTS(SELECT *
FROM INFORMATION_SCHEMA.COLUMNS AS C
WHERE T.TABLE_SCHEMA = C.TABLE_SCHEMA
AND T.TABLE_NAME = C.TABLE_NAME
AND C.DATA_TYPE IN ('text', 'ntext', 'image', 'xml', 'geometry', 'geography'));
OPEN C;
FETCH C INTO @T, @N;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CMD = REPLACE(REPLACE(@SQL, '~1', @N), '~2', @T);
EXEC (@CMD);
FETCH C INTO @T, @N;
END;
CLOSE C;
DEALLOCATE C;
GO
4 РUn batch de suppression de tous les triggers de tracabilit̩ mis en place
SELECT @SQL = @SQL + 'DROP TRIGGER [' + s.name + '].' + d.name + ';'
FROM sys.triggers AS d
JOIN sys.objects AS o
ON d.object_id = o.object_id
JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
WHERE d.name LIKE 'E?_auditTrail?_%' ESCAPE '?'
AND LEN(d.name) = 49
AND REPLACE(RIGHT(d.name, 36), '_', '') LIKE REPLICATE('[0-9A-F]', 32);
EXEC (@SQL);
GO
5 РUn batch de d̩sactivation des d̩clencheurs de tra̤abilit̩
SELECT @SQL = @SQL + 'DISABLE TRIGGER [' + s.name + '].' + d.name
+ ' ON [' + s.name + '].[' + t.name + '];'
FROM sys.triggers AS d
JOIN sys.objects AS o
ON d.object_id = o.object_id
JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
JOIN sys.objects AS t
ON d.parent_id = t.object_id
WHERE d.name LIKE 'E?_auditTrail?_%' ESCAPE '?'
AND LEN(d.name) = 49
AND REPLACE(RIGHT(d.name, 36), '_', '') LIKE REPLICATE('[0-9A-F]', 32);
EXEC (@SQL);
GO
6 РUn batch de r̩activation des d̩clencheurs de tracabilit̩
SELECT @SQL = @SQL + 'ENABLE TRIGGER [' + s.name + '].' + d.name
+ ' ON [' + s.name + '].[' + t.name + '];'
FROM sys.triggers AS d
JOIN sys.objects AS o
ON d.object_id = o.object_id
JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
JOIN sys.objects AS t
ON d.parent_id = t.object_id
WHERE d.name LIKE 'E?_auditTrail?_%' ESCAPE '?'
AND LEN(d.name) = 49
AND REPLACE(RIGHT(d.name, 36), '_', '') LIKE REPLICATE('[0-9A-F]', 32);
EXEC (@SQL);
GO
NOTA :
Conseil important : si vous avez attribué des privilèges très fermés, il faut autoriser les utilisateurs à voir les DMV d’état du serveur pour recueillir les données système, sinon les déclencheurs vont planter.pour cela vous devez attribuer le privilège « VIEW SERVER STATE » à tous les utilisateurs de la base à travers leur compte de connexion.
Vous pouvez faire ceci à l’aide du script SQL suivant :
SELECT @SQL = @SQL + 'GRANT VIEW SERVER STATE TO [' + L.name + '];'
FROM sys.database_principals AS U
INNER JOIN sys.server_principals AS L
ON L.sid = U.sid
WHERE L.name 'sa'
AND U.name NOT IN ('dbo', 'INFORMATION_SCHEMA', 'sys');
EXEC (@SQL);
GO
LE CODE * LE CODE * LE CODE * LE CODE * LE CODE * LE CODE * LE CODE * LE CODE * LE CODE
Le code !
Expert S.G.B.D relationnelles et langage S.Q.L
Moste Valuable Professionnal Microsoft SQL Server
Société SQLspot : modélisation, conseil, formation,
optimisation, audit, tuning, administration SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.
L’entreprise SQL Spot
Le site web sur le SQL et les SGBDR
Développez et administrez pour la performance avec SQL Server 2014
quelques erreurs dans les scripts + l’inconvenient de sauvegarder plusieurs lignes dans l’xml.
il vaut mieux avoir une ligne dans l’audit = 1 record audité
j’ai fini par faire une table de parametre pour l’audit, et de m’appuyer dessus pour generer les triggers.
cela me permet de choisir precisemment quels champs je veux auditer
Je stoke aussi quel ID pour les tables, je peux donc faire la jointure entre inserted et deleted et generer une ligne d’audit par record.
pour l’instant trop lent, j’attends la version SQL 2016 pour voir la performance si c’est plus rapide.
Dernier petite coquille, dans la partie du code :
– cas de la modification
INSERT INTO S_ADT.T_MAJ (MAJ_SCHEMA, MAJ_OBJECT, MAJ_TYPE, MAJ_DATA,
MAJ_APPLICATION, MAJ_HOST, MAJ_WINLOGIN, MAJ_WINDOMAIN)
SELECT @S, @O, ‘I’,
il faut remplacer le ‘I’ par ‘U’ (pour Update)
Merci
Rebonjour M. BROUARD,
J’ai vu pourquoi le code de la partie Mise à jour ne fonctionnait pas; c’est ici :
— cas de la suppression
BEGIN
IF NOT EXISTS(SELECT * FROM inserted)
INSERT INTO S_ADT.T_MAJ (MAJ_SCHEMA, MAJ_OBJECT, MAJ_TYPE, MAJ_DATA,
MAJ_APPLICATION, MAJ_HOST, MAJ_WINLOGIN, MAJ_WINDOMAIN)
SELECT @S, @O, ‘D’,
CAST((SELECT * FROM deleted FOR XML AUTO, ELEMENTS, ROOT(‘delete’), BINARY BASE64) AS XML),
@A, @H, @L, @D;
RETURN;
END;
au lieu de BEGIN
IF NOT EXISTS(SELECT * FROM inserted) il fallait écrire IF NOT EXISTS(SELECT * FROM inserted) BEGIN—–END; dans le cas d’espèce, la procédure sort toujours après le RETURN et donc on ne passe jamais à la partie mise à jour que j’ai modifiée ainsi :
— cas de la modification
IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
BEGIN
INSERT INTO S_ADT.T_MAJ (MAJ_SCHEMA, MAJ_OBJECT, MAJ_TYPE, MAJ_DATA,MAJ_APPLICATION, MAJ_HOST, MAJ_WINLOGIN, MAJ_WINDOMAIN)
SELECT @S, @O, ‘U’,
CAST((SELECT *
FROM (SELECT ‘OLD’ AS _old_, * FROM deleted
UNION ALL
SELECT ‘NEW’ AS _new_, * FROM inserted) AS _auditTrail_
FOR XML AUTO, ELEMENTS, ROOT(‘update’), BINARY BASE64) AS XML),
@A, @H, @L, @D;
RETURN;
END
car dans le cas d’une mise à jour et la table inserted et la table deleted sont populées. Rappelons que le RETURN n’est pas obligatoire….à présent tout fonctionne nickel. Merci infiniment
Bonjour M. BROUARD,
J’ai voulu tester le trigger mais je me rends compte qu’il y a un souci au niveau de cette clause SELECT
SELECT @A program_name, @H host_name, @L login_name, @D domain_name
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;
il manquait les égalités; CAD
SELECT @A=program_name, @H=host_name, @L=login_name, @D=domain_name
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;
L’autre souci c’est que la partie update ne fonctionne pas….
Merci beaucoup pour cet outil très précieux