[アーティクル]
10/25/2012

モジュール署名 (データベースエンジン)

データベースアプリケーションでは、多くの場合、アプリケーションスキーマ内の基になるテーブルおよびオブジェクトへのアクセスを、エントリレベルのプロシージャまたはビューによって媒介する必要があります。そうすることで、エンドユーザーに対し、そのユーザーに代わって基になるオブジェクトにアクセスする、エントリレベルのオブジェクトへのアクセス権を与えることが可能になります。これにより、エンドユーザーには、アプリケーションスキーマ内のすべてのオブジェクトへのアクセス権を与える必要がなくなります。このアプローチは、次の 2 つの目的に役立ちます。

権限を、アプリケーションスキーマ内のすべてのオブジェクトについて管理する場合に比べて、比較的少数のオブジェクトのサブセットについてのみ管理すれば済むため、権限の管理が簡素化されます。
エントリポイントのみが公開されるため、基になるスキーマのレイアウトをエンドユーザーから隠すことができます。

Microsoft SQL Server には、所有権の継承や、EXECUTE AS ステートメントの使用など、これらのシナリオを実現するために役立つさまざまな機能が含まれています。また、SQL Server 2005 以降の SQL Server には、データベース内のモジュールに署名する機能も用意されています。モジュール署名では、同様の機能が提供されますが、実行コンテキストは変更されません。このコンテキストでのモジュールとは、ストアドプロシージャ、関数、トリガ、またはアセンブリのことを指します。詳細については、「CREATE ROLE (Transact-SQL)」および「EXECUTE AS の使用によるカスタム権限セットの作成」を参照してください。

モジュール署名

SQL Server 2005 では、ストアドプロシージャ、関数、トリガ、またはアセンブリなどの、データベース内のモジュールに署名する機能が導入されました。ただし、データ定義言語 (DDL) トリガには署名できません。デジタル署名は、署名者の秘密キーで暗号化されたデータダイジェストです。秘密キーにより、デジタル署名がその保持者または所有者に固有であることが保証されます。

データに署名するには、署名者がデータをダイジェストし、秘密キーで暗号化して、暗号化されたダイジェスト値をデータに添付します。署名を検証するには、検証者が署名者の公開キーを使用して、データに添付されている、暗号化されたダイジェスト値を暗号化解除します。次に検証者は、この暗号化解除されたダイジェスト値を、それが添付されていたデータから計算したダイジェスト値と比較します。署名者と検証者の両方が、データをダイジェストするために、同じハッシュ関数を使用することが重要です。

注意
モジュール署名は、権限を許可するためにのみ使用する必要があります。権限の拒否や取り消しには使用しません。

シナリオ

sys.sysprocesses ビューへのアクセスが、usp_sysprocesses ストアドプロシージャによって媒介される必要があるとします。ユーザーは、sys.sysprocesses の情報に、usp_sysprocesses プロシージャを介してのみアクセスできます。usp_sysprocesses および sys.sysprocesses オブジェクトは所有権が異なっているため、所有権の継承は適用されません。

まず、CREATE CERTIFICATE ステートメントを使用して、キーのペアから、サーバー上で証明書を作成する必要があります。次に、その証明書に、sys.sysprocesses テーブルから選択する権限を与えます。ただし、SQL Server では権限はプリンシパルのみに与えられるため、最初に CREATE LOGIN ステートメントを使用して、証明書からログインを作成しておく必要が生じる場合があります。このログインは、権限のプレースホルダであり、サーバーインスタンスに接続することは意図されていないため、このログインにはサーバーへの接続権限は必要ありません。以上が完了したら、GRANT VIEW SERVER STATE TO ステートメントを使用して、この証明書からマップされたログインに、sys.sysprocesses テーブルに対する SELECT 権限を与えることができます。usp_sysprocesses ストアドプロシージャを作成したら、ADD SIGNATURE ステートメントにより、そのストアドプロシージャに証明書 (実際には、この証明書に対応する秘密キー) を使用して署名することができます。新しいロールを作成し、usp_sysprocesses ストアドプロシージャの実行権限を与えます。このロールのメンバであるすべてのユーザーは、usp_sysprocesses ストアドプロシージャの実行権限を与えられ、それによって sys.sysprocess ビューからの SELECT を実行できます。ユーザーが署名付きモジュールを実行する場合、署名に使用された証明書に関連付けられている、プリンシパルに (GRANT ステートメントを使用して) 与えられた権限は、呼び出しの実行時間中、そのユーザーの実行時セキュリティトークンに一時的に UNION されます。実行制御が戻されると、これらの権限はセキュリティトークンから直ちに除去されます。このようにして、ユーザーには、モジュール実行の有効期間中に限って、追加の権限セットが付与されます。このプロシージャに対する EXECUTE 権限を持つ他のすべてのユーザーまたはロールにも、同じ権限が与えられます。

例

前のシナリオの例として、Transact-SQL スクリプトを次に示します。証明書は、キーのペアから作成され、新しいログインにマップされます。最初に、.NET Framework SDK に含まれている MakeCert ツールを使用して、テスト用のキーのペアを作成しておく必要があります。次に、sys.sysproceses ビューに対する選択権限を、証明書に関連付けられたログインに付与します。新しいデータベースで usp_sysprocesses マネージストアドプロシージャを作成し、証明書を使用して署名します。SysProcRole ロールを作成し、そのロールに usp_sysprocesses ストアドプロシージャに対する実行権限を与えます。テストユーザーを作成し、SysProcRole ロールに追加します。テストユーザーは sys.sysprocess に対する SELECT ステートメントを実行し、次に、比較のために usp_sysprocesses ストアドプロシージャを実行します。スクリプトの最後に、テスト環境がクリーンアップされます。

use master
go

-- Create a test database.
CREATE DATABASE db_Demo
go

-- Create a certificate on the server. A test key pair can be created
-- using the MakeCert tool that ships with the .NET Framework SDK.
CREATE CERTIFICATE SysProcCert FROM FILE = 'e:\programming\testCert.cer'
go

-- Create a login and map it to the certificate.
CREATE LOGIN login_SysProcCert FROM CERTIFICATE SysProcCert
Go

-- Revoke the connect permission.
REVOKE CONNECT SQL FROM login_SysProcCert ;
go 
 
-- Grant the certificate, through the login, permission to select from sys.sysprocesses view.
GRANT VIEW SERVER STATE TO login_SysProcCert
go

-- Create a test login.
CREATE LOGIN bob WITH PASSWORD = '<enterStrongPasswordHere>'
go

-- Connect to the test database.
use db_Demo
go

-- Create the master key for the test database (used to protect 
-- private keys and certificates in the database).
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<enterStrongPasswordHere>' 

-- Create a certificate from a private key.
CREATE CERTIFICATE SysProcCert FROM FILE = 'e:\programming\testCert.cer'
WITH PRIVATE KEY
(FILE = 'e:\programming\testCert.pvk', 
 DECRYPTION BY PASSWORD= '<enterStrongPasswordHere>', 
 ENCRYPTION BY PASSWORD='<enterStrongPasswordHere>')
go 

-- Create the assembly on the server. The assembly DLL must be signed.
CREATE ASSEMBLY SysStoredProcedures
FROM 'E:\programming\SysStoredProcs.dll'
WITH PERMISSION_SET = SAFE
go 

-- Create the managed stored procedure on the server.
CREATE PROCEDURE usp_sysprocesses
AS EXTERNAL NAME SysStoredProcedures.StoredProcedures.usp_sysprocesses
go 

-- Add the signature to the stored procedure.
ADD SIGNATURE TO [dbo].[usp_sysprocesses] 
BY CERTIFICATE SysProcCert WITH PASSWORD = '<enterStrongPasswordHere>'
go 

-- Create a role.
CREATE ROLE SysProcRole
go

-- Create a test user
CREATE USER bob
go

-- Add the test user to the role.
EXEC sp_addrolemember 'SysProcRole', 'bob'
go

-- Grant execute permissions on the stored procedure to the new role.
GRANT EXECUTE ON [dbo].[usp_sysprocesses] TO SysProcRole
go
 
-- Connect as the test user.
EXECUTE AS LOGIN = 'bob'
use db_Demo
go
 
-- User only has permission to see their own processes.
SELECT * FROM sys.sysprocesses
go

-- Execute the stored procedure, which has been signed.
exec usp_sysprocesses
go

-- REVERT
REVERT
----------------------------------------------------------------------
-- Cleanup

use db_Demo
go

use master
go

DROP DATABASE db_Demo
go 

DROP login login_SysProcCert
DROP login bob
go

DROP CERTIFICATE SysProcCert
go

use master
go

-- Create a test database.
CREATE DATABASE db_Demo
go

-- Create a certificate on the server. A test key pair can be created
-- using the MakeCert tool that ships with the .NET Framework SDK.
CREATE CERTIFICATE SysProcCert FROM FILE = 'e:\programming\testCert.cer'
go

-- Create a login and map it to the certificate.
CREATE LOGIN login_SysProcCert FROM CERTIFICATE SysProcCert
Go

-- Revoke the connect permission.
REVOKE CONNECT SQL FROM login_SysProcCert ;
go 
 
-- Grant the certificate, through the login, permission to select from sys.sysprocesses view.
GRANT VIEW SERVER STATE TO login_SysProcCert
go

-- Create a test login.
CREATE LOGIN bob WITH PASSWORD = '<enterStrongPasswordHere>'
go

-- Connect to the test database.
use db_Demo
go

-- Create the master key for the test database (used to protect 
-- private keys and certificates in the database).
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<enterStrongPasswordHere>' 

-- Create a certificate from a private key.
CREATE CERTIFICATE SysProcCert FROM FILE = 'e:\programming\testCert.cer'
WITH PRIVATE KEY
(FILE = 'e:\programming\testCert.pvk', 
 DECRYPTION BY PASSWORD= '<enterStrongPasswordHere>', 
 ENCRYPTION BY PASSWORD='<enterStrongPasswordHere>')
go 

-- Create the assembly on the server. The assembly DLL must be signed.
CREATE ASSEMBLY SysStoredProcedures
FROM 'E:\programming\SysStoredProcs.dll'
WITH PERMISSION_SET = SAFE
go 

-- Create the managed stored procedure on the server.
CREATE PROCEDURE usp_sysprocesses
AS EXTERNAL NAME SysStoredProcedures.StoredProcedures.usp_sysprocesses
go 

-- Add the signature to the stored procedure.
ADD SIGNATURE TO [dbo].[usp_sysprocesses] 
BY CERTIFICATE SysProcCert WITH PASSWORD = '<enterStrongPasswordHere>'
go 

-- Create a role.
CREATE ROLE SysProcRole
go

-- Create a test user
CREATE USER bob
go

-- Add the test user to the role.
EXEC sp_addrolemember 'SysProcRole', 'bob'
go

-- Grant execute permissions on the stored procedure to the new role.
GRANT EXECUTE ON [dbo].[usp_sysprocesses] TO SysProcRole
go
 
-- Connect as the test user.
EXECUTE AS LOGIN = 'bob'
use db_Demo
go
 
-- User only has permission to see their own processes.
SELECT * FROM sys.sysprocesses
go

-- Execute the stored procedure, which has been signed.
exec usp_sysprocesses
go

-- REVERT
REVERT
----------------------------------------------------------------------
-- Cleanup

use db_Demo
go

use master
go

DROP DATABASE db_Demo
go 

DROP login login_SysProcCert
DROP login bob
go

DROP CERTIFICATE SysProcCert
go

次に、usp_sysprocesses ストアドプロシージャのソースコードを示します。このプロシージャは、sys.sysprocesses ビューに対する SELECT * ステートメントを実行します。アセンブリは、作成時に署名する必要があります。

using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void usp_sysprocesses()
{
    using(SqlConnection connection = new SqlConnection("context connection=true"))
    {
        connection.Open();
        SqlCommand command = new SqlCommand("SELECT * FROM sys.sysprocesses", connection);
        SqlContext.Pipe.ExecuteAndSend(command);
    }
}
};

using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void usp_sysprocesses()
{
    using(SqlConnection connection = new SqlConnection("context connection=true"))
    {
        connection.Open();
        SqlCommand command = new SqlCommand("SELECT * FROM sys.sysprocesses", connection);
        SqlContext.Pipe.ExecuteAndSend(command);
    }
}
};

Visual Basic

Imports System
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub  usp_sysprocesses ()
    Using connection As New SqlConnection("context connection=true")
        connection.Open()

        Dim command As New SqlCommand("SELECT * FROM sys.sysprocesses", connection)
        SqlContext.Pipe.ExecuteAndSend(command)
    End Using
End Sub
End Class

Imports System
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub  usp_sysprocesses ()
    Using connection As New SqlConnection("context connection=true")
        connection.Open()

        Dim command As New SqlCommand("SELECT * FROM sys.sysprocesses", connection)
        SqlContext.Pipe.ExecuteAndSend(command)
    End Using
End Sub
End Class

次の方法で共有

モジュール署名 (データベースエンジン)

モジュール署名

シナリオ

例

関連項目

参照

概念

その他のリソース

次の方法で共有

モジュール署名 (データベース エンジン)

モジュール署名

シナリオ

例

関連項目

参照

概念

その他のリソース

モジュール署名 (データベースエンジン)