ADD SIGNATURE (Transact-SQL)
S’applique à : 
SQL Server Azure SQL Database Azure 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. Valeur par défaut pour les modules dont l'étendue concerne le schéma 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 contre-signer 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 contresigner 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 aussi 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.
Ni les déclencheurs de langage de définition de données (DDL) ni les fonctions table incluses ne peuvent être signés.
Des informations sur les signatures sont visibles dans l'affichage catalogue sys.crypt_properties.
Avertissement
Lorsqu'on recrée une procédure pour la signature, toutes les instructions du traitement d'origine doivent correspondre au traitement de recréation. Si une partie du traitement est différée, même au niveau des espaces ou des commentaires, la signature obtenue sera différente.
Contre-signatures
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’utilisatrice Alice appelle la procédure ProcForAlice, qui appelle à son tour la procédure ProcSelectT1, laquelle effectue une sélection dans la table T1. Alice a l’autorisation EXECUTE sur ProcForAlice et ProcSelectT1, mais elle n’a pas l’autorisation SELECT sur T1, et aucun chaînage de 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 ProcForAlice et ProcSelectT1. Dans la mesure où nous voulons qu’Alice utilise toujours ProcForAlice 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 ProcForAlice.
Nous pourrions refuser l’autorisation EXECUTE sur ProcSelectT1 à Alice, mais Alice ne serait alors pas en mesure d’appeler ProcSelectT1 via ProcForAlice.
La signature de ProcForAlice n’est pas suffisante à elle seule, car elle est perdue dans l’appel de ProcSelectT1.
Toutefois, en contresignant ProcSelectT1 avec le même certificat que celui utilisé pour signer ProcForAlice, SQL Server conserve la signature sur la chaîne d’appels et autorise l’accès à T1. Si Alice essaie d’appeler ProcSelectT1 directement, elle ne peut pas accéder à T1 parce que la contresignature n’accorde pas de droits. L’exemple C ci-dessous affiche le code Transact-SQL pour cet exemple.
Autorisations
Nécessite l'autorisation ALTER sur l'objet et l'autorisation CONTROL 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. Signature d'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. Signature d'une procédure stockée à l'aide d'un 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 simple et récupère le BLOB de signature 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 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 retournée par cette instruction différera à chaque création de procédure. Notez le résultat afin de le réutiliser plus tard dans cet exemple. Pour cet exemple, le résultat illustré est le suivant : 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ès à 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.
-- 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;
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
EXEC ProcSelectT1;
END;
GO;
GRANT EXECUTE ON ProcForAlice TO PUBLIC;
-- Verify procedure works for a sysadmin user
EXEC ProcForAlice;
-- Alice still can't use the procedure yet
EXECUTE AS LOGIN = 'Alice';
EXEC ProcForAlice;
REVERT;
-- Sign procedure to grant it SELECT permission
ADD SIGNATURE TO ProcForAlice BY CERTIFICATE csSelectT
WITH PASSWORD = 'SimplePwd01';
-- Counter sign ProcSelectT1, 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 ProcForAlice;
EXEC ProcSelectT1;
REVERT;
-- Cleanup
USE master;
GO
DROP DATABASE testDB;
DROP LOGIN Alice;