Partage via

ADD SIGNATURE (Transact-SQL)

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Ajoute une signature numérique à une procédure stockée, à une fonction, à un assembly ou à un déclencheur DML. Ajoute également une contre-signature à une procédure stockée, à une fonction, à un assembly ou à un déclencheur DML.

Conventions de la syntaxe Transact-SQL

Syntaxe

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

Arguments

module_class

Classe du module auquel la signature est ajoutée. La valeur par défaut pour les modules délimités par le schéma est OBJECT.

module_name

Nom d’une procédure stockée, d’une fonction, d’un assembly ou d’un déclencheur à signer ou à contre-signer.

CERTIFICATE cert_name

Nom d’un certificat avec lequel signer ou contresigner la procédure stockée, la fonction, l’assembly ou le déclencheur.

WITH PASSWORD = 'password'

Mot de passe requis pour déchiffrer la clé privée du certificat ou de la clé asymétrique. Cette clause n'est requise que si la clé privée n'est pas protégée par la clé principale de la base de données.

SIGNATURE = signed_blob

Spécifie le BLOB (Binary Large Object) signé du module. Cette clause est utile si vous souhaitez livrer un module sans fournir la clé privée. Dans ce cas, seuls le module, la signature et la clé publique sont nécessaires pour ajouter l'objet BLOB signé à une base de données. signed_blob est l’objet BLOB proprement dit au format hexadécimal.

ASYMMETRIC KEY Asym_Key_Name

Nom d’une clé asymétrique avec laquelle signer ou contre-signer la procédure stockée, la fonction, l’assembly ou le déclencheur.

Notes

Le module signé ou contresigné et le certificat ou la clé asymétrique utilisés pour la signature doivent déjà exister. Chaque caractère du module est inclus dans le calcul de la signature, y compris les retours chariot et les sauts de ligne.

Un module peut être signé et contresigné par n'importe quel nombre de certificats et de clés asymétriques.

La signature d'un module est supprimée lorsque ce dernier est modifié.

Si un module contient une clause EXECUTE AS, l’ID de sécurité (SID) du principal est également inclus dans le cadre du processus de signature.

Attention

La signature de module ne doit être utilisée que pour accorder des autorisations, jamais pour en refuser ou en révoquer.

Les déclencheurs DDL (Data Definition Language) et les fonctions table inline ne peuvent pas être signées.

Les informations sur les signatures sont visibles dans l’affichage catalogue sys.crypt_properties.

Avertissement

Lorsque vous recréez une procédure de signature, toutes les instructions du lot d’origine doivent correspondre au lot recréé. Si une partie du lot diffère, même dans des espaces ou des commentaires, la signature résultante est différente.

Contre-signatures

Lorsque vous exécutez un module signé, les signatures sont temporairement ajoutées au jeton SQL, mais les signatures sont perdues si le module exécute un autre module ou si le module met fin à l’exécution. Une contre-signature est une forme spéciale de signature. Par lui-même, une contre-signature n’accorde aucune autorisation. Toutefois, il 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 effectué à l’objet contre-signé.

Par exemple, supposons que l’utilisateur Alice appelle la procédure ProcForAlice, qui appelle la procédure ProcSelectT1, qui sélectionne dans la table T1. Alice a EXECUTE autorisation sur ProcForAlice, mais n’a pas d’autorisations EXECUTE sur ProcSelectT1 ou SELECT autorisation sur T1, et aucun chaînage de propriété n’est impliqué dans cette chaîne entière. Alice ne peut pas accéder aux T1de table, directement ou à l’aide de ProcForAlice et de ProcSelectT1. Étant donné que nous voulons qu’Alice utilise toujours ProcForAlice pour l’accès, nous ne voulons pas lui accorder l’autorisation d’exécuter ProcSelectT1. Comment pouvons-nous accomplir ce scénario ?

  • Si nous signons ProcSelectT1, de sorte que ProcSelectT1 puisse accéder à T1, Alice peut appeler ProcSelectT1 directement et elle n’a pas besoin d’appeler ProcForAlice.

  • Nous pourrions refuser EXECUTE autorisation sur ProcSelectT1 à Alice, mais Alice ne peut pas appeler ProcSelectT1 via ProcForAlice.

  • La signature ProcForAlice ne fonctionnerait pas par elle-même, car la signature est perdue dans l’appel à ProcSelectT1.

Toutefois, en contresignant ProcSelectT1 avec le même certificat utilisé pour signer ProcForAlice, la signature est conservée sur la chaîne d’appels et est autorisée à accéder à T1. Si Alice tente d’appeler ProcSelectT1 directement, elle ne peut pas accéder à T1, car la contre-signature n’accorde aucun droit. Exemple de C affiche la Transact-SQL de cet exemple.

Capture d’écran de l’exemple de signature.

Autorisations

Nécessite ALTER autorisation sur l’objet et CONTROL autorisation sur le certificat ou la clé asymétrique. Si une clé privée associée est protégée par un mot de passe, l'utilisateur doit également disposer de ce mot de passe.

Exemples

R. Signer une procédure stockée à l’aide d’un certificat

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

USE AdventureWorks2022;

ADD SIGNATURE TO HumanResources.uspUpdateEmployeeLogin
    BY CERTIFICATE HumanResourcesDP;
GO

B. Signer une procédure stockée à l’aide d’un objet BLOB signé

L'exemple suivant crée une base de données et un certificat à utiliser dans cet exemple. L’exemple crée et signe une procédure stockée de base et récupère le blob de signature à partir de sys.crypt_properties. La signature est ensuite supprimée, puis ajoutée de nouveau. L’exemple signe la procédure à l’aide de 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 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 signature crypt_property retournée par cette instruction est différente chaque fois que vous créez une procédure. Notez le résultat afin de le réutiliser plus tard dans cet exemple. Dans ce cas particulier, le résultat démontré est 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. Accéder à une procédure à l’aide d’une contre-signature

L'exemple suivant montre comment une contre-signature peut aider à contrôler l'accès à un objet. Vous devez remplacer <password> par un mot de passe approprié.

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

Contenu connexe