ADD SIGNATURE (Transact-SQL)

Lors de l'exécution d'un module signé, les signatures sont ajoutées temporairement au jeton SQL, mais elles sont perdues si le module exécute un autre module ou si l'exécution du module se termine. Une contre-signature est une forme spéciale de signature. Une contre-signature seule n'accorde pas d'autorisations, mais elle permet de conserver les signatures effectuées par le même certificat ou la même clé asymétrique pendant la durée de l'appel passé à l'objet contresigné.

Par exemple, supposons que l'utilisateur Alice appelle la procédure ProcSelectT1ForAlice, qui appelle la procédure procSelectT1, qui effectue une sélection dans la table T1. Alice a l'autorisation EXECUTE sur ProcSelectT1ForAlice et procSelectT1, mais elle n'a pas d'autorisation SELECT sur T1, et aucun chaînage des propriétés n'est impliqué dans cette chaîne entière. Alice ne peut pas accéder à la table T1, que ce soit directement ou via l'utilisation de ProcSelectT1ForAlice et procSelectT1. Dans la mesure où nous voulons qu'Alice utilise toujours ProcSelectT1ForAlice pour l'accès, nous ne souhaitons pas lui accorder l'autorisation d'exécuter procSelectT1. Comment pouvons-nous effectuer cette opération ?

• Si nous signons procSelectT1, afin que procSelectT1 puisse accéder à T1, Alice peut appeler procSelectT1 directement et elle n'a pas à appeler ProcSelectT1ForAlice.

• Nous pouvons refuser l'autorisation EXECUTE sur procSelectT1 à Alice, mais elle ne serait alors pas en mesure d'appeler procSelectT1 via ProcSelectT1ForAlice

• La signature de ProcSelectT1ForAlice ne fonctionne pas seule, parce qu'elle est perdue dans l'appel à procSelectT1.

Toutefois, en contresignant procSelectT1 avec le même certificat que celui utilisé pour signer ProcSelectT1ForAlice, SQL Server conserve la signature sur la chaîne d'appel et autorise l'accès à T1. Si Alice essaie d'appeler procSelectT1 directement, elle ne peut pas accéder à T1, parce que la contre-signature n'accorde pas de droits. L'exemple C ci-dessous affiche le code Transact-SQL pour cet exemple.

L'exemple suivant signe la procédure stockée HumanResources.uspUpdateEmployeeLogin avec le certificat HumanResourcesDP.

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

Dans l'exemple suivant, on crée une base de données et un certificat à utiliser. Cet exemple décrit la création et la signature d'une simple procédure stockée pour récupérer le blob de signature depuis sys.crypt_properties. La procédure est ensuite abandonnée, puis recréée. L'exemple suivant décrit la signature d'une procédure avec la syntaxe 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 signature crypt_property que vous obtenez via cette instruction est différente chaque fois que vous créez une procédure. Notez le résultat pour le reprendre ultérieurement dans cet exemple. Dans cet exemple, le résultat est le suivant : 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

L'exemple suivant montre comment une contre-signature peut aider à contrôler l'accès à un objet.

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