ADD SIGNATURE (Transact-SQL)
Applies to: SQL Server (all supported versions) Azure SQL Database
Adds a digital signature to a stored procedure, function, assembly, or DML-trigger. Also adds a countersignature to a stored procedure, function, assembly, or DML-trigger.
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
To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
Is the class of the module to which the signature is added. The default for schema-scoped modules is OBJECT.
Is the name of a stored procedure, function, assembly, or trigger to be signed or countersigned.
Is the name of a certificate with which to sign or countersign the stored procedure, function, assembly, or trigger.
WITH PASSWORD ='password'
Is the password that is required to decrypt the private key of the certificate or asymmetric key. This clause is only required if the private key isn't protected by the database master key.
Specifies the signed, binary large object (BLOB) of the module. This clause is useful if you want to ship a module without shipping the private key. When you use this clause, only the module, signature, and public key are required to add the signed binary large object to a database. signed_blob is the blob itself in hexadecimal format.
ASYMMETRIC KEY Asym_Key_Name
Is the name of an asymmetric key with which to sign or counter-sign the stored procedure, function, assembly, or trigger.
The module being signed or countersigned and the certificate or asymmetric key used to sign it must already exist. Every character in the module is included in the signature calculation. This includes leading carriage returns and line feeds.
A module can be signed and countersigned by any number of certificates and asymmetric keys.
The signature of a module is dropped when the module is changed.
If a module contains an EXECUTE AS clause, the security ID (SID) of the principal is also included as a part of the signing process.
Module signing should only be used to grant permissions, never to deny or revoke permissions.
Data definition language (DDL) triggers and Inline table-valued functions cannot be signed.
Information about signatures is visible in the sys.crypt_properties catalog view.
When recreating a procedure for signature, all the statements in the original batch must match recreation batch. If any portion of the batch differs, even in spaces or comments, the resultant signature will be different.
When executing a signed module, the signatures will be temporarily added to the SQL token, but the signatures are lost if the module executes another module or if the module terminates execution. A countersignature is a special form of signature. By itself, a countersignature doesn't grant any permissions, however, it allows signatures made by the same certificate or asymmetric key to be kept for the duration of the call made to the countersigned object.
For example, presume that user Alice calls procedure ProcForAlice, which calls procedure ProcSelectT1, which selects from table T1. Alice has EXECUTE permission on ProcForAlice and ProcSelectT1, but she doesn't have SELECT permission on T1, and no ownership chaining is involved in this entire chain. Alice cannot access table T1, either directly, or through the use of ProcForAlice and ProcSelectT1. Since we want Alice to always use ProcForAlice for access, we don't want to grant her permission to execute ProcSelectT1. How can we accomplish this?
If we sign ProcSelectT1, such that ProcSelectT1 can access T1, then Alice can invoke ProcSelectT1 directly and she doesn't have to call ProcForAlice.
We could deny EXECUTE permission on ProcSelectT1 to Alice, but then Alice would not be able to call ProcSelectT1 through ProcForAlice.
Signing ProcForAlice would not work by itself, because the signature would be lost in the call to ProcSelectT1.
However, by countersigning ProcSelectT1 with the same certificate used to sign ProcForAlice, SQL Server the signature will be kept across the call chain and will allow access to T1. If Alice attempts to call ProcSelectT1 directly, she cannot access T1, because the countersignature doesn't grant any rights. Example C below, shows the Transact-SQL for this example.
Requires ALTER permission on the object and CONTROL permission on the certificate or asymmetric key. If an associated private key is protected by a password, the user also must have the password.
A. Signing a stored procedure by using a certificate
The following example signs the stored procedure
HumanResources.uspUpdateEmployeeLogin with the certificate
USE AdventureWorks2012; ADD SIGNATURE TO HumanResources.uspUpdateEmployeeLogin BY CERTIFICATE HumanResourcesDP; GO
B. Signing a stored procedure by using a signed BLOB
The following example creates a new database and creates a certificate to use in the example. The example creates and signs a simple stored procedure and retrieves the signature BLOB from
sys.crypt_properties. The signature is then dropped and added again. The example signs the procedure by using the WITH SIGNATURE syntax.
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
crypt_property signature that is returned by this statement will be different each time you create a procedure. Make a note of the result for use later in this example. For this example, the result demonstrated is:
-- 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. Accessing a procedure using a countersignature
The following example shows how countersigning can help control access to an object.
-- 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;