Discussion :

[Jester]

Bonjour,

J'aimerais mettre en place un audit d'accès à des données d'un serveur SQL Server 2012. Mon besoin et qu'une table soit remplie d'une ligne d'audit à chaque fois qu'un utilisateur exécute une vue.

Je tente de passer par une procédure stockée qui contient le SELECT de la vue ainsi qu'un INSERT dans la table d'audit. Je bloque à créer la vue qui exécute cette procédure stockée.

J'ai trouvé la source de l'erreur je suppose sur ce site. L'OPENQUERY semble faire un rollback.

Ci-dessous un code de test, [SELF] est un serveur lié qui pointe sur localhost. La première exécution insert bien une ligne dans la table d'audit mais pas quand je l'appelle par OPENQUERY. J'ai aussi essayé OPENROWSET, Utiliser OLEDB en place du client natif, ... rien n'y fait. Je suis prenneur si vous avez des idées.

Merci

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
 
create table dbo.stored_procedure_log (
	sp_schema varchar(255),
	sp_name varchar(255),
	sp_user varchar(255),
	start_time datetime,
	duration int default 0
);
go
 
create procedure sp_dummy
as 
begin
select 1 as col1, 2 as col2;
insert into dbo.stored_procedure_log(sp_schema,sp_name,sp_user,start_time) 
	values ('dbo','dummy','toto',getdate());
end;
go
 
exec sp_dummy;
 
-- On vérifie qu'une ligne d'audit a bien été mise
select * from dbo.stored_procedure_log;
 
 
select * from OPENQUERY([SELF], '
	exec master.dbo.sp_dummy;
');
 
-- La ligne d'audit n'a pas été insérée
select * from dbo.stored_procedure_log;

[SQLpro]

Au lieu d'utiliser une telle usine à gaz, utilisez les outils adéquats et performants comme DATABASE AUDIT !

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
-- mise en place du stockage de l'audit
USE master;
GO
CREATE SERVER AUDIT SAD_VUE
    TO FILE ( FILEPATH ='C:\TEMP\AUDIT_SQL\' );
GO
ALTER SERVER AUDIT SAD_VUE 
WITH (STATE = ON) ;
GO
 
-- mise en place d'un objet à auditer
USE MABASE
GO
CREATE DATABASE AUDIT SPECIFICATION ADS_VUE
FOR SERVER AUDIT SAD_VUE
ADD (SELECT 
     ON dbo.MMavue BY dbo )
WITH (STATE = ON) ;
GO
 
-- pour lire les auditions effectuées :
SELECT * 
FROM sys.fn_get_audit_file ('C:\TEMP\AUDIT_SQL\*', default, default);
GO

Bref, formez-vous à SQL Server... Venez par exemple à un de mes cours Admin à Orsys !

A +

[Jester]

Merci pour cette idée. Je l'avais déjà explorée car effectivement elle est plus logique. Malheureusement, je n'ai que la version BI de SQL Server donc pas d'audit fin.

Le coté usine à gaz n'est pas trop dérangeant pour mon usage, je n'ai pas besoin de performances incroyables et les scripts seront générés et déployés de manière automatisées.

[aieeeuuuuu]

Le coté usine à gaz n'est pas trop dérangeant pour mon usage, ...

Dans ce cas, (et uniquement dans ce cas ) coupez lui l'herbe sous le pied et forcez la validation de la transaction, en faisant un commit juste après votre INSERT.

Code :

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

1
2
3
4
5
6
7
8
9
10
 
CREATE procedure sp_dummy
AS 
begin
SELECT 1 AS col1, 2 AS col2;
INSERT INTO dbo.stored_procedure_log(sp_schema,sp_name,sp_user,start_time) 
	VALUES ('dbo','dummy','toto',getdate());
COMMIT TRAN
 
end;

[Jester]

Bonne piste. Merci

L'emplacement indiqué ne fonctionnait pas. Mais si je le met dans le openquery directement ça fonctionne. Après réflexion, ça fait sens. J'ai pus faire le test sur un cas concret et quand on rafraichit un lien ODBC sur la vue dans un Excel (mon objectif), j’obtiens bien l'info.

Code :

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

1
2
3
4
5
6
 
CREATE VIEW v_dummy as 
select * from OPENQUERY([SELF], '
	exec master.dbo.sp_dummy;
	commit;
');