このチュートリアルでは、SQL Server で生成された証明書を使用してストアド プロシージャに署名する方法について説明します。
注
このチュートリアルのコードを実行するには、混合モード のセキュリティが構成され、 AdventureWorks2012 データベースがインストールされている必要があります。 シナリオ
証明書を使用したストアド プロシージャへの署名は、ストアド プロシージャに対するアクセス許可を必要とするが、ユーザーに明示的に権限を付与したくない場合に便利です。 このタスクは、EXECUTE AS ステートメントの使用など、他の方法で実行できますが、証明書を使用すると、トレースを使用してストアド プロシージャの元の呼び出し元を検索できます。 これにより、特にセキュリティまたはデータ定義言語 (DDL) の操作中に、高度な監査が提供されます。
マスター データベースに証明書を作成してサーバー レベルのアクセス許可を許可することも、任意のユーザー データベースに証明書を作成してデータベース レベルのアクセス許可を許可することもできます。 このシナリオでは、ベース テーブルに対する権限を持たないユーザーが AdventureWorks2012 データベースのストアド プロシージャにアクセスする必要があり、オブジェクト アクセス 証跡を監査する必要があります。 他の所有権チェーンメソッドを使用するのではなく、基本オブジェクトに対する権限を持たないサーバーとデータベースのユーザー アカウントと、テーブルとストアド プロシージャに対する権限を持つデータベース ユーザー アカウントを作成します。 ストアド プロシージャと 2 番目のデータベース ユーザー アカウントの両方が証明書で保護されます。 2 番目のデータベース アカウントは、すべてのオブジェクトにアクセスでき、ストアド プロシージャへのアクセス権を最初のデータベース ユーザー アカウントに付与します。
このシナリオでは、最初にデータベース証明書、ストアド プロシージャ、およびユーザーを作成し、次の手順に従ってプロセスをテストします。
環境を構成します。
証明書を作成する。
証明書を使用してストアド プロシージャを作成して署名します。
証明書を使用して証明書アカウントを作成します。
証明書アカウントのデータベース権限を付与します。
アクセス コンテキストを表示します。
環境をリセットします。
この例では、各コードブロックがその場で説明されています。 完全な例をコピーするには、このチュートリアルの最後にある 完全な例 を参照してください。
1. 環境を構成する
この例の初期コンテキストを設定するには、SQL Server Management Studio で新しいクエリを開き、次のコードを実行して AdventureWorks2012 データベースを開きます。 このコードは、データベース コンテキストを AdventureWorks2012 に変更し、パスワードを使用して新しいサーバー ログインとデータベース ユーザー アカウント (TestCreditRatingUser) を作成します。
USE AdventureWorks2012;
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.証明書を作成する
マスター データベースをコンテキストとして使用するか、ユーザー データベースを使用するか、またはその両方を使用して、サーバーで証明書を作成できます。 証明書をセキュリティで保護するには、複数のオプションがあります。 証明書の詳細については、「CREATE CERTIFICATE (Transact-SQL)」 を参照してください。
このコードを実行してデータベース証明書を作成し、パスワードを使用してセキュリティで保護します。
CREATE CERTIFICATE TestCreditRatingCer
ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
WITH SUBJECT = 'Credit Rating Records Access',
EXPIRY_DATE = '12/05/2014';
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
このコードを実行して、パスワードを使用してデータベース証明書でストアド プロシージャに署名します。
ADD SIGNATURE TO TestCreditRatingSP
BY CERTIFICATE TestCreditRatingCer
WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';
GO
ストアド プロシージャの詳細については、「 ストアド プロシージャ (データベース エンジン)」を参照してください。
ストアド プロシージャの署名の詳細については、「 ADD SIGNATURE (Transact-SQL)」を参照してください。
4. 証明書を使用して証明書アカウントを作成する
このコードを実行して、証明書からデータベース ユーザー (TestCreditRatingcertificateAccount) を作成します。 このアカウントにはサーバー ログインがなく、最終的には基になるテーブルへのアクセスが制御されます。
USE AdventureWorks2012;
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」のコンテキストに設定されていることがわかります。
EXECUTE AS LOGIN = 'TestCreditRatingUser';
GO
EXECUTE TestCreditRatingSP;
GO
ストアド プロシージャに署名したために使用できる監査が表示されます。
注
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 AdventureWorks2012 database */
USE AdventureWorks2012;
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 AdventureWorks2012 database */
CREATE CERTIFICATE TestCreditRatingCer
ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
WITH SUBJECT = 'Credit Rating Records Access',
EXPIRY_DATE = '12/05/2014';
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 AdventureWorks2012;
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