Руководство по цепочкам владения и переключению контекста

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

Замечание

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

Сценарий

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

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

  • Вторая учетная запись (TestEmployeeUser) должна иметь возможность видеть номер заказа на покупку, дату заказа, дату доставки, номера идентификаторов продукта, а также заказанные и полученные элементы, которые организованы по номеру заказа на покупку, для элементов с полученными частичными поставками.

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

Для выполнения требований этого сценария пример разбивается на четыре части, демонстрирующие основные понятия цепочек владения и переключения контекста:

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

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

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

  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). Дополнительные сведения о хранимых процедурах см. в разделе Хранимые процедуры (ядро СУБД). Для получения плаката, содержащего все разрешения ядра СУБД, см. https://github.com/microsoft/sql-server-samples/blob/master/samples/features/security/permissions-posters/Microsoft_SQL_Server_2017_and_Azure_SQL_Database_permissions_infographic.pdf.

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 не имеет разрешений на объекты базы данных AdventureWorks2012 , отличные от имени входа и прав, назначенных роли общедоступной базы данных. Следующий код возвращает ошибку при 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

См. также

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

  • Last updated on