發行項
09/24/2010

教學課程：擁有權鏈結和內容切換

這個教學課程利用案例來說明 SQL Server 涉及擁有權鏈結和使用者內容切換的安全性概念。如需有關擁有權鏈結的詳細資訊，請參閱＜擁有權鏈結＞。如需內容切換的詳細資訊，請參閱＜內容切換 (Database Engine)＞。

[!附註]

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

狀況

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

第一個帳戶 (TestManagerUser) 必須能夠看見每筆訂單的所有詳細資料。
第二個帳戶 (TestEmployeeUser) 必須能夠看見訂單號碼、訂貨日期、送貨日期、產品識別碼，以及已部分送達的每筆訂單所採購和收到的貨品數量。
其餘所有帳戶必須保有各自現行的權限。

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

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

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

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)＞。如需有關資料庫結構描述的詳細資訊，請參閱＜結構描述 (Database Engine)＞。如需有關預存程序的詳細資訊，請參閱＜預存程序 (Database Engine)＞。

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. 透過預存程序存取資料

TestEmployeeUser 除了在 public 資料庫角色中具備登入身分及受指派的權限外，對 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

完整範例

本節顯示完整的範例程式碼。

[!附註]

下列程式碼剔除了用以示範 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

請參閱

概念

擁有權鏈結

安全性與保護 (Database Engine)

其他資源

內容切換 (Database Engine)

Share via

教學課程：擁有權鏈結和內容切換

狀況

1. 設定環境

2. 建立預存程序用於存取資料

3. 透過預存程序存取資料

4. 重設環境

完整範例

請參閱

概念

其他資源

其他資源