Självstudie: Ägarskapskedjor och kontextväxling

gäller för:SQL Server Azure SQL Managed Instance

I den här självstudien används ett scenario för att illustrera säkerhetsbegrepp för SQL Server som omfattar ägarskapskedjor och växling av användarkontext.

Anmärkning

Om du vill köra koden i den här handledningen måste du ha både konfigurerad säkerhet i blandat läge och AdventureWorks2025-databasen installerad. Mer information om säkerhet i blandat läge finns i Välj ett autentiseringsläge.

Scenario

I det här scenariot behöver två användare konton för att komma åt inköpsorderdata som lagras i AdventureWorks2025 databasen. Kraven är följande:

Det första kontot (TestManagerUser) måste kunna se all information i varje inköpsorder.
Det andra kontot (TestEmployeeUser) måste kunna se inköpsordernummer, orderdatum, leveransdatum, produkt-ID-nummer och beställda och mottagna artiklar per inköpsorder, efter inköpsordernummer, för artiklar där partiella leveranser har tagits emot.
Alla andra konton måste behålla sina aktuella behörigheter.
För att uppfylla kraven i det här scenariot delas exemplet upp i fyra delar som visar begreppen ägarskapskedjor och kontextväxling:

Konfigurera miljön.
Skapa en lagrad procedur för att komma åt data via inköpsorder.
Åtkomst till data via den lagrade proceduren.
Återställa miljön.

Varje kodblock i det här exemplet förklaras på rad. Om du vill kopiera det fullständiga exemplet, se Fullständigt exempel i slutet av den här självstudien.

Förutsättningar

För att slutföra den här självstudien behöver du SQL Server Management Studio, åtkomst till en server som kör SQL Server och en AdventureWorks2025 databas.

Installera SQL Server Management Studio.
Installera SQL Server 2017 Developer Edition.
Ladda ned AdventureWorks-exempeldatabaser.

Anvisningar om hur du återställer en databas i SQL Server Management Studio finns i Återställa en databas.

1. Konfigurera miljön

Använd SQL Server Management Studio och följande kod för att öppna AdventureWorks2025 databasen och använd instruktionen CURRENT_USER Transact-SQL för att kontrollera att dbo-användaren visas som kontext.

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

Mer information om CURRENT_USER-instruktionen finns i CURRENT_USER (Transact-SQL).

Använd den här koden som dbo-användare för att skapa två användare på servern och i AdventureWorks2025 databasen.

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

Mer information om INSTRUKTIONEN SKAPA ANVÄNDARE finns i CREATE USER (Transact-SQL). Mer information om INSTRUKTIONEN SKAPA INLOGGNING finns i SKAPA INLOGGNING (Transact-SQL).

Använd följande kod för att ändra ägarskapet för schemat Purchasing till kontot TestManagerUser. Detta gör att kontot kan använda all DML-instruktionsåtkomst (datamanipuleringsspråk) (till exempel SELECT och INSERT behörigheter) för de objekt som det innehåller. TestManagerUser ges också möjlighet att skapa lagrade procedurer.

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

Mer information om GRANT-instruktionen finns i GRANT (Transact-SQL). Mer information om lagrade procedurer finns i Lagrade procedurer (databasmotor). En affisch med alla behörigheter för databasmotorn finns i https://aka.ms/sql-permissions-poster.

2. Skapa en lagrad procedur för åtkomst till data

Om du vill växla kontext i en databas använder du EXECUTE AS-instruktionen. KÖR SOM kräver IMPERSONATE-behörigheter.

Använd -instruktionen EXECUTE AS i följande kod för att ändra kontexten till TestManagerUser och skapa en lagrad procedur som endast visar de data som krävs av TestEmployeeUser. För att uppfylla kraven accepterar den lagrade proceduren en variabel för inköpsordernumret och visar inte ekonomisk information, och WHERE-satsen begränsar resultatet till partiella leveranser.

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

För närvarande TestEmployeeUser har inte åtkomst till några databasobjekt. Följande kod (fortfarande i kontexten TestManagerUser) ger användarkontot möjlighet att hämta basinformation från tabellen via den lagrade proceduren.

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

Den lagrade proceduren är en del av Purchasing-schemat, även om inget schema uttryckligen angavs, eftersom TestManagerUser som standard tilldelas schemat Purchasing. Du kan använda systemkataloginformation för att hitta objekt, enligt följande kod.

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

När det här avsnittet av exemplet har slutförts växlar koden tillbaka kontexten till dbo med hjälp av -instruktionen REVERT .

REVERT;  
GO

Mer information om REVERT-instruktionen finns i REVERT (Transact-SQL).

3. Få åtkomst till data via den lagrade proceduren

TestEmployeeUser har inga behörigheter för databasobjekten AdventureWorks2025 förutom en inloggning och de rättigheter som tilldelats till den offentliga databasrollen. Följande kod returnerar ett fel när TestEmployeeUser försöker komma åt bastabeller.

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

Felet som returneras:

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

Eftersom objekten som den lagrade proceduren som refereras till skapades i det sista avsnittet ägs av TestManagerUser i kraft av schemaägarskapet Purchasing kan TestEmployeeUser komma åt bastabellerna via den lagrade proceduren. Följande kod, som fortfarande använder kontexten TestEmployeeUser , skickar inköpsorder 952 som en parameter.

EXEC Purchasing.usp_ShowWaitingItems 952  
GO

4. Återställ miljön

Följande kod använder REVERT kommandot för att returnera kontexten för det aktuella kontot till dbooch återställer sedan miljön.

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

Fullständigt exempel

Det här avsnittet visar den fullständiga exempelkoden.

Anmärkning

Den här koden innehåller inte de två förväntade felen som demonstrerar TestEmployeeUsers oförmåga att välja från basala tabeller.

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

Se även

Säkerhetscenter för SQL Server Database Engine och Azure SQL Database

Feedback

Var den här sidan till hjälp?

Last updated on 2025-11-19