Lernprogramm: Besitzketten und Kontextwechsel

Szenario

In diesem Szenario benötigen zwei Benutzer Konten, über die sie auf die Bestellungsdaten zugreifen können, die in der AdventureWorks-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.

1. Konfigurieren der Umgebung

Verwenden Sie SQL Server Management Studio sowie den folgenden Code, um die AdventureWorks-Datenbank zu öffnen. Vergewissern Sie sich mit der Transact-SQL-Anweisung CURRENT_USER, dass der Benutzer dbo als Kontext angezeigt wird.

USE AdventureWorks;
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 AdventureWorks-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. Da dies keine DDL-Berechtigungen (Data Definition Language) umfasst, werden TestManagerUser explizit Berechtigungen für die Tabellen PurchaseOrderHeader und PurchaseOrderDetail sowie die Berechtigung zum Erstellen von gespeicherten Prozeduren erteilt.

/* Change owner of the Purchasing Schema to TestManagerUser */
ALTER AUTHORIZATION 
   ON SCHEMA::Purchasing 
   TO TestManagerUser;
GO

/* Grant permissions to TestManagerUser on these objects with GRANT option */
GRANT ALL 
   ON OBJECT::AdventureWorks.Purchasing.PurchaseOrderHeader
    TO TestManagerUser 
   WITH GRANT OPTION;
GO
GRANT ALL 
   ON OBJECT::AdventureWorks.Purchasing.PurchaseOrderDetail
    TO TestManagerUser WITH GRANT OPTION;
GO
/* Note: DML works fine with Schema owner, but not DDL. */
GRANT CREATE PROCEDURE 
   TO TestManagerUser 
   WITH GRANT OPTION;
GO

Weitere Informationen zur GRANT-Anweisung finden Sie unter GRANT (Transact-SQL). Weitere Informationen zu Datenbankschemas finden Sie unter Schemas (Datenbankmodul). Weitere Informationen zu gespeicherten Prozeduren finden Sie unter Gespeicherte Prozeduren (Datenbankmodul).

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

Sie haben zwei Möglichkeiten, einem Benutzer ein Wechseln des Kontextes in einer Datenbank zu ermöglichen: SETUSER oder EXECUTE AS. Wenn die SETUSER-Anweisung verwendet werden soll, muss der Aufrufer Mitglied der festen Serverrolle sysadmin oder unter dem Konto dbo angemeldet sein. EXECUTE AS erfordert IMPERSONATE-Berechtigungen. Weitere Informationen zu diesen Konzepten finden Sie unter EXECUTE AS im Vergleich zu SETUSER.

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 TestEmployeeUser benö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

Weitere Informationen zu Systemkatalogen finden Sie unter Abfragen des SQL Server-Systemkatalogs.

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 AdventureWorks-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

Über die gespeicherte Prozedur, die im vorherigen Abschnitt erstellt wurde, kann TestEmployeeUser 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 TestManagerUser 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 dbo zurü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 AdventureWorks;
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 permissions to TestManagerUser on these objects with GRANT option */
GRANT ALL 
   ON OBJECT::AdventureWorks.Purchasing.PurchaseOrderHeader
    TO TestManagerUser 
   WITH GRANT OPTION;
GO
GRANT ALL 
   ON OBJECT::AdventureWorks.Purchasing.PurchaseOrderDetail
    TO TestManagerUser WITH GRANT OPTION;
GO
/* Note: DML works fine with Schema owner, but not DDL */
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 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

/* 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 a
   INNER JOIN sys.objects 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 auch

Konzepte

Andere Ressourcen