ADD SIGNATURE (Transact-SQL)

署名付きモジュールを実行すると、署名が SQL トークンに一時的に追加されますが、モジュールで別のモジュールが実行されるかモジュールで実行が終了すると、署名は失われます。副署名は特殊な形式の署名です。副署名だけでなんらかの権限が許可されるわけではありませんが、副署名されたオブジェクトに対して行われた呼び出しの間、同じ証明書または非対称キーで作成された署名を使用できるようになります。

たとえば、ユーザー Alice が ProcSelectT1ForAlice プロシージャを呼び出したとします。このプロシージャを呼び出すと、テーブル T1 から選択を行う procSelectT1 プロシージャが呼び出されます。Alice は ProcSelectT1ForAlice と procSelectT1 に対する EXECUTE 権限を持っていますが、T1 に対する SELECT 権限は持っていないため、このチェーン全体に関与する組み合わせ所有権はありません。Alice は、T1 テーブルに直接アクセスすることも、ProcSelectT1ForAlice と procSelectT1 を使用してアクセスすることもできません。Alice がアクセスのために常に ProcSelectT1ForAlice を使用するようにしたいので、procSelectT1 を実行する権限は許可したくありません。このためにはどうするとよいでしょうか。

• procSelectT1 に署名して procSelectT1 が T1 にアクセスできるようにした場合、Alice は procSelectT1 を直接呼び出すことができるようになるので、ProcSelectT1ForAlice を呼び出す必要はありません。

• procSelectT1 に対する Alice の EXECUTE 権限を拒否することもできますが、その場合、Alice は ProcSelectT1ForAlice から procSelectT1 を呼び出すこともできなくなります。

• ProcSelectT1ForAlice への署名は procSelectT1 の呼び出し時に失われるので、この署名だけでは効果はありません。

ただし、ProcSelectT1ForAlice への署名に使用したものと同じ証明書を使用して procSelectT1 に副署名すると、SQL Server は呼び出しチェーン全体にわたって署名を保持することで、T1 にアクセスできるようにします。副署名では権限は許可されないため、Alice が procSelectT1 を直接呼び出そうとしても、T1 にはアクセスできません。後の「例 C」で、この例の Transact-SQL を示します。

次の例では、ストアド プロシージャ HumanResources.uspUpdateEmployeeLogin に対して、証明書 HumanResourcesDP を使用して署名を行います。

USE AdventureWorks;
ADD SIGNATURE TO HumanResources.uspUpdateEmployeeLogin 
    BY CERTIFICATE HumanResourcesDP;
GO

次の例では、新しいデータベースを作成し、例で使用する証明書を作成します。この例では単純なストアド プロシージャを作成し署名して、sys.crypt_properties から署名付き BLOB を取得します。次に、プロシージャを削除してから再作成します。この例では、WITH SIGNATURE 構文を使用してプロシージャに署名します。

CREATE DATABASE TestSignature ;
GO
USE TestSignature ;
GO
-- Create a CERTIFICATE to sign the procedure.
CREATE CERTIFICATE cert_signature_demo 
    ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
    WITH SUBJECT = 'ADD SIGNATURE demo';
GO
-- Create a simple procedure.
CREATE PROC [sp_signature_demo]
AS
    PRINT 'This is the content of the procedure.' ;
GO
-- Sign the procedure.
ADD SIGNATURE TO [sp_signature_demo] 
    BY CERTIFICATE [cert_signature_demo] 
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y' ;
GO
-- Get the signature binary BLOB for the sp_signature_demo procedure.
SELECT cp.crypt_property
    FROM sys.crypt_properties AS cp
    JOIN sys.certificates AS cer
        ON cp.thumbprint = cer.thumbprint
    WHERE cer.name = 'cert_signature_demo' ;
GO

このステートメントが返す crypt_property 署名は、プロシージャを作成するたびに異なります。この例で、後から使用するために結果をメモしてください。この例では、結果は 0x831F5530C86CC8ED606E5BC2720DA835351E46219A6D5DE9CE546297B88AEF3B6A7051891AF3EE7A68EAB37CD8380988B4C3F7469C8EABDD9579A2A5C507A4482905C2F24024FFB2F9BD7A953DD5E98470C4AA90CE83237739BB5FAE7BAC796E7710BDE291B03C43582F6F2D3B381F2102EEF8407731E01A51E24D808D54B373 のようになります。

-- Drop the procedure so that a new version can be created.
DROP PROC [sp_signature_demo] ;
GO
-- Re-create the procedure by using the exact text including spaces.
CREATE PROC [sp_signature_demo]
AS
    PRINT 'This is the content of the procedure.' ;
GO
-- Add the signature. Use the signature BLOB obtained earlier.
ADD SIGNATURE TO [sp_signature_demo] 
    BY CERTIFICATE [cert_signature_demo]
    WITH SIGNATURE = 0x831F5530C86CC8ED606E5BC2720DA835351E46219A6D5DE9CE546297B88AEF3B6A7051891AF3EE7A68EAB37CD8380988B4C3F7469C8EABDD9579A2A5C507A4482905C2F24024FFB2F9BD7A953DD5E98470C4AA90CE83237739BB5FAE7BAC796E7710BDE291B03C43582F6F2D3B381F2102EEF8407731E01A51E24D808D54B373 ;
GO

次の例では、副署名によってオブジェクトへのアクセスを制御する方法を示します。

-- Create tesT1 database
CREATE DATABASE testDB;
GO
USE testDB;
GO
-- Create table T1
CREATE TABLE T1 (c varchar(11));
INSERT INTO T1 VALUES ('This is T1.');

-- Create a TestUser user to own table T1
CREATE USER TestUser WITHOUT LOGIN;
ALTER AUTHORIZATION ON T1 TO TestUser;

-- Create a certificate for signing
CREATE CERTIFICATE csSelectT
  ENCRYPTION BY PASSWORD = 'SimplePwd01'
  WITH SUBJECT = 'Certificate used to grant SELECT on T1';
CREATE USER ucsSelectT1 FROM CERTIFICATE csSelectT;
GRANT SELECT ON T1 TO ucsSelectT1;

-- Create a principal with low privileges
CREATE LOGIN Alice WITH PASSWORD = 'SimplePwd01';
CREATE USER Alice;

-- Verify Alice cannoT1 access T1;
EXECUTE AS LOGIN = 'Alice';
    SELECT * FROM T1;
REVERT;

-- Create a procedure that directly accesses T1
CREATE PROCEDURE procSelectT1 AS
BEGIN
    PRINT 'Now selecting from T1...';
    SELECT * FROM T1;
END;
GO
GRANT EXECUTE ON procSelectT1 to public;

-- Create special procedure for accessing T1
CREATE PROCEDURE  procSelectT1ForAlice AS
BEGIN
   IF USER_ID() <> USER_ID('Alice')
    BEGIN
        PRINT 'Only Alice can use this.';
        RETURN
    END
   EXEC procSelectT1;
END;
GO;
GRANT EXECUTE ON procSelectT1ForAlice TO PUBLIC;

-- Verify procedure works for a sysadmin user
EXEC procSelectT1ForAlice;

-- Alice still can't use the procedure yet
EXECUTE AS LOGIN = 'Alice';
    EXEC procSelectT1ForAlice;
REVERT;

-- Sign procedure to grant it SELECT permission
ADD SIGNATURE TO procSelectT1ForAlice BY CERTIFICATE csSelectT 
WITH PASSWORD = 'SimplePwd01';

-- Counter sign proc_select_t, to make this work
ADD COUNTER SIGNATURE TO procSelectT1 BY CERTIFICATE csSelectT 
WITH PASSWORD = 'SimplePwd01';

-- Now the proc works. 
-- Note that calling procSelectT1 directly still doesn't work
EXECUTE AS LOGIN = 'Alice';
    EXEC procSelectT1ForAlice;
    EXEC procSelectT1;
REVERT;

-- Cleanup
USE master;
GO
DROP DATABASE testDB;
DROP LOGIN Alice;