ADD SIGNATURE (Transact-SQL)

Beim Ausführen eines signierten Moduls werden die Signaturen dem SQL-Token vorübergehend hinzugefügt, sie gehen jedoch verloren, wenn das Modul ein weiteres Modul ausführt oder die Ausführung beendet. Eine Gegensignatur ist eine besondere Form der Signatur. Eine Gegensignatur gewährt selbst keine Berechtigungen, sie lässt jedoch zu, dass Signaturen, die vom selben Zertifikat oder asymmetrischen Schlüssel erstellt wurden, über die Dauer des Aufrufs des gegensignierten Objekts beibehalten werden.

Beispiel: Die Benutzerin Alice ruft die Prozedur ProcSelectT1ForAlice auf, die wiederum die Prozedur procSelectT1 aufruft, von der eine Auswahl in Tabelle T1 getroffen wird. Alice verfügt über die EXECUTE-Berechtigung für ProcSelectT1ForAlice und procSelectT1, aber sie verfügt nicht über die SELECT-Berechtigung für T1. Außerdem weist die gesamte Kette keine Besitzverkettung auf. Alice kann weder direkt noch über ProcSelectT1ForAlice und procSelectT1 auf die Tabelle T1 zugreifen. Alice soll für den Zugriff immer ProcSelectT1ForAlice verwenden und erhält deshalb keine Berechtigung zur Ausführung von proSelectT1. Welche Vorgehensweise empfiehlt sich?

• Wenn procSelectT1 signiert wird, sodass procSelectT1 auf T1 zugreifen kann, ist Alice in der Lage, procSelectT1 direkt aufzurufen, ohne ProcSelectT1ForAlice aufzurufen.

• Die EXECUTE-Berechtigung für procSelectT1 könnte Alice verweigert werden, allerdings wäre sie dann auch nicht in der Lage, procSelectT1 über ProcSelectT1ForAlice aufzurufen.

• Das Signieren von ProcSelectT1ForAlice funktioniert alleine nicht, weil die Signatur im Aufruf von procSelectT1 verloren gehen würde.

Wenn procSelectT1 jedoch anhand desselben Zertifikats, mit dem ProcSelectT1ForAlice signiert wurde, gegensigniert wird, behält SQL Server die Signatur während der gesamten Aufrufkette bei und ermöglicht den Zugriff auf T1. Wenn Alice versucht, procSelectT1 direkt aufzurufen, ist kein Zugriff auf T1 möglich, weil durch die Gegensignatur keine Rechte gewährt werden. In Beispiel C unten wird Transact-SQL für dieses Beispiel veranschaulicht.

Im folgenden Beispiel wird die gespeicherte Prozedur HumanResources.uspUpdateEmployeeLogin mit dem Zertifikat HumanResourcesDP signiert.

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

Im folgenden Beispiel werden eine neue Datenbank und ein Zertifikat erstellt, die im Beispiel verwendet werden sollen. Im Beispiel wird eine einfache gespeicherte Prozedur erstellt und signiert sowie die Signatur-BLOB aus sys.crypt_properties abgerufen. Die Prozedur wird dann gelöscht und neu erstellt. Im Beispiel wird die Prozedur mithilfe der WITH SIGNATURE-Syntax signiert.

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

Die von dieser Anweisung zurückgegebene crypt_property-Signatur ist bei jeder erstellten Prozedur anders. Notieren Sie sich das Ergebnis, um es später in diesem Beispiel zu verwenden. Für dieses Beispiel lautet das Ergebnis wie folgt: 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

Im folgenden Beispiel wird gezeigt, wie durch das Gegensignieren der Zugriff auf ein Objekt gesteuert wird.

-- 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;