ADICIONAR ASSINATURA (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureBanco de dados SQL no Microsoft Fabric

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 com escopo de esquema é OBJECT.

module_name

O nome de um procedimento armazenado, função, assembly ou gatilho a ser assinado ou contraassinado.

CERT_NAME DE CERTIFICADO

O nome de um certificado com o qual assinar ou contratribuir o procedimento armazenado, a função, o assembly ou o gatilho.

COM SENHA = 'senha'

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.

ASSINATURA = 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.

CHAVE Asym_Key_Name ASSIMÉTRICA

O nome de uma chave assimétrica com a qual assinar ou contraassinar o procedimento armazenado, a função, o assembly ou o 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 contiver uma cláusula EXECUTE AS, a SID (ID de segurança) da entidade de segurança 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.

Gatilhos DDL (linguagem de definição de dados) e funções com valor de tabela embutida não podem ser assinados.

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

Aviso

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

Referendas

Quando você executa um módulo assinado, as assinaturas são temporariamente adicionadas ao token SQL, mas as assinaturas são perdidas se o módulo executa outro módulo ou se o módulo encerra a execução. Uma de contra-atribuição é uma forma especial de assinatura. Por si só, uma contra-atribuição não concede permissões. No entanto, ele permite que assinaturas feitas pelo mesmo certificado ou chave assimétrica sejam mantidas durante a chamada feita ao objeto contraassinado.

Por exemplo, suponha que o usuário Alice o procedimento de chamadas ProcForAlice, que chama o procedimento ProcSelectT1, que seleciona na tabela T1. Alice tem permissão EXECUTE em ProcForAlice, mas não tem permissões EXECUTE em ProcSelectT1 ou permissão SELECT em T1, e nenhum encadeamento de propriedade está envolvido em toda essa cadeia. Alice não pode acessar T1de tabela, diretamente ou usando ProcForAlice e ProcSelectT1. Como queremos que Alice sempre use ProcForAlice para acesso, não queremos conceder a ela permissão para executar ProcSelectT1. Como podemos realizar esse cenário?

• Se assinarmos ProcSelectT1, de modo que ProcSelectT1 possa acessar T1, Alice poderá invocar ProcSelectT1 diretamente e ela não precisará chamar ProcForAlice.

• Poderíamos negar EXECUTE permissão em ProcSelectT1 para Alice, mas então Alice não pode ligar para ProcSelectT1 através de ProcForAlice.

• Assinar ProcForAlice não funcionaria sozinho, porque a assinatura é perdida na chamada para ProcSelectT1.

No entanto, ao contra-atribuir ProcSelectT1 com o mesmo certificado usado para assinar ProcForAlice, a assinatura é mantida em toda a cadeia de chamadas e tem permissão de acesso ao T1. Se Alice tentar ligar ProcSelectT1 diretamente, ela não poderá acessar T1, porque a contra-atribuição não concede direitos. exemplo de C mostra o Transact-SQL deste exemplo.

Permissões

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

Exemplos

a. Assinar um procedimento armazenado usando 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. Assinar um procedimento armazenado usando 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 básico e recupera o BLOB de assinatura do 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 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

A assinatura crypt_property retornada por esta instrução é diferente cada vez que você cria um procedimento. Anote o resultado para uso posteriormente neste exemplo. Nesse caso específico, 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. Acessar um procedimento usando uma contra-atribuição

O exemplo a seguir mostra como a referenda pode ajudar a controlar o acesso a um objeto. Você deve substituir <password> por uma senha apropriada.

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

Conteúdo relacionado

• sys.crypt_properties (Transact-SQL)
• ASSINATURA DE DROP (Transact-SQL)