Tutorial: Assinando procedimentos armazenados com um certificado

Artigo
08/29/2023

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

Este tutorial ilustra como assinar procedimentos armazenados usando um certificado gerado pelo SQL Server.

Observação

Para executar o código nesse tutorial será necessário ter a segurança do Modo Misto configurada e o banco de dados AdventureWorks2022 instalado.

A assinatura de procedimentos armazenados usando um certificado é útil quando você quer solicitar permissões sobre procedimentos armazenados, mas não quer conceder esses direitos explicitamente a um usuário. Embora essas tarefas possam ser realizadas de outras formas como, por exemplo, usando a instrução EXECUTE AS, o uso de um certificado permite que você utilize um rastreamento para localizar o chamador original do procedimento armazenado. Isso fornece um alto nível de auditoria, principalmente durante operações de segurança ou DDL (Linguagem de Definição de Dados).

Você pode criar um certificado no banco de dados mestre para conceder permissões em nível de servidor ou criar um certificado em banco de dados de usuário para conceder permissões em nível de banco de dados. Nesse cenário, um usuário sem direitos a tabelas base deve acessar um procedimento armazenado no banco de dados AdventureWorks2022 , e você deseja auditar a trilha de acesso ao objeto. Em vez de usar outros métodos de cadeia de propriedade, você criará uma conta de usuário de servidor e de banco de dados sem direitos aos objetos base e uma conta de usuário de banco de dados com direitos a uma tabela e a procedimentos armazenados. O procedimento armazenado e a segunda conta de usuário de banco de dados serão protegidos com um certificado. A segunda conta de banco de dados terá acesso a todos os objetos e concederá acesso aos procedimentos armazenados à primeira conta de usuário do banco de dados.

Nesse cenário, primeiramente você criará um certificado de banco de dados, um procedimento armazenado e um usuário e, depois, testará o processo seguindo estas etapas:

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

Pré-requisitos

Para concluir este tutorial, você precisará do SQL Server Management Studio, bem como acesso a um servidor que executa o SQL Server e um banco de dados do AdventureWorks.

Instalar o SQL Server Management Studio.
Instalar o SQL Server 2017 Developer Edition.
Baixar Bancos de dados de exemplo do AdventureWorks.

Para obter instruções sobre como restaurar um banco de dados no SQL Server Management Studio, veja Restaurar um banco de dados.

1. Configure o ambiente

Para definir o contexto inicial do exemplo, no SQL Server Management Studio abra uma nova consulta e execute o código a seguir para abrir o banco de dados AdventureWorks2022. Esse código altera o contexto do banco de dados para AdventureWorks2022 e cria um novo logon de servidor e conta de usuário de banco de dados (TestCreditRatingUser), usando uma senha.

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 obter mais informações sobre a instrução CREATE USER, consulte CREATE USER (Transact-SQL). Para obter mais informações sobre a instrução CREATE LOGIN, consulte CREATE LOGIN (Transact-SQL).

2. Criar um certificado

Você pode criar certificados no servidor usando o banco de dados mestre como contexto, usando um banco de dados de usuário ou ambos. Há várias opções para proteger o certificado. Para obter mais informações sobre certificados, confira CREATE CERTIFICATE (Transact-SQL).

Execute este código para criar um certificado de banco de dados e protegê-lo usando uma senha.

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 a seguir para criar um procedimento armazenado que seleciona dados da tabela Vendor no esquema do banco de dados Purchasing , restringindo o acesso apenas a empresas que tenham 1 como classificação de crédito. Observe que a primeira seção do procedimento armazenado exibe o contexto da conta do usuário que executa o procedimento armazenado apenas para demonstração dos conceitos. Não é exigido atender 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 de banco de dados usando uma senha.

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

Para obter mais informações sobre procedimentos armazenados, veja Procedimentos armazenados (Mecanismo de Banco de Dados).

Para obter mais informações sobre como assinar procedimentos armazenados, consulte ADD SIGNATURE (Transact-SQL).

4. Criar uma conta de certificado usando o certificado

Execute este código para criar um usuário de banco de dados (TestCreditRatingcertificateAccount) do certificado. Essa conta não tem logon de servidor e, em última instância, controlará o acesso às tabelas subjacentes.

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

5. Conceder direitos de banco de dados à conta do certificado

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

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

Para obter mais informações sobre como conceder permissões a objetos, consulte GRANT (Transact-SQL).

6. Exibir o contexto de acesso

Para exibir os direitos associados ao acesso de procedimento armazenado, execute o código a seguir para conceder os direitos de execução do procedimento armazenado ao usuário TestCreditRatingUser .

GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingUser;  
GO

Em seguida, execute o código a seguir para executar o procedimento armazenado como o logon dbo usado no servidor. Observe a saída das informações de contexto de usuário. Ela mostrará a conta dbo como o contexto com seus próprios direitos e não por meio de uma associação a um grupo.

EXECUTE TestCreditRatingSP;  
GO

Execute o código a seguir para usar a instrução EXECUTE AS como a conta TestCreditRatingUser e executar o procedimento armazenado. Dessa vez você verá que o contexto de usuário está definido como o contexto de USER MAPPED TO CERTIFICATE. Observe que essa opção não tem suporte em um banco de dados independente, no Banco de Dados SQL do Azure ou no Azure Synapse Analytics.

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

Como você assinou o procedimento, a auditoria disponível será exibida.

Observação

Use EXECUTE as para alternar contextos dentro de um banco de dados.

7. Redefina o ambiente

O código a seguir usa a instrução REVERT para retornar o contexto da conta atual para dbo e redefine 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 obter mais informações sobre a instrução REVERT, consulte REVERT (Transact-SQL).

Exemplo completo

Esta seção exibe 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

Confira também

Central de segurança do Mecanismo de Banco de Dados do SQL Server e Banco de Dados SQL do Azure

Compartilhar via