• [アーティクル]

チュートリアル : 証明書を使用したストアド プロシージャへの署名

このチュートリアルでは、SQL Server で生成された証明書を使用してストアド プロシージャに署名する方法について説明します。

注意注

このチュートリアルのコードを実行するには、混合モードのセキュリティが構成されていることと、AdventureWorks2008R2 データベースがインストールされていることが条件となります。シナリオ

証明書を使用したストアド プロシージャへの署名は、ストアド プロシージャに権限を設定する一方、ユーザーにはこのような権限を明示的に与えないでおこうとする場合に便利です。この動作は EXECUTE AS ステートメントを使用するなどの方法でも実現できますが、証明書を使用すると、トレースによってプロシージャの最初の呼び出し元を見つけることができ、高いレベルの監査を行うことができます。特に、セキュリティやデータ定義言語 (DDL) の操作に対しては有効です。

master データベースに証明書を作成し、サーバーレベルの権限を付与するか、任意のユーザー データベースに証明書を作成し、データベースレベルの権限を付与することができます。このシナリオでは、ベース テーブルに対する権限を持たないユーザーが AdventureWorks2008R2 データベースにアクセスする必要があることを想定し、また、オブジェクト アクセスの記録を監査します。ここでは所有権を継承する方法を使用するのではなく、サーバーおよびデータベース ユーザー アカウントを 1 つとデータベース ユーザー アカウントを 1 つ作成し、前者のアカウントにはベース オブジェクトに対する権限を与えず、後者にはテーブルとストアド プロシージャに対する権限を与えます。ストアド プロシージャと 2 番目のデータベース ユーザー アカウントは両方とも、証明書によりセキュリティ保護されます。2 番目のデータベース アカウントではすべてのオブジェクトにアクセスでき、ストアド プロシージャへのアクセス許可を最初のデータベース ユーザー アカウントに与えることができます。

このシナリオでは、最初にデータベース証明書、ストアド プロシージャ、ユーザーを 1 つずつ作成し、次にプロセスをテストします。手順は次のとおりです。

  1. 環境を構成する。

  2. 証明書を作成する。

  3. ストアド プロシージャを作成し、証明書を使用して署名する。

  4. 証明書を使用して証明書アカウントを作成する。

  5. 証明書アカウントにデータベース権限を与える。

  6. アクセス コンテキストを表示する。

  7. 環境をリセットする。

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

1. 環境を構成する

この例の初期コンテキストを設定するには、SQL Server Management Studio で新しいクエリを開き、次のコードを実行して AdventureWorks2008R2 データベースを開きます。このコードにより、データベース コンテキストが AdventureWorks2008R2 に変更され、新しいサーバー ログインとデータベース ユーザー アカウント (TestCreditRatingUser) が作成されます。ログインにはパスワードが設定されます。

USE AdventureWorks2008R2;
GO
-- Set up a login for the test user
CREATE LOGIN TestCreditRatingUser
   WITH PASSWORD = 'ASDECd2439587y'
GO
CREATE USER TestCreditRatingUser
FOR LOGIN TestCreditRatingUser;
GO

USE AdventureWorks2008R2;
GO
-- Set up a login for the test user
CREATE LOGIN TestCreditRatingUser
   WITH PASSWORD = 'ASDECd2439587y'
GO
CREATE USER TestCreditRatingUser
FOR LOGIN TestCreditRatingUser;
GO

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

2. 証明書を作成する

コンテキストの master データベース、またはユーザー データベース、あるいはその両方を使用して、サーバー内に証明書を作成できます。証明書をセキュリティ保護するには複数のオプションがあります。証明書の詳細については、「CREATE CERTIFICATE (Transact-SQL)」を参照してください。

次のコードを実行し、データベース証明書をパスワードの保護付きで作成します。

CREATE CERTIFICATE TestCreditRatingCer
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
      WITH SUBJECT = 'Credit Rating Records Access', 
      EXPIRY_DATE = '12/05/2010';
GO

CREATE CERTIFICATE TestCreditRatingCer
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
      WITH SUBJECT = 'Credit Rating Records Access', 
      EXPIRY_DATE = '12/05/2010';
GO

3. ストアド プロシージャを作成し、証明書を使用して署名する

次のコードを実行し、Purchasing データベース スキーマ内の Vendor テーブルからデータを選択するストアド プロシージャを作成して、信用格付け 1 の会社にアクセスを限定します。ストアド プロシージャの最初のセクションに、ストアド プロシージャを実行するユーザー アカウントのコンテキストが表示されていますが、これは概念を説明するためだけのものです。要件を満たすために必要なものではありません。

CREATE PROCEDURE TestCreditRatingSP
AS
BEGIN
   -- Show who is running the stored procedure
   SELECT SYSTEM_USER 'system Login'
   , USER AS 'Database Login'
   , NAME AS 'Context'
   , TYPE
   , USAGE 
   FROM sys.user_token   

   -- Now get the data
   SELECT AccountNumber, Name, CreditRating 
   FROM Purchasing.Vendor
   WHERE CreditRating = 1
END
GO

CREATE PROCEDURE TestCreditRatingSP
AS
BEGIN
   -- Show who is running the stored procedure
   SELECT SYSTEM_USER 'system Login'
   , USER AS 'Database Login'
   , NAME AS 'Context'
   , TYPE
   , USAGE 
   FROM sys.user_token   

   -- Now get the data
   SELECT AccountNumber, Name, CreditRating 
   FROM Purchasing.Vendor
   WHERE CreditRating = 1
END
GO

次のコードを実行し、データベース証明書を使用してストアド プロシージャに署名します。このときパスワードも使用します。

ADD SIGNATURE TO TestCreditRatingSP 
   BY CERTIFICATE TestCreditRatingCer
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';
GO

ADD SIGNATURE TO TestCreditRatingSP 
   BY CERTIFICATE TestCreditRatingCer
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';
GO

ストアド プロシージャの詳細については、「ストアド プロシージャ (データベース エンジン)」を参照してください。

データベース スキーマの詳細については、「スキーマ (データベース エンジン)」を参照してください。

ストアド プロシージャへの署名の詳細については、「ADD SIGNATURE (Transact-SQL)」を参照してください。

4. 証明書を使用して証明書アカウントを作成する

次のコードを実行し、証明書からデータベース ユーザー (TestCreditRatingcertificateAccount) を作成します。このアカウントにサーバー ログインは与えません。最終的には、このアカウントによって基になるテーブルに対するアクセスが制御されます。

USE AdventureWorks2008R2;
GO
CREATE USER TestCreditRatingcertificateAccount
   FROM CERTIFICATE TestCreditRatingCer;
GO

USE AdventureWorks2008R2;
GO
CREATE USER TestCreditRatingcertificateAccount
   FROM CERTIFICATE TestCreditRatingCer;
GO

5. 証明書アカウントにデータベース権限を与える

次のコードを実行し、ベース テーブルとストアド プロシージャに TestCreditRatingcertificateAccount 権限を与えます。

GRANT SELECT 
   ON Purchasing.Vendor 
   TO TestCreditRatingcertificateAccount;
GO

GRANT EXECUTE 
   ON TestCreditRatingSP 
   TO TestCreditRatingcertificateAccount;
GO

GRANT SELECT 
   ON Purchasing.Vendor 
   TO TestCreditRatingcertificateAccount;
GO

GRANT EXECUTE 
   ON TestCreditRatingSP 
   TO TestCreditRatingcertificateAccount;
GO

オブジェクトに対する権限付与の詳細については、「GRANT (Transact-SQL)」を参照してください。

6. アクセス コンテキストを表示する

ストアド プロシージャへのアクセスに関連する権限を表示するには、次のコードを実行して、ストアド プロシージャの実行権限を TestCreditRatingUser ユーザーに与えます。

GRANT EXECUTE 
   ON TestCreditRatingSP 
   TO TestCreditRatingUser;
GO

GRANT EXECUTE 
   ON TestCreditRatingSP 
   TO TestCreditRatingUser;
GO

その後、次のコードを実行し、サーバーに使用した dbo ログインでストアド プロシージャを実行して、ユーザー コンテキスト情報の出力を確認します。この出力では、dbo アカウントはグループ メンバーシップを介してではなく、自身の権限を持つコンテキストとして表示されます。

EXECUTE TestCreditRatingSP;
GO

EXECUTE TestCreditRatingSP;
GO

次のコードを実行し、EXECUTE AS ステートメントにより TestCreditRatingUser アカウントとなってストアド プロシージャを実行します。今度は、ユーザー コンテキストが USER MAPPED TO CERTIFICATE コンテキストに設定されていることを確認できます。

EXECUTE AS LOGIN = 'TestCreditRatingUser';
GO
EXECUTE TestCreditRatingSP;
GO

EXECUTE AS LOGIN = 'TestCreditRatingUser';
GO
EXECUTE TestCreditRatingSP;
GO

これは、ストアド プロシージャへの署名により監査が可能になったことを示しています。

注意注

ユーザーがデータベース内でコンテキストを切り替えられるようにするには、SETUSER と EXECUTE AS の 2 つの方法があります。コンテキストの切り替えの詳細については、「EXECUTE AS と SETUSER」を参照してください。

7. 環境をリセットする

次のコードを実行して、REVERT ステートメントにより現在のアカウントのコンテキストを dbo に戻し、環境をリセットします。

REVERT;
GO
DROP PROCEDURE TestCreditRatingSP;
GO
DROP USER TestCreditRatingcertificateAccount;
GO
DROP USER TestCreditRatingUser;
GO
DROP LOGIN TestCreditRatingUser;
GO
DROP CERTIFICATE TestCreditRatingCer;
GO

REVERT;
GO
DROP PROCEDURE TestCreditRatingSP;
GO
DROP USER TestCreditRatingcertificateAccount;
GO
DROP USER TestCreditRatingUser;
GO
DROP LOGIN TestCreditRatingUser;
GO
DROP CERTIFICATE TestCreditRatingCer;
GO

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

完全なサンプル コード

次に、完全なサンプル コードを示します。

/* Step 1 - Open the AdventureWorks2008R2 database */
USE AdventureWorks2008R2;
GO
-- Set up a login for the test user
CREATE LOGIN TestCreditRatingUser
   WITH PASSWORD = 'ASDECd2439587y'
GO
CREATE USER TestCreditRatingUser
FOR LOGIN TestCreditRatingUser;
GO

/* Step 2 - Create a certificate in the AdventureWorks2008R2 database */
CREATE CERTIFICATE TestCreditRatingCer
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
      WITH SUBJECT = 'Credit Rating Records Access', 
      EXPIRY_DATE = '12/05/2010';
GO

/* Step 3 - Create a stored procedure and
sign it using the certificate */
CREATE PROCEDURE TestCreditRatingSP
AS
BEGIN
   -- Shows who is running the stored procedure
   SELECT SYSTEM_USER 'system Login'
   , USER AS 'Database Login'
   , NAME AS 'Context'
   , TYPE
   , USAGE 
   FROM sys.user_token;   

   -- Now get the data
   SELECT AccountNumber, Name, CreditRating 
   FROM Purchasing.Vendor
   WHERE CreditRating = 1;
END
GO

ADD SIGNATURE TO TestCreditRatingSP 
   BY CERTIFICATE TestCreditRatingCer
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';
GO

/* Step 4 - Create a database user for the certificate. 
This user has the ownership chain associated with it. */
USE AdventureWorks2008R2;
GO
CREATE USER TestCreditRatingcertificateAccount
   FROM CERTIFICATE TestCreditRatingCer;
GO

/* Step 5 - Grant the user database rights */
GRANT SELECT 
   ON Purchasing.Vendor 
   TO TestCreditRatingcertificateAccount;
GO

GRANT EXECUTE
   ON TestCreditRatingSP 
   TO TestCreditRatingcertificateAccount;
GO

/* Step 6 - Test, using the EXECUTE AS statement */
GRANT EXECUTE 
   ON TestCreditRatingSP 
   TO TestCreditRatingUser;
GO

-- Run the procedure as the dbo user, notice the output for the type
EXEC TestCreditRatingSP;
GO

EXECUTE AS LOGIN = 'TestCreditRatingUser';
GO
EXEC TestCreditRatingSP;
GO

/* Step 7 - Clean up the example */
REVERT;
GO
DROP PROCEDURE TestCreditRatingSP;
GO
DROP USER TestCreditRatingcertificateAccount;
GO
DROP USER TestCreditRatingUser;
GO
DROP LOGIN TestCreditRatingUser;
GO
DROP CERTIFICATE TestCreditRatingCer;
GO

/* Step 1 - Open the AdventureWorks2008R2 database */
USE AdventureWorks2008R2;
GO
-- Set up a login for the test user
CREATE LOGIN TestCreditRatingUser
   WITH PASSWORD = 'ASDECd2439587y'
GO
CREATE USER TestCreditRatingUser
FOR LOGIN TestCreditRatingUser;
GO

/* Step 2 - Create a certificate in the AdventureWorks2008R2 database */
CREATE CERTIFICATE TestCreditRatingCer
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
      WITH SUBJECT = 'Credit Rating Records Access', 
      EXPIRY_DATE = '12/05/2010';
GO

/* Step 3 - Create a stored procedure and
sign it using the certificate */
CREATE PROCEDURE TestCreditRatingSP
AS
BEGIN
   -- Shows who is running the stored procedure
   SELECT SYSTEM_USER 'system Login'
   , USER AS 'Database Login'
   , NAME AS 'Context'
   , TYPE
   , USAGE 
   FROM sys.user_token;   

   -- Now get the data
   SELECT AccountNumber, Name, CreditRating 
   FROM Purchasing.Vendor
   WHERE CreditRating = 1;
END
GO

ADD SIGNATURE TO TestCreditRatingSP 
   BY CERTIFICATE TestCreditRatingCer
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';
GO

/* Step 4 - Create a database user for the certificate. 
This user has the ownership chain associated with it. */
USE AdventureWorks2008R2;
GO
CREATE USER TestCreditRatingcertificateAccount
   FROM CERTIFICATE TestCreditRatingCer;
GO

/* Step 5 - Grant the user database rights */
GRANT SELECT 
   ON Purchasing.Vendor 
   TO TestCreditRatingcertificateAccount;
GO

GRANT EXECUTE
   ON TestCreditRatingSP 
   TO TestCreditRatingcertificateAccount;
GO

/* Step 6 - Test, using the EXECUTE AS statement */
GRANT EXECUTE 
   ON TestCreditRatingSP 
   TO TestCreditRatingUser;
GO

-- Run the procedure as the dbo user, notice the output for the type
EXEC TestCreditRatingSP;
GO

EXECUTE AS LOGIN = 'TestCreditRatingUser';
GO
EXEC TestCreditRatingSP;
GO

/* Step 7 - Clean up the example */
REVERT;
GO
DROP PROCEDURE TestCreditRatingSP;
GO
DROP USER TestCreditRatingcertificateAccount;
GO
DROP USER TestCreditRatingUser;
GO
DROP LOGIN TestCreditRatingUser;
GO
DROP CERTIFICATE TestCreditRatingCer;
GO