Fil de l'eau transaction

**lafron971** · 11/02/2015, 15h32

Bonjour,

J'utilise une base de données SQL pour ma gestion commerciale (SAGE SQL V16)
Je souhaiterai avoir un fil de l'eau de toutes les transactions ou opérations passées sur deux tables spécifiques. A chaque fois qu'une opération est saisie ou modifiée ou supprimée sur l'une de ces deux tables, je souhaite savoir quand et par qui elle l'a été.
Un peu comme une bande de contrôle sur une caisse enregistreuse.
Comment créer simplement un tel fichier log, ou exploiter l'existant des fonctions que propose SQL en la matière ?

Merci pour vos conseils

Jean Luc

**Boubou2020** · 11/02/2015, 16h29

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
DECLARE @table VARCHAR(100), @schema VARCHAR(100), @generateScriptOnly BIT
 
-- Details about the table to audit.
SET @schema = 'Assets'
SET @table = 'Employee'
 
-- Set @generateScriptOnly = 1 if you just want to generate a script that you
-- can run yourself later.
SET @generateScriptOnly = 1
 
------------------------------------------------------------------------------
-- Create _Audit table
------------------------------------------------------------------------------
 
DECLARE @crlf CHAR(2), @sql NVARCHAR(MAX)
SET @crlf = CHAR(10)
 
SET @sql = '
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[' + @schema + '].[_' + @table + '_Audit]'') AND type in (N''U''))
	DROP TABLE [' + @schema + '].[_' + @table + '_Audit]'
 
PRINT @sql
IF @generateScriptOnly != 1 EXEC sp_executesql @sql ELSE PRINT 'GO' + @crlf
 
set @sql = 'CREATE TABLE [' + @schema + '].[_' + @table + '_Audit]
(' + @crlf
 
DECLARE
	@columnID INT,
	@columnName VARCHAR(MAX),
	@columnType VARCHAR(MAX),
	@columnSize INT,
	@done BIT
 
SET @columnID = 0
SET @done = 0
 
WHILE @done = 0   
BEGIN            
    SELECT TOP 1
		@columnID = c.column_id,
		@columnName = c.name,
		@columnType = ut.name,
		@columnSize = CAST(
			CASE
				WHEN bt.name IN (N'nchar', N'nvarchar') AND c.max_length != -1
				THEN c.max_length/2
				ELSE c.max_length
			END
			AS INT)
    FROM
		sys.tables t
		INNER JOIN sys.all_columns c ON
			c.object_id = t.object_id
		LEFT JOIN sys.types AS ut ON
			ut.user_type_id = c.user_type_id
		LEFT JOIN sys.types AS bt ON
			bt.user_type_id = c.system_type_id 
			AND bt.user_type_id = bt.system_type_id
    WHERE
		(t.name = @table AND SCHEMA_NAME(t.schema_id) = @schema)
		AND c.column_id > @columnID
	ORDER BY
		c.column_id
 
    IF @@ROWCOUNT = 0   
		SET @done = 1
    ELSE
    BEGIN
        SET @sql = @sql + '	[' + @columnName + '] [' + @columnType + ']'
        IF @columnType IN ('varchar', 'nvarchar')
			IF @columnSize = -1
				SET @sql = @sql + '(MAX)'
			ELSE
				SET @sql = @sql + '('+LTRIM(STR(@columnSize)) + ')'
        SET @sql = @sql + ' NULL, ' + @crlf
    END
END
 
SET @sql = @sql + '	[Audit_Command] [varchar](20),
	[Audit_TimeStamp] [datetime],
	[Audit_SystemUser] [nvarchar](255)
)'
PRINT @sql
IF @generateScriptOnly != 1 EXEC sp_executesql @sql ELSE PRINT 'GO' + @crlf
 
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
 
------------------------------------------------------------------------------
-- Create UPDATE trigger
------------------------------------------------------------------------------
 
SET @sql = 'IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[' + @schema + '].[_' + @table + '_AuditOnUpdate]''))
	DROP TRIGGER [' + @schema + '].[_' + @table + '_AuditOnUpdate]'
PRINT @sql
IF @generateScriptOnly != 1 EXEC sp_executesql @sql ELSE PRINT 'GO' + @crlf
 
set @sql = 'CREATE TRIGGER [_' + @table + '_AuditOnUpdate] 
   ON  ['+@schema+'].['+@table+'] 
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
 
    INSERT INTO [' + @schema + '].[_' + @table + '_Audit]
    SELECT
		*,
		''UPDATE'',
		GETDATE(),
		system_user
	FROM
		Inserted
END'
PRINT @sql
IF @generateScriptOnly != 1 EXEC sp_executesql @sql ELSE PRINT 'GO' + @crlf
 
------------------------------------------------------------------------------
-- Create INSERT trigger
------------------------------------------------------------------------------
 
SET @sql = 'IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[' + @schema + '].[_' + @table + '_AuditOnInsert]''))
	DROP TRIGGER [' + @schema + '].[_' + @table + '_AuditOnInsert]'
PRINT @sql
IF @generateScriptOnly != 1 EXEC sp_executesql @sql ELSE PRINT 'GO' + @crlf
 
set @sql = 'CREATE TRIGGER [_'+@table+'_AuditOnInsert] 
   ON  ['+@schema+'].['+@table+'] 
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
 
    INSERT INTO [' + @schema + '].[_' + @table + '_Audit]
    SELECT
		*,
		''INSERT'',
		GETDATE(),
		system_user
	FROM
		Inserted
END'
PRINT @sql
IF @generateScriptOnly != 1 EXEC sp_executesql @sql ELSE PRINT 'GO' + @crlf
 
------------------------------------------------------------------------------
-- Create DELETE trigger
------------------------------------------------------------------------------
 
SET @sql = 'IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[' + @schema + '].[_' + @table + '_AuditOnDelete]''))
	DROP TRIGGER [' + @schema + '].[_' + @table + '_AuditOnDelete]'
PRINT @sql
IF @generateScriptOnly != 1 EXEC sp_executesql @sql ELSE PRINT 'GO' + @crlf
 
set @sql = 'CREATE TRIGGER [_'+@table+'_AuditOnDelete] 
   ON  ['+@schema+'].['+@table+'] 
   AFTER DELETE
AS 
BEGIN
    SET NOCOUNT ON;
 
    INSERT INTO [' + @schema + '].[_' + @table + '_Audit]
    SELECT
		*,
		''DELETE'',
		GETDATE(),
		system_user
	FROM
		DELETED
END'
PRINT @sql
IF @generateScriptOnly != 1 EXEC sp_executesql @sql ELSE PRINT 'GO' + @crlf
 
-- End

**SQLpro** · 11/02/2015, 21h56

Le plus simple est d'utiliser le bon outil qui est l'audit de bases de données (DATABASE AUDIT) , disponible à partir de la version Enterprise 2008.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Create du stockage de l'audit :
CREATE SERVER AUDIT SAD_operations
   TO FILE ( FILEPATH = 'C:\AUDIT_SQL\Operations' ) ; --> le répertoire C:\AUDIT_SQL\ doit exister
GO
-- Démarrage de la capture.
ALTER SERVER AUDIT SAD_operations 
   WITH (STATE = ON) ;
GO
-- On se place dans la base à anlyser.
USE MaBase ;
GO
-- On met en place les événements à auditer sur les objets
CREATE DATABASE AUDIT SPECIFICATION ADS_operations
FOR SERVER AUDIT SAD_operations
ADD (INSERT, UPDATE, DELETE
     ON MonSchema.MaTable1 BY dbo),
	(INSERT, UPDATE, DELETE
     ON MonSchema.MaTable2 BY dbo) --> dbo => tous les utilisateurs
WITH (STATE = ON) ;
GO

Évitez la solution par déclencheurs du fait de leur coût !

A +

**lafron971** · 12/02/2015, 13h47

Merci pour vos conseils.
Que pensez-vous du logiciel ApexSQL Log qui permet de lire clairement le fichier des transactions ? En existe-il des meilleurs ou des plus performants ?
Mon problème c'est que je veux pister les éventuels petits malins qui suppriment des lignes de document de ventes ou d'entête de vente ou de règlement.
Est-ce que la solution d'Audit proposée par SQL le permet ? Si une ligne a été supprimée à un instant T, est-ce que l'audit lancé à l'instant T + 1va permettre de visualiser ce qui m'intéresse c'est à dire ce qui a été supprimé ou modifié, par qui, a quelle heure, et quelles étaient les valeurs avant suppression ou modification ?
Qu'en pensez vous ?
Merci pour votre aide en tout cas
Jluc

**SQLpro** · 12/02/2015, 15h26

Envoyé par lafron971

Merci pour vos conseils.
Que pensez-vous du logiciel ApexSQL Log qui permet de lire clairement le fichier des transactions ? En existe-il des meilleurs ou des plus performants ?
Mon problème c'est que je veux pister les éventuels petits malins qui suppriment des lignes de document de ventes ou d'entête de vente ou de règlement.
Est-ce que la solution d'Audit proposée par SQL le permet ? Si une ligne a été supprimée à un instant T, est-ce que l'audit lancé à l'instant T + 1va permettre de visualiser ce qui m'intéresse c'est à dire ce qui a été supprimé ou modifié, par qui, a quelle heure, et quelles étaient les valeurs avant suppression ou modification ?
Qu'en pensez vous ?
Merci pour votre aide en tout cas
Jluc

L'audit de bases de données est justement fait pour cela (sécurité Sabarne Oxley et tutti quanti..) le APEX log n'est PAS DU TOUT FAIT POU çA !

A +

**lafron971** · 13/02/2015, 15h19

Bonjour,

Je n'ai pas la version Enterprise, seulement standard.... Comment me procurer les fonctionnalités DATABASE AUDIT ?
Merci pour le conseil

A+
JLuc

Envoyé par SQLpro

Le plus simple est d'utiliser le bon outil qui est l'audit de bases de données (DATABASE AUDIT) , disponible à partir de la version Enterprise 2008.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Create du stockage de l'audit :
CREATE SERVER AUDIT SAD_operations
   TO FILE ( FILEPATH = 'C:\AUDIT_SQL\Operations' ) ; --> le répertoire C:\AUDIT_SQL\ doit exister
GO
-- Démarrage de la capture.
ALTER SERVER AUDIT SAD_operations 
   WITH (STATE = ON) ;
GO
-- On se place dans la base à anlyser.
USE MaBase ;
GO
-- On met en place les événements à auditer sur les objets
CREATE DATABASE AUDIT SPECIFICATION ADS_operations
FOR SERVER AUDIT SAD_operations
ADD (INSERT, UPDATE, DELETE
     ON MonSchema.MaTable1 BY dbo),
	(INSERT, UPDATE, DELETE
     ON MonSchema.MaTable2 BY dbo) --> dbo => tous les utilisateurs
WITH (STATE = ON) ;
GO

Évitez la solution par déclencheurs du fait de leur coût !

A +

**SQLpro** · 15/02/2015, 15h26

Envoyé par lafron971

Je n'ai pas la version Enterprise, seulement standard.... Comment me procurer les fonctionnalités DATABASE AUDIT ?
Merci pour le conseil

Vous ne pouvez pas. C'est intégré à la version Enterprise.

A +

**lafron971** · 16/02/2015, 14h23

... C'est bien ce que je pensais.
Je dois donc mettre à jour.
Est-ce qu'une mise à jour Standard --> Enterprise est simple à faire ? J'ai demandé à mon informaticien, il m'a dit qu'il avait essayé sur un serveur d'essai où était installée la version Standard et qu'à priori il s'était fait jeter.
BOnne journée

Envoyé par SQLpro

Vous ne pouvez pas. C'est intégré à la version Enterprise.

A +

**SQLpro** · 16/02/2015, 15h52

Vous devez utiliser l'assistant d'installation en mode maintenance et faire une mise à jour in situ :
http://www.thesqlreport.com/?p=349

Bref, votre informaticien est nul... !

A +

Fil de l'eau transaction

MS SQL Server

Discussions similaires

Partager

Partager