ADD SIGNATURE (Transact-SQL)

Al ejecutar un módulo firmado, las firmas se agregarán temporalmente al token de SQL, pero se pierden si el módulo ejecuta otro módulo o si el módulo termina la ejecución. Una contrafirma es una forma especial de firma. Por si sola, una contrafirma no concede ningún permiso; sin embargo, permite a las firmas realizadas por el mismo certificado o clave asimétrica conservarse mientras dure la llamada realizada al objeto de contrafirma.

Por ejemplo, suponga que la usuaria Alice llama al procedimiento ProcSelectT1ForAlice, que llama a su vez al procedimiento procSelectT1, que realiza una selección en la tabla T1. Alice dispone del permiso EXECUTE en ProcSelectT1ForAlice y procSelectT1, pero no tiene el permiso SELECT en T1 y no hay ninguna cadena de propiedad implicada en esta cadena completa. Alice no puede tener acceso a la tabla T1, ni directamente ni a través del uso de ProcSelectT1ForAlice y procSelectT1. Puesto que deseamos que Alice utilice siempre ProcSelectT1ForAlice para el acceso, no es aconsejable concederle permiso para ejecutar procSelectT1. ¿Cómo podemos lograr esto?

• Si firmamos procSelectT1, de modo que procSelectT1 pueda tener acceso a T1, entonces Alice puede llamar a procSelectT1 directamente y no tiene que llamar a ProcSelectT1ForAlice.

• Podríamos denegar el permiso EXCEUTE en procSelectT1 a Alice, pero entonces Alice no podría llamar a procSelectT1 a través de ProcSelectT1ForAlice.

• Si solo se firma ProcSelectT1ForAlice, no funcionaría, porque la firma se perdería en la llamada a procSelectT1.

Sin embargo, si se realiza la contrafirma de procSelectT1 con el mismo certificado que se usó para firmar ProcSelectT1ForAlice, SQL Server mantendrá la firma a través de la cadena de llamadas y permitirá el acceso a T1. Si Alice intenta llamar directamente a procSelectT1, no puede tener acceso a T1, porque la contrafirma no concede ningún derecho. En el ejemplo C siguiente se muestra el código de Transact-SQL para este ejemplo.

En el siguiente ejemplo se firma el procedimiento almacenado HumanResources.uspUpdateEmployeeLogin con el certificado HumanResourcesDP.

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

En el ejemplo siguiente se crea una nueva base de datos y un certificado que se va a usar en el ejemplo. En el ejemplo se crea y se firma un procedimiento almacenado y se recupera el BLOB de la firma de sys.crypt_properties. A continuación, el procedimiento se quita y se crea de nuevo. En el ejemplo, el procedimiento se firma usando la sintaxis 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 de crypt_property que esta instrucción devuelve será diferente cada vez que se cree un procedimiento. Anote el resultado para usarlo más tarde en este ejemplo. En este ejemplo, el resultado mostrado es: 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

En el ejemplo siguiente se muestra el modo en que una contrafirma puede ayudar a controlar el acceso a un objeto.

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