Discussion :

[bilb0t]

Bonjour,

j'ai une table qui a disparu sur un server de test.

Et évidemment c'est personne... Come les gens m'ont l'air sincère, je me demande s'il n'y a pas un gugusse qui a créer un code quelque part qui risque de dropper des tables quand on publiera...

Bref, y a-t-il un moyen de savoir qui a dropper une table et quand ?

merci

[ylarvor]

Si le code malicieux est dans une procedure stockée, tu fais une generation du script de toutes tes procédures et tu recherches le mot DROP.

Si le code malicieux se trouve dans votre code, vous procéder de la même manière avec visual studio.

[SQLpro]

qui, quand, difficile à postériori....
A priori vous pouvez mettre en place ce genre d'audit à l'aide de triggers DDL.
A postériori certains éditeurs vendent des outils de lecture du JT (Apex par exemple). Je ne sais pas si cette info, QUI, y figure... En revanche, QUAND vous l'aurez...

A +

[bilb0t]

Bonjour, merci pour vos réponses.

Est-il possible d'imaginer d'auditer la DB pour loguer les drop table ?

Si oui, qu'est-ce que ça implique en terme de performances ?

merci

[ylarvor]

A priori vous pouvez mettre en place ce genre d'audit à l'aide de triggers DDL.

d'apres ma recherche sur internet, le trigger DDL est une fonctionnalité sql serveur 2005.

reference : http://www.google.fr/search?sourceid...dl+sql+serveur

De plus, cela me parait un gros travail de développement pour une information qui présente peu d'intérêt.

Mieux vaut chercher le code malicieux une bonne fois pour toute et oubliez le problème une fois résolu, non ?

[elsuket]

Bonjour,

Si vous êtes sous SQL Server 2005, vous pouvez effectivement créer un trigger DDL, comme par exemple celui-ci (doc)

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
USE MASTER;
GO
 
-- Crée la table de log
CREATE TABLE MASTER.dbo.SchemaModification
(
	Date DATETIME NOT NULL,
	Utilisateur VARCHAR(128) NOT NULL,
	Objet VARCHAR(1024) NOT NULL,
	Commande TEXT
)
GO
 
USE ELSUKET;
GO
 
-- Crée le trigger DDL qui logguera dans MASTER.dbo.SchemaModification
CREATE TRIGGER DDL_TRIGGER
	ON DATABASE
FOR DDL_VIEW_EVENTS, DDL_INDEX_EVENTS, DDL_TABLE_EVENTS,
	DROP_PROCEDURE, CREATE_PROCEDURE, ALTER_PROCEDURE,
	DROP_TRIGGER, CREATE_TRIGGER, ALTER_TRIGGER
AS 
BEGIN
	SET NOCOUNT ON;
 
	DECLARE @EventData XML;
	DECLARE @SQL_Statement VARCHAR(MAX);
	SET @EventData = EventData();
 
	SELECT @SQL_Statement = @EventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'VARCHAR(MAX)');
	INSERT MASTER.dbo.ShemaModification
		SELECT GETDATE(),
				@EventData.value('data(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(128)'),
				@EventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(1024)'),
				@SQL_Statement;
 
	SET NOCOUNT OFF;
END;
GO
ENABLE TRIGGER DDL_TRIGGER ON DATABASE
GO

Vous pouvez aussi créer un job de trace des actions utilisateur. Cela consomme peu de ressources, mais présente deux désavantages:

- Logguer dans un fichier et non pas dans une table. Vous pouvez néanmoins remonter le ficher de trace dans une table en base de données à l'aide de l'utilitaire SQL Server Profiler
- 1 utilisateur = 1 appel à sp_trace_setfilter

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
CREATE PROCEDURE [dbo].[spJOB_MONITOR_USER_COMMANDS]
AS
BEGIN
	DECLARE @rc INT;
	DECLARE @TraceID INT;
 
	-- Calcule le nom du fciher de trace
	DECLARE @filename NVARCHAR(245);
	SELECT @filename = '[LECTEUR]:\RepertoireDeTraces\' + DB_NAME() + '_' +
			CONVERT(VARCHAR, DATEPART(year, GETDATE())) + '_' + 
			CASE LEN(CONVERT(VARCHAR, DATEPART(month, GETDATE())))
				WHEN 1 THEN '0' + CONVERT(VARCHAR, DATEPART(month, GETDATE()))
				ELSE CONVERT(VARCHAR, DATEPART(month, GETDATE()))
			END + '_' +
			CONVERT(VARCHAR, DATEPART(day, GETDATE())) + '_' +
			CONVERT(VARCHAR, DATEPART(hour, GETDATE())) + 'H' +
			CONVERT(VARCHAR, DATEPART(minute, GETDATE())) + 'min' +
			CONVERT(VARCHAR, DATEPART(second, GETDATE())) + 's';
 
	DECLARE @STOP_TIME DATETIME;
	SELECT @STOP_TIME = DATEADD(Hour, 12, GETDATE());
 
	DECLARE @trace_file_size BIGINT; SET @trace_file_size = 50;
 
	-- Crée le fichier de trace
	-- sp_trace_create @traceid OUTPUT, @options, @trace_file, @maxfilesize, @stoptime, @filecount
	EXEC @rc = sp_trace_create @TraceID OUTPUT, 6, @filename, @trace_file_size, @STOP_TIME, NULL 
	DECLARE @ERR VARCHAR(128);
	SELECT @ERR = CASE @rc
					WHEN 0 THEN 'sp_trace_create: created TRACEID ' + CONVERT(VARCHAR, @TraceID)
					WHEN 1 THEN 'sp_trace_create: UNKNOWN ERROR'
					WHEN 10 THEN 'sp_trace_create: INVALID OPTIONS'
					WHEN 12 THEN 'sp_trace_create: FILE NOT CREATED'
					WHEN 13 THEN 'sp_trace_create: OUT OF MEMORY'
					WHEN 14 THEN 'sp_trace_create: INVALID STOP TIME'
					WHEN 15 THEN  'sp_trace_create: INVALID PARAMETERS'
				END;
	-- Mettez ici le traitement de @rc : vous pouvez par exemple vous envoyer un e-mail lorsque la trace vient d'être créée,
	-- à l'aide de la procédure msdb.dbo.sp_send_db_mail
 
	DECLARE @on BIT; SET @on = 1;
 
	EXEC sp_trace_setevent @TraceID, 13, 12, @on; -- Event : SQL:BatchStarting, SPID column (Mandatory)
	EXEC sp_trace_setevent @TraceID, 13, 64, @on; -- Event : SQL:BatchStarting, SessionLoginName 
	EXEC sp_trace_setevent @TraceID, 13, 1, @on; -- Event : SQL:BatchStarting, TextData 
	EXEC sp_trace_setevent @TraceID, 13, 14, @on; -- Event : SQL:BatchStarting, StartTime 
	EXEC sp_trace_setevent @TraceID, 13, 35, @on; -- Event : SQL:BatchStarting, DatabaseName 
 
	-- Ajout des filtres
	/*
	 sp_trace_setfilter myTrace,
						myColumn (64 = SessionLoginName), 
						myLogicalOperator (AND : 0, OR : 1),
						myComparisonOperator (6 = LIKE),
						myComparisonValue
	*/
	EXEC sp_trace_setfilter @TraceID, 64, 1, 6, N'nomDeLogin'
 
	-- Démarrage de la trace
	EXEC sp_trace_setstatus @TraceID, 1;
END;
GO

Dans cet exemple j'ai mis 12 heures pour mon fichier de trace, mais c'est à vous de le paramétrer en fonction du nombre de personnes qui travaillent sur votre BD et du nombre d'opérations qu'ils effectuent. Vous pouvez exécuter cette procédure stockée par un job qui démarre toutes les 12 heures, si vous suivez mon exemple

Je vous laisse le soin de traiter la suppression des fichiers de trace