Tutorial: Ownership Chains and Context Switching
S’applique à :
SQL ServerAzure SQL Managed Instance
Ce tutoriel explore en se fondant sur un scénario les concepts de sécurité de SQL Server impliquant les chaînes de propriétés et le changement de contexte utilisateur.
Note
Pour exécuter le code de ce didacticiel, vous devez à la fois configurer la sécurité en mode mixte et installer la base de données AdventureWorks2022 . Pour plus d’informations sur la sécurité en mode mixte, consultez Choisir un mode d’authentification.
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 AdventureWorks2022 . 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 :
- Configuration de l'environnement
- Création d'une procédure stockée pour l'accès aux données par bon de commande
- Accès aux données par le biais de la procédure stockée
- 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.
Prerequisites
Pour suivre ce tutoriel, vous avez besoin de SQL Server Management Studio, de l'accès à un serveur qui exécute SQL Server et d'une base de données AdventureWorks2022.
- Installez SQL Server Management Studio.
- Installez SQL Server 2017 Developer Edition.
- Téléchargez un échantillon de base de données AdventureWorks.
Pour obtenir des instructions sur la restauration d’une base de données dans SQL Server Management Studio, consultez Restaurer une base de données.
1. Configurez l'environnement
Utilisez SQL Server Management Studio et le code suivant pour ouvrir la AdventureWorks2022 base de données et utilisez l’instruction CURRENT_USER Transact-SQL pour vérifier que l’utilisateur dbo s’affiche en tant que contexte.
USE AdventureWorks2022;
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 AdventureWorks2022.
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. TestManagerUser se voit également octroyer 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 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 procédures stockées, consultez Procédures stockées (moteur de base de données). Pour obtenir une affiche de toutes les autorisations du moteur de base de données, consultez https://aka.ms/sql-permissions-poster.
2. Créer une procédure stockée pour accéder aux données
Pour basculer le contexte dans une base de données, utilisez l’instruction EXECUTE AS. EXECUTE AS nécessite des autorisations IMPERSONATE.
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
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 AdventureWorks2022 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
Erreur retournée :
Msg 229, Level 14, State 5, Line 6
The SELECT permission was denied on the object 'PurchaseOrderHeader', database 'AdventureWorks2022', schema 'Purchasing'.
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é.
Note
Ce code n'inclut pas les deux erreurs attendues et expliquant l'inaptitude de TestEmployeeUser à effectuer une sélection à partir des tables de base.
/*
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 AdventureWorks2022;
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 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 AS a
INNER JOIN Purchasing.PurchaseOrderDetail AS 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 AS a
INNER JOIN sys.objects AS 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
Centre de sécurité pour le moteur de base de données SQL Server et Azure SQL Database
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour