教程：擁有權鏈結和上下文切換

此教學課程利用案例來說明涉及擁有權鏈結與使用者環境切換的 SQL Server 安全性概念。

備註

若要執行本教學課程中的程序代碼，您必須同時設定混合模式安全性，以及已安裝 AdventureWorks2012 資料庫。如需混合模式安全性的詳細資訊，請參閱選擇驗證模式。

情境

在此案例中，兩位使用者需要帳戶來存取 AdventureWorks2012 資料庫中儲存的採購單數據。需求如下：

第一個帳戶（TestManagerUser）必須能夠查看每個採購單中的所有詳細數據。
第二個帳戶（TestEmployeeUser）必須能夠根據採購單號碼查看已收到部分出貨的專案的購買訂單號碼、訂單日期、出貨日期、產品標識碼，及每個採購單中已訂購和已接收的專案。
所有其他帳戶都必須保留其目前的許可權。

為了滿足此案例的需求，此範例分成四個部分來示範擁有權鏈結和內容切換的概念：

設定環境。
建立預存程式，以依採購單存取數據。
透過預存程式存取數據。
重設環境。

此範例中的每個程式代碼區塊都會以行說明。若要複製完整的範例，請參閱本教學課程結尾的完整範例。

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）。如需預存程序的詳細資訊，請參閱預存程序 (資料庫引擎)。如需所有 Database Engine 權限的海報，請參閱 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

由於在上一節中建立的預存程式所參考的對象是因Purchasing結構的擁有權而由TestManagerUser所擁有，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 Database Engine 和 Azure SQL Database 的資訊安全中心

Last updated on 2017-06-13

共用方式為