Audit trail générique

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 SCHEMA S_ADT
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

CREATE TRIGGER E_auditTrail_???
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…

DECLARE @SQL NVARCHAR(MAX) =
'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

DECLARE @SQL NVARCHAR(MAX) = N''
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é

DECLARE @SQL NVARCHAR(MAX) = N''
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é

DECLARE @SQL NVARCHAR(MAX) = N''
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 :

DECLARE @SQL NVARCHAR(MAX) = N'USE master;'
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 !

Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES
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

MVP Microsoft SQL
Server

Développez et administrez pour la performance avec SQL Server 2014

Développez et administrez pour la performance avec SQL Server 2014

Laisser un commentaire