Tutorial: Ownership Chains and Context Switching

Gilt für: SQL Server Azure SQL Managed Instance

Anhand des Szenarios in diesem Tutorial werden SQL Server-Sicherheitskonzepte verdeutlicht, die Besitzketten und Kontextwechsel umfassen.

Hinweis

Damit Sie den Code ausführen können, der in diesem Lernprogramm enthalten ist, müssen Sie die Sicherheit für den gemischte Modus konfiguriert und die AdventureWorks2022 -Datenbank installiert haben. Weitere Informationen zur Sicherheit für den gemischte Modus finden Sie unter Auswählen eines Authentifizierungsmodus.

Szenario

In diesem Szenario benötigen zwei Benutzer Konten, über die sie auf die Bestellungsdaten zugreifen können, die in der AdventureWorks2022 -Datenbank gespeichert sind. Es gelten folgende Anforderungen:

• Über das erste Konto (TestManagerUser) muss es möglich sein, alle Details in jeder Bestellung anzeigen zu können.
• Über das zweite Konto (TestEmployeeUser) muss es möglich sein, für Artikel, für die Teillieferungen eingegangen sind, folgende Informationen anzeigen zu können: Bestellnummer, Bestelldatum, Versanddatum, Produktnummern sowie die pro Bestellung bestellten und eingegangenen Artikel.
• Alle anderen Konten müssen ihre aktuellen Berechtigungen behalten.
  Damit die Anforderungen dieses Szenarios erfüllt werden können, ist dieses Beispiel in vier Abschnitte unterteilt, in denen die Konzepte für Besitzketten und Kontextwechsel dargestellt werden:

1. Konfigurieren der Umgebung.
2. Erstellen einer gespeicherten Prozedur, damit nach Bestellung auf die Daten zugegriffen werden kann.
3. Zugreifen auf die Daten über die gespeicherte Prozedur.
4. Zurücksetzen der Umgebung.

Jeder Codeblock dieses Beispiels wird jeweils sofort erläutert. Informationen, wie Sie das vollständige Beispiel kopieren können, finden Sie unter Vollständiges Beispiel am Ende dieses Lernprogramms.

Voraussetzungen

Zur Durchführung dieses Tutorials benötigen Sie SQL Server Management Studio, Zugriff auf einen Server, auf dem SQL-Server ausgeführt wird, und eine AdventureWorks2022-Datenbank.

• Installieren Sie SQL Server Management Studio.
• Installieren Sie die SQL Server 2017 Developer Edition.
• Laden Sie eine AdventureWorks-Beispieldatenbank herunter.

Weitere Informationen zum Wiederherstellen einer Datenbank in SQL Server Management Studio finden Sie unter Wiederherstellen einer Datenbank.

1. Konfigurieren der Umgebung

Verwenden Sie SQL Server Management Studio und den folgenden Code, um die Datenbank AdventureWorks2022 zu öffnen, und verwenden Sie die Transact-SQL-Anweisung CURRENT_USER, um zu überprüfen, ob der dbo-Benutzer als Kontext angezeigt wird.

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

Weitere Informationen zur CURRENT_USER-Anweisung finden Sie unter CURRENT_USER (Transact-SQL).

Verwenden Sie diesen Code als Benutzer dbo dazu, auf dem Server und in der AdventureWorks2022-Datenbank zwei Benutzer zu erstellen.

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   

Weitere Informationen zur CREATE USER-Anweisung finden Sie unter CREATE USER (Transact-SQL). Weitere Informationen zur CREATE LOGIN-Anweisung finden Sie unter CREATE LOGIN (Transact-SQL).

Übertragen Sie mit dem folgenden Code den Besitz des Purchasing -Schemas auf das Konto TestManagerUser . Dadurch wird es ermöglicht, unter diesem Konto mit allen DML-Anweisungen (Data Manipulation Language, z. B. SELECT - und INSERT -Berechtigungen) auf die Objekte zuzugreifen, die das Schema enthält. TestManagerUser wird auch die Berechtigung zum Erstellen gespeicherter Prozeduren erteilt.

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

Weitere Informationen zur GRANT-Anweisung finden Sie unter GRANT (Transact-SQL). Weitere Informationen zum Aufrufen von gespeicherten Prozeduren finden Sie unter Gespeicherte Prozeduren (Datenbank-Engine). Ein Poster über alle Datenbank-Engine-Berechtigungen finden Sie unter https://aka.ms/sql-permissions-poster.

2. Erstellen einer gespeicherten Prozedur für Zugriff auf Daten

Um den Kontext innerhalb einer Datenbank zu wechseln, verwenden Sie die EXECUTE AS-Anweisung. EXECUTE AS erfordert IMPERSONATE-Berechtigungen.

Im folgenden Code wird die EXECUTE AS -Anweisung dazu verwendet, einen Kontextwechsel auf TestManagerUser vorzunehmen und eine gespeicherte Prozedur zu erstellen, die nur die Daten anzeigt, die von TestEmployeeUserbenötigt werden. Damit die Anforderungen erfüllt werden, ist die gespeicherte Prozedur wie folgt aufgebaut: Sie nimmt eine Variable für die Bestellnummer entgegen, zeigt keine Finanzinformationen an und enthält eine WHERE-Klausel, die die Ergebnisse auf Teillieferungen beschränkt.

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  

Momentan kann TestEmployeeUser auf keines der Datenbankobjekte zugreifen. Der folgende Code (nach wie vor im TestManagerUser -Kontext) erteilt dem Benutzerkonto die Berechtigung, über die gespeicherte Prozedur Informationen aus den Basistabellen abrufen zu können.

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

Die gespeicherte Prozedur gehört, obwohl kein Schema explizit angegeben wurde, zum Purchasing -Schema, weil TestManagerUser standardmäßig dem Purchasing -Schema zugeordnet ist. Wie im folgenden Code gezeigt, können Sie Systemkataloginformationen dazu verwenden, nach Objekten zu suchen.

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  

Nachdem dieser Abschnitt des Beispiels abgeschlossen ist, wird im Code die REVERT-Anweisung dazu verwendet, wieder einen Kontextwechsel auf dbo vorzunehmen.

REVERT;  
GO  

Weitere Informationen zur REVERT-Anweisung finden Sie unter REVERT (Transact-SQL).

3. Zugreifen auf Daten über die gespeicherte Prozedur

TestEmployeeUser hat für die Objekte der AdventureWorks2022 -Datenbank keine Berechtigungen, die über eine Anmeldung und die Berechtigungen hinausgehen, die der public-Datenbankrolle zugeordnet sind. Der folgende Code gibt einen Fehler zurück, wenn TestEmployeeUser versucht, auf eine der Basistabellen zuzugreifen.

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  

Der zurückgegebene Fehler:

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

Über die gespeicherte Prozedur, die im vorherigen Abschnitt erstellt wurde, kann TestManagerUser auf die Basistabellen zugreifen, weil sich die Objekte, auf die in der gespeicherten Prozedur verwiesen wird, über den Besitz des Purchasing -Schemas im Besitz von TestEmployeeUser befinden. Im folgenden Code, für den weiterhin der TestEmployeeUser -Kontext verwendet wird, wird die Bestellnummer 952 als Parameter übergeben.

EXEC Purchasing.usp_ShowWaitingItems 952  
GO  

4. Zurücksetzen der Umgebung

Im folgenden Code wird der Befehl REVERT verwendet, um den Kontext des aktuellen Kontos auf dbozurückzusetzen, und dann die Umgebung zurückgesetzt.

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  

Vollständiges Beispiel

In diesem Abschnitt wird der vollständige Beispielcode angezeigt.

Hinweis

Dieser Code enthält nicht die beiden erwarteten Fehler, die verdeutlichen, dass TestEmployeeUser keine Informationen aus Basistabellen auswählen kann.

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

Weitere Informationen

Sicherheitscenter für SQL Server-Datenbank-Engine und Azure SQL-Datenbank