Permissão – Database Role

Tempo de leitura: 4 minutos

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.