Assinatura de módulo (Mecanismo de Banco de Dados)

Os aplicativos de banco de dados geralmente requerem que o acesso a tabelas e objetos subjacentes no esquema do aplicativo seja mediado por procedimentos ou exibições no nível de entrada. A intenção é poder conceder acesso aos usuários finais nos objetos desse nível, os quais então acessam os objetos subjacentes para o usuário. Portanto, os usuários finais não precisam ter acesso em todos os objetos no esquema do aplicativo. Essa abordagem tem dois objetivos:

• Ela simplifica o gerenciamento de permissões que então só precisam ser gerenciadas em um subconjunto menor de objetos, e não em todos os objetos do esquema de aplicativo.

• É possível ocultar o layout de esquema subjacente do usuário, uma vez que somente os pontos de entrada são expostos.

O Microsoft SQL Server tem vários recursos que ajudam a conseguir esses cenários, como encadeamento de propriedades e uso da instrução EXECUTE AS. A partir do SQL Server 2005, o SQL Server também fornece a capacidade de assinar módulos no banco de dados. A assinatura de módulo oferece recursos semelhantes, mas não altera o contexto de execução. Um módulo nesse contexto se refere a um procedimento armazenado, função, gatilho ou assembly. Para obter mais informações, consulte CREATE ROLE (Transact-SQL) e Usando EXECUTE AS para criar conjuntos de permissão personalizados..

Assinaturas de módulo

O SQL Server 2005 introduziu a capacidade de assinar módulos no banco de dados, como procedimentos armazenados, funções, gatilhos ou assemblies. Observe que os gatilhos DDL (linguagem de definição de dados) não podem ser assinados. A assinatura digital é um resumo de dados criptografados com a chave privada do signatário. Essa chave assegura que a assinatura digital seja exclusiva de seu portador ou proprietário.

Para assinar dados, o signatário resume os dados, criptografa-os com uma chave privada e anexa o valor do resumo criptografado aos dados. Para verificar a assinatura, o verificador usa a chave pública do assinante para descriptografar o valor do resumo criptografado anexado aos dados. O verificador então compara esse valor de resumo descriptografado com o valor do resumo computado nos dados complementares. É importante que o signatário e o verificador usem a mesma função de hash para resumir os dados.

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

Cenário

Vamos considerar que o acesso à exibição sys.sysprocesses deve ser mediado pelo procedimento armazenado usp_sysprocesses. Os usuários só poderão acessar as informações de sys.sysprocesses quando passarem pelo procedimento usp_sysprocesses. Como os objetos usp_sysprocesses e sys.sysprocesses têm propriedades diferentes, o encadeamento de propriedades não se aplica.

Em primeiro lugar, um certificado deve ser criado no servidor a partir de um par de chaves usando a instrução CREATE CERTIFICATE. Em seguida, concedemos ao certificado as permissões para selecionar na tabela sys.sysprocesses. No entanto, como o SQL Server só concede permissões aos principais, primeiro é necessário criar um logon a partir do certificado usando a instrução CREATE LOGIN. Esse logon não precisa de permissões de conexão no servidor, pois ele é apenas um espaço reservado para permissões e não se destina à conexão com a instância do servidor. Esse logon mapeado por certificado pode então receber permissões SELECT na tabela sys.sysprocesses usando a instrução GRANT VIEW SERVER STATE TO. Após a criação do procedimento armazenado usp_sysprocesses, é possível assinar o procedimento armazenado com o certificado (na realidade, a chave particular correspondente a esse certificado) usando a instrução ADD SIGNATURE. Uma nova função é criada e recebe a permissão executar no procedimento armazenado usp_sysprocesses. Os usuários que forem membros dessa função têm permissão para executar o procedimento armazenado usp_sysprocesses e, desse modo, SELECT na exibição sys.sysprocess. Quando você executar o módulo assinado, as permissões concedidas ao principal (usando a instrução GRANT) associado ao certificado de assinatura serão temporariamente unidas com UNION no token de segurança em tempo de execução enquanto durar a chamada. Assim que o controle de execução retornar, essas permissões serão removidas do token de segurança. Assim, apenas durante o tempo de vida da execução do módulo, você tem efetivamente um conjunto extra de permissões. Qualquer outro usuário ou função que receber permissões EXECUTE neste procedimento também terão os mesmos recursos.

Exemplo

O script Transact-SQL a seguir fornece um exemplo do cenário anterior. Um certificado é criado a partir de um par de chaves e é mapeado para um novo logon. Primeiro, é necessário criar um par de chaves de teste usando a ferramenta MakeCert incluída no SDK do .NET Framework. As permissões selecionadas para a exibição sys.sysproceses são concedidas para o logon associado ao certificado. O procedimento armazenado gerenciado usp_sysprocesses é criado no novo banco de dados e é assinado com o certificado. A função SysProcRole é criada e recebe permissões executar no procedimento armazenado usp_sysprocesses. Um usuário de teste é criado e adicionado à função SysProcRole. O usuário de teste executa uma instrução SELECT em sys.sysprocess e então executa o procedimento armazenado usp_sysprocesses, para comparação. O script limpa o ambiente de teste.

use master
go

-- Create a test database.
CREATE DATABASE db_Demo
go

-- Create a certificate on the server. A test key pair can be created
-- using the MakeCert tool that ships with the .NET Framework SDK.
CREATE CERTIFICATE SysProcCert FROM FILE = 'e:\programming\testCert.cer'
go

-- Create a login and map it to the certificate.
CREATE LOGIN login_SysProcCert FROM CERTIFICATE SysProcCert
Go

-- Revoke the connect permission.
REVOKE CONNECT SQL FROM login_SysProcCert ;
go 
 
-- Grant the certificate, through the login, permission to select from sys.sysprocesses view.
GRANT VIEW SERVER STATE TO login_SysProcCert
go

-- Create a test login.
CREATE LOGIN bob WITH PASSWORD = '<enterStrongPasswordHere>'
go

-- Connect to the test database.
use db_Demo
go

-- Create the master key for the test database (used to protect 
-- private keys and certificates in the database).
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<enterStrongPasswordHere>' 

-- Create a certificate from a private key.
CREATE CERTIFICATE SysProcCert FROM FILE = 'e:\programming\testCert.cer'
WITH PRIVATE KEY
(FILE = 'e:\programming\testCert.pvk', 
 DECRYPTION BY PASSWORD= '<enterStrongPasswordHere>', 
 ENCRYPTION BY PASSWORD='<enterStrongPasswordHere>')
go 

-- Create the assembly on the server. The assembly DLL must be signed.
CREATE ASSEMBLY SysStoredProcedures
FROM 'E:\programming\SysStoredProcs.dll'
WITH PERMISSION_SET = SAFE
go 

-- Create the managed stored procedure on the server.
CREATE PROCEDURE usp_sysprocesses
AS EXTERNAL NAME SysStoredProcedures.StoredProcedures.usp_sysprocesses
go 

-- Add the signature to the stored procedure.
ADD SIGNATURE TO [dbo].[usp_sysprocesses] 
BY CERTIFICATE SysProcCert WITH PASSWORD = '<enterStrongPasswordHere>'
go 

-- Create a role.
CREATE ROLE SysProcRole
go

-- Create a test user
CREATE USER bob
go

-- Add the test user to the role.
EXEC sp_addrolemember 'SysProcRole', 'bob'
go

-- Grant execute permissions on the stored procedure to the new role.
GRANT EXECUTE ON [dbo].[usp_sysprocesses] TO SysProcRole
go
 
-- Connect as the test user.
EXECUTE AS LOGIN = 'bob'
use db_Demo
go
 
-- User only has permission to see their own processes.
SELECT * FROM sys.sysprocesses
go

-- Execute the stored procedure, which has been signed.
exec usp_sysprocesses
go

-- REVERT
REVERT
----------------------------------------------------------------------
-- Cleanup

use db_Demo
go

use master
go

DROP DATABASE db_Demo
go 

DROP login login_SysProcCert
DROP login bob
go

DROP CERTIFICATE SysProcCert
go

A seguir encontra-se o código-fonte do procedimento armazenado usp_sysprocesses, o qual executa uma instrução SELECT * na exibição sys.sysprocesses. O assembly deverá ser assinado quando for criado.

C#

using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void usp_sysprocesses()
{
    using(SqlConnection connection = new SqlConnection("context connection=true"))
    {
        connection.Open();
        SqlCommand command = new SqlCommand("SELECT * FROM sys.sysprocesses", connection);
        SqlContext.Pipe.ExecuteAndSend(command);
    }
}
};

Visual Basic

Imports System
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub  usp_sysprocesses ()
    Using connection As New SqlConnection("context connection=true")
        connection.Open()

        Dim command As New SqlCommand("SELECT * FROM sys.sysprocesses", connection)
        SqlContext.Pipe.ExecuteAndSend(command)
    End Using
End Sub
End Class

Consulte também

Referência

CREATE CERTIFICATE (Transact-SQL)

CREATE LOGIN (Transact-SQL)

GRANT (Transact-SQL)

CREATE MASTER KEY (Transact-SQL)

ADD SIGNATURE (Transact-SQL)

CREATE ROLE (Transact-SQL)

Conceitos

Protegendo o SQL Server

Certificados e chaves assimétricas do SQL Server