ADD SIGNATURE (Transact-SQL)

In caso di esecuzione di un modulo con segno, le firme verranno aggiunte temporaneamente al token SQL, tuttavia verranno perse se tramite il modulo viene eseguito un altro modulo o viene terminata l'esecuzione. Una controfirma è uno speciale tipo di firma che da sola non concede autorizzazioni, tuttavia consente il mantenimento delle firme create dallo stesso certificato o dalla stessa chiave asimmetrica per la durata della chiamata eseguita all'oggetto controfirmato.

Si supponga, ad esempio, che l'utente Alice chiami la routine ProcSelectT1ForAlice tramite cui viene chiamata la routine procSelectT1 che consente di eseguire la selezione dalla tabella T1. Alice dispone dell'autorizzazione EXECUTE su ProcSelectT1ForAlice e procSelectT1, ma non dell'autorizzazione SELECT su T1 e nell'intera catena non sono coinvolti concatenamenti proprietà. Alice non può accedere alla tabella T1, né direttamente né tramite l'utilizzo di ProcSelectT1ForAlice e procSelectT1. Poiché si desidera che Alice utilizzi sempre ProcSelectT1ForAlice per l'accesso, non si concederà l'autorizzazione per eseguire procSelectT1. Di seguito viene indicato come ottenere questo risultato

• Se si firma procSelectT1, in modo che procSelectT1 possa accedere a T1, Alice può richiamare direttamente procSelectT1 senza dover chiamare ProcSelectT1ForAlice.

• Si potrebbe negare ad Alice l'autorizzazione EXECUTE su procSelectT1, ma in questo modo non sarebbe in grado di chiamare procSelectT1 tramite ProcSelectT1ForAlice.

• Firmare ProcSelectT1ForAlice non funzionerebbe, in quanto la firma andrebbe persa nella chiamata a procSelectT1.

Se invece si controfirma procSelectT1 con lo stesso certificato utilizzato per firmare ProcSelectT1ForAlice, in SQL Server potranno essere mantenute la firma attraverso la catena di chiamate e verrà consentito l'accesso a T1. Se Alice prova a chiamare direttamente procSelectT1, non potrà accedere a T1, poiché la controfirma non concede diritti. Nell'esempio C di seguito viene mostrato l'utilizzo di Transact-SQL per l'esempio proposto.

Nell'esempio seguente la stored procedure HumanResources.uspUpdateEmployeeLogin viene firmata tramite il certificato HumanResourcesDP.

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

Nell'esempio seguente viene creato un nuovo database e un certificato da utilizzare nell'esempio. Nell'esempio viene creata e firmata una stored procedure semplice e viene recuperato l'oggetto BLOB della firma da sys.crypt_properties. La stored procedure viene quindi eliminata e ricreata. Nell'esempio la stored procedure viene firmata tramite la sintassi 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

La firma crypt_property restituita da questa istruzione sarà diversa ogni volta che si crea una stored procedure. Annotare il risultato per utilizzarlo successivamente in questo esempio. Per questo esempio, il risultato dimostrato è: 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

Nell'esempio seguente viene mostrato come la controfirma può aiutare a controllare l'accesso a un oggetto.

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