Tutorial: Ownership Chains and Context Switching

[アーティクル]
04/26/2023

適用対象: SQL Server Azure SQL Managed Instance

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

注意

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

シナリオ

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

最初のアカウント (TestManagerUser) では、すべての購買注文のすべての詳細を確認できる必要があります。
2 番目のアカウント (TestEmployeeUser) では、配送の一部が受領された場合の品目に関して、発注番号、発注日、出荷日、製品 ID 番号、発注品目数と受領品目数を、購買注文ごとに発注番号で確認できる必要があります。
他のすべてのアカウントでは、各自の現在の権限を保持する必要があります。
このシナリオの要件を満たすため、ここでは例を次のように 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 データベース内に 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 アカウントに変更します。所有権を与えられたアカウントでは、 SELECT 権限や INSERT 権限などすべてのデータ操作言語 (DML) のアクセス権限を、中のオブジェクトに対し使用できます。 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に必要とされるデータのみを表示するストアドプロシージャを作成します。要件を満たすには、ストアドプロシージャでは発注番号を表す変数を 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

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 は、 AdventureWorks2022 データベースのオブジェクトに対し、ログイン権限と 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

返されるエラー:

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 はベーステーブルから選択することができないため、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 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 データベースエンジンと Azure SQL Database のセキュリティセンター

Tutorial: Ownership Chains and Context Switching

シナリオ

前提条件

1.環境を構成する

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

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

4.環境をリセットする

コード例全体

参照

フィードバック

フィードバック

その他のリソース

Tutorial: Ownership Chains and Context Switching

シナリオ

前提条件

1.環境を構成する

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

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

4.環境をリセットする

コード例全体

参照

フィードバック

フィードバック

その他のリソース

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

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