Учебник. Цепочки владения и переключение контекста

Статья
05/08/2013

В этом учебнике приведен пример, в котором рассматриваются основные понятия безопасности SQL Server, включая цепочки владения и переключение контекста. Дополнительные сведения о цепочках владения см. в разделе Цепочки владения. Дополнительные сведения о переключении контекста см. в разделе Переключение контекста (компонент Database Engine).

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

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

Сценарий

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

Первая учетная запись (TestManagerUser) должна иметь доступ ко всем сведениям о каждом заказе на покупку.
Вторая учетная запись (TestEmployeeUser) должна иметь доступ к номерам заказов на покупку, датам заказов, датам отгрузки, кодам продуктов, а также количеству отправленных и полученных экземпляров продукта в заказе по номеру заказа (для заказов, получаемых частичной отгрузкой).
Все другие учетные записи должны сохранять текущие разрешения.

Чтобы выполнялись требования этого сценария, этот пример разбит на 4 части, в которых проиллюстрированы основные понятия, касающиеся цепочек владения и переключения контекста.

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

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

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

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

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

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

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

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). Дополнительные сведения о схемах баз данных см. в разделе Схемы (компонент Database Engine). Дополнительные сведения о хранимых процедурах см. в разделе Хранимые процедуры (компонент Database Engine). Плакат всех разрешений компонента Компонент Database Engine см. по ссылке https://go.microsoft.com/fwlink/?LinkId=229142.

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

Есть два способа разрешить пользователю переключение контекстов в базе данных: SETUSER или EXECUTE AS. Для использования инструкции SETUSER участник должен быть членом предопределенной роли сервера sysadmin или иметь учетную запись dbo. Инструкции EXECUTE AS требуются разрешения IMPERSONATE. Дополнительные сведения об этих понятиях см. в разделе EXECUTE AS и SETUSER.

С помощью инструкции 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

Дополнительные сведения о системных каталогах см. в разделе Запрос к системному каталогу сервера SQL Server.

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

REVERT;
GO

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

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

Учетная запись TestEmployeeUser не обладает разрешениями на объекты базы данных AdventureWorks2012 , кроме разрешения на вход в систему и прав, унаследованных от роли базы данных 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

Поскольку объекты, на которые ссылается процедура, созданная в предыдущем разделе, принадлежат 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 AdventureWorks2012;
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

См. также

Основные понятия

Защита и обеспечение безопасности (компонент Database Engine)

Другие ресурсы

Переключение контекста (компонент Database Engine)

Цепочки владения

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