Руководство. Цепочки владения и переключение контекстаTutorial: Ownership Chains and Context Switching

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server нетБаза данных SQL Azure нетAzure Synapse Analytics (хранилище данных SQL) нетParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

В этом учебнике приведен пример, в котором рассматриваются основные понятия безопасности SQL ServerSQL Server , включая цепочки владения и переключение контекста.This tutorial uses a scenario to illustrate SQL ServerSQL Server security concepts involving ownership chains and user context switching.

Примечание

Для запуска кода в этом учебнике необходимо, чтобы был настроен режим смешанной безопасности. Кроме того, необходимо наличие установленной базы данных AdventureWorks2017.To run the code in this tutorial you must have both Mixed Mode security configured and the AdventureWorks2017 database installed. Дополнительные сведения о смешанном режиме безопасности см. в разделе Выбор режима проверки подлинности.For more information about Mixed Mode security, see Choose an Authentication Mode.

СценарийScenario

В этом сценарии двум пользователям нужны учетные записи для доступа к данным о заказах на покупку, которые хранятся в базе данных AdventureWorks2017.In this scenario, two users need accounts to access purchase order data stored in the AdventureWorks2017 database. Требования:The requirements are as follows:

  • Пользователь первой учетной записи (ТестовыйМенеджер) должен видеть все сведения о каждом заказе на покупку.The first account (TestManagerUser) must be able to see all details in every purchase order.
  • Пользователь второй учетной записи (ТестовыйСотрудник) должен видеть номера заказов на покупку, даты заказов, даты отгрузки, коды продуктов, а также количество отправленных и полученных экземпляров продукта в заказе по номеру заказа (для заказов, получаемых частичной отгрузкой).The second account (TestEmployeeUser) must be able to see the purchase order number, order date, shipping date, product ID numbers, and the ordered and received items per purchase order, by purchase order number, for items where partial shipments have been received.
  • Все другие учетные записи должны сохранять текущие разрешения.All other accounts must retain their current permissions.
    Чтобы выполнялись требования этого сценария, этот пример разбит на 4 части, в которых проиллюстрированы основные понятия, касающиеся цепочек владения и переключения контекста.To fulfill the requirements of this scenario, the example is broken into four parts that demonstrate the concepts of ownership chains and context switching:
  1. Настройка среды.Configuring the environment.
  2. Создание хранимой процедуры для получения доступа к данным по заказам на покупку.Creating a stored procedure to access data by purchase order.
  3. Доступ к данным через хранимую процедуру.Accessing the data through the stored procedure.
  4. Сброс среды.Resetting the environment.

Каждый блок кода в этом примере объясняется по порядку.Each code block in this example is explained in line. Чтобы скопировать весь пример, см. раздел Пример целиком в конце этого учебника.To copy the complete example, see Complete Example at the end of this tutorial.

предварительные требованияPrerequisites

Для работы с этим учебником требуется среда SQL Server Management Studio, доступ к серверу SQL Server и база данных AdventureWorks.To complete this tutorial, you need SQL Server Management Studio, access to a server that's running SQL Server, and an AdventureWorks database.

Инструкции по восстановлению базы данных в SQL Server Management Studio см. в разделе Восстановление базы данных.For instructions on restoring a database in SQL Server Management Studio, see Restore a database.

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

С помощью среды SQL Server Management StudioSQL Server Management Studio и приведенного ниже кода откройте базу данных AdventureWorks2017, затем с помощью инструкции CURRENT_USER Transact-SQLTransact-SQL проверьте, отображается ли пользователь dbo в качестве контекста.Use SQL Server Management StudioSQL Server Management Studio and the following code to open the AdventureWorks2017 database, and use the CURRENT_USER Transact-SQLTransact-SQL statement to check that the dbo user is displayed as the context.

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

Дополнительные сведения об инструкции CURRENT_USER см. в разделе CURRENT_USER (Transact-SQL).For more information about the CURRENT_USER statement, see CURRENT_USER (Transact-SQL).

От имени пользователя dbo создайте с помощью этого кода двух пользователей на сервере и в базе данных AdventureWorks2017.Use this code as the dbo user to create two users on the server and in the AdventureWorks2017 database.

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).For more information about the CREATE USER statement, see CREATE USER (Transact-SQL). Дополнительные сведения об инструкции CREATE LOGIN см. в разделе CREATE LOGIN (Transact-SQL).For more information about the CREATE LOGIN statement, see CREATE LOGIN (Transact-SQL).

Изменить владельца схемы Purchasing на учетную запись TestManagerUser можно с помощью приведенного ниже кода.Use the following code to change the ownership of the Purchasing schema to the TestManagerUser account. Это позволит учетной записи использовать все инструкции доступа языка обработки данных DML (например, разрешения SELECT или INSERT ) для объектов, которые содержит эта схема.This allows that account to use all Data Manipulation Language (DML) statement access (such as SELECT and INSERT permissions) on the objects it contains. TestManagerUser также предоставляет возможность создавать хранимые процедуры.TestManagerUser is also granted the ability to create stored procedures.

/* 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).For more information about the GRANT statement, see GRANT (Transact-SQL). Дополнительные сведения о хранимых процедурах см. в разделе Хранимые процедуры (компонент Database Engine).For more information about stored procedures, see Stored Procedures (Database Engine). Плакат со всеми разрешениями Компонент Database EngineDatabase Engine см. по ссылке https://aka.ms/sql-permissions-poster.For a poster of all Компонент Database EngineDatabase Engine permissions, see https://aka.ms/sql-permissions-poster.

2. Создание хранимой процедуры для доступа к данным2. Create a Stored Procedure to Access Data

Для переключения контекста внутри базы данных используйте инструкцию EXECUTE AS.To switch context within a database, use the EXECUTE AS statement. Инструкции EXECUTE AS требуются разрешения IMPERSONATE.EXECUTE AS requires IMPERSONATE permissions.

С помощью инструкции EXECUTE AS в приведенном ниже коде измените контекст на TestManagerUser и создайте хранимую процедуру, показывающую только те данные, которые должны быть видны пользователю TestEmployeeUser.Use the EXECUTE AS statement in the following code to change the context to TestManagerUser and create a stored procedure showing only the data required by TestEmployeeUser. Для соответствия требованиям хранимая процедура принимает одну переменную для номера заказа на покупку и не показывает финансовую информацию, а предложение WHERE ограничивает результаты для частичных отгрузок.To satisfy the requirements, the stored procedure accepts one variable for the purchase order number and does not display financial information, and the WHERE clause limits the results to partial shipments.

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 не имеет доступа к объектам базы данных.Currently TestEmployeeUser does not have access to any database objects. Следующий код (все еще в контексте TestManagerUser ) предоставляет учетной записи пользователя возможность запрашивать информацию из базовой таблицы через хранимую процедуру.The following code (still in the TestManagerUser context) grants the user account the ability to query base-table information through the stored procedure.

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

Хотя схема не была указана явно, хранимая процедура является частью схемы Purchasing , поскольку пользователь TestManagerUser по умолчанию связан со схемой Purchasing .The stored procedure is part of the Purchasing schema, even though no schema was explicitly specified, because TestManagerUser is assigned by default to the Purchasing schema. Для поиска объектов можно использовать информацию из системного каталога, как показано в следующем коде.You can use system catalog information to locate objects, as shown in the following code.

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.With this section of the example completed, the code switches context back to dbo using the REVERT statement.

REVERT;  
GO  

Дополнительные сведения об инструкции REVERT см. в разделе REVERT (Transact-SQL).For more information about the REVERT statement, see REVERT (Transact-SQL).

3. Доступ к данным через хранимую процедуру3. Access Data Through the Stored Procedure

TestEmployeeUser не обладает разрешениями на объекты базы данных AdventureWorks2012AdventureWorks2012 , кроме разрешения на вход в систему и прав, присвоенных роли базы данных public.TestEmployeeUser has no permissions on the AdventureWorks2012AdventureWorks2012 database objects other than a login and the rights assigned to the public database role. Следующий код возвращает ошибку при попытке обращения TestEmployeeUser к базовым таблицам.The following code returns an error when TestEmployeeUser attempts to access base tables.

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  

Возвращается следующая ошибка:The error that's returned:

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

Поскольку объекты, на которые ссылается процедура, созданная в предыдущем разделе, принадлежат TestManagerUser по причине владения схемой Purchasing , TestEmployeeUser может получить доступ к базовым таблицам через хранимую процедуру.Because the objects referenced by the stored procedure created in the last section are owned by TestManagerUser by virtue of the Purchasing schema ownership, TestEmployeeUser can access the base tables through the stored procedure. Следующий код, все еще в контексте TestEmployeeUser , проводит заказ на покупку 952 как параметр.The following code, still using the TestEmployeeUser context, passes purchase order 952 as a parameter.

EXEC Purchasing.usp_ShowWaitingItems 952  
GO  

4. Сброс среды4. Reset the Environment

Следующий код с помощью команды REVERT изменяет контекст текущей учетной записи обратно на dboи затем выполняет сброс среды.The following code uses the REVERT command to return the context of the current account to dbo, and then resets the environment.

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  

Пример целикомComplete Example

В этом разделе приведен полный код примера.This section displays the complete example code.

Примечание

В этот код не включены две ошибки, которые иллюстрировали невозможность TestEmployeeUser получить данные из базовых таблиц.This code does not include the two expected errors that demonstrate the inability of TestEmployeeUser to select from base tables.

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

См. также:See Also

Центр обеспечения безопасности для базы данных Azure SQL и SQL Server Database EngineSecurity Center for SQL Server Database Engine and Azure SQL Database