ADD SIGNATURE (Transact-SQL)

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Agrega una firma digital a un procedimiento almacenado, una función, un ensamblado o un desencadenador de DML. También agrega una contrafirma a un procedimiento almacenado, una función, un ensamblado o un desencadenador de DML.

Convenciones de sintaxis de Transact-SQL

Sintaxis

ADD [ COUNTER ] SIGNATURE TO module_class::module_name
    BY <crypto_list> [ , ...n ]

<crypto_list> ::=
    CERTIFICATE cert_name
    | CERTIFICATE cert_name [ WITH PASSWORD = 'password' ]
    | CERTIFICATE cert_name WITH SIGNATURE = signed_blob
    | ASYMMETRIC KEY Asym_Key_Name
    | ASYMMETRIC KEY Asym_Key_Name [ WITH PASSWORD = 'password' ]
    | ASYMMETRIC KEY Asym_Key_Name WITH SIGNATURE = signed_blob

Argumentos

module_class

Clase del módulo al que se agrega la firma. El valor predeterminado para los módulos con ámbito de esquema es OBJECT.

module_name

Nombre de un procedimiento almacenado, función, ensamblado o desencadenador que se va a firmar o contrasignar.

CERTIFICATE cert_name

Nombre de un certificado con el que firmar o contrasignar el procedimiento almacenado, la función, el ensamblado o el desencadenador.

WITH PASSWORD = 'password'

Contraseña necesaria para descifrar la clave privada del certificado o la clave asimétrica. Esta cláusula solo se requiere si la clave privada no la protege la clave maestra de la base de datos.

SIGNATURE = signed_blob

Especifica el objeto binario grande (BLOB) firmado del módulo. Esta cláusula es útil si desea enviar un módulo sin enviar la clave privada. Si utiliza esta cláusula, solo se necesitan el módulo, la firma y la clave pública para agregar el objeto binario grande firmado a una base de datos. signed_blob es el propio blob en formato hexadecimal.

ASYMMETRIC KEY Asym_Key_Name

Nombre de una clave asimétrica con la que firmar o contrafirmar el procedimiento almacenado, la función, el ensamblado o el desencadenador.

Observaciones

El módulo que se va a firmar o contrafirmar, y el certificado o la clave simétrica utilizada para firmarlo ya deben existir. En el cálculo de la firma se incluyen todos los caracteres del módulo. Esto incluye saltos de línea y retornos de carro iniciales.

Un módulo se puede firmar y contrafirmar por diversos certificados y claves simétricas.

La firma de un módulo se quita cuando se cambia el módulo.

Si un módulo contiene una cláusula EXECUTE AS, el identificador de seguridad (SID) de la entidad de seguridad también se incluye como parte del proceso de firma.

Precaución

La firma de módulos solo se debe utilizar para conceder permisos, nunca para denegarlos ni revocarlos.

Los desencadenadores del lenguaje de definición de datos (DDL) y las funciones con valores de tabla insertados no se pueden firmar.

La información sobre las firmas está visible en la vista de catálogo de sys.crypt_properties.

Advertencia

Al volver a crear un procedimiento para la firma, todas las instrucciones del lote original deben coincidir con el lote vuelto a crear. Si alguna parte del lote difiere, incluso en espacios o comentarios, la firma resultante es diferente.

Contrafirmas

Al ejecutar un módulo firmado, las firmas se agregan temporalmente al token de SQL, pero las firmas se pierden si el módulo ejecuta otro módulo o si el módulo finaliza la ejecución. Una contrafirma es una forma especial de firma. Por sí mismo, una contrafirma no concede ningún permiso. Sin embargo, permite que las firmas realizadas por el mismo certificado o clave asimétrica se conserven durante la llamada realizada al objeto contrasignado.

Por ejemplo, supongamos que el usuario Alice llama al procedimiento ProcForAlice, que llama al procedimiento ProcSelectT1, que selecciona de la tabla T1. Alice tiene EXECUTE permiso para ProcForAlice, pero no tiene permisos de EXECUTE en ProcSelectT1 ni SELECT permiso en T1, y ningún encadenamiento de propiedad está implicado en esta cadena completa. Alice no puede tener acceso a la tabla T1, ya sea directamente o mediante ProcForAlice y ProcSelectT1. Puesto que queremos que Alice siempre use ProcForAlice para el acceso, no queremos concederle permiso para ejecutar ProcSelectT1. ¿Cómo podemos lograr este escenario?

• Si firmamos ProcSelectT1, de modo que ProcSelectT1 pueda acceder a T1, Alice puede invocar ProcSelectT1 directamente y no tiene que llamar a ProcForAlice.

• Podríamos denegar EXECUTE permiso en ProcSelectT1 a Alice, pero entonces Alice no puede llamar a ProcSelectT1 a través de ProcForAlice.

• La firma ProcForAlice no funcionaría por sí misma, porque la firma se pierde en la llamada a ProcSelectT1.

Sin embargo, al contrasignar ProcSelectT1 con el mismo certificado usado para firmar ProcForAlice, la firma se mantiene en la cadena de llamadas y se permite el acceso a T1. Si Alice intenta llamar directamente a ProcSelectT1, no puede acceder a T1, porque la contrafirma no concede ningún derecho. ejemplo de C muestra el Transact-SQL de este ejemplo.

Permisos

Requiere ALTER permiso en el objeto y CONTROL permiso en el certificado o la clave asimétrica. Si una clave privada asociada está protegida por una contraseña, el usuario también debe tener la contraseña.

Ejemplos

A. Firmar un procedimiento almacenado mediante un certificado

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

USE AdventureWorks2022;

ADD SIGNATURE TO HumanResources.uspUpdateEmployeeLogin
    BY CERTIFICATE HumanResourcesDP;
GO

B. Firmar un procedimiento almacenado mediante un BLOB firmado

En el siguiente ejemplo se crea una base de datos nueva y un certificado que se utilizará en el ejemplo. En el ejemplo se crea y se firma un procedimiento almacenado básico y se recupera la firma BLOB de sys.crypt_properties. La firma se quita y se agrega otra vez. En el ejemplo se firma el procedimiento mediante 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 basic procedure.
CREATE PROCEDURE [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
     INNER JOIN sys.certificates AS cer
         ON cp.thumbprint = cer.thumbprint
WHERE cer.name = 'cert_signature_demo';
GO

La firma crypt_property devuelta por esta instrucción es diferente cada vez que se crea un procedimiento. Tome nota del resultado para su uso posteriormente en este ejemplo. En este caso concreto, el resultado demostrado es 0x831F5530C86CC8ED606E5BC2720DA835351E46219A6D5DE9CE546297B88AEF3B6A7051891AF3EE7A68EAB37CD8380988B4C3F7469C8EABDD9579A2A5C507A4482905C2F24024FFB2F9BD7A953DD5E98470C4AA90CE83237739BB5FAE7BAC796E7710BDE291B03C43582F6F2D3B381F2102EEF8407731E01A51E24D808D54B373.

-- Drop the signature so that it can be signed again.
DROP SIGNATURE FROM [sp_signature_demo]
    BY CERTIFICATE [cert_signature_demo];
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

C. Acceso a un procedimiento mediante una contrafirma

El siguiente ejemplo muestra el modo en que la contrafirma puede ayudar a controlar el acceso a un objeto. Debe reemplazar <password> por una contraseña adecuada.

-- 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 = '<password>'
    WITH SUBJECT = 'Certificate used to grant SELECT on T1';

CREATE USER ucsSelectT1 FOR CERTIFICATE csSelectT;
GRANT SELECT ON T1 TO ucsSelectT1;

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

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

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

-- Create special procedure for accessing T1
CREATE PROCEDURE ProcForAlice
AS
BEGIN
    IF USER_ID() <> USER_ID('Alice')
        BEGIN
            PRINT 'Only Alice can use this.';
            RETURN;
        END
    EXECUTE ProcSelectT1;
END
GO

GRANT EXECUTE ON ProcForAlice TO PUBLIC;

-- Verify procedure works for a sysadmin user
EXECUTE ProcForAlice;

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

-- Sign procedure to grant it SELECT permission
ADD SIGNATURE TO ProcForAlice
BY CERTIFICATE csSelectT WITH PASSWORD = '<password>';

ADD COUNTER SIGNATURE TO ProcSelectT1
BY CERTIFICATE csSelectT WITH PASSWORD = '<password>';

-- Now the stored procedure works.   
-- Note that calling ProcSelectT1 directly still doesn't work.
EXECUTE AS LOGIN = 'Alice';
EXECUTE ProcForAlice;
EXECUTE ProcSelectT1;
REVERT;

-- Cleanup
USE master;
GO

DROP DATABASE testDB;
DROP LOGIN Alice;

Contenido relacionado

• sys.crypt_properties (Transact-SQL)
• DROP SIGNATURE (Transact-SQL)