Trabalhando com Certificados no SQL Server 2008

Tempo de leitura: 3 minutos

Boa tarde pessoal.

Este post é dedicado a um amigo da empresa, Rogério Santini, extremamente inteligente, sempre me da os conselhos certos.No post anterior falamos sobre a importância de utilizar database roles para controlar o acesso aos servidores de banco de dados através das aplicações. Hoje iremos falar sobre um caso real que foi aplicado aqui na nossa empresa. Temos nossas aplicações executando as procedures através de database roles, porém, existe uma migração acontecendo que estamos trabalhamos com informações se sistemas legados.

Vamos partir do nosso exemplo anterior onde tínhamos uma tabela de clientes com a coluna IdCliente que tem a propriedade identity de 1,1. Imagine que essa tabela será populado pelo nosso sistema atual. A cada inserção, será atribuído um valor incremental, até ai nenhuma novidade, comportamento normal. Agora aparece uma variável em nossa implantação. O sistema legado que temos, ainda ficará em funcionamento, sendo assim, ele precisará realizar inserções em nossa tabela de clientes. Thiago não entendi!? Aonde você quer chegar? Vamos lá meu caro blog…rs.

O sistema legado já existe os seus IdClientes, com uma sequencia própria dele, neste caso, as procedures farão uso da opção SET IDENTITY_INSERT. E dai? Como já tenho permissão de execução de procedures no sistema, nada muda certo? ERRADO. Para realizar a operação de SET IDENTITY_INSERT o usuário deve ter permissão de DDL_ADMIN. No momento que liberar essa permissão, o login da aplicação pode criar qualquer objeto dentro do banco de dados e esse é um risco muito grande. Thiago então qual a solução? Vamos lá bloguinho. A partir do SQL Server 2005, aprimorou-se as features de segurança do produto. Uma das “novidades” é o uso de certificados e máster key. Podemos realizar o uso dessas features, para fazer com que o login que chamou a procedure possa executar a mesma com o comando set identity_insert sem ter permissão de DDL_ADMIN. Vamos ver como isso funciona?

Vamos se conectar ao banco de dados dbPermissoes e alterar a procedure stp_ins_cliente que usamos no exemplo anterior para pode adicionar o comando SET_IDENTITY_INSERT.

alter  PROCEDURE dbo.stp_ins_cliente(@Id int, @Nome VARCHAR(50), @Cpf CHAR(11))

AS

begin

       SET IDENTITY_INSERT dbo.Clientes ON

            INSERT INTO dbo.Clientes(IdCliente, NomeCliente,CpfCliente) VALUES(@id, @Nome, @Cpf)

      SET IDENTITY_INSERT dbo.Clientes OFF

 END

go

Executando a procedure após de alterada, temos a seguinte mensagem de erro:

EXECUTE AS LOGIN = ‘AplSistema’

go

EXEC dbo.stp_ins_cliente @id = 8 ,@Nome = ‘Alencar’,@Cpf =’133xxx63215′

GO

REVERT

Msg 1088, Level 16, State 11, Procedure stp_ins_cliente, Line 5

Cannot find the object “dbo.Clientes” because it does not exist or you do not have permissions.

A mensagem acima é clara, ou não tenho permissão ou o objeto não existe. Segundo que a hipótese da permissão é verdadeira. Vamos dar permissão de DDL_ADMIN para o login e testar a execução, para garantir que é isso mesmo.

USE [dbPermissoes]

GO

EXEC sp_addrolemember N’db_ddladmin’, N’AplSistema’

GO

Executando novamente:

EXECUTE AS LOGIN = ‘AplSistema’

go

EXEC dbo.stp_ins_cliente @id = 8 ,@Nome = ‘Alencar’,@Cpf =’133xxx63215′

GO

REVERT

Vejamos o resultado e o ID 8 foi inserido.

Iremos remover o login do database role do DD_ALMIN e faremos a solução “Elegante”.

Criaremos o database máster key dentro da base dbPermissoes:

use dbPermissao

go
CREATE MASTER KEY ENCRYPTION BY password = ‘MkE2012@#’

Criamos o certificado:

CREATE CERTIFICATE CrtPermissoes WITH SUBJECT = ‘Schema user Certificate’

Agora precisamos criar um usuário para a base de dados em questão. Esse usuário é o que chamamos de “without login”. Quando criamos um usuário no banco de dados que não está atribuído a nenhum login, a sintax é completamente simples. Na criação do usuário, atribuo ele aio certificado:

CREATE USER UsrCert FROM CERTIFICATE CrtPermissoes

No momento da mágica, apenas realizamos o comando abaixo:

GRANT ALTER ANY SCHEMA TO UsrCert ADD SIGNATURE TO dbo.stp_ins_cliente BY CERTIFICATE CrtPermissoes

GO

Esse comando da permissão de “ANY SCHEMA” para o usuario UsrCert que criamos, e essa permissão é atribuida pelo certificado CrtPermissoes. Como o Usuario AplSistema tem permissão de execução de procedures a solução torna-se viável e “clean”.  Iremos remover o login do DDL_ADMIN e realizar uma execução de insert, inserindo o IdCliente 666.

USE [dbPermissoes]

GO

EXEC sp_droprolemember N’db_ddladmin’, N’AplSistema’

GO

Executamos a procedure:

EXECUTE AS LOGIN = ‘AplSistema’

go

EXEC dbo.stp_ins_cliente @id = 666 ,@Nome = ‘Alencar’,@Cpf =’133xxx63215′

GO

REVERT

E BAZINGA!!!!!