次の方法で共有


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

データベース アプリケーションでは、多くの場合、アプリケーション スキーマ内の基になるテーブルおよびオブジェクトへのアクセスを、エントリ レベルのプロシージャまたはビューによって媒介する必要があります。そうすることで、エンド ユーザーに対し、そのユーザーに代わって基になるオブジェクトにアクセスする、エントリ レベルのオブジェクトへのアクセス権を与えることが可能になります。これにより、エンド ユーザーには、アプリケーション スキーマ内のすべてのオブジェクトへのアクセス権を与える必要がなくなります。このアプローチは、次の 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 * ステートメントを実行します。アセンブリは、作成時に署名する必要があります。

C#

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