Partilhar via


ADD SIGNATURE (Transact-SQL)

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Adiciona uma assinatura digital a um procedimento armazenado, uma função, um assembly ou um gatilho DML. Além disso, adiciona uma referenda a um procedimento armazenado, uma função, um assembly ou um gatilho DML.

Convenções de sintaxe de Transact-SQL

Sintaxe

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

Argumentos

module_class
É a classe do módulo ao qual a assinatura é adicionada. O padrão para módulos de escopo de esquema é OBJECT.

module_name
É o nome de um procedimento armazenado, função, assembly ou gatilho a serem assinados ou referendados.

CERTIFICATE cert_name
É o nome de um certificado com o qual assinar ou referendar o procedimento armazenado, função, assembly ou gatilho.

WITH PASSWORD ='password'
É a senha necessária para descriptografar a chave privada do certificado ou chave assimétrica. Esta cláusula só será necessária se a chave privada não for protegida pela chave mestra de banco de dados.

SIGNATURE =signed_blob
Especifica o BLOB (objeto binário grande) assinado do módulo. Esta cláusula será útil se você quiser enviar um módulo sem enviar a chave privada. Ao usar esta cláusula, somente o módulo, a assinatura e a chave pública são necessários para adicionar o objeto binário grande a um banco de dados. signed_blob é o próprio blob em formato hexadecimal.

ASYMMETRIC KEY Asym_Key_Name
É o nome de uma chave assimétrica com a qual assinar ou referendar o procedimento armazenado, função, assembly ou gatilho.

Comentários

O módulo que é assinado ou referendado e o certificado ou chave assimétrica usados para assiná-lo já devem existir. Todo caractere no módulo é incluído no cálculo de assinatura. Isso inclui retornos de carro à esquerda e alimentação de linha.

Um módulo pode ser assinado e referendado por qualquer número de certificados e chaves assimétricas.

A assinatura de um módulo é descartada quando o módulo é alterado.

Se um módulo tiver uma cláusula EXECUTE AS, a SID (ID de segurança) do principal também será incluída como parte do processo de assinatura.

Cuidado

A assinatura de módulo só deve ser usada para conceder permissões, e nunca para negar ou revogar permissões.

Os gatilhos DDL (linguagem de definição de dados) e as funções com valor de tabela embutidas não podem ser assinados.

As informações sobre assinaturas são visíveis na exibição do catálogo sys.crypt_properties.

Aviso

Ao recriar um procedimento para a assinatura, todas as instruções no lote original devem corresponder ao lote de recriação. Se qualquer parte do lote for diferente, mesmo em espaços ou comentários, a assinatura resultante será diferente.

Referendas

Ao executar um módulo assinado, as assinaturas serão adicionadas temporariamente ao token do SQL, mas as assinaturas serão perdidas se o módulo executar outro módulo ou se o módulo terminar a execução. Uma referenda é uma forma especial de assinatura. Por si só, uma referenda não concede permissões, porém permite que as assinaturas feitas pelo mesmo certificado ou chave assimétrica sejam mantidas enquanto durar a chamada feita ao objeto referendado.

Por exemplo, suponha que a usuária Alice chame o procedimento ProcForAlice, que chama o procedimento ProcSelectT1, que seleciona da tabela T1. Alice tem permissão EXECUTE em ProcForAlice e ProcSelectT1, mas ela não tem permissão SELECT em T1 e nenhum encadeamento de propriedade está envolvido nessa cadeia inteira. Alice não pode acessar a tabela T1 diretamente nem pelo uso de ProcForAlice e ProcSelectT1. Considerando que desejamos que Alice use sempre ProcForAlice para obter acesso, não desejamos conceder permissão a ela para executar ProcSelectT1. Como podemos fazer isso?

  • Se nós assinarmos ProcSelectT1, de modo que ProcSelectT1 acesse T1, Alice poderá invocar ProcSelectT1 diretamente e não terá que chamar ProcForAlice.

  • Poderíamos negar permissão de EXECUTE em ProcSelectT1 para Alice, porém Alice também não poderia chamar ProcSelectT1 por meio de ProcForAlice.

  • Assinar ProcForAlice não funcionaria por si só, porque a assinatura seria perdida na chamada para ProcSelectT1.

Porém, ao referendar ProcSelectT1 com o mesmo certificado usado para assinar ProcForAlice, o SQL Server vai manter a assinatura na cadeia de chamadas e permitirá o acesso a T1. Se Alice tentar chamar ProcSelectT1 diretamente, ela não poderá acessar T1, porque a referenda não concede nenhum direito. O exemplo C abaixo mostra o Transact-SQL para este exemplo.

exemplo de assinatura

Permissões

Requer a permissão ALTER no objeto e a permissão CONTROL no certificado ou chave assimétrica. Se uma chave privada associada estiver protegida por uma senha, o usuário também precisará ter a senha.

Exemplos

a. Assinando um procedimento armazenado com um certificado

O exemplo a seguir assina o procedimento armazenado HumanResources.uspUpdateEmployeeLogin com o certificado HumanResourcesDP.

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

B. Assinando um procedimento armazenado com um BLOB assinado

O exemplo a seguir cria um novo banco de dados e cria um certificado para uso no exemplo. O exemplo cria e assina um procedimento armazenado simples e recupera o BLOB de assinatura de sys.crypt_properties. A assinatura é então descartada e adicionada novamente. O exemplo assina o procedimento usando a sintaxe 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  

A assinatura crypt_property que é retornada por essa instrução será diferente a cada vez que você criar um procedimento. Anote o resultado para uso posteriormente neste exemplo. Neste exemplo, o resultado demonstrado é: 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. Acessando um procedimento usando uma referenda

O exemplo a seguir mostra como a referenda pode ajudar a controlar o acesso a um objeto.

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

Consulte Também