ADD SIGNATURE (Transact-SQL)

Articolo
03/11/2025

Si applica a:SQL Server Database SQL di Azure Istanza gestita di SQL di Azure

Aggiunge una firma digitale a una stored procedure, una funzione, un assembly o un trigger DML. Aggiunge anche una controfirma a una stored procedure, una funzione, un assembly o un trigger DML.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

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

Argomenti

module_class

Classe del modulo a cui viene aggiunta la firma. Il valore predefinito per i moduli con ambito schema è OBJECT.

module_name

Nome di una stored procedure, di una funzione, di un assembly o di un trigger da firmare o controfirmare.

CERTIFICATE cert_name

Nome di un certificato con cui firmare o controfirmare la stored procedure, la funzione, l'assembly o il trigger.

WITH PASSWORD = 'password'

Password necessaria per decrittografare la chiave privata del certificato o della chiave asimmetrica. Questa clausola è necessaria solo se la chiave privata non è protetta tramite la chiave master del database.

SIGNATURE = signed_blob

Specifica l'oggetto BLOB (Binary Large Object) firmato del modulo. Questa clausola risulta utile se si desidera fornire un modulo senza fornire la chiave privata. Se si utilizza questa clausola, sono necessari solo il modulo, la firma e la chiave pubblica per aggiungere l'oggetto BLOB firmato a un database. signed_blob è l'oggetto BLOB in formato esadecimale.

ASYMMETRIC KEY Asym_Key_Name

Nome di una chiave asimmetrica con cui firmare o controfirmare la stored procedure, la funzione, l'assembly o il trigger.

Osservazioni:

Il modulo che viene firmato o controfirmato e il certificato o la chiave asimmetrica utilizzati per la firma devono essere già esistenti. Ogni carattere del modulo è utilizzato nel calcolo della firma, inclusi gli avanzamenti di riga e i ritorni a capo iniziali.

Un modulo può essere firmato e controfirmato da un numero qualsiasi di certificati e chiavi simmetriche.

Se un modulo viene modificato, la firma viene eliminata.

Se un modulo contiene una clausola EXECUTE AS, anche l'ID di sicurezza (SID) dell'entità viene incluso come parte del processo di firma.

Attenzione

È consigliabile utilizzare la firma del modulo solo per concedere le autorizzazioni, mai per negarle o revocarle.

I trigger DDL (Data Definition Language) e le funzioni con valori di tabella inline non possono essere firmati.

Le informazioni sulle firme sono visibili nella vista del catalogo sys.crypt_properties.

Avviso

Quando si ricrea una routine per la firma, tutte le istruzioni nel batch originale devono corrispondere al batch ricreato. Se una parte del batch è diversa, anche in spazi o commenti, la firma risultante è diversa.

Controfirme

Quando si esegue un modulo firmato, le firme vengono aggiunte temporaneamente al token SQL, ma le firme andranno perse se il modulo esegue un altro modulo o se il modulo termina l'esecuzione. Un controfirma è una forma speciale di firma. Da solo, una controfirma non concede alcuna autorizzazione. Tuttavia, consente di conservare le firme effettuate dallo stesso certificato o dalla stessa chiave asimmetrica per la durata della chiamata effettuata all'oggetto controfirmato.

Si supponga, ad esempio, che l'utente Alice chiami la routine ProcForAlice, che chiama la routine ProcSelectT1, che seleziona dalla tabella T1. Alice dispone dell'autorizzazione EXECUTE per ProcForAlice, ma non dispone delle autorizzazioni di EXECUTE per ProcSelectT1 o SELECT per T1e l'intera catena non è coinvolta nel concatenamento della proprietà. Alice non può accedere alla tabella T1direttamente oppure usando ProcForAlice e ProcSelectT1. Poiché si vuole che Alice usi sempre ProcForAlice per l'accesso, non si vuole concedere l'autorizzazione per eseguire ProcSelectT1. Come è possibile eseguire questo scenario?

Se si firma ProcSelectT1, in modo che ProcSelectT1 possa accedere a T1, Alice può richiamare direttamente ProcSelectT1 e non deve chiamare ProcForAlice.
È possibile negare l'autorizzazione EXECUTE per ProcSelectT1 ad Alice, ma alice non può chiamare ProcSelectT1 tramite ProcForAlice.
La firma ProcForAlice non funzionerebbe da sola, perché la firma viene persa nella chiamata a ProcSelectT1.

Tuttavia, con la controfirma ProcSelectT1 con lo stesso certificato usato per firmare ProcForAlice, la firma viene mantenuta attraverso la catena di chiamate ed è consentito l'accesso a T1. Se Alice tenta di chiamare direttamente ProcSelectT1, non può accedere T1perché la controfirma non concede alcun diritto. esempio C mostra il Transact-SQL per questo esempio.

Screenshot dell'esempio di firma.

Autorizzazioni

È richiesta ALTER autorizzazione per l'oggetto e CONTROL l'autorizzazione per il certificato o la chiave asimmetrica. Se una chiave privata associata è protetta tramite una password, è necessario che anche l'utente disponga della password.

Esempi

R. Firmare una stored procedure usando un certificato

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

USE AdventureWorks2022;

ADD SIGNATURE TO HumanResources.uspUpdateEmployeeLogin
    BY CERTIFICATE HumanResourcesDP;
GO

B. Firmare una stored procedure usando un BLOB firmato

Nell'esempio seguente viene creato un nuovo database e un certificato da utilizzare nell'esempio. L'esempio crea e firma una stored procedure di base e recupera il BLOB della firma da sys.crypt_properties. La firma viene quindi eliminata e aggiunta nuovamente. L'esempio firma la routine utilizzando 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 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 restituita da questa istruzione è diversa ogni volta che si crea una routine. Annotare il risultato per utilizzarlo successivamente in questo esempio. In questo caso specifico, il risultato dimostrato è 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. Accedere a una procedura usando una controfirma

Nell'esempio seguente viene mostrato come la controfirma può aiutare a controllare l'accesso a un oggetto. È necessario sostituire <password> con una password appropriata.

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

Condividi tramite

ADD SIGNATURE (Transact-SQL)

Sintassi

Argomenti

module_class

module_name

CERTIFICATE cert_name

WITH PASSWORD = 'password'

SIGNATURE = signed_blob

ASYMMETRIC KEY Asym_Key_Name

Osservazioni:

Controfirme

Autorizzazioni

Esempi

R. Firmare una stored procedure usando un certificato

B. Firmare una stored procedure usando un BLOB firmato

C. Accedere a una procedura usando una controfirma

Commenti e suggerimenti

Risorse aggiuntive

Condividi tramite

ADD SIGNATURE (Transact-SQL)

Sintassi

Argomenti

module_class

module_name

CERTIFICATE cert_name

WITH PASSWORD = 'password'

SIGNATURE = signed_blob

ASYMMETRIC KEY Asym_Key_Name

Osservazioni:

Controfirme

Autorizzazioni

Esempi

R. Firmare una stored procedure usando un certificato

B. Firmare una stored procedure usando un BLOB firmato

C. Accedere a una procedura usando una controfirma

Contenuto correlato

Commenti e suggerimenti

Risorse aggiuntive