Tutorial: Ownership Chains and Context SwitchingTutorial: Ownership Chains and Context Switching

Anhand des Szenarios in diesem Lernprogramm werden SQL ServerSQL Server -Sicherheitskonzepte verdeutlicht, die Besitzketten und Kontextwechsel umfassen.This tutorial uses a scenario to illustrate SQL ServerSQL Server security concepts involving ownership chains and user context switching.

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 AdventureWorks2012AdventureWorks2012 -Datenbank installiert haben.To run the code in this tutorial you must have both Mixed Mode security configured and the AdventureWorks2012AdventureWorks2012 database installed. Weitere Informationen zur Sicherheit für den gemischte Modus finden Sie unter Auswählen eines Authentifizierungsmodus.For more information about Mixed Mode security, see Choose an Authentication Mode.

SzenarioScenario

In diesem Szenario benötigen zwei Benutzer Konten, über die sie auf die Bestellungsdaten zugreifen können, die in der AdventureWorks2012AdventureWorks2012 -Datenbank gespeichert sind.In this scenario, two users need accounts to access purchase order data stored in the AdventureWorks2012AdventureWorks2012 database. Es gelten folgende Anforderungen:The requirements are as follows:

  • Über das erste Konto (TestManagerUser) muss es möglich sein, alle Details in jeder Bestellung anzeigen zu können.The first account (TestManagerUser) must be able to see all details in every purchase order.

  • Ü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.The second account (TestEmployeeUser) must be able to see the purchase order number, order date, shipping date, product ID numbers, and the ordered and received items per purchase order, by purchase order number, for items where partial shipments have been received.

  • Alle anderen Konten müssen ihre aktuellen Berechtigungen behalten.All other accounts must retain their current permissions.

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:To fulfill the requirements of this scenario, the example is broken into four parts that demonstrate the concepts of ownership chains and context switching:

  1. Konfigurieren der Umgebung.Configuring the environment.

  2. Erstellen einer gespeicherten Prozedur, damit nach Bestellung auf die Daten zugegriffen werden kann.Creating a stored procedure to access data by purchase order.

  3. Zugreifen auf die Daten über die gespeicherte Prozedur.Accessing the data through the stored procedure.

  4. Zurücksetzen der Umgebung.Resetting the environment.

Jeder Codeblock dieses Beispiels wird jeweils sofort erläutert.Each code block in this example is explained in line. Informationen, wie Sie das vollständige Beispiel kopieren können, finden Sie unter Vollständiges Beispiel am Ende dieses Lernprogramms.To copy the complete example, see Complete Example at the end of this tutorial.

1. Konfigurieren der Umgebung1. Configure the Environment

Verwenden Sie SQL Server Management StudioSQL Server Management Studio sowie den folgenden Code, um die AdventureWorks2012-Datenbank zu öffnen. Vergewissern Sie sich mit der Transact-SQLTransact-SQL-Anweisung CURRENT_USER, dass der Benutzer dbo als Kontext angezeigt wird.Use SQL Server Management StudioSQL Server Management Studio and the following code to open the AdventureWorks2012 database, and use the CURRENT_USER Transact-SQLTransact-SQL statement to check that the dbo user is displayed as the context.

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

Weitere Informationen zur CURRENT_USER-Anweisung finden Sie unter CURRENT_USER (Transact-SQL).For more information about the CURRENT_USER statement, see CURRENT_USER (Transact-SQL).

Verwenden Sie diesen Code als Benutzer dbo dazu, auf dem Server und in der AdventureWorks2012AdventureWorks2012-Datenbank zwei Benutzer zu erstellen.Use this code as the dbo user to create two users on the server and in the AdventureWorks2012AdventureWorks2012 database.

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).For more information about the CREATE USER statement, see CREATE USER (Transact-SQL). Weitere Informationen zur CREATE LOGIN-Anweisung finden Sie unter CREATE LOGIN (Transact-SQL).For more information about the CREATE LOGIN statement, see CREATE LOGIN (Transact-SQL).

Übertragen Sie mit dem folgenden Code den Besitz des Purchasing -Schemas auf das Konto TestManagerUser .Use the following code to change the ownership of the Purchasing schema to the TestManagerUser account. 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.This allows that account to use all Data Manipulation Language (DML) statement access (such as SELECT and INSERT permissions) on the objects it contains. TestManagerUser wird auch die Berechtigung zum Erstellen gespeicherter Prozeduren erteilt.TestManagerUser is also granted the ability to create stored procedures.

/* 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).For more information about the GRANT statement, see GRANT (Transact-SQL). Weitere Informationen zum Aufrufen von gespeicherten Prozeduren finden Sie unter Gespeicherte Prozeduren (Datenbankmodul).For more information about stored procedures, see Stored Procedures (Database Engine). Eine Sammlung aller DatenbankmodulDatabase Engine-Berechtigungen als Poster finden Sie unter http://go.microsoft.com/fwlink/?LinkId=229142.For a poster of all DatenbankmodulDatabase Engine permissions, see http://go.microsoft.com/fwlink/?LinkId=229142.

2. Erstellen einer gespeicherten Prozedur für Zugriff auf Daten2. Create a Stored Procedure to Access Data

Um den Kontext innerhalb einer Datenbank zu wechseln, verwenden Sie die EXECUTE AS-Anweisung.To switch context within a database, use the EXECUTE AS statement. EXECUTE AS erfordert IMPERSONATE-Berechtigungen.EXECUTE AS requires IMPERSONATE permissions.

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.Use the EXECUTE AS statement in the following code to change the context to TestManagerUser and create a stored procedure showing only the data required by TestEmployeeUser. 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.To satisfy the requirements, the stored procedure accepts one variable for the purchase order number and does not display financial information, and the WHERE clause limits the results to partial shipments.

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.Currently TestEmployeeUser does not have access to any database objects. 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.The following code (still in the TestManagerUser context) grants the user account the ability to query base-table information through the stored procedure.

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.The stored procedure is part of the Purchasing schema, even though no schema was explicitly specified, because TestManagerUser is assigned by default to the Purchasing schema. Wie im folgenden Code gezeigt, können Sie Systemkataloginformationen dazu verwenden, nach Objekten zu suchen.You can use system catalog information to locate objects, as shown in the following 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  

Nachdem dieser Abschnitt des Beispiels abgeschlossen ist, wird im Code die REVERT-Anweisung dazu verwendet, wieder einen Kontextwechsel auf dbo vorzunehmen.With this section of the example completed, the code switches context back to dbo using the REVERT statement.

REVERT;  
GO  

Weitere Informationen zur REVERT-Anweisung finden Sie unter REVERT (Transact-SQL).For more information about the REVERT statement, see REVERT (Transact-SQL).

3. Zugreifen auf Daten über die gespeicherte Prozedur3. Access Data Through the Stored Procedure

TestEmployeeUser hat für die Objekte der AdventureWorks2012AdventureWorks2012 -Datenbank keine Berechtigungen, die über eine Anmeldung und die Berechtigungen hinausgehen, die der public-Datenbankrolle zugeordnet sind.TestEmployeeUser has no permissions on the AdventureWorks2012AdventureWorks2012 database objects other than a login and the rights assigned to the public database role. Der folgende Code gibt einen Fehler zurück, wenn TestEmployeeUser versucht, auf eine der Basistabellen zuzugreifen.The following code returns an error when TestEmployeeUser attempts to access base tables.

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  

Ü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.Because the objects referenced by the stored procedure created in the last section are owned by TestManagerUser by virtue of the Purchasing schema ownership, TestEmployeeUser can access the base tables through the stored procedure. Im folgenden Code, für den weiterhin der TestEmployeeUser -Kontext verwendet wird, wird die Bestellnummer 952 als Parameter übergeben.The following code, still using the TestEmployeeUser context, passes purchase order 952 as a parameter.

EXEC Purchasing.usp_ShowWaitingItems 952  
GO  

4. Zurücksetzen der Umgebung4. Reset the Environment

Im folgenden Code wird der Befehl REVERT verwendet, um den Kontext des aktuellen Kontos auf dbozurückzusetzen, und dann die Umgebung zurückgesetzt.The following code uses the REVERT command to return the context of the current account to dbo, and then resets the environment.

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 BeispielComplete Example

In diesem Abschnitt wird der vollständige Beispielcode angezeigt.This section displays the complete example code.

Hinweis

Dieser Code enthält nicht die beiden erwarteten Fehler, die verdeutlichen, dass TestEmployeeUser keine Informationen aus Basistabellen auswählen kann.This code does not include the two expected errors that demonstrate the inability of TestEmployeeUser to select from base tables.

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

Siehe auchSee Also

Sicherheitscenter für SQL Server-Datenbankmodul und Azure SQL-DatenbankSecurity Center for SQL Server Database Engine and Azure SQL Database