Partilhar via

Tutorial: Assinar Procedimentos Armazenados com um Certificado

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do Azure

Este tutorial ilustra a assinatura de procedimentos armazenados usando um certificado gerado pelo SQL Server.

Observação

Para executar o código deste tutorial, deve ter tanto a segurança em Modo Misto configurada como a AdventureWorks2025 base de dados instalada.

Assinar procedimentos armazenados usando um certificado é útil quando se quer exigir permissões sobre o procedimento armazenado, mas não se pretende conceder explicitamente esses direitos a um utilizador. Embora possa realizar esta tarefa de outras formas, como usando a instrução EXECUTE AS, usar um certificado permite-lhe usar um rastreio para encontrar o chamador original do procedimento armazenado. Isto proporciona um elevado nível de auditoria, especialmente durante operações de segurança ou de Linguagem de Definição de Dados (DDL).

Pode criar um certificado na base de dados mestre para permitir permissões ao nível do servidor, ou pode criar um certificado em qualquer base de dados de utilizador para permitir permissões ao nível da base de dados. Neste cenário, um utilizador sem direitos sobre tabelas base deve aceder a um procedimento armazenado na AdventureWorks2025 base de dados, e deve auditar o trilho de acesso ao objeto. Em vez de usar outros métodos de cadeia de propriedade, irá criar uma conta de utilizador de servidor e base de dados sem direitos sobre os objetos base, e uma conta de utilizador de base de dados com direitos sobre uma tabela e um procedimento armazenado. Tanto o procedimento armazenado como a segunda conta de utilizador da base de dados serão protegidos com um certificado. A segunda conta de base de dados terá acesso a todos os objetos e concederá acesso ao procedimento armazenado à primeira conta de utilizador da base de dados.

Neste cenário, irá primeiro criar um certificado de base de dados, um procedimento armazenado e um utilizador, e depois testará o processo seguindo estes passos:

Cada bloco de código neste exemplo é explicado em linha. Para copiar o exemplo completo, veja Exemplo Completo no final deste tutorial.

Pré-requisitos

Para concluir este tutorial, você precisa do SQL Server Management Studio, acesso a um servidor que esteja executando o SQL Server e um banco de dados AdventureWorks.

Para instruções sobre como restaurar uma base de dados no SQL Server Management Studio, consulte Restaurar uma base de dados.

1. Configurar o Ambiente

Para definir o contexto inicial do exemplo, no SQL Server Management Studio abre uma nova Consulta e executa o código seguinte para abrir a AdventureWorks2025 base de dados. Este código altera o contexto da base de dados para AdventureWorks2025 e cria um novo login do servidor e uma conta de utilizador da base de dados (TestCreditRatingUser), usando uma palavra-passe.

USE AdventureWorks2022;  
GO  
-- Set up a login for the test user  
CREATE LOGIN TestCreditRatingUser  
   WITH PASSWORD = 'ASDECd2439587y'  
GO  
CREATE USER TestCreditRatingUser  
FOR LOGIN TestCreditRatingUser;  
GO

Para mais informações sobre a instrução CRIAR UTILIZADOR, consulte CRIAR UTILIZADOR (Transact-SQL). Para mais informações sobre a instrução CRIAR LOGIN, consulte CRIAR LOGIN (Transact-SQL).

2. Criar um certificado

Pode criar certificados no servidor usando a base de dados mestre como contexto, usando uma base de dados de utilizadores, ou ambos. Existem várias opções para obter o certificado. Para mais informações sobre certificados, consulte CRIAR CERTIFICADO (Transact-SQL).

Executa este código para criar um certificado de base de dados e protege-o usando uma palavra-passe.

CREATE CERTIFICATE TestCreditRatingCer  
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
      WITH SUBJECT = 'Credit Rating Records Access',   
      EXPIRY_DATE = '12/31/2022';  -- Error 3701 will occur if this date is not in the future
GO

3. Criar e assinar um procedimento armazenado usando o certificado

Use o código seguinte para criar um procedimento armazenado que seleciona dados da Vendor tabela no esquema da Purchasing base de dados, restringindo o acesso apenas às empresas com classificação de crédito 1. Note que a primeira secção do procedimento armazenado mostra o contexto da conta de utilizador a executar o procedimento armazenado, que serve apenas para demonstrar os conceitos. Não é obrigatório cumprir os requisitos.

CREATE PROCEDURE TestCreditRatingSP  
AS  
BEGIN  
   -- Show who is running the stored procedure  
   SELECT SYSTEM_USER 'system Login'  
   , USER AS 'Database Login'  
   , NAME AS 'Context'  
   , TYPE  
   , USAGE   
   FROM sys.user_token     
  
   -- Now get the data  
   SELECT AccountNumber, Name, CreditRating   
   FROM Purchasing.Vendor  
   WHERE CreditRating = 1  
END  
GO

Execute este código para assinar o procedimento armazenado com o certificado da base de dados, usando uma palavra-passe.

ADD SIGNATURE TO TestCreditRatingSP   
   BY CERTIFICATE TestCreditRatingCer  
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';  
GO

Para mais informações sobre procedimentos armazenados, consulte Procedimentos Armazenados (Motor de Base de Dados).

Para mais informações sobre como assinar procedimentos armazenados, consulte ADICIONAR SINATURA (Transact-SQL).

4. Criar uma conta de certificado usando o certificado

Execute este código para criar um utilizador de base de dados (TestCreditRatingcertificateAccount) a partir do certificado. Esta conta não tem login no servidor e, em última análise, controlará o acesso às tabelas subjacentes.

USE AdventureWorks2022;  
GO  
CREATE USER TestCreditRatingcertificateAccount  
   FROM CERTIFICATE TestCreditRatingCer;  
GO

5. Conceder direitos à base de dados da conta de certificados

Execute este código para conceder TestCreditRatingcertificateAccount direitos à tabela base e ao procedimento armazenado.

GRANT SELECT   
   ON Purchasing.Vendor   
   TO TestCreditRatingcertificateAccount;  
GO  
  
GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingcertificateAccount;  
GO

Para mais informações sobre a concessão de permissões a objetos, consulte GRANT (Transact-SQL).

6. Mostrar o Contexto de Acesso

Para mostrar os direitos associados ao acesso ao procedimento armazenado, execute o seguinte código para conceder os direitos de execução do procedimento armazenado ao TestCreditRatingUser utilizador.

GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingUser;  
GO

De seguida, execute o seguinte código para executar o procedimento armazenado como o login DBO que usou no servidor. Verifique a saída da informação de contexto do utilizador. Vai mostrar a conta dbo como o contexto com os seus próprios direitos e não através de uma filiação a um grupo.

EXECUTE TestCreditRatingSP;  
GO

Execute o seguinte código para usar a instrução EXECUTE AS para se tornar na conta TestCreditRatingUser e executar o procedimento armazenado. Desta vez, verá que o contexto do utilizador está definido como UTILIZADOR ASSOCIADO AO CONTEXTO DO CERTIFICADO. Note que esta opção não é suportada numa base de dados contida, nem na Azure SQL Database ou Azure Synapse Analytics.

EXECUTE AS LOGIN = 'TestCreditRatingUser';  
GO  
EXECUTE TestCreditRatingSP;  
GO

Isto mostra-lhe a auditoria disponível porque assinou o procedimento armazenado.

Observação

Use EXECUTE AS para mudar de contexto dentro de uma base de dados.

7. Reiniciar o Ambiente

O código seguinte utiliza a instrução REVERT para devolver o contexto da conta atual para o dbo e reinicia o ambiente.

REVERT;  
GO  
DROP PROCEDURE TestCreditRatingSP;  
GO  
DROP USER TestCreditRatingcertificateAccount;  
GO  
DROP USER TestCreditRatingUser;  
GO  
DROP LOGIN TestCreditRatingUser;  
GO  
DROP CERTIFICATE TestCreditRatingCer;  
GO

Para mais informações sobre a instrução REVERT, veja REVERT (Transact-SQL).

Exemplo completo

Esta secção mostra o código de exemplo completo.

/* Step 1 - Open the AdventureWorks2022 database */  
USE AdventureWorks2022;  
GO  
-- Set up a login for the test user  
CREATE LOGIN TestCreditRatingUser  
   WITH PASSWORD = 'ASDECd2439587y'  
GO  
CREATE USER TestCreditRatingUser  
FOR LOGIN TestCreditRatingUser;  
GO  
  
/* Step 2 - Create a certificate in the AdventureWorks2022 database */  
CREATE CERTIFICATE TestCreditRatingCer  
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
      WITH SUBJECT = 'Credit Rating Records Access',   
      EXPIRY_DATE = '12/31/2021';   -- Error 3701 will occur if this date is not in the future
GO  
  
/* Step 3 - Create a stored procedure and  
sign it using the certificate */  
CREATE PROCEDURE TestCreditRatingSP  
AS  
BEGIN  
   -- Shows who is running the stored procedure  
   SELECT SYSTEM_USER 'system Login'  
   , USER AS 'Database Login'  
   , NAME AS 'Context'  
   , TYPE  
   , USAGE   
   FROM sys.user_token;     
  
   -- Now get the data  
   SELECT AccountNumber, Name, CreditRating   
   FROM Purchasing.Vendor  
   WHERE CreditRating = 1;  
END  
GO  
  
ADD SIGNATURE TO TestCreditRatingSP   
   BY CERTIFICATE TestCreditRatingCer  
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';  
GO  
  
/* Step 4 - Create a database user for the certificate.   
This user has the ownership chain associated with it. */  
USE AdventureWorks2022;  
GO  
CREATE USER TestCreditRatingcertificateAccount  
   FROM CERTIFICATE TestCreditRatingCer;  
GO  
  
/* Step 5 - Grant the user database rights */  
GRANT SELECT   
   ON Purchasing.Vendor   
   TO TestCreditRatingcertificateAccount;  
GO  
  
GRANT EXECUTE  
   ON TestCreditRatingSP   
   TO TestCreditRatingcertificateAccount;  
GO  
  
/* Step 6 - Test, using the EXECUTE AS statement */  
GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingUser;  
GO  
  
-- Run the procedure as the dbo user, notice the output for the type  
EXEC TestCreditRatingSP;  
GO  
  
EXECUTE AS LOGIN = 'TestCreditRatingUser';  
GO  
EXEC TestCreditRatingSP;  
GO  
  
/* Step 7 - Clean up the example */  
REVERT;  
GO  
DROP PROCEDURE TestCreditRatingSP;  
GO  
DROP USER TestCreditRatingcertificateAccount;  
GO  
DROP USER TestCreditRatingUser;  
GO  
DROP LOGIN TestCreditRatingUser;  
GO  
DROP CERTIFICATE TestCreditRatingCer;  
GO

Ver também

Centro de Segurança para SQL Server Database Engine e Azure SQL Database

  • Last updated on