教程：所有权链和上下文切换

2017-06-13

本教程使用方案来说明涉及所有权链和用户上下文切换的 SQL Server 安全概念。

注释

若要在本教程中运行代码，必须同时配置混合模式安全性并安装 AdventureWorks2012 数据库。有关混合模式安全性的详细信息，请参阅 “选择身份验证模式”。

情景

在此方案中，两个用户需要帐户才能访问 AdventureWorks2012 数据库中存储的采购订单数据。要求如下：

第一个帐户（TestManagerUser）必须能够查看每个采购订单中的所有详细信息。
第二个帐户（TestEmployeeUser）必须能够查看已收到部分发货的项目的采购订单号、订单日期、发货日期、产品 ID 号以及每个采购订单的已订购和接收项目。
所有其他帐户必须保留其当前权限。

为了满足此方案的要求，此示例分为四个部分，演示所有权链和上下文切换的概念：

配置环境。
创建存储过程以按采购订单访问数据。
通过存储过程访问数据。
重置环境。

此示例中的每个代码块都按行说明。若要复制完整示例，请参阅本教程末尾的完整示例。

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

完成本示例的此部分后，代码将使用 REVERT 语句将上下文切换回 dbo。

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

由于上一节中创建的存储过程所引用的对象具有TestManagerUserPurchasing架构所有权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 数据库安全中心

通过