Tutorial: Assinar procedimentos armazenados com um certificado

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

Assinar procedimentos armazenados usando um certificado é útil quando você deseja exigir permissões no procedimento armazenado, mas não deseja conceder explicitamente a um usuário esses direitos. Embora você possa realizar essa tarefa de outras maneiras, como usar a instrução EXECUTE AS, usar um certificado permite que você use um rastreamento para localizar o chamador original do procedimento armazenado. Isso fornece um alto nível de auditoria, especialmente 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 permitir permissões no nível do servidor ou criar um certificado em qualquer banco de dados de usuário para permitir permissões no nível do banco de dados. Nesse cenário, um usuário sem direitos para tabelas base deve acessar um procedimento armazenado no banco de dados AdventureWorks2012 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 banco de dados sem direitos aos objetos base e uma conta de usuário de banco de dados com direitos a uma tabela e um procedimento armazenado. O procedimento armazenado e a segunda conta de usuário do 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 ao procedimento armazenado à primeira conta de usuário do banco de dados.

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

1. Configurar o ambiente.

2. Criar um certificado.

3. Crie e assine um procedimento armazenado usando o certificado.

4. Crie uma conta de certificado usando o certificado.

5. Conceda direitos de banco de dados à conta de certificado.

6. Exiba o contexto de acesso.

7. Redefina o ambiente.

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

1. Configurar 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 AdventureWorks2012 . Esse código altera o contexto AdventureWorks2012 do banco de dados e cria um novo logon do servidor e uma conta de usuário do banco de dados (TestCreditRatingUser), usando uma senha.

USE AdventureWorks2012;  
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 o 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/05/2014';  
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 Vendor tabela no esquema de banco de dados, restringindo o Purchasing acesso apenas às empresas com uma classificação de crédito de 1. Observe que a primeira seção do procedimento armazenado exibe o contexto da conta de usuário executando o procedimento armazenado, que é demonstrar apenas os conceitos. Não é necessário atender aos 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, consulte 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) a partir do certificado. Essa conta não tem logon no servidor e, em última análise, controlará o acesso às tabelas subjacentes.

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

Conceder direitos de acesso ao banco de dados da conta de certificado

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 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 ao procedimento armazenado, execute o código a seguir para conceder os direitos para executar o procedimento armazenado ao TestCreditRatingUser usuário.

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 do usuário. Ele mostrará a conta dbo como o contexto com direitos próprios e não por meio de uma associação em grupo.

EXECUTE TestCreditRatingSP;  
GO  

Execute o código a seguir para usar o EXECUTE AS comando para assumir a TestCreditRatingUser conta e rodar o procedimento armazenado. Desta vez, você verá que o contexto do usuário está definido como o contexto do "USER MAPPED TO CERTIFICATE".

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

A seguir, é exibida a auditoria disponível porque você assinou o procedimento armazenado.

7. Redefinir o ambiente

O código a seguir usa a REVERT instrução para retornar o contexto da conta atual para o 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 AdventureWorks2012 database */  
USE AdventureWorks2012;  
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 AdventureWorks2012 database */  
CREATE CERTIFICATE TestCreditRatingCer  
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
      WITH SUBJECT = 'Credit Rating Records Access',   
      EXPIRY_DATE = '12/05/2014';  
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 AdventureWorks2012;  
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  

Consulte Também

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