[アーティクル]
10/25/2012

チュートリアル : 所有権の継承とコンテキストの切り替え

このチュートリアルでは、1 つのシナリオを使用して、所有権の継承とユーザーコンテキストの切り替えに関係する SQL Server のセキュリティ概念について説明します。所有権の継承の詳細については、「所有権の継承」を参照してください。コンテキスト切り替えの詳細については、「コンテキストの切り替え (データベースエンジン)」を参照してください。

注
このチュートリアルのコードを実行するには、混合モードのセキュリティが構成されていることと、AdventureWorks2008R2 データベースがインストールされていることが条件となります。混合モードのセキュリティの詳細については、「認証モードの選択」を参照してください。

シナリオ

このシナリオでは、2 人のユーザーが、AdventureWorks2008R2 データベースに格納されている購買発注データにアクセスするためのアカウントを必要としていることを想定します。要件は次のとおりです。

最初のアカウント (TestManagerUser) では、すべての購買注文のすべての詳細を確認できる必要があります。
2 番目のアカウント (TestEmployeeUser) では、配送の一部が受領された場合の品目に関して、発注番号、発注日、出荷日、製品 ID 番号、発注品目数と受領品目数を、購買注文ごとに発注番号で確認できる必要があります。
他のすべてのアカウントでは、各自の現在の権限を保持する必要があります。

このシナリオの要件を満たすため、ここでは例を次のように 4 分割して、所有権の継承とコンテキストの切り替えの各概念を示します。

環境を構成する。
購買注文によってデータにアクセスするストアドプロシージャを作成する。
ストアドプロシージャからデータにアクセスする。
環境をリセットする。

以下で、この例の各コードブロックについて説明します。完全なサンプルコードをコピーするには、このチュートリアルの最後の「完全なサンプルコード」を参照してください。

1. 環境を構成する

SQL Server Management Studio と次のコードを使用して AdventureWorks2008R2 データベースを開き、CURRENT_USERTransact-SQL ステートメントを使用して、dbo ユーザーがコンテキストとして表示されていることを確認します。

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

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

CURRENT_USER ステートメントの詳細については、「CURRENT_USER (Transact-SQL)」を参照してください。

次のコードを dbo ユーザーとして実行し、サーバー上と AdventureWorks2008R2 データベース内に 2 ユーザーを作成します。

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 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 アカウントに変更します。所有権を与えられたアカウントでは、SELECT 権限や INSERT 権限などすべてのデータ操作言語 (DML) のアクセス権限を、中のオブジェクトに対し使用できます。これにはデータ定義言語 (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

/* 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 の 2 つの方法があります。SETUSER ステートメントを使用する場合、呼び出し元は sysadmin 固定サーバーロールのメンバーシップを持っているか、dbo アカウントである必要があります。EXECUTE AS を使用するには IMPERSONATE 権限が必要です。これらの概念の詳細については、「EXECUTE AS と SETUSER」を参照してください。

次のコードでは、EXECUTE AS ステートメントによりコンテキストを TestManagerUser に変更し、TestEmployeeUser に必要とされるデータのみを表示するストアドプロシージャを作成します。要件を満たすには、ストアドプロシージャでは発注番号を表す変数を 1 つ受け取ります。財務情報は表示せず、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

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 です。次のコードを実行し、ストアドプロシージャを介して TestEmployeeUser ユーザーアカウントがベーステーブル情報をクエリできるようにします。

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

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

TestManagerUser は既定では Purchasing スキーマに割り当てられるため、スキーマが明示的に指定されていない場合でも、ストアドプロシージャは 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

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;
GO

REVERT ステートメントの詳細については、「REVERT (Transact-SQL)」を参照してください。

3. ストアドプロシージャからデータにアクセスする

TestEmployeeUser は、AdventureWorks2008R2 データベースのオブジェクトに対し、ログイン権限と public データベースロールに割り当てられた権限だけを持ちます。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

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

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

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 はベーステーブルから選択することができないため、2 つのエラーが発生することが予測されますが、このコードには含まれていません。

/* 
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 AdventureWorks2008R2;
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::AdventureWorks2008R2.Purchasing.PurchaseOrderHeader
    TO TestManagerUser 
   WITH GRANT OPTION;
GO
GRANT ALL 
   ON OBJECT::AdventureWorks2008R2.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

/* 
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 AdventureWorks2008R2;
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::AdventureWorks2008R2.Purchasing.PurchaseOrderHeader
    TO TestManagerUser 
   WITH GRANT OPTION;
GO
GRANT ALL 
   ON OBJECT::AdventureWorks2008R2.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

次の方法で共有

チュートリアル : 所有権の継承とコンテキストの切り替え

シナリオ

1. 環境を構成する

2. データにアクセスするストアドプロシージャを作成する

3. ストアドプロシージャからデータにアクセスする

4. 環境をリセットする

完全なサンプルコード

関連項目

概念

その他の技術情報

その他のリソース

次の方法で共有

チュートリアル : 所有権の継承とコンテキストの切り替え

シナリオ

1. 環境を構成する

2. データにアクセスするストアド プロシージャを作成する

3. ストアド プロシージャからデータにアクセスする

4. 環境をリセットする

完全なサンプル コード

関連項目

概念

その他の技術情報

その他のリソース

2. データにアクセスするストアドプロシージャを作成する

3. ストアドプロシージャからデータにアクセスする

完全なサンプルコード