Clause EXECUTE AS (Transact-SQL)

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance

Dans SQL Server, vous pouvez définir le contexte d'exécution de ces modules définis par l'utilisateur : fonctions (à l'exception des fonctions table incluses), procédures, files d'attente et déclencheurs.

En spécifiant le contexte dans lequel le module s'exécute, vous contrôlez le compte d'utilisateur que le Moteur de base de données utilise pour valider les autorisations sur des objets référencés par le module. Cela améliore la souplesse et offre une meilleure gestion des autorisations sur la chaîne d'objets qui existe entre les modules définis par l'utilisateur et les objets qu'ils référencent. Les autorisations doivent être accordées aux utilisateurs uniquement sur le module lui-même, sans qu'il soit nécessaire de leur accorder des autorisations explicites sur les objets référencés. Seul l'utilisateur du module en cours d'exécution doit avoir les autorisations sur les objets auxquels le module accède.

Conventions de la syntaxe Transact-SQL

Syntaxe

{ EXEC | EXECUTE } AS { CALLER | SELF | OWNER | 'user_name' }

{ EXEC | EXECUTE } AS { CALLER | SELF | 'user_name' }

{ EXEC | EXECUTE } AS { CALLER | SELF | 'login_name' }

{ EXEC | EXECUTE } AS { SELF | OWNER | 'user_name' }

{ EXEC | EXECUTE } AS { CALLER | SELF | OWNER | 'user_name' }

{ EXEC | EXECUTE } AS { CALLER | SELF | 'user_name' }

Arguments

CALLER

Spécifie que les instructions à l'intérieur du module sont exécutées dans le contexte de l'appelant du module. L'utilisateur qui exécute le module doit disposer des autorisations nécessaires non seulement sur le module lui-même, mais également sur tout objet de base de données référencé par le module.

CALLER est la valeur par défaut pour tous les modules, à l’exception des files d’attente, et est identique au comportement de SQL Server 2005 (9.x).

CALLERne peut pas être spécifié dans une instruction ou ALTER QUEUE une CREATE QUEUE instruction.

SELF

EXECUTE AS SELF équivaut à EXECUTE AS <user_name>, où l’utilisateur spécifié est la personne qui crée ou modifie le module. L’ID utilisateur réel de la personne qui crée ou modifie les modules est stocké dans la colonne dans l’affichage execute_as_principal_id sys.sql_modules catalogue ou sys.service_queues .

SELF est la valeur par défaut pour les files d’attente.

OWNER

Spécifie que les instructions à l’intérieur du module s’exécutent dans le contexte du propriétaire actuel du module. Si le module n’a pas de propriétaire spécifié, le propriétaire du schéma du module est utilisé. OWNER ne peut pas être spécifié pour les déclencheurs DDL ou d’ouverture de session.

'user_name'

Spécifie que les instructions à l’intérieur du module sont exécutées dans le contexte de l’utilisateur spécifié dans user_name. Les autorisations sur n’importe quel objet du module sont vérifiées par rapport à l’utilisateur user_name. user_name ne peut pas être spécifié pour les déclencheurs DDL avec l’étendue du serveur ou les déclencheurs d’ouverture de session. Utilisez login_name à la place.

user_name doit exister dans la base de données active et doit être un compte singleton. user_name ne peut pas être un groupe, un rôle, un certificat, une clé ou un compte intégré, tel que NT AUTHORITY\LocalService, NT AUTHORITY\NetworkServiceou NT AUTHORITY\LocalSystem.

L’ID utilisateur du contexte d’exécution est stocké dans les métadonnées et peut être consulté dans la colonne dans l’affichage execute_as_principal_id catalogue ousys.assembly_modules.sys.sql_modules

'login_name'

Spécifie que les instructions à l’intérieur du module sont exécutées dans le contexte de la connexion SQL Server spécifiée dans login_name. Les autorisations sur n’importe quel objet du module sont vérifiées pour l’utilisateur login_name. Vous pouvez spécifier login_name uniquement pour des déclencheurs DDL sur l’étendue du serveur ou des déclencheurs de connexion.

login_name ne peut pas être un groupe, un rôle, un certificat, une clé ou un compte intégré, tel que NT AUTHORITY\LocalService, NT AUTHORITY\NetworkServiceou NT AUTHORITY\LocalSystem.

Notes

La façon dont le Moteur de base de données évalue les autorisations sur les objets référencés dans le module dépend de la chaîne d’appartenance entre les objets appelants et les objets référencés. Dans les versions antérieures de SQL Server, la chaîne d'appartenance était la seule méthode qui permettait d'éviter d'accorder l'accès de l'utilisateur appelant à tous les objets référencés.

La chaîne d'appartenance est soumise aux limitations suivantes :

• S’applique uniquement aux instructions DML : SELECT, , INSERTUPDATE, et DELETE.
• Les propriétaires des objets appelants et appelés doivent être identiques.
• Ne s’applique pas aux requêtes dynamiques à l’intérieur du module.

Indépendamment du contexte d'exécution spécifié dans le module, les actions suivantes s'appliquent toujours :

• Lorsque le module est exécuté, le Moteur de base de données vérifie d’abord que l’utilisateur qui exécute le module a EXECUTE l’autorisation sur le module.

• Les règles de la chaîne d'appartenance s'appliquent toujours. Cela signifie que, si les propriétaires des objets appelants et appelés sont identiques, aucune autorisation n'est vérifiée sur les objets sous-jacents.

Lorsqu’un utilisateur exécute un module qui a été spécifié pour s’exécuter dans un contexte autre que CALLER, l’autorisation de l’utilisateur d’exécuter le module est vérifiée, mais des vérifications d’autorisations supplémentaires sur les objets accessibles par le module sont effectuées sur le compte d’utilisateur spécifié dans la EXECUTE AS clause. En effet, l'utilisateur qui exécute le module emprunte l'identité de l'utilisateur spécifié.

Le contexte spécifié dans la EXECUTE AS clause du module est valide uniquement pendant la durée de l’exécution du module. Le contexte de l'appelant est rétabli à la fin de l'exécution du module.

Spécifier un nom d’utilisateur ou de connexion

Une connexion utilisateur ou serveur de base de données spécifiée dans la EXECUTE AS clause d’un module ne peut pas être supprimée tant que le module n’est pas modifié pour s’exécuter dans un autre contexte.

Le nom d’utilisateur ou de connexion spécifié dans la clause doit exister en EXECUTE AS tant que principal dans sys.database_principals ou sys.server_principals, respectivement, ou bien l’opération de création ou de modification du module échoue. De plus, l'utilisateur qui crée ou modifie le module doit posséder les autorisations IMPERSONATE sur le principal.

Si l’utilisateur a un accès implicite à la base de données ou à l’instance de SQL Server par le biais d’une appartenance à un groupe Windows, l’utilisateur spécifié dans la EXECUTE AS clause est créé implicitement lorsque le module est créé lorsque l’une des conditions suivantes existe :

• L’utilisateur ou le nom de connexion spécifié est membre du rôle serveur fixe sysadmin.
• L'utilisateur qui crée le module possède l'autorisation de créer des principaux.

Si aucune de ces conditions n'est remplie, la création du module échoue.

Par exemple, supposons les conditions suivantes :

• CompanyDomain\SQLUsers groupe a accès à la Sales base de données.

• CompanyDomain\SqlUser1 est membre de SQLUsers la base de données et a donc accès à la Sales base de données.

• L'utilisateur qui crée ou modifie le module a les autorisations pour créer des principaux.

Lorsque l'instruction CREATE PROCEDURE suivante est exécutée, l'utilisateur CompanyDomain\SqlUser1 est implicitement créé en tant que principal dans la base de données Sales.

USE Sales;
GO
CREATE PROCEDURE dbo.usp_Demo
WITH EXECUTE AS 'CompanyDomain\SqlUser1'
AS
SELECT USER_NAME();
GO

Utiliser l’instruction EXECUTE AS CALLER autonome

Utilisez l’instruction EXECUTE AS CALLER autonome à l’intérieur d’un module pour définir le contexte d’exécution sur l’appelant du module.

Supposons que SqlUser2 appelle la procédure stockée suivante.

CREATE PROCEDURE dbo.usp_Demo
WITH EXECUTE AS 'SqlUser1'
AS
SELECT USER_NAME(); -- Shows execution context is set to SqlUser1.
EXECUTE AS CALLER;
SELECT USER_NAME(); -- Shows execution context is set to SqlUser2, the caller of the module.
REVERT;
SELECT USER_NAME(); -- Shows execution context is set to SqlUser1.
GO

Utiliser EXECUTE AS pour définir des jeux d’autorisations personnalisés

La spécification d’un contexte d’exécution pour un module peut être utile lorsque vous souhaitez définir des jeux d’autorisations personnalisés. Par exemple, certaines actions, telles que TRUNCATE TABLE ne disposent pas d’autorisations accordées. En incorporant l’instruction TRUNCATE TABLE dans un module et en spécifiant que ce module s’exécute en tant qu’utilisateur disposant d’autorisations pour modifier la table, vous pouvez étendre les autorisations pour tronquer la table à l’utilisateur auquel vous accordez EXECUTE des autorisations sur le module.

Pour afficher la définition du module avec le contexte d’exécution spécifié, utilisez la vue de catalogue sys.sql_modules (Transact-SQL).

Bonnes pratiques

Spécifiez une connexion ou un utilisateur qui possède les privilèges minimum requis pour effectuer les opérations définies dans le module. Par exemple, ne spécifiez pas de compte propriétaire de base de données, sauf si ces autorisations sont requises.

autorisations

Pour exécuter un module spécifié avec EXECUTE AS, l’appelant doit disposer EXECUTE d’autorisations sur le module.

Pour exécuter un module CLR spécifié avec EXECUTE AS qui accède aux ressources d’une autre base de données ou d’un autre serveur, la base de données ou le serveur cible doit approuver l’authentificateur de la base de données à partir de laquelle le module provient (la base de données source).

Pour spécifier la EXECUTE AS clause lorsque vous créez ou modifiez un module, vous devez disposer IMPERSONATE d’autorisations sur le principal spécifié et également des autorisations pour créer le module. Vous pouvez toujours emprunter votre propre identité. Quand aucun contexte d’exécution n’est spécifié ou EXECUTE AS CALLER spécifié, IMPERSONATE les autorisations ne sont pas requises.

Pour spécifier un login_name ou un user_name qui a un accès implicite à la base de données via une appartenance à un groupe Windows, vous devez disposer CONTROL d’autorisations sur la base de données.

Exemples

Le code exemple suivant crée une procédure stockée dans la base de données AdventureWorks2022 et affecte le contexte d’exécution à OWNER.

CREATE PROCEDURE HumanResources.uspEmployeesInDepartment @DeptValue INT
    WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

SELECT e.BusinessEntityID,
    c.LastName,
    c.FirstName,
    e.JobTitle
FROM Person.Person AS c
INNER JOIN HumanResources.Employee AS e
    ON c.BusinessEntityID = e.BusinessEntityID
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
    ON e.BusinessEntityID = edh.BusinessEntityID
WHERE edh.DepartmentID = @DeptValue
ORDER BY c.LastName,
    c.FirstName;
GO

-- Execute the stored procedure by specifying department 5.
EXECUTE HumanResources.uspEmployeesInDepartment 5;
GO

Contenu connexe

• sys.assembly_modules (Transact-SQL)
• sys.sql_modules (Transact-SQL)
• sys.service_queues (Transact-SQL)
• REVERT (Transact-SQL)
• EXECUTE AS (Transact-SQL)