Didacticiel : chaînes de propriétés et changement de contexte

Scénario

Dans ce scénario, deux utilisateurs ont besoin de comptes leur permettant d'accéder aux données des bons de commande stockées dans la base de données AdventureWorks2008R2. Les exigences requises sont les suivantes :

• Le premier compte (TestManagerUser) doit être en mesure de dévoiler toutes les données détaillées dans chaque bon de commande.

• Le deuxième compte (TestEmployeeUser) doit afficher le numéro de bon de commande, la date de commande, la date d'expédition, les ID des produits et les articles commandés et reçus par bon de commande (par numéro de bon de commande) pour les articles pour lesquels des livraisons partielles ont été reçues.

• Tous les autres comptes doivent conserver leurs autorisations actuelles.

Pour répondre aux exigences de ce scénario, l'exemple est divisé en quatre parties décrivant les concepts relatifs aux chaînes de propriétés et au changement de contexte :

1. Configuration de l'environnement

2. Création d'une procédure stockée pour l'accès aux données par bon de commande

3. Accès aux données par le biais de la procédure stockée

4. Réinitialisation de l'environnement

Chaque bloc de code dans cet exemple est présenté sous forme de lignes. Pour copier l'exemple tout entier, consultez la section Exemple complet à la fin de ce didacticiel.

1. Configurez l'environnement

Utilisez SQL Server Management Studio et le code ci-dessous pour ouvrir la base de données AdventureWorks2008R2 ; ensuite, à l'aide de l'instruction CURRENT_USERTransact-SQL, vérifiez que l'utilisateur dbo est affiché dans le contexte.

USE AdventureWorks2008R2;
GO
SELECT CURRENT_USER AS 'Current User Name';
GO

Pour plus d'informations sur l'instruction CURRENT_USER, consultez CURRENT_USER (Transact-SQL).

Utilisez ce code en tant qu'utilisateur dbo pour créer deux utilisateurs sur le serveur et dans la base de données AdventureWorks2008R2.

CREATE LOGIN TestManagerUser 
    WITH PASSWORD = '340$Uuxwp7Mcxo7Khx';
GO
CREATE USER TestManagerUser 
   FOR LOGIN TestManagerUser
   WITH DEFAULT_SCHEMA = Purchasing;
GO 

CREATE LOGIN TestEmployeeUser
    WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';
GO
CREATE USER TestEmployeeUser 
   FOR LOGIN TestEmployeeUser;
GO 

Pour plus d'informations sur l'instruction CREATE USER, consultez CREATE USER (Transact-SQL). Pour plus d'informations sur l'instruction CREATE LOGIN, consultez CREATE LOGIN (Transact-SQL).

Utilisez le code suivant pour modifier la propriété du schéma Purchasing du compte TestManagerUser. Le compte peut alors exploiter l'accès à toutes les instructions DML (Data Manipulation Language, langage de manipulation de données), notamment les autorisations SELECT et INSERT, sur les objets qu'il contient. Ceci n'incluant pas les autorisations DDL (Data Definition Language, langage de définition de données), TestManagerUser se voit explicitement accorder des droits pour les tables PurchaseOrderHeader et PurchaseOrderDetail, ainsi que la possibilité de créer des procédures stockées.

/* Change owner of the Purchasing Schema to TestManagerUser */
ALTER AUTHORIZATION 
   ON SCHEMA::Purchasing 
   TO TestManagerUser;
GO

/* Grant permissions to TestManagerUser on these objects with GRANT option */
GRANT ALL 
   ON OBJECT::AdventureWorks.Purchasing.PurchaseOrderHeader
    TO TestManagerUser 
   WITH GRANT OPTION;
GO
GRANT ALL 
   ON OBJECT::AdventureWorks.Purchasing.PurchaseOrderDetail
    TO TestManagerUser WITH GRANT OPTION;
GO
/* Note: DML works fine with Schema owner, but not DDL. */
GRANT CREATE PROCEDURE 
   TO TestManagerUser 
   WITH GRANT OPTION;
GO

Pour plus d'informations sur l'instruction GRANT, consultez GRANT (Transact-SQL). Pour plus d'informations sur les schémas de base de données, consultez Schémas (moteur de base de données). Pour plus d'informations sur les procédures stockées, consultez Procédures stockées (moteur de base de données).

2. Créer une procédure stockée pour accéder aux données

Vous avez deux moyens d'autoriser un utilisateur à changer de contexte au sein d'une base de données : SETUSER ou EXECUTE AS. Pour utiliser l'instruction SETUSER, l'appelant doit être membre du rôle serveur fixe sysadmin ou défini en tant que compte dbo. EXECUTE AS nécessite des autorisations IMPERSONATE. Pour plus d'informations sur ces concepts, consultez EXECUTE AS ou SETUSER.

Utilisez l'instruction EXECUTE AS dans le code ci-après pour modifier le contexte et le redéfinir à TestManagerUser et pour créer une procédure stockée affichant uniquement les données requises par TestEmployeeUser. Pour répondre aux exigences, la procédure stockée accepte une variable pour le numéro de bon de commande et n'affiche pas de données financières. De même, la clause WHERE limite les résultats aux expéditions partielles.

EXECUTE AS LOGIN = 'TestManagerUser'
GO
SELECT CURRENT_USER AS 'Current User Name';
GO

/* Note: The user that calls the EXECUTE AS statement must have IMPERSONATE permissions on the target principal */
CREATE PROCEDURE usp_ShowWaitingItems @ProductID int
AS
BEGIN 
   SELECT a.PurchaseOrderID, a.OrderDate, a.ShipDate
      , b.ProductID, b.OrderQty, b.ReceivedQty
   FROM Purchasing.PurchaseOrderHeader a
      INNER JOIN Purchasing.PurchaseOrderDetail b
         ON a.PurchaseOrderID = b.PurchaseOrderID
   WHERE b.OrderQty > b.ReceivedQty
      AND @ProductID = b.ProductID
   ORDER BY b.ProductID ASC
END
GO

Actuellement, TestEmployeeUser n'a accès à aucun objet de base de données. Le code suivant (toujours dans le contexte TestManagerUser) accorde au compte de l'utilisateur la possibilité d'interroger les informations des tables de base par l'intermédiaire de la procédure stockée.

GRANT EXECUTE
   ON OBJECT::Purchasing.usp_ShowWaitingItems
   TO TestEmployeeUser;
GO

La procédure stockée est un élément inhérent au schéma Purchasing, même si aucun schéma n'a été spécifié, puisque TestManagerUser est attribué par défaut au schéma Purchasing. Vous pouvez utiliser les informations du catalogue système pour rechercher des objets, comme le montre le code suivant.

SELECT a.name AS 'Schema'
   , b.name AS 'Object Name'
   , b.type AS 'Object Type'
FROM sys.schemas a
   INNER JOIN sys.objects b
      ON a.schema_id = b.schema_id 
WHERE b.name = 'usp_ShowWaitingItems';
GO

Pour plus d'informations sur les catalogues système, consultez Interrogation des catalogues système de SQL Server.

Une fois cette section de l'exemple terminée, le code change de nouveau de contexte pour repasser à dbo à l'aide de l'instruction REVERT.

REVERT;
GO

Pour plus d'informations sur l'instruction REVERT, consultez REVERT (Transact-SQL).

3. Accéder aux données par le biais de la procédure stockée

TestEmployeeUser ne dispose d'aucune autorisation pour les objets de la base de données AdventureWorks2008R2 en dehors d'une connexion et des droits attribués au rôle de base de données public. Le code suivant retourne une erreur lorsque TestEmployeeUser tente d'accéder aux tables de base.

EXECUTE AS LOGIN = 'TestEmployeeUser'
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
/* This won't work */
SELECT *
FROM Purchasing.PurchaseOrderHeader;
GO
SELECT *
FROM Purchasing.PurchaseOrderDetail;
GO

Du fait que les objets référencés par la procédure stockée créée au cours de la dernière section appartiennent à TestManagerUser en raison de la propriété du schéma Purchasing, TestEmployeeUser peut accéder aux tables de base par le biais de la procédure stockée. Le code suivant qui utilise toujours le contexte TestEmployeeUser transmet le bon de commande 952 en guise de paramètre.

EXEC Purchasing.usp_ShowWaitingItems 952
GO

4. Réinitialisez l'environnement

Le code ci-dessous exploite la commande REVERT pour retourner le contexte du compte actuel à dbo, puis réinitialise l'environnement.

REVERT;
GO
ALTER AUTHORIZATION 
ON SCHEMA::Purchasing TO dbo;
GO
DROP PROCEDURE Purchasing.usp_ShowWaitingItems
GO
DROP USER TestEmployeeUser;
GO
DROP USER TestManagerUser;
GO
DROP LOGIN TestEmployeeUser;
GO
DROP LOGIN TestManagerUser;
GO

Exemple complet

Cette section affiche l'exemple de code dans son intégralité.

/* 
Script:       UserContextTutorial.sql
Author:       Microsoft
Last Updated: Books Online
Conditions:   Execute as DBO or sysadmin in the AdventureWorks database
Section 1:    Configure the Environment 
*/

USE AdventureWorks2008R2;
GO
SELECT CURRENT_USER AS 'Current User Name';
GO

/* Create server and database users */
CREATE LOGIN TestManagerUser 
    WITH PASSWORD = '340$Uuxwp7Mcxo7Khx';
GO
CREATE USER TestManagerUser 
   FOR LOGIN TestManagerUser
   WITH DEFAULT_SCHEMA = Purchasing;
GO 

CREATE LOGIN TestEmployeeUser
    WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';
GO
CREATE USER TestEmployeeUser 
   FOR LOGIN TestEmployeeUser;
GO 

/* Change owner of the Purchasing Schema to TestManagerUser */
ALTER AUTHORIZATION 
   ON SCHEMA::Purchasing 
   TO TestManagerUser;
GO

/* Grant permissions to TestManagerUser on these objects with GRANT option */
GRANT ALL 
   ON OBJECT::AdventureWorks2008R2.Purchasing.PurchaseOrderHeader
    TO TestManagerUser 
   WITH GRANT OPTION;
GO
GRANT ALL 
   ON OBJECT::AdventureWorks2008R2.Purchasing.PurchaseOrderDetail
    TO TestManagerUser WITH GRANT OPTION;
GO
/* Note: DML works fine with Schema owner, but not DDL */
GRANT CREATE PROCEDURE 
   TO TestManagerUser 
   WITH GRANT OPTION;
GO

/* 
Section 2: Switch Context and Create Objects
*/
EXECUTE AS LOGIN = 'TestManagerUser'
GO
SELECT CURRENT_USER AS 'Current User Name';
GO

/* Note: The user that calls the EXECUTE AS statement must have IMPERSONATE permissions on the target principal */
CREATE PROCEDURE usp_ShowWaitingItems @ProductID int
AS
BEGIN 
   SELECT a.PurchaseOrderID, a.OrderDate, a.ShipDate
      , b.ProductID, b.OrderQty, b.ReceivedQty
   FROM Purchasing.PurchaseOrderHeader a
      INNER JOIN Purchasing.PurchaseOrderDetail b
         ON a.PurchaseOrderID = b.PurchaseOrderID
   WHERE b.OrderQty > b.ReceivedQty
      AND @ProductID = b.ProductID
   ORDER BY b.ProductID ASC
END
GO

/* Give the employee the ability to run the procedure */
GRANT EXECUTE 
   ON OBJECT::Purchasing.usp_ShowWaitingItems
   TO TestEmployeeUser;
GO 

/* Notice that the stored procedure is located in the Purchasing 
schema. This also demonstrates system catalogs */
SELECT a.name AS 'Schema'
   , b.name AS 'Object Name'
   , b.type AS 'Object Type'
FROM sys.schemas a
   INNER JOIN sys.objects b
      ON a.schema_id = b.schema_id 
WHERE b.name = 'usp_ShowWaitingItems';
GO

/* Go back to being the dbo user */
REVERT;
GO

/* 
Section 3: Switch Context and Observe Security 
*/
EXECUTE AS LOGIN = 'TestEmployeeUser'
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
EXEC Purchasing.usp_ShowWaitingItems 952
GO

/* 
Section 4: Clean Up Example
*/
REVERT;
GO
ALTER AUTHORIZATION 
ON SCHEMA::Purchasing TO dbo;
GO
DROP PROCEDURE Purchasing.usp_ShowWaitingItems
GO
DROP USER TestEmployeeUser;
GO
DROP USER TestManagerUser;
GO
DROP LOGIN TestEmployeeUser;
GO
DROP LOGIN TestManagerUser;
GO

Voir aussi

Concepts

Autres ressources