Discussion :

[mad_martigan]

Bonjour,

Je souhaiterais faire un audit sur une table contenant des infos sensibles.

Je voudrais auditer les requêtes de type "select" qui y sont faites et enregistrer les résultats de l'audit dans une table.

Est-ce que c'est faisable sous SQL Server 2005 ?

Merci

[zinzineti]

SQLPro

[mad_martigan]

La solution proposée par SQLPro ne fonctionne que sur la version 2008.

Qu'en est-il pour SQL Server 2005 ?

Merci

[zinzineti]

Bon tu peux utiliser ceci :

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
USE AdventureWorks; -- Mets ici ta base
GO
--Créer la table d'audit T_audit
CREATE TABLE T_audit (Dateheure datetime,DatabaseName nvarchar(100),ObjectName nvarchar(100),LoginName nvarchar(100), Event nvarchar(100), Text_SQL nvarchar(2000));
GO
--Créer le trigger d'audit TR_audit
CREATE TRIGGER TR_audit 
ON DATABASE 
FOR DDL_DATABASE_LEVEL_EVENTS 
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT T_audit 
   (Dateheure,DatabaseName,ObjectName,LoginName,Event,Text_SQL) 
   VALUES 
   (GETDATE(), 
    @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)'), 
    @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
GO
--Tester le trigger.
CREATE TABLE TestTable (a int)
GO
DROP TABLE TestTable ;
GO
-- Que dit la table d'audit ?
SELECT * FROM T_audit ;
GO

[mad_martigan]

Merci, c'est pas mal mais je ne peux pas tracer les évènements "SELECT" d'une table et c'est précisément ceci qui m'intéresse.
On ne peut pas auditer les select?

Merci

[zinzineti]

Pour auditer uniquement les SELECT portant sur une table en particulier :

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
--Créer la table d'audit T_audit
CREATE TABLE T_audit (creation_time datetime,last_execution_time datetime,execution_count int, Text_SQL nvarchar(4000));
GO
 
-- Créer la procédure stockée d'audit 
CREATE PROCEDURE P_Audit_SELECT
@TableName sysname
AS
INSERT INTO T_audit (creation_time,last_execution_time,execution_count,Text_SQL)
SELECT  creation_time,
last_execution_time,
execution_count,
text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.text LIKE '%SELECT%' + @TableName + '%';

Tu peux planifier l'exécution de la procédure stockée :

Code :

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

1
2
-- Test 
EXEC P_Audit_SELECT N'myTable'

La fouille de la table d'audit

Code :

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

1
2
3
 
-- Que contient la table d'audit ?
SELECT * FROM T_audit

[mad_martigan]

Lorsque je lance la partie du SELECT, j'obtiens bien des résultats.

Par contre si je lance le code :

Code :

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

1
2
 
EXEC P_Audit_SELECT N'myTable'

j'obtiens le message suivant :

"Les données de chaîne ou binaires seront tronquées."

Ok, j'imagine que la colonne text n'a pas la capacité suffisante...

Merci

[mad_martigan]

Je voudrais également savoir qui a lancé la requête, c'est possible?

[zinzineti]

As-tu tenté de remplacer Text_SQL nvarchar(4000) par

Text_SQL nvarchar(max)

Pour avoir

Code :

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

1
2
CREATE TABLE T_audit (creation_time datetime,last_execution_time datetime,execution_count int, Text_SQL nvarchar(max));
GO

?

[mad_martigan]

Effectivement ça fonctionne, il ne me reste plus qu'à stocker le login, c'est faisable?

Merci

[zinzineti]

Effectivement ça fonctionne, il ne me reste plus qu'à stocker le login, c'est faisable?

Merci

Code :

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

SUSER_SNAME()

[zinzineti]

pour obtenir les users ayant exécutés les requêtes SELECT tu peux créer une trace Profiler, l'utiliser pour insérer les infos dans la table T_audit

[mad_martigan]

Il semble qu'en incluant SUSER_SNAME() je récupère le compte de service de SQL Server...
Pourquoi?

[zinzineti]

Il semble qu'en incluant SUSER_SNAME() je récupère le compte de service de SQL Server...
Pourquoi?

SUSER_SNAME() indique le l'utilisateur (le compte) qui exécute la procédure stockée. Ce qui n'est pas ton besoin...

Comme je l'ai dit plus haut, si tu veux aussi l'utilisateur qui a exécuté la requête SELECT, il faut plutôt créer une trace Profiler pour alimenter la table T_audit.