Tutorial: Ownership Chains and Context Switching

適用於：SQL ServerAzure SQL 受控執行個體

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

注意

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

狀況

在此狀況下，有兩位使用者需要使用其帳戶來存取 AdventureWorks2022 資料庫內儲存的採購訂單資料。 需求如下：

• 第一個帳戶 (TestManagerUser) 必須能夠看見每筆訂單的所有詳細資料。
• 第二個帳戶 (TestEmployeeUser) 必須能夠看見訂單號碼、訂貨日期、送貨日期、產品識別碼，以及已部分送達的每筆訂單所採購和收到的貨品數量。
• 其餘所有帳戶必須保有各自現行的權限。
  為了滿足此案例的需求，範例將拆成四部分示範擁有權鏈結和內容切換的概念：

1. 設定環境。
2. 建立預存程序用於存取訂單資料。
3. 透過預存程序存取資料。
4. 重設環境。

此範例會在每個程式碼區塊中各行附上說明。 若要複製整個範例，請參閱本教學課程結尾處的＜ 完整範例 ＞一節。

必要條件

若要完成本教學課程，您需要 SQL Server Management Studio、執行 SQL Server 伺服器的存取權，以及 AdventureWorks2022 資料庫。

• 安裝 SQL Server Management Studio。
• 安裝 SQL Server 2017 Developer Edition。
• 下載 AdventureWorks 範例資料庫。

如需還原 SQL Server Management Studio 中資料庫的指示，請參閱還原資料庫。

1.設定環境

使用 SQL Server Management Studio 與下列程式碼開啟 AdventureWorks2022 資料庫，然後使用 CURRENT_USER Transact-SQL 陳述式檢查 dbo 使用者是否顯示為內容。

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

如需 CURRENT_USER 陳述式的詳細資訊，請參閱 CURRENT_USER (Transact-SQL)。

使用下列程式碼，以 dbo 使用者身分在伺服器和 AdventureWorks2022 資料庫中建立兩位使用者。

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://aka.ms/sql-permissions-poster。

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 除了在 public 資料庫角色中具備登入身分及受指派的權限外，對 AdventureWorks2022 資料庫物件毫無任何權限。 下列程式碼將因 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  

傳回的錯誤：

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

由於 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 AdventureWorks2022;  
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 的資訊安全中心