Tutorial: Ownership Chains and Context Switching

Статья
04/26/2023

Область применения: SQL Server Управляемый экземпляр SQL Azure

В этом руководстве приведен пример, в котором рассматриваются основные понятия безопасности SQL Server, включая цепочки владения и переключение контекста пользователя.

Примечание.

Для запуска кода в этом учебнике необходимо, чтобы был настроен режим смешанной безопасности. Кроме того, необходимо установить базу данных AdventureWorks2022 . Дополнительные сведения о смешанном режиме безопасности см. в разделе Выбор режима проверки подлинности.

Сценарий

В этом сценарии двум пользователям нужны учетные записи для доступа к данным о заказах на покупку, которые хранятся в базе данных AdventureWorks2022 . Требования:

Пользователь первой учетной записи (ТестовыйМенеджер) должен видеть все сведения о каждом заказе на покупку.
Пользователь второй учетной записи (ТестовыйСотрудник) должен видеть номера заказов на покупку, даты заказов, даты отгрузки, коды продуктов, а также количество отправленных и полученных экземпляров продукта в заказе по номеру заказа (для заказов, получаемых частичной отгрузкой).
Все другие учетные записи должны сохранять текущие разрешения.
Чтобы выполнялись требования этого сценария, этот пример разбит на 4 части, в которых проиллюстрированы основные понятия, касающиеся цепочек владения и переключения контекста.

Настройка среды.
Создание хранимой процедуры для получения доступа к данным по заказам на покупку.
Доступ к данным через хранимую процедуру.
Сброс среды.

Каждый блок кода в этом примере объясняется по порядку. Чтобы скопировать весь пример, см. раздел Пример целиком в конце этого учебника.

Необходимые компоненты

Для работы с этим руководством вам потребуется СРЕДА SQL Server Management Studio, доступ к серверу под управлением SQL Server и AdventureWorks2022 базе данных.

Установите SQL Server Management Studio.
Установите выпуск SQL Server 2017 Developer Edition.
Скачайте примеры баз данных AdventureWorks.

Инструкции по восстановлению базы данных в SQL Server Management Studio см. в разделе Восстановление базы данных.

1. Настройка среды

Используйте SQL Server Management Studio и следующий код, чтобы открыть AdventureWorks2022 базу данных, и используйте CURRENT_USER инструкцию Transact-SQL, чтобы убедиться, что пользователь dbo отображается в качестве контекста.

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

Дополнительные сведения об инструкции CURRENT_USER см. в разделе CURRENT_USER (Transact-SQL).

От имени пользователя dbo создайте с помощью этого кода двух пользователей на сервере и в базе данных AdventureWorks2022.

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

Дополнительные сведения об инструкции CREATE USER см. в статье CREATE USER (Transact-SQL). Дополнительные сведения об инструкции CREATE LOGIN см. в разделе CREATE LOGIN (Transact-SQL).

Изменить владельца схемы Purchasing на учетную запись TestManagerUser можно с помощью приведенного ниже кода. Это позволит учетной записи использовать все инструкции доступа языка обработки данных DML (например, разрешения SELECT или INSERT ) для объектов, которые содержит эта схема. TestManagerUser также предоставляет возможность создавать хранимые процедуры.

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

Дополнительные сведения об инструкции GRANT см. в разделе GRANT (Transact-SQL). Дополнительные сведения о хранимых процедурах см. в разделе Хранимые процедуры (ядро СУБД). Плакат всех ядро СУБД разрешений см. в разделе https://aka.ms/sql-permissions-poster.

2. Создание хранимой процедуры для доступа к данным

Для переключения контекста внутри базы данных используйте инструкцию EXECUTE AS. Инструкции EXECUTE AS требуются разрешения IMPERSONATE.

С помощью инструкции EXECUTE AS в приведенном ниже коде измените контекст на TestManagerUser и создайте хранимую процедуру, показывающую только те данные, которые должны быть видны пользователю TestEmployeeUser. Для соответствия требованиям хранимая процедура принимает одну переменную для номера заказа на покупку и не показывает финансовую информацию, а предложение WHERE ограничивает результаты для частичных отгрузок.

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

В данный момент пользователь TestEmployeeUser не имеет доступа к объектам базы данных. Следующий код (все еще в контексте TestManagerUser ) предоставляет учетной записи пользователя возможность запрашивать информацию из базовой таблицы через хранимую процедуру.

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

Хотя схема не была указана явно, хранимая процедура является частью схемы Purchasing , поскольку пользователь TestManagerUser по умолчанию связан со схемой Purchasing . Для поиска объектов можно использовать информацию из системного каталога, как показано в следующем коде.

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

После завершения этого раздела примера код переключает контекст обратно на dbo с помощью инструкции REVERT.

REVERT;  
GO

Дополнительные сведения об инструкции REVERT см. в разделе REVERT (Transact-SQL).

3. Доступ к данным через хранимую процедуру

TestEmployeeUser не обладает разрешениями на объекты базы данных AdventureWorks2022 , кроме разрешения на вход в систему и прав, присвоенных роли базы данных public. Следующий код возвращает ошибку при попытке обращения TestEmployeeUser к базовым таблицам.

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

Возвращается следующая ошибка:

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

Поскольку объекты, на которые ссылается процедура, созданная в предыдущем разделе, принадлежат TestManagerUser по причине владения схемой Purchasing , TestEmployeeUser может получить доступ к базовым таблицам через хранимую процедуру. Следующий код, все еще в контексте TestEmployeeUser , проводит заказ на покупку 952 как параметр.

EXEC Purchasing.usp_ShowWaitingItems 952  
GO

4. Сброс среды

Следующий код с помощью команды REVERT изменяет контекст текущей учетной записи обратно на dboи затем выполняет сброс среды.

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

Полный пример

В этом разделе приведен полный код примера.

Примечание.

В этот код не включены две ошибки, которые иллюстрировали невозможность TestEmployeeUser получить данные из базовых таблиц.

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

См. также

Центр безопасности для ядра СУБД SQL Server и Базы данных Azure SQL

Поделиться через