Tutorial: Assinando procedimentos armazenados com um certificado

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 AdventureWorks2008R2 instalado. Cenário

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 permissões de nível de servidor ou pode criar um certificado em qualquer banco de dados de usuário para permissões de 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 AdventureWorks2008R2, 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:

1. Configurar o ambiente.

2. Criar um certificado.

3. Criar e assinar um procedimento armazenado usando o certificado.

4. Criar uma conta de certificado usando o certificado.

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

6. Exibir o contexto de acesso.

7. Redefinir o ambiente.

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

1. Configurar o ambiente

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

USE AdventureWorks2008R2;
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, consulte 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/2010';
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, consulte Procedimento armazenados (Mecanismo de Banco de Dados).

Para obter mais informações sobre esquemas de bancos de dados, consulte Esquemas (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 AdventureWorks2008R2;
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 uma associação de 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.

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

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

Observação
Existem duas maneiras de permitir que um usuário alterne contextos em um banco de dados: SETUSER ou EXECUTE AS. Para obter mais informações sobre alternância de contexto, consulte EXECUTE AS versus SETUSER.

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 AdventureWorks2008R2 database */
USE AdventureWorks2008R2;
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 AdventureWorks2008R2 database */
CREATE CERTIFICATE TestCreditRatingCer
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
      WITH SUBJECT = 'Credit Rating Records Access', 
      EXPIRY_DATE = '12/05/2010';
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 AdventureWorks2008R2;
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

Conceitos

Segurança e proteção (Mecanismo de Banco de Dados)

Desenvolvimento seguro (Mecanismo de Banco de Dados)