ADD SIGNATURE (Transact-SQL)

Статья
10/16/2013

Добавляет цифровую подпись для хранимой процедуры, функции, сборки или триггера. Добавляет также скрепляющую подпись для хранимой процедуры, функции, сборки или триггера.

Значок ссылки на раздел Синтаксические обозначения в Transact-SQL

Синтаксис

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

Аргументы

module_class
Класс модуля, к которому добавляется подпись. По умолчанию для модулей области схемы это класс OBJECT.
module_name
Имя хранимой процедуры, функции, сборки или триггера, для которых будет добавлена подпись или скрепляющая подпись.
CERTIFICATE cert_name
Имя сертификата, который будет использован для добавления подписи или скрепляющей подписи для хранимой процедуры, функции, сборки или триггера.
WITH PASSWORD ='password'
Пароль, необходимый для расшифровки закрытого ключа сертификата или асимметричного ключа. Это предложение необходимо, только когда закрытый ключ не защищен главным ключом базы данных.
SIGNATURE =signed_blob
Указывает подписанный большой двоичный объект (BLOB) модуля. Это предложение полезно, если нужно передать модуль без передачи закрытого ключа. При использовании этого предложения необходимы только модуль, подпись и открытый ключ, чтобы добавить подписанный большой двоичный объект в базу данных. Аргумент signed_blob является самим большим двоичным объектом в шестнадцатеричном формате.
ASYMMETRIC KEY Asym_Key_Name
Имя ассиметричного ключа, который будет использован для добавления подписи или скрепляющей подписи для хранимой процедуры, функции, сборки или триггера.

Замечания

Подписываемый модуль или модуль, на который ставится скрепляющая подпись, а также сертификат или асимметричный ключ, используемые для подписания, должны существовать. Каждый символ в модуле включен в вычисление подписи. Сюда включены начальные символы возврата каретки и перевода строки.

Подписи и скрепляющие подписи для модуля могут добавляться любым числом сертификатов и асимметричных ключей.

Подпись модуля удаляется при изменении модуля.

Если модуль содержит предложение EXECUTE AS, это значит, что идентификатор безопасности (SID) участника также является частью процесса подписи.

Внимание!
Подписание модуля должно использоваться только для предоставления разрешений, но не для запрета или отмены разрешений.

Встроенные функции с табличным значением нельзя подписывать.

Сведения о сигнатурах являются видимыми в представлении каталога sys.crypt_properties.

Внимание!
При воссоздании процедуры для подписи все инструкции в исходном пакете должны соответствовать повторно созданному пакету. Если какая-либо часть пакета будет отличаться, даже количеством пробелов или комментариями, будет получена другая подпись.

При воссоздании процедуры для подписи все инструкции в исходном пакете должны соответствовать повторно созданному пакету. Если какая-либо часть пакета будет отличаться, даже количеством пробелов или комментариями, будет получена другая подпись.

Скрепляющая подпись

Во время выполнения модуля с обычной подписью эта подпись временно добавляется в токен SQL, но, если этот модуль выполняет другой модуль или если выполнение модуля прерывается, подпись теряется. Скрепляющая подпись — это особая форма подписи. Сама по себе скрепляющая подпись не предоставляет никаких разрешений, но позволяет сохранить подписи, поставленные тем же самым сертификатом или асимметричным ключом, на все время вызова объекта, на котором стоит скрепляющая подпись.

Предположим, Алиса вызывает процедуру ProcSelectT1ForAlice, из которой вызывается процедура procSelectT1, выбирающая данные из таблицы T1. Алиса имеет разрешение EXECUTE для процедур ProcSelectT1ForAlice и procSelectT1, но нет разрешения SELECT для таблицы T1, а во всей этой цепочке нет никаких цепочек владения. Алиса не имеет доступа к таблице T1 — ни прямого, ни с помощью процедур ProcSelectT1ForAlice и procSelectT1. Так как мы хотим, чтобы Алиса всегда использовала для обращения к таблице процедуру ProcSelectT1ForAlice, мы не будем предоставлять ей разрешение на выполнение процедуры procSelectT1. Что делать в этом случае?

Если мы подпишем procSelectT1, то procSelectT1 сможет производить доступ к T1, тогда Алиса сможет вызывать procSelectT1 напрямую и ей не потребуется вызывать ProcSelectT1ForAlice.
Можно не давать Алисе разрешения EXECUTE на процедуру procSelectT1, но в этом случае Алиса не сможет вызвать процедуру procSelectT1 даже через процедуру ProcSelectT1ForAlice.
Подписание процедуры ProcSelectT1ForAlice также не будет работать само по себе, так как подпись будет потеряна при вызове процедуры procSelectT1.

Однако скрепляющая подпись на procSelectT1, сделанная с использованием того же сертификата, которым подписывалась ProcSelectT1ForAlice, позволит SQL Server сохранить подпись по всей цепочке вызова и обеспечит доступ к T1. Если Алиса захочет вызвать процедуру procSelectT1, она не сможет этого сделать (не имея доступа к T1), так как скрепляющая подпись не предоставляет никаких прав. В приведенном ниже примере В показан код Transact-SQL для этого примера.

Разрешения

Требует разрешения ALTER на объект и разрешение CONTROL на сертификат или асимметричный ключ. Если соответствующий закрытый ключ защищен паролем, то у пользователя также должен быть этот пароль.

Примеры

А.Подписание хранимой процедуры с помощью сертификата

В следующем примере хранимая процедура HumanResources.uspUpdateEmployeeLogin подписывается сертификатом HumanResourcesDP.

USE AdventureWorks2012;
ADD SIGNATURE TO HumanResources.uspUpdateEmployeeLogin 
    BY CERTIFICATE HumanResourcesDP;
GO

Б.Подписание хранимой процедуры с помощью подписанного большого двоичного объекта

В следующем примере создается новая база данных и сертификат для использования в примере. В этом примере будет создана и подписана простая хранимая процедура и выполнена выборка большого двоичного объекта сигнатуры из sys.crypt_properties. Подпись удаляется, затем добавляется еще раз. Процедура подписывается с помощью конструкции 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

Подпись crypt_property, возвращаемая этой инструкции, будет изменяться каждый раз при создании процедуры. Запишите результат для использования далее в ходе примера. Для этого примера выводится следующий результат: 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

В.Обращение к процедуре с помощью скрепляющей подписи

В следующем примере показано, как скрепляющая подпись помогает контролировать доступ к объекту.

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

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

-- Create special procedure for accessing T1
CREATE PROCEDURE  procSelectT1ForAlice AS
BEGIN
   IF USER_ID() <> USER_ID('Alice')
    BEGIN
        PRINT 'Only Alice can use this.';
        RETURN
    END
   EXEC procSelectT1;
END;
GO;
GRANT EXECUTE ON procSelectT1ForAlice TO PUBLIC;

-- Verify procedure works for a sysadmin user
EXEC procSelectT1ForAlice;

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

-- Sign procedure to grant it SELECT permission
ADD SIGNATURE TO procSelectT1ForAlice BY CERTIFICATE csSelectT 
WITH PASSWORD = 'SimplePwd01';

-- Counter sign proc_select_t, 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 procSelectT1ForAlice;
    EXEC procSelectT1;
REVERT;

-- Cleanup
USE master;
GO
DROP DATABASE testDB;
DROP LOGIN Alice;

См. также

Справочник

sys.crypt_properties (Transact-SQL)

DROP SIGNATURE (Transact-SQL)

Поделиться через