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

2017-06-13

このチュートリアルでは、1 つのシナリオを使用して、所有権の継承とユーザーコンテキストの切り替えに関係する SQL Server のセキュリティ概念について説明します。

注

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

シナリオ

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

最初のアカウント (TestManagerUser) は、すべての発注書のすべての詳細を表示できる必要があります。
2 番目のアカウント (TestEmployeeUser) は、部分的な出荷が受け取られた品目について、発注書番号、注文日、出荷日、製品 ID 番号、および発注書ごとの注文済みおよび受信済みアイテムを発注書番号で確認できる必要があります。
他のすべてのアカウントは、現在のアクセス許可を保持する必要があります。

このシナリオの要件を満たすために、この例は所有権チェーンとコンテキスト切り替えの概念を示す 4 つの部分に分かれています。

環境の構成。
発注書でデータにアクセスするストアドプロシージャを作成する。
ストアドプロシージャを使用してデータにアクセスする。
環境をリセットする。

この例では、各コードブロックがその場で説明されています。完全な例をコピーするには、このチュートリアルの最後にある完全な例を参照してください。

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 データベースに 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 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://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に必要なデータのみを表示するストアドプロシージャを作成します。要件を満たすために、ストアドプロシージャは発注書番号に対して 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

現在、 TestEmployeeUser はデータベースオブジェクトにアクセスできません。次のコード (まだ TestManagerUser コンテキスト内) は、ストアドプロシージャを使用してベーステーブル情報を照会する機能をユーザーアカウントに付与します。

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

このセクションを完了すると、コードは 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 を選択できないことを示す 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 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 データベースエンジンと Azure SQL Database のセキュリティセンター

次の方法で共有