Archive

Posts Tagged ‘Seguranção SQL Server’

Trabalhando com Credenciais e Proxy Account no SQL Server

Este post é dedicado à mulher mais importante da minha vida, que fez ser o que sou hoje com muita luta e dedicação minha mãe, Francisca de Alencar.

Após a dedicatória, vamos falar sobre SQL.

Bom dia. Galera, blz? Hoje vamos falar de alguns recursos que ganharam muita força a partir do SQL Server 2005. Segurança, no SQL Server foram incluídas diversas features, como por exemplo: TDE, Certificados, Credenciais, Proxy Account e etc.

No post , falamos de como podemos utilizar certificados para atribuir permissões especificas, sem ter que aumentar o nível de acesso de nossos logins. Nesse post vamos falar de como utilizar credenciais e proxy account. Um dos problemas que tínhamos no SQL Server 2000 era que quando era necessário executar o comando xp_cmdsheel, o login que disparava a chamada do comando precisava ser membro do server role sysadmin para poder executar com sucesso. A partir do SQL Server 2005, temos como permitir esse comportamento, sem que o login seja membro do sysadmin utilizando proxy account.

Imagine o cenário: No nosso ambiente temos diversos pacotes SSIS de integrações, porém, o login que os executa é um login do tipo SQL Server authentication que não pode fazer parte do server role sysadmin. Um dos motivos é que a publicação do pacote é feita através de file system. Thiago existe outra maneira mais segura de fazer isso? Claro, que sim meu querido blog, mas, no momento foi essa solução implementada.

Obs: Com o file system, é aberto uma brecha de segurança, pois, o login que tiver acesso a pasta onde os arquivos serão publicados, podem abrir o webconfig do pacote.

É triste dizer, mas, vi muitos DBAs que tiveram esse problema e colocaram o login dentro do server role sysadmin para “resolver” o problema. A solução que encontrei sem ter que dar a permissão “motherfucker” para o login que executa o pacote será explicada a seguir.

Vamos criar o login do tipo SQL Server dentro do server role bulkadmin.

/****** Object:  Login [PkgSSIS]    Script Date: 09/19/2012 20:00:58 ******/

IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N’PkgSSIS’)

DROP LOGIN [PkgSSIS]

GO

 

/* For security reasons the login is created disabled and with a random password. */

/****** Object:  Login [PkgSSIS]    Script Date: 09/19/2012 20:00:58 ******/

CREATE

            LOGIN [PkgSSIS] WITH PASSWORD=N’pkgssis’,

                  DEFAULT_DATABASE=[master],

                  DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

 EXEC sys.sp_addsrvrolemember @loginame = N’PkgSSIS’, @rolename = N’bulkadmin’

GO

 ALTER LOGIN [PkgSSIS] ENABLE

GO

Vamos atribuir permissão para a base de dados utilizadas nos post anteriores e para o msdb. No banco dbPermissoes o login terá que ficar dentro do database role DR_EXEC. Dentro do msdb o login ficará dentro do database role SQLAgentUserRole e SQLAgentReaderRole.

dbPermissoes:

Atribuição ao Role:

Msdb e atribuição aos roles:

 Por enquanto até aqui sem muitas surpresas e complicações. Agora queremos que esse login tenha permissão de executar pacotes, quando tentamos executar pacotes sem ser membro do server role sysadmin, a mensagem é retornada:

Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy accoutnt.

Primeiro temos que criar uma credencial, conforme script:

–Cria a credencial utilizando a conta de dominio SQLSVC e coloque o password da conta.

USE [master]

GO

CREATE CREDENTIAL [SSISCredencial] WITH IDENTITY = N’Dominio\SQLSvc’, SECRET = N’PasswordAqui’

GO

Vai ficar da seguinte forma:

Agora iremos criar um proxy account , atribuindo a essa credencial.

USE [msdb]

GO

 /****** Object:  ProxyAccount [SSISProxy]    Script Date: 09/18/2012 13:31:46 ******/

IF  EXISTS (SELECT name FROM msdb.dbo.sysproxies WHERE name = N’SSISProxy’)

begin

      EXEC msdb.dbo.sp_delete_proxy @proxy_name=N’SSISProxy’

end

GO

 USE [msdb]

GO

 /****** Object:  ProxyAccount [SSISProxy]    Script Date: 09/18/2012 13:31:46 ******/

EXEC msdb.dbo.sp_add_proxy @proxy_name=N’SSISProxy’,@credential_name=N’SSISCredencial’,

            @enabled=1

GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’SSISProxy’, @subsystem_id=3 — SubSystem: CMDExec

GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’SSISProxy’, @subsystem_id=11 — SubSystem: SSIS

GO

EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N’SSISProxy’, @login_name=N’PkgSSIS’

GO

O subsystem de Id 3 e 11 são os subsystem para utilizar CmdExec e SSIS..No modo gráfico é possível verificar que eles estão “ticados”:

Clique na gui “Principals” e verifique que o login PkgSSIS está marcado como SQL Login:

Tudo isso, foi feito no script anterior. Isso está sendo mostrado para que o leitor consiga identificar o que está sendo criado e onde fica.

Calma galera! Já estamos quase lá. Agora vamos ver onde essas informações devem ser atribuídas no Job. Neste meu caso o job já foi criado, apenas colocarei os prints aonde tive que modificar.

O owner do job, que executará será o login: Pkgs:

No steps podemos ver que o job é um pacote SSIS. Temos apenas que mudar quem executa o nosso job que não será mais a conta do SQL Server Agent e sim no Proxy.

Agora iremos executar o job e “Eureca”.

O que tem dentro do pacote? Apenas uma procedure que executa um comando update sem Where dentro de uma proc. A procedure é chamado dentro de um “Execute SQL Task” do SSIS. Não existe nada de funcionalidade, pois, o post é apenas pra mostrar que é possível executar um pacote sem ter que dar permissões “absurdos” para um login especifico.

Espero que tenha ajudado.

Abs

Trabalhando com Certificados no SQL Server 2008

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!!!!!

Permissão – Database Role

Galera, boa tarde

O post de hoje é dedicado para o meu Amigo Silas Mendes, um ótimo DBA e um tremendo SQL Geek. O cara esté me pentelhando para eu instalar um plugin que deixa os códigos coloridos,mas, infelizmente ficará para o próximo  post. Nesse ficará tudo em AZUL o que for script.

Hoje vamos falar sobre uma coisa que “teoricamente”, não deveria acontecer no desenvolvimento de softwares, porém, é bem comum acontecer quando a aplicação está sendo desenvolvida, o desenvolvedor ou o mesmo DBA coloca o login da aplicação como owner do database, assim os problemas de permissão serão “resolvidos” mais rápidos.

O  login da aplicação que acessa o servidor de banco de dados deve ter as permissões mais restritivas possiveis. Na maioria das vezes o usuário deve estar dentro de um database role, e esse database role deve apenas ter permissão de execução nas procedures.

Quando o cenário acima é verdadeiro, nem o próprio login tem acesso de realizar qualquer operação diretamente nas tabelas, só através das procedures, isso é uma das vantagens da utilização de stored procedures, isolamento. As vezes acontece do usuário estar dentro dos roles: db_datareader e db_datawriter. Mas, se todas as interações entre banco de dados e aplicação for via stored procedures isso não deveria acontecer, o login apenas deve estar no role de execução.

Vamos colocar a mão na massa.

–Cria o banco de dados dbPermissoes

CREATE DATABASE dbPermissoes

USE dbPermissoes

go

–Cria o login AplSistema

CREATE LOGIN AplSistema WITH PASSWORD =’$@123456′ ,CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

go

–Cria o role que receberá as permissões de execução

CREATE ROLE [DR_EXEC]

go

–Crio usuario no banco de dados dbPermissoes. Ao fazer isso, estou atribuindo o usuario ao login que foi criado anteriormente

CREATE USER AplSistema FOR LOGIN AplSistema

–Adiciono o usuario AplSistema ao Role: DR_EXEC

EXEC sp_addrolemember N’DR_EXEC’, N’AplSistema’

No script acima criamos um banco de dados chamado:dbPermissoes, com um usuário e um database role. Vendo pelo modo gráfico, ficou da seguinte maneira:

Atribuição ao banco de dados:

Roles:

Agora criaremos uma tabela de clientes e realizaremos algumas inserções.

USE dbPermissoes

go

 IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID(‘dbo.Clientes’))

BEGIN

      DROP TABLE db.Clientes

END

go

CREATE TABLE dbo.Clientes

(

      IdCliente INT IDENTITY(1,1) NOT NULL

      ,NomeCliente VARCHAR (50) NOT NULL

      ,CpfCliente CHAR(11) NOT NULL

)

INSERT INTO dbo.Clientes (NomeCliente,CpfCliente) VALUES(‘Sheldon Cooper’,’12345685961′),(‘Leonard Hofstadter’,’98632156978′),(‘Penny’,’85236974112′)

Após nossa tabela ser criada, iremos criar duas procedures: Uma de inserção e outra de seleção. No código dessas procedures, iremos atribuir permissão de execute para o database role DR_EXEC que criamos no banco de dados em questão.

USE dbPermissoes

go

IF EXISTS(SELECT 1 FROM sys.procedures WHERE object_id= OBJECT_ID(‘dbo.STP_INS_Cliente’))

BEGIN

      DROP PROCEDURE dbo.stp_ins_cliente

END

go

CREATE PROCEDURE dbo.stp_ins_cliente(@Nome VARCHAR(50), @Cpf CHAR(11))

AS

begin

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

 END

GO

GRANT EXECUTE ON  dbo.stp_ins_cliente TO [DR_EXEC]

go

 IF EXISTS(SELECT 1 FROM sys.procedures WHERE object_id= OBJECT_ID(‘dbo.STP_SEL_Cliente’))

BEGIN

      DROP PROCEDURE dbo.STP_SEL_Cliente

END

go

CREATE PROCEDURE dbo.STP_SEL_Cliente

AS

begin

       SELECT IdCliente AS Id,  NomeCliente AS Nome ,CpfCliente AS CPF

            FROM dbo.Clientes

END

GO

GRANT EXECUTE ON  dbo.STP_SEL_Cliente TO [DR_EXEC]

go

Agora executaremos a instrução utilizando o login que foi criado no inicio do artigo. No nosso exemplo, não precisamos nem conectar na instancia, pois, podemos simular a execução de um outro usuário, utilizando a opção: Execute as Login.

Faremos os seguintes testes:

1º -Inserção na tabela de Clientes com o login AplSistema
2º -Inserção na tabela Clientes atraves da execução da procedure
3º -Select  na tabela de Clientes com o login AplSistema
4º -Select na tabela Clientes atraves da execução da procedure

Ao executar o teste 1º e 2º, veja o que acontece:

EXECUTE AS LOGIN = ‘AplSistema’

go

INSERT INTO dbo.Clientes(NomeCliente,CpfCliente) VALUES(‘Alencar’,’133xxx63215′)

GO

REVERT

Erro:

Msg 229, Level 14, State 5, Line 1

The INSERT permission was denied on the object ‘Clientes’, database ‘dbPermissoes’, schema ‘dbo’.

Com a procedure o registro é inserido:

EXECUTE AS LOGIN = ‘AplSistema’

go

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

GO

REVERT

Teste 2º e 3º:

EXECUTE AS LOGIN = ‘AplSistema’

go

SELECT IdCliente AS Id,  NomeCliente AS Nome ,CpfCliente AS CPF

            FROM dbo.Clientes

GO

REVERT

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object ‘Clientes’, database ‘dbPermissoes’, schema ‘dbo’.

 EXECUTE AS LOGIN = ‘AplSistema’

go

EXEC dbo.STP_SEL_Cliente

GO

REVERT

Como podemos ver utilizar roles para restringir o acesso das nossas aplicações à recursos dos nossos banco de dados, pode aumentar drasticamente o esquema da segurança. Lembrando que a aplicação só pode realizar comandos DMLs e Execução de procedures. No conceito do mundo perfeito a aplicação não tem direito de criar objetos no banco de dados, e é disso que iremos falar no próximo post.