Problème d’utilisation de l’option WITH EXECUTE AS dans une procédure stockée avec les serveurs liés

Publié le par

L’emprunt d’identité peut s’avérer nécessaire lorsque l’on commence à vouloir faire de la sécurité à un niveau granulaire relativement bas. Par exemple la politique de sécurité en vigueur peut nous contraindre à restreindre l’accès aux données via des procédures stockées. Cependant si ces données sont distantes et implique l’implémentation d’un serveur lié pour y avoir accès, cela peut donner matière à plus de réflexion. En effet, dans ce cas il va falloir donner les droits adéquates aux utilisateurs concernés sur le serveur distant pour accéder aux données, ce qui risque pour le coup d’augmenter la surface d’exposition. Un utilisateur A devra pouvoir exécuter une procédure stockée et sélectionner les données sur le serveur distant. L’utilisateur A peut par conséquent passer outre la procédure stockée et se connecter directement au serveur distant pour accéder aux données. Pour éviter cela on peut tout à fait utiliser le mécanisme d’emprunt d’identité dans une procédure stockée à l’aide de WITH EXECUTE AS et associer l’utilisateur dédié à cette tâche à un utilisateur sur le serveur distant. On bénéficie ainsi d’un contrôle d’accès beaucoup fort et maitrisé.

 

Le schéma ci-dessous illustre bien notre problématique :

 

icon_arrow Dans le 1er cas un mapping est nécessaire pour chaque utilisateur qui va vouloir accéder aux données du serveur distant Instance 2, ce qui signifie qu’il devra exister les mêmes logins sur le serveur distant Instance 2. Comme je l’ai évoqué au début cela implique également que les utilisateurs concernés pourront directement accéder aux données du serveur distant avec leurs informations de connexion.

 

image

 

icon_arrow Dans le 2ème cas nous mettons en place un mécanisme d’emprunt d’identité via l’option WITH EXECUTE AS USER dans la procédure stockée. L’idée ici est de n’autoriser que cet utilisateur à avoir accès aux données distantes.

 

image

 

La mise en place d’un tel mécanisme dans le contexte des serveurs liés nécessitent un certain paramétrage :

  • L’activation obligatoire de l’option de bases de données trustworthy. Malheureusement seule cette option est viable lorsqu’il s’agit d’accès inter instances. L’utilisation des certificats dans ce cas ne fonctionne pas.
  • Différer l’exécution de la procédure stockée concernée. Je n’ai pas trouvé d’article Microsoft sur le sujet mais simplement un item connect. C’est ce point que je vais détailler ici.

 

Si l’on utilise directement l’option WITH EXECUTE AS dans une procédure stockée de la manière suivante :

 

CREATE PROCEDURE dbo.GetRemoteData
WITH EXECUTE AS 'USER1'
AS

SELECT col1, col2
FROM [linked_server].[DATABASE].[schema].[TABLE]
GO

 

… avec l’appel suivant …

 

EXEC dbo.GetRemoteData

 

… il y a de grandes chances que cela ne fonctionne pas surtout si l’on a définit le contexte d’authentification de serveur lié suivant :

 

image

 

Dans ce cas seul le mapping défini pour l’utilisateur USER1 fonctionnera et aucun autre login ou aucun autre type d’authentification ne fonctionnera.

 

Si maintenant j’exécute ou je crée la procédure stockée dbo.GetRemoteData dans le contexte d’un utilisateur différent de l’utilisateur USER1 ayant les droits de création et d’exécution je peux me retrouver avec le message suivant :

 

image

 

Pourtant me direz-vous que nous avons bien défini l’emprunt d’identité dans la procédure stockée et qu’ici visiblement SQL Server n’est pas capable de trouver le mapping défini dans les options de sécurité du serveur lié. En réalité il faut bien comprendre ce qui se passe lorsqu’une procédure stockée est compilée et lorsque celle-ci est exécutée (run-time context). SQL Server compile une procédure stockée avec les credentials de l’appelant de la procédure et non ceux de l’utilisateur associée à l’emprunt d’identité. C’est pendant l’exécution de la procédure que le mécanisme d’emprunt d’identité jouera tout son rôle. Lorsqu’on accède aux données aux travers d’un serveur lié la compilation va générer l’erreur 7416 si l’appelant de la procédure n’a pas un mapping inter-instances configuré (SQL Server va vérifier lors de la compilation l’accès aux différents objets du serveur distant).

Pour contourner ce problème on peut user de 2 mécanismes qui permettent de différer l’exécution de la procédure stockée et de laisser le mécanisme d’emprunt d’identité opérer en premier lieu :

  • Créer une nouvelle procédure stockée qui va scinder l’exécution de notre procédure stockée initiale en 2 étapes
  • Encapsuler le code à exécuter de la procédure stockée avec EXEC( »), ce qui permettra au final de faire la même que dans la 1ère solution.

 

Par exemple :

CREATE PROCEDURE dbo.GetRemoteDataWithImpersonate
WITH EXECUTE AS 'USER1'  
AS

EXEC dbo.GetRemoteData
GO

ou encore :

CREATE PROCEDURE dbo.GetRemoteData    
WITH EXECUTE AS 'USER1'    
AS

EXEC('SELECT col1, col2 FROM [linked_server].[database].[schema].[table]')    
GO

 

Le choix de la méthode dépendra bien du contexte.

 

Bonne programmation !

David BARBARIN (Mikedavem)
MVP SQL Server

Laisser un commentaire