Tutorial: Ownership Chains and Context Switching
Применимо к:
Управляемому экземпляру SQL Server 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
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по