Tutorial: Ownership Chains and Context Switching
Gilt für:
SQL ServerAzure 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:
- Konfigurieren der Umgebung.
- Erstellen einer gespeicherten Prozedur, damit nach Bestellung auf die Daten zugegriffen werden kann.
- Zugreifen auf die Daten über die gespeicherte Prozedur.
- 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 AdventureWorks2022 Datenbank zu öffnen, und verwenden Sie die CURRENT_USER Transact-SQL-Anweisung, 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).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 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).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).
Ü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).For more information about the GRANT statement, see GRANT (Transact-SQL). Weitere Informationen zum Aufrufen von gespeicherten Prozeduren finden Sie unter Gespeicherte Prozeduren (Datenbank-Engine). Ein Poster aller Datenbankmodulberechtigungen 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).For more information about the REVERT statement, see 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
Feedback
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Issues stufenweise als Feedbackmechanismus für Inhalte abbauen und durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter https://aka.ms/ContentUserFeedback.
Feedback senden und anzeigen für