Samouczek: łańcuchy własności i przełączanie kontekstu

Dotyczy:SQL ServerAzure SQL Managed Instance

W tym samouczku użyto scenariusza ilustrowania pojęć dotyczących zabezpieczeń programu SQL Server obejmujących łańcuchy własności i przełączanie kontekstu użytkownika.

Scenario

W tym scenariuszu dwóch użytkowników potrzebuje kont, aby mieć dostęp do danych dotyczących zamówień zakupu przechowywanych w AdventureWorks2025 bazie danych. Wymagania są następujące:

• Pierwsze konto (TestManagerUser) musi mieć możliwość wyświetlenia wszystkich szczegółów w każdym zamówieniu zakupu.
• Drugie konto (TestEmployeeUser) musi mieć możliwość wyświetlenia numeru zamówienia zakupu, daty zamówienia, daty wysyłki, numerów identyfikacyjnych produktów oraz zamówionych i odebranych pozycji dla każdego zamówienia zakupu, według numeru tego zamówienia, w przypadku pozycji, dla których odebrano częściowe przesyłki.
• Wszystkie inne konta muszą zachować swoje bieżące uprawnienia.
  Aby spełnić wymagania tego scenariusza, przykład jest podzielony na cztery części, które przedstawiają pojęcia łańcuchów własności i przełączania kontekstu:

1. Konfigurowanie środowiska.
2. Tworzenie procedury składowanej w celu uzyskania dostępu do danych według zamówienia zakupu.
3. Uzyskiwanie dostępu do danych za pośrednictwem procedury składowanej.
4. Resetowanie środowiska.

Każdy blok kodu w tym przykładzie jest objaśniony w wierszu. Aby skopiować kompletny przykład, zobacz Kompletny przykład na końcu tego samouczka.

Wymagania wstępne

Do ukończenia tego samouczka potrzebny jest program SQL Server Management Studio, dostęp do serwera z uruchomionym programem SQL Server i bazą danych AdventureWorks2025.

• Zainstaluj program SQL Server Management Studio.
• Zainstaluj program SQL Server 2017 Developer Edition.
• Pobierz przykładowe bazy danych AdventureWorks.

Aby uzyskać instrukcje dotyczące przywracania bazy danych w programie SQL Server Management Studio, zobacz Przywracanie bazy danych.

1. Konfigurowanie środowiska

Użyj programu SQL Server Management Studio i poniższego kodu, aby otworzyć AdventureWorks2025 bazę danych, a następnie użyj CURRENT_USER instrukcji Transact-SQL, aby sprawdzić, czy użytkownik dbo jest wyświetlany jako kontekst.

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

Aby uzyskać więcej informacji na temat instrukcji CURRENT_USER, zobacz CURRENT_USER (Transact-SQL).

Użyj tego kodu jako użytkownik dbo, aby utworzyć dwóch użytkowników na serwerze i w AdventureWorks2025 bazie danych.

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   

Aby uzyskać więcej informacji na temat instrukcji CREATE USER, zobacz CREATE USER (Transact-SQL). Aby uzyskać więcej informacji na temat instrukcji CREATE LOGIN, zobacz CREATE LOGIN (Transact-SQL).

Użyj następującego kodu, aby zmienić właściciela schematu Purchasing na konto TestManagerUser. Dzięki temu konto ma dostęp do wszystkich instrukcji języka DML oraz do uprawnień takich jak SELECT i INSERT na obiektach, które zawiera. TestManagerUser posiada również możliwość tworzenia procedur składowanych.

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

Aby uzyskać więcej informacji na temat instrukcji GRANT, zobacz GRANT (Transact-SQL). Aby uzyskać więcej informacji na temat procedur składowanych, zobacz Procedury składowane (aparat bazy danych). Aby zapoznać się z plakatem wszystkich uprawnień silnika bazy danych, zobacz https://aka.ms/sql-permissions-poster.

2. Tworzenie procedury składowanej w celu uzyskania dostępu do danych

Aby przełączyć kontekst w bazie danych, użyj instrukcji EXECUTE AS. Wykonywanie jako EXECUTE AS wymaga uprawnień IMPERSONATE.

Użyj instrukcji EXECUTE AS w poniższym kodzie, aby zmienić kontekst na TestManagerUser i utworzyć procedurę składowaną, która pokazuje tylko dane wymagane przez program TestEmployeeUser. Aby spełnić wymagania, procedura składowana akceptuje jedną zmienną reprezentującą numer zamówienia zakupu i nie uwzględnia informacji finansowych, a klauzula WHERE ogranicza wyniki do przesyłek częściowych.

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  

Obecnie TestEmployeeUser nie ma dostępu do żadnych obiektów bazy danych. Poniższy kod (nadal w kontekście TestManagerUser) przyznaje kontu użytkownika możliwość pobierania informacji z tabel bazowych za pomocą procedury składowanej.

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

Procedura składowana jest częścią schematu Purchasing , mimo że żaden schemat nie został jawnie określony, ponieważ TestManagerUser jest domyślnie przypisany do schematu Purchasing . Informacje o wykazie systemu umożliwiają lokalizowanie obiektów, jak pokazano w poniższym kodzie.

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  

Po ukończeniu tej sekcji przykładu kod przełącza kontekst z powrotem do dbo przy użyciu instrukcji REVERT .

REVERT;  
GO  

Aby uzyskać więcej informacji na temat instrukcji REVERT, zobacz REVERT (Transact-SQL).

3. Uzyskiwanie dostępu do danych za pośrednictwem procedury składowanej

TestEmployeeUser nie ma uprawnień do AdventureWorks2025 obiektów bazy danych innych niż login i prawa przypisane do roli publicznej bazy danych. Poniższy kod zwraca błąd podczas TestEmployeeUser próby uzyskania dostępu do tabel podstawowych.

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  

Zwrócony błąd:

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

Ponieważ obiekty, do których odnosi się procedura składowana utworzona w ostatniej sekcji, są własnością TestManagerUser dzięki własności schematu Purchasing, TestEmployeeUser może uzyskiwać dostęp do tabel podstawowych za pomocą procedury składowanej. Poniższy kod, nadal używając kontekstu TestEmployeeUser, przekazuje zamówienie zakupu o numerze 952 jako parametr.

EXEC Purchasing.usp_ShowWaitingItems 952  
GO  

4. Resetowanie środowiska

Poniższy kod używa polecenia , REVERT aby zwrócić kontekst bieżącego konta do dbo, a następnie resetuje środowisko.

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  

Kompletny przykład

W tej sekcji zostanie wyświetlony kompletny przykładowy kod.

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

Zobacz też

Centrum zabezpieczeń dla silnika bazy danych SQL Server i bazy danych Azure SQL