ADICIONAR ASSINATURA (Transact-SQL)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

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

Transact-SQL convenções de sintaxe

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 assinado.

CERTIFICADO cert_name

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

COM SENHA = 'senha'

A palavra-passe necessária para desencriptar a chave privada do certificado ou a chave assimétrica. Esta cláusula só é necessária se a chave privada não estiver protegida pela chave mestra do banco de dados.

ASSINATURA = signed_blob

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

CHAVE ASSIMÉTRICA Asym_Key_Name

O nome de uma chave assimétrica com a qual assinar ou contraassinar o procedimento armazenado, função, assembly ou gatilho.

Comentários

O módulo que está sendo assinado ou assinado e o certificado ou chave assimétrica usada para assiná-lo já devem existir. Cada caractere no módulo é incluído no cálculo da assinatura. Isso inclui retornos de carro principais e alimentações de linha.

Um módulo pode ser assinado e assinado 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 ID de segurança (SID) da entidade de segurança também será incluída como parte do processo de assinatura.

Atenção

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

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

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

Advertência

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

Contraassinaturas

Quando você executa um módulo assinado, as assinaturas são adicionadas temporariamente ao token SQL, mas as assinaturas são perdidas se o módulo executar outro módulo ou se o módulo encerrar a execução. Um de contra-assinatura é uma forma especial de assinatura. Por si só, uma contraassinatura 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 para o objeto assinado.

Por exemplo, suponha que o usuário Alice chama o procedimento ProcForAlice, que chama o procedimento ProcSelectT1, que seleciona da tabela T1. Alice EXECUTE tem permissão 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 a tabela T1, diretamente ou usando ProcForAlice e ProcSelectT1. Como queremos que Alice sempre use ProcForAlice para acesso, não queremos conceder permissão a ela para executáProcSelectT1. Como podemos concretizar este cenário?

• Se assinarmos ProcSelectT1, de modo que ProcSelectT1 possa acessar T1, então Alice pode invocáProcSelectT1 diretamente e ela não precisa ligar para ProcForAlice.

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

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

No entanto, ao contraassinar ProcSelectT1 com o mesmo certificado usado para assinar ProcForAlice, a assinatura é mantida em toda a cadeia de chamada e tem acesso a T1. Se Alice tentar ligar para ProcSelectT1 diretamente, ela não poderá acessáT1porque a contraassinatura não concede nenhum direito. Exemplo C mostra as Transact-SQL para este exemplo.

Permissões

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

Exemplos

Um. 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 usar no exemplo. O exemplo cria e assina um procedimento armazenado básico 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 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 posterior neste exemplo. Neste 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. Aceder a um procedimento utilizando uma contraassinatura

O exemplo a seguir mostra como a contraassinatura 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)