[SQL 2005] Audit d'un select

Version imprimable

03/01/2011, 18h53
mad_martigan

[SQL 2005] Audit d'un select

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
03/01/2011, 23h12
zinzineti

SQLPro
04/01/2011, 10h19
mad_martigan

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

Qu'en est-il pour SQL Server 2005 ?

Merci

Bon tu peux utiliser ceci :

Code:

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

04/01/2011, 16h10
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

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

Code:

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:

1
2
-- Test 
EXEC P_Audit_SELECT N'myTable'

La fouille de la table d'audit

Code:

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

04/01/2011, 16h31
mad_martigan
Lorsque je lance la partie du SELECT, j'obtiens bien des résultats.

Par contre si je lance le code :
Code:

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
04/01/2011, 16h39
mad_martigan

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

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

Text_SQL nvarchar(max)

Pour avoir
Code:

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

04/01/2011, 16h56
mad_martigan

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

Merci
04/01/2011, 17h09
zinzineti

Citation:

Envoyé par mad_martigan

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

Merci

Code:

SUSER_SNAME()
04/01/2011, 18h38
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
04/01/2011, 18h46
mad_martigan

Il semble qu'en incluant SUSER_SNAME() je récupère le compte de service de SQL Server...
Pourquoi?
05/01/2011, 00h24
zinzineti

Citation:

Envoyé par mad_martigan

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.