教學課程:擁有權鏈結和環境切換Tutorial: Ownership Chains and Context Switching

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

這個教學課程利用案例來說明涉及擁有權鏈結和使用者內容切換的 SQL ServerSQL Server 安全性概念。This tutorial uses a scenario to illustrate SQL ServerSQL Server security concepts involving ownership chains and user context switching.

注意

若要執行本教學課程中的程式碼,您必須設定使用混合模式安全性,並已安裝 AdventureWorks2017 資料庫。To run the code in this tutorial you must have both Mixed Mode security configured and the AdventureWorks2017 database installed. 如需混合模式安全性的詳細資訊,請參閱 選擇驗證模式For more information about Mixed Mode security, see Choose an Authentication Mode.

狀況Scenario

在此狀況下,有兩位使用者需要使用其帳戶來存取 AdventureWorks2017 資料庫內儲存的採購訂單資料。In this scenario, two users need accounts to access purchase order data stored in the AdventureWorks2017 database. 需求如下:The requirements are as follows:

  • 第一個帳戶 (TestManagerUser) 必須能夠看見每筆訂單的所有詳細資料。The first account (TestManagerUser) must be able to see all details in every purchase order.
  • 第二個帳戶 (TestEmployeeUser) 必須能夠看見訂單號碼、訂貨日期、送貨日期、產品識別碼,以及已部分送達的每筆訂單所採購和收到的貨品數量。The second account (TestEmployeeUser) must be able to see the purchase order number, order date, shipping date, product ID numbers, and the ordered and received items per purchase order, by purchase order number, for items where partial shipments have been received.
  • 其餘所有帳戶必須保有各自現行的權限。All other accounts must retain their current permissions.
    為了滿足此案例的需求,範例將拆成四部分示範擁有權鏈結和內容切換的概念:To fulfill the requirements of this scenario, the example is broken into four parts that demonstrate the concepts of ownership chains and context switching:
  1. 設定環境。Configuring the environment.
  2. 建立預存程序用於存取訂單資料。Creating a stored procedure to access data by purchase order.
  3. 透過預存程序存取資料。Accessing the data through the stored procedure.
  4. 重設環境。Resetting the environment.

我們會持續聽取您的意見: 如果您發現本文中有過時或不正確的內容 (例如步驟或程式碼範例),請告訴我們。We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. 您可以按一下此頁面底部 [意見反應] 區段中的 [本頁] 按鈕。You can click the This page button in the Feedback section at the bottom of this page. 我們通常會在隔天閱讀有關 SQL 的每一個意見反應。We read every item of feedback about SQL, typically the next day. 謝謝。Thanks.

此範例會在每個程式碼區塊中各行附上說明。Each code block in this example is explained in line. 若要複製整個範例,請參閱本教學課程結尾處的< 完整範例 >一節。To copy the complete example, see Complete Example at the end of this tutorial.

PrerequisitesPrerequisites

若要完成本教學課程,您需要 SQL Server Management Studio、執行 SQL Server 伺服器的存取權,以及 AdventureWorks 資料庫。To complete this tutorial, you need SQL Server Management Studio, access to a server that's running SQL Server, and an AdventureWorks database.

如需還原 SQL Server Management Studio 中資料庫的指示,請參閱還原資料庫For instructions on restoring a database in SQL Server Management Studio, see Restore a database.

1.設定環境1. Configure the Environment

使用 SQL Server Management StudioSQL Server Management Studio 與下列程式碼開啟 AdventureWorks2017 資料庫,然後使用 CURRENT_USER Transact-SQLTransact-SQL 陳述式檢查 dbo 使用者是否顯示為內容。Use SQL Server Management StudioSQL Server Management Studio and the following code to open the AdventureWorks2017 database, and use the CURRENT_USER Transact-SQLTransact-SQL statement to check that the dbo user is displayed as the context.

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

如需 CURRENT_USER 陳述式的詳細資訊,請參閱 CURRENT_USER (Transact-SQL)For more information about the CURRENT_USER statement, see CURRENT_USER (Transact-SQL).

使用下列程式碼,以 dbo 使用者身分在伺服器和 AdventureWorks2017 資料庫中建立兩位使用者。Use this code as the dbo user to create two users on the server and in the AdventureWorks2017 database.

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)For more information about the CREATE USER statement, see CREATE USER (Transact-SQL). 如需 CREATE LOGIN 陳述式的詳細資訊,請參閱 CREATE LOGIN (Transact-SQL)For more information about the CREATE LOGIN statement, see CREATE LOGIN (Transact-SQL).

使用下列陳述式,將 Purchasing 結構描述的擁有權變更為 TestManagerUser 帳戶。Use the following code to change the ownership of the Purchasing schema to the TestManagerUser account. 這樣一來,該帳戶對結構描述內含物件就具備了所有的資料操作語言 (DML) 陳述式存取權 (例如 SELECTINSERT 權限)。This allows that account to use all Data Manipulation Language (DML) statement access (such as SELECT and INSERT permissions) on the objects it contains. TestManagerUser 也已獲授與建立預存程序的能力。TestManagerUser is also granted the ability to create stored procedures.

/* 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)For more information about the GRANT statement, see GRANT (Transact-SQL). 如需預存程序的詳細資訊,請參閱預存程序 (Database Engine)For more information about stored procedures, see Stored Procedures (Database Engine). 如需所有 Database EngineDatabase Engine 權限的海報,請參閱 https://aka.ms/sql-permissions-posterFor a poster of all Database EngineDatabase Engine permissions, see https://aka.ms/sql-permissions-poster.

2.建立預存程序來存取資料2. Create a Stored Procedure to Access Data

若要在資料庫內切換內容,請使用 EXECUTE AS 陳述式。To switch context within a database, use the EXECUTE AS statement. EXECUTE AS 則需要 IMPERSONATE 權限。EXECUTE AS requires IMPERSONATE permissions.

下列程式碼使用 EXECUTE AS 陳述式將內容變更為 TestManagerUser ,並建立預存程序單僅顯示 TestEmployeeUser所需的資料。Use the EXECUTE AS statement in the following code to change the context to TestManagerUser and create a stored procedure showing only the data required by TestEmployeeUser. 為了滿足需求,預存程序接受訂單號碼做為變數且並未顯示財務資訊,而 WHERE 子句則將結果限制在已部分送達的貨品。To satisfy the requirements, the stored procedure accepts one variable for the purchase order number and does not display financial information, and the WHERE clause limits the results to partial shipments.

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 尚無權存取任何資料庫物件。Currently TestEmployeeUser does not have access to any database objects. 下列程式碼 (內容仍是 TestManagerUser ) 將授與該使用者帳戶透過預存程序查詢基底資料表資訊的能力。The following code (still in the TestManagerUser context) grants the user account the ability to query base-table information through the stored procedure.

GRANT EXECUTE  
   ON OBJECT::Purchasing.usp_ShowWaitingItems  
   TO TestEmployeeUser;  
GO  

儘管並未明確指定結構描述,此預存程序係屬 Purchasing 結構描述的一部分,因為 TestManagerUser 預設即指派到 Purchasing 結構描述。The stored procedure is part of the Purchasing schema, even though no schema was explicitly specified, because TestManagerUser is assigned by default to the Purchasing schema. 您可以利用系統目錄資訊找出物件,如下列程式碼所示。You can use system catalog information to locate objects, as shown in the following code.

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。With this section of the example completed, the code switches context back to dbo using the REVERT statement.

REVERT;  
GO  

如需 REVERT 陳述式的詳細資訊,請參閱 REVERT (Transact-SQL)For more information about the REVERT statement, see REVERT (Transact-SQL).

3.透過預存程序存取資料3. Access Data Through the Stored Procedure

TestEmployeeUser 除了在 public 資料庫角色中具備登入身分及受指派的權限外,對 AdventureWorks2012AdventureWorks2012 資料庫物件毫無任何權限。TestEmployeeUser has no permissions on the AdventureWorks2012AdventureWorks2012 database objects other than a login and the rights assigned to the public database role. 下列程式碼將因 TestEmployeeUser 試圖存取基底資料表而傳回錯誤。The following code returns an error when TestEmployeeUser attempts to access base tables.

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  

傳回的錯誤:The error that's returned:

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

由於 TestManagerUser 憑藉其 Purchasing 結構描述擁有權而成為上一節所建立預存程序中參考之物件的擁有者, TestEmployeeUser 可透過預存程序存取基底資料表。Because the objects referenced by the stored procedure created in the last section are owned by TestManagerUser by virtue of the Purchasing schema ownership, TestEmployeeUser can access the base tables through the stored procedure. 下列程式碼仍舊使用 TestEmployeeUser 內容,傳遞訂單號碼 952 做為參數。The following code, still using the TestEmployeeUser context, passes purchase order 952 as a parameter.

EXEC Purchasing.usp_ShowWaitingItems 952  
GO  

4.重設環境4. Reset the Environment

下列程式碼使用 REVERT 命令,將目前帳戶的內容切換回 dbo然後重設環境。The following code uses the REVERT command to return the context of the current account to dbo, and then resets the environment.

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  

完整範例Complete Example

本節顯示完整的範例程式碼。This section displays the complete example code.

注意

下列程式碼剔除了用以示範 TestEmployeeUser 無法從基底資料表選取以致發生兩項預期錯誤的部分。This code does not include the two expected errors that demonstrate the inability of TestEmployeeUser to select from base tables.

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

另請參閱See Also

SQL Server Database Engine 和 Azure SQL Database 的資訊安全中心Security Center for SQL Server Database Engine and Azure SQL Database