所有权链和上下文切换

  • 项目

新建日期： 2006 年 12 月 12 日

本教程使用一个应用场景说明 SQL Server 安全性概念，其中包括所有权链和用户上下文切换。有关所有权链接的详细信息，请参阅所有权链。有关上下文切换的详细信息，请参阅上下文切换。

Bb153640.note(zh-cn,SQL.90).gif注意：
若要在本教程中运行代码，必须已配置混合模式安全性并且已安装 AdventureWorks 数据库。有关混合模式安全性的详细信息，请参阅身份验证模式。有关安装 AdventureWorks 数据库的详细信息，请参阅安装 AdventureWorks 示例数据库和示例。

应用场景

在此应用场景中，两个用户需要帐户访问存储在 AdventureWorks 数据库中的采购订单数据。要求如下：

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

若要满足本应用场景的要求，此示例分为四个部分来说明所有权链和上下文切换的概念：

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

本示例中的每个代码块都将逐一加以说明。若要复制完整的示例，请参阅本教程结尾部分的完整示例。

1. 配置环境

使用 SQL Server Management Studio 及以下代码打开 AdventureWorks 数据库，然后使用 CURRENT_USER Transact-SQL 语句检查 dbo 用户是否显示为上下文。

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

有关 CURRENT_USER 语句的详细信息，请参阅 CURRENT_USER (Transact-SQL)。

使用此代码以使 dbo 用户在服务器及 AdventureWorks 数据库中创建两个用户。

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)。有关数据库架构的详细信息，请参阅架构。有关存储过程的详细信息，请参阅存储过程（数据库引擎）。

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 系统目录。

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

REVERT;
GO

有关 REVERT 语句的详细信息，请参阅 REVERT (Transact-SQL)。

3. 通过存储过程访问数据

除了拥有一个登录名以及分配给 public 数据库角色的权限之外，TestEmployeeUser 对 AdventureWorks 数据库对象没有其他权限。如果 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

完整代码

本部分显示完整的示例代码。

Bb153640.note(zh-cn,SQL.90).gif注意：
此代码不包括两个说明 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 AdventureWorks;
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::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

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

请参阅

其他资源

SQL Server 安全注意事项
数据库和数据库应用程序的安全注意事项

帮助和信息

获取 SQL Server 2005 帮助