チュートリアル : 証明書を使用したストアド プロシージャへの署名
適用対象: 
SQL Server Azure SQL Database Azure SQL Managed Instance
このチュートリアルでは、SQL Server で生成された証明書を使用してストアド プロシージャに署名する方法について説明します。
Note
このチュートリアルのコードを実行するには、混合モードのセキュリティが構成されていることと、 AdventureWorks2022 データベースがインストールされていることが条件となります。
証明書を使用したストアド プロシージャへの署名は、ストアド プロシージャに権限を設定する一方、ユーザーにはこのような権限を明示的に与えないでおこうとする場合に便利です。 この動作は EXECUTE AS ステートメントを使用するなどの方法でも実現できますが、証明書を使用すると、トレースによってプロシージャの最初の呼び出し元を見つけることができ、 高いレベルの監査を行うことができます。特に、セキュリティやデータ定義言語 (DDL) の操作に対しては有効です。
master データベースに証明書を作成し、サーバーレベルの権限を付与するか、任意のユーザー データベースに証明書を作成し、データベースレベルの権限を付与することができます。 このシナリオでは、ベース テーブルに対する権限を持たないユーザーが AdventureWorks2022 データベースにアクセスする必要があることを想定し、また、オブジェクト アクセスの記録を監査します。 ここでは所有権を継承する方法を使用するのではなく、サーバーおよびデータベース ユーザー アカウントを 1 つとデータベース ユーザー アカウントを 1 つ作成し、前者のアカウントにはベース オブジェクトに対する権限を与えず、後者にはテーブルとストアド プロシージャに対する権限を与えます。 ストアド プロシージャと 2 番目のデータベース ユーザー アカウントは両方とも、証明書によりセキュリティ保護されます。 2 番目のデータベース アカウントではすべてのオブジェクトにアクセスでき、ストアド プロシージャへのアクセス許可を最初のデータベース ユーザー アカウントに与えることができます。
このシナリオでは、最初にデータベース証明書、ストアド プロシージャ、ユーザーを 1 つずつ作成し、次にプロセスをテストします。手順は次のとおりです。
以下で、この例の各コード ブロックについて説明します。 完全なサンプル コードをコピーするには、このチュートリアルの最後の「 完全なサンプル コード 」を参照してください。
前提条件
このチュートリアルを実行するには、SQL Server Management Studio、SQL Server を実行しているサーバーへのアクセス、および AdventureWorks データベースが必要です。
- SQL Server Management Studio をインストールします。
- SQL Server 2017 Developer Edition をインストールします。
- AdventureWorks サンプル データベースをダウンロードします。
SQL Server Management Studio でデータベースを復元する手順については、データベースの復元に関するページを参照してください。
1.環境を構成する
この例の初期コンテキストを設定するには、SQL Server Management Studio で新しいクエリを開き、次のコードを実行して AdventureWorks2022 データベースを開きます。 このコードにより、データベース コンテキストが AdventureWorks2022 に変更され、新しいサーバー ログインとデータベース ユーザー アカウント (TestCreditRatingUser) が作成されます。ログインにはパスワードが設定されます。
USE AdventureWorks2022;
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/31/2022'; -- Error 3701 will occur if this date is not in the future
GO
3.ストアド プロシージャを作成し、証明書を使用して署名する
次のコードを実行し、 Vendor データベース スキーマ内の Purchasing テーブルからデータを選択するストアド プロシージャを作成して、信用格付け 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
次のコードを実行し、データベース証明書を使用してストアド プロシージャに署名します。このときパスワードも使用します。
ADD SIGNATURE TO TestCreditRatingSP
BY CERTIFICATE TestCreditRatingCer
WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';
GO
ストアド プロシージャの詳細については、「ストアド プロシージャ (データベース エンジン)」を参照してください。
ストアド プロシージャへの署名の詳細については、「ADD SIGNATURE (Transact-SQL)」 を参照してください。
4.証明書を使用して証明書アカウントを作成する
次のコードを実行し、証明書からデータベース ユーザー (TestCreditRatingcertificateAccount) を作成します。 このアカウントにサーバー ログインは与えません。最終的には、このアカウントによって基になるテーブルに対するアクセスが制御されます。
USE AdventureWorks2022;
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 (Transact-SQL)」 を参照してください。
6.アクセス コンテキストを表示する
ストアド プロシージャへのアクセスに関連する権限を表示するには、次のコードを実行して、ストアド プロシージャの実行権限を TestCreditRatingUser ユーザーに与えます。
GRANT EXECUTE
ON TestCreditRatingSP
TO TestCreditRatingUser;
GO
その後、次のコードを実行し、サーバーに使用した dbo ログインでストアド プロシージャを実行して、 ユーザー コンテキスト情報の出力を確認します。 この出力では、dbo アカウントはグループ メンバーシップを介してではなく、自身の権限を持つコンテキストとして表示されます。
EXECUTE TestCreditRatingSP;
GO
次のコードを実行し、 EXECUTE AS ステートメントにより TestCreditRatingUser アカウントとなってストアド プロシージャを実行します。 今度は、ユーザー コンテキストが USER MAPPED TO CERTIFICATE コンテキストに設定されていることを確認できます。 包含データベース、Azure SQL Database、Azure Synapse Analytics ではこのオプションはサポートされていないことにご注意ください。
EXECUTE AS LOGIN = 'TestCreditRatingUser';
GO
EXECUTE TestCreditRatingSP;
GO
これは、ストアド プロシージャへの署名により監査が可能になったことを示しています。
Note
データベース内でコンテキストを切り替えるには、EXECUTE AS を使用します。
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 ステートメントの詳細については、「REVERT (Transact-SQL)」 を参照してください。
コード例全体
次に、完全なサンプル コードを示します。
/* Step 1 - Open the AdventureWorks2022 database */
USE AdventureWorks2022;
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 AdventureWorks2022 database */
CREATE CERTIFICATE TestCreditRatingCer
ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
WITH SUBJECT = 'Credit Rating Records Access',
EXPIRY_DATE = '12/31/2021'; -- Error 3701 will occur if this date is not in the future
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 AdventureWorks2022;
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