Zelfstudie: Eigendomsketens en contextwisselingen

Van toepassing op:SQL Server Azure SQL Managed Instance

In deze zelfstudie wordt een scenario gebruikt om beveiligingsconcepten van SQL Server te illustreren met betrekking tot eigendomsketens en het schakelen tussen gebruikerscontexten.

Opmerking

Als u de code in deze zelfstudie wilt uitvoeren, moet zowel de beveiliging in de gemengde modus als de AdventureWorks2025 database zijn geïnstalleerd. Zie Een verificatiemodus kiezen voor meer informatie over de beveiliging van gemengde modus.

Scenario

In dit scenario hebben twee gebruikers accounts nodig voor toegang tot inkoopordergegevens die zijn opgeslagen in de AdventureWorks2025 database. De vereisten zijn als volgt:

Het eerste account (TestManagerUser) moet alle details in elke inkooporder kunnen zien.
Het tweede account (TestEmployeeUser) moet het inkoopordernummer, de besteldatum, de verzenddatum, de product-id-nummers en de bestelde en ontvangen artikelen per inkooporder, op inkoopordernummer, kunnen zien voor artikelen waar gedeeltelijke zendingen zijn ontvangen.
Alle andere accounts moeten hun huidige machtigingen behouden.
Om aan de vereisten van dit scenario te voldoen, wordt het voorbeeld onderverdeeld in vier delen die de concepten van eigendomsketens en contextwisselingen demonstreren:

De omgeving configureren.
Een opgeslagen procedure maken voor toegang tot gegevens per inkooporder.
Toegang tot de gegevens via de opgeslagen procedure.
De omgeving opnieuw instellen.

Elk codeblok in dit voorbeeld wordt regel voor regel uitgelegd. Zie Volledig voorbeeld aan het einde van deze zelfstudie als u het volledige voorbeeld wilt kopiëren.

Vereiste voorwaarden

Voor het voltooien van deze zelfstudie hebt u SQL Server Management Studio nodig, toegang tot een server waarop SQL Server wordt uitgevoerd en een AdventureWorks2025-database.

Installeer SQL Server Management Studio-.
Installeer SQL Server 2017 Developer Edition.
Download AdventureWorks-voorbeelddatabases.

Zie Een database herstellen voor instructies over het herstellen van een database in SQL Server Management Studio.

1. De omgeving configureren

Gebruik SQL Server Management Studio en de volgende code om de AdventureWorks2025 database te openen en gebruik de Transact-SQL instructie CURRENT_USER om te controleren of de dbo-gebruiker wordt weergegeven als context.

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

Zie CURRENT_USER (Transact-SQL) voor meer informatie over de CURRENT_USER-instructie.

Gebruik deze code als dbo-gebruiker om twee gebruikers op de server en in de AdventureWorks2025 database te maken.

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

Zie CREATE USER (Transact-SQL) voor meer informatie over de instructie CREATE USER. Zie CREATE LOGIN (Transact-SQL) voor meer informatie over de instructie CREATE LOGIN.

Gebruik de volgende code om het eigendom van het Purchasing schema te wijzigen in het TestManagerUser account. Hierdoor kan dit account alle toegangsrechten van de DML-instructie (Data Manipulation Language) (zoals SELECT en INSERT machtigingen) gebruiken voor de objecten die het bevat. TestManagerUser krijgt ook de mogelijkheid om opgeslagen procedures te maken.

/* 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

Zie GRANT (Transact-SQL) voor meer informatie over de GRANT-instructie. Zie Opgeslagen procedures (Database Engine) voor meer informatie over opgeslagen procedures. Zie voor een poster met alle Database Engine-machtigingen https://aka.ms/sql-permissions-poster.

2. Een opgeslagen procedure maken voor toegang tot gegevens

Als u de context in een database wilt wijzigen, gebruikt u de EXECUTE AS-instructie. EXECUTE AS vereist IMPERSONATE-machtigingen.

Gebruik de EXECUTE AS instructie in de volgende code om de context te TestManagerUser wijzigen en een opgeslagen procedure te maken die alleen de vereiste gegevens toont voor TestEmployeeUser. Om aan de vereisten te voldoen, accepteert de opgeslagen procedure één variabele voor het inkoopordernummer en geeft geen financiële informatie weer, en de WHERE-component beperkt de resultaten tot gedeeltelijke verzendingen.

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

Momenteel TestEmployeeUser heeft geen toegang tot databaseobjecten. De volgende code (nog steeds in de TestManagerUser context) verleent het gebruikersaccount de mogelijkheid om gegevens uit de basistabel op te vragen via de opgeslagen procedure.

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

De opgeslagen procedure maakt deel uit van het Purchasing schema, ook al is er geen schema expliciet opgegeven, omdat TestManagerUser deze standaard is toegewezen aan het Purchasing schema. U kunt systeemcatalogusgegevens gebruiken om objecten te zoeken, zoals wordt weergegeven in de volgende code.

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

Wanneer deze sectie van het voorbeeld is voltooid, wordt de context teruggezet naar dbo met behulp van de REVERT instructie.

REVERT;  
GO

Zie REVERT (Transact-SQL)voor meer informatie over de revert-instructie.

3. Toegang tot gegevens via de opgeslagen procedure

TestEmployeeUser heeft geen machtigingen voor de AdventureWorks2025 databaseobjecten anders dan een aanmelding en de rechten die zijn toegewezen aan de rol openbare database. De volgende code retourneert een fout wanneer TestEmployeeUser wordt geprobeerd toegang te krijgen tot basistabellen.

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

De fout die wordt teruggegeven:

Msg 229, Level 14, State 5, Line 6
The SELECT permission was denied on the object 'PurchaseOrderHeader', database 'AdventureWorks2022', schema 'Purchasing'.

Omdat de objecten waarnaar wordt verwezen door de opgeslagen procedure die in de laatste sectie is gemaakt, eigendom zijn van TestManagerUser door het Purchasing schema-eigendom, kan TestEmployeeUser toegang hebben tot de basistabellen via de opgeslagen procedure. De volgende code, nog steeds met behulp van de TestEmployeeUser context, geeft inkooporder 952 door als parameter.

EXEC Purchasing.usp_ShowWaitingItems 952  
GO

4. De omgeving opnieuw instellen

De volgende code gebruikt de REVERT opdracht om de context van het huidige account naar dbo te retourneren en stelt vervolgens de omgeving opnieuw in.

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

Volledig voorbeeld

In deze sectie wordt de volledige voorbeeldcode weergegeven.

Opmerking

Deze code bevat niet de twee verwachte fouten die aantonen TestEmployeeUser dat het niet mogelijk is om te selecteren uit basistabellen.

/*   
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

Zie ook

Security Center voor SQL Server Database Engine en Azure SQL Database

Feedback

Is deze pagina nuttig?

Last updated on 2025-11-19