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

Сценарий

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

• Пользователь первой учетной записи (ТестовыйМенеджер) должен видеть все сведения о каждом заказе на покупку.

• Пользователь второй учетной записи (ТестовыйСотрудник) должен видеть номера заказов на покупку, даты заказов, даты отгрузки, коды продуктов, а также количество отправленных и полученных экземпляров продукта в заказе по номеру заказа (для заказов, получаемых частичной отгрузкой).

• Все другие учетные записи должны сохранять текущие разрешения.

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

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

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

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

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

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

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

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

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

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

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

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) для объектов, которые содержит эта схема. Поскольку при этом не включаются разрешения языка описания данных DDL, учетной записи TestManagerUser явно предоставляются права на таблицы PurchaseOrderHeader и PurchaseOrderDetail, а также возможность создавать хранимые процедуры.

/* Change owner of the Purchasing Schema to TestManagerUser */
ALTER AUTHORIZATION 
   ON SCHEMA::Purchasing 
   TO TestManagerUser;
GO

/* Grant permissions to TestManagerUser on these objects with GRANT option */
GRANT ALL 
   ON OBJECT::AdventureWorks.Purchasing.PurchaseOrderHeader
    TO TestManagerUser 
   WITH GRANT OPTION;
GO
GRANT ALL 
   ON OBJECT::AdventureWorks.Purchasing.PurchaseOrderDetail
    TO TestManagerUser WITH GRANT OPTION;
GO
/* Note: DML works fine with Schema owner, but not DDL. */
GRANT CREATE PROCEDURE 
   TO TestManagerUser 
   WITH GRANT OPTION;
GO

Дополнительные сведения об инструкции GRANT см. в разделе Инструкция GRANT (Transact-SQL). Дополнительные сведения о схемах баз данных см. в разделе Схемы (компонент Database Engine). Дополнительные сведения о хранимых процедурах см. в разделе Хранимые процедуры (компонент Database Engine).

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 не обладает разрешениями на объекты базы данных База данных AdventureWorks2008R2, кроме разрешения на вход в систему и прав, присвоенных роли базы данных 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 AdventureWorks2008R2;
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 permissions to TestManagerUser on these objects with GRANT option */
GRANT ALL 
   ON OBJECT::AdventureWorks2008R2.Purchasing.PurchaseOrderHeader
    TO TestManagerUser 
   WITH GRANT OPTION;
GO
GRANT ALL 
   ON OBJECT::AdventureWorks2008R2.Purchasing.PurchaseOrderDetail
    TO TestManagerUser WITH GRANT OPTION;
GO
/* Note: DML works fine with Schema owner, but not DDL */
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 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

/* 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 a
   INNER JOIN sys.objects 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

См. также

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

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