------------------------------------------------------------
-- traçage des mise à jours des utilisateurs ("audit trail")
------------------------------------------------------------

--> 1) 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) exemple de création de trigger sur une table pour tracer tous les événememts
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 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;
GO


--> 3) 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) 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) désactivation des déclencheurs de tracabilité
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) 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


-- 7) 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