Utilizando certificados para autenticação entre bancos de dados no SQL Server

Uma alternativa mais saudável para cenários onde é necessário executar stored procedures, funções, triggers e assemblies que requerem utilizar objetos de outros bancos de dados, é a utilização de certificados, ao invés da já conhecida configuração de Cross DB Ownership Chaining.

A desvantagem dos certificados se dá a complexidade na utilização dos mesmos, mas com a demonstração a seguir, acredito que vocês terão poucas dúvidas.

Para o início da demonstração, temos dois bancos de dados com Cross DB Ownership Chaining desabilitado (configuração padrão em muitos cenários), onde teremos uma stored procedure em um banco tentando acessar uma tabela de outro.

-- Criando os bancos de dados
CREATE DATABASE DB01;

ALTER DATABASE DB01 SET DB_CHAINING OFF;

CREATE DATABASE DB02;

ALTER DATABASE DB02 SET DB_CHAINING OFF;
GO

-- Criando a tabela
USE DB01;
GO
CREATE TABLE SuperTabela (Id INT);
GO

-- Criando a stored procedure
USE DB02;
GO
CREATE PROC SuperProc AS
	SELECT * FROM DB01.dbo.SuperTabela
	WHERE ID > 10;
GO

A nossa usuária Maria somente terá acesso á stored procedure, mas não a tabela, logo numa tentativa de utilizar o usuário dela para executar a stored procecure teremos um erro por falta de permissão:

-- Criando a usuária Maria
USE [master];
GO
CREATE LOGIN Maria WITH PASSWORD = '9455w0rd';
GO

USE DB02;
GO
CREATE USER Maria FOR LOGIN Maria;
GO

-- Permitindo o acesso à stored procedure
USE DB02;
GO
GRANT EXEC ON dbo.SuperProc TO Maria;
GO

-- Executando a stored procedure
USE DB02;
GO
EXECUTE AS LOGIN = 'Maria';
GO
EXEC dbo.SuperProc;
GO
REVERT;
GO

Msg 916, Level 14, State 1, Procedure SuperProc, Line 2
The server principal “Maria” is not able to access the database “DB01” under the current security context.
Ah “Maria” sua n00b!!

Agora para resolver a questão da permissão, criaremos um certificado no banco de dados onde esta a tabela que precisamos de acesso, e permitimos acesso a esta tabela por meio de um usuário criado a partir deste certificado:

USE DB01;
GO

-- Criando o certificado
CREATE CERTIFICATE CERT_DB01
	ENCRYPTION BY PASSWORD = '5up3r53nh4'
	WITH SUBJECT = 'CERT_DB01';

-- Criando um usuário a partir do certificado
CREATE USER USU_CERT_DB01
	FOR CERTIFICATE CERT_DB01;

-- Permitindo acesso do usuário à tabela
GRANT SELECT ON dbo.SuperTabela
	TO USU_CERT_DB01;

Salvamos o certificado para o movimentarmos para o outro banco de dados:

-- Salvando o certificado para movimentar para o outro banco de dados
BACKUP CERTIFICATE CERT_DB01
	TO FILE = 'C:\Projetos\CERT_DB01.cer'
	WITH PRIVATE KEY (
		DECRYPTION BY PASSWORD = '5up3r53nh4',
		FILE = 'C:\Projetos\CERT_DB01.pvk',
		ENCRYPTION BY PASSWORD = '5up3r53nh4-01'
	);

-- Apagando a chave privada do certificado
ALTER CERTIFICATE CERT_DB01
	REMOVE PRIVATE KEY;
GO

Restauramos o certificado onde esta a stored procedure, e assinamos a stored procedure com o certificado:

USE DB02;
GO

-- Restaurando o ceritificado no banco de dados
CREATE CERTIFICATE CERT_DB01
	FROM FILE = 'C:\Projetos\CERT_DB01.cer'
	WITH PRIVATE KEY (
		DECRYPTION BY PASSWORD = '5up3r53nh4-01',
		FILE = 'C:\Projetos\CERT_DB01.pvk',
		ENCRYPTION BY PASSWORD = '5up3r53nh4'
	);
GO

-- Assinando a stored procedure com o certificado
ADD SIGNATURE TO dbo.SuperProc
	BY CERTIFICATE CERT_DB01
	WITH PASSWORD = '5up3r53nh4';
GO

Agora podemos executar a stored procedure com o usuário da Maria sem problemas:

-- Executando a stored procedure
USE DB02;
GO
EXECUTE AS LOGIN = 'Maria';
GO
EXEC dbo.SuperProc;
GO
REVERT;
GO

Por fim, finalizamos o cenário:

-- Finalizando o cenário
USE [master];

DROP DATABASE DB01;

DROP DATABASE DB02;

DROP LOGIN Maria;
GO

Um pensamento sobre “Utilizando certificados para autenticação entre bancos de dados no SQL Server

  1. Pingback: Acessando dados de Web Service com Ole Automation Procedures « SQL From Hell.com

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s