Conhecendo as Server Roles e Database Roles do SQL Server

Olá pessoal, tudo certo?

No post de hoje vou falar um pouco sobre segurança. O que me motivou a fazer esse post é que comumente encontramos dúvidas em fóruns sobre o nível de permissão de cada role do SQL Server, tanto as server roles quanto as database roles, ainda mais depois da LGPD (Lei Geral de Proteção de Dados).

Depois da implantação da LGPD, estamos vendo um grande despertar e movimento das empresas quanto a importância da proteção dos seus dados, ainda mais quando esses dados envolvem informações de terceiros. Até mesmo porque, a LGDP, prevê sanções para quem não estiver em conformidade com as boas práticas. Elas englobam advertências, multas e no pior dos casos, a proibição, total ou parcial, de atividades relacionadas ao tratamento de dados. As multas podem variar de 2% do faturamento do ano anterior da empresa, até a R$ 50 milhões, passando por penalidades diárias.

Então, diante disso, vamos passar pelos níveis de acesso que cada role, seja Server Role ou Database Role, concede aos usuários.

Mas antes vamos revisar como funciona a criação dos usuários no SQL Server.

No SQL Server temos 2 tipos de logins: Windows Authentication, que são os usuários do Windows ou Active Directory (AD) e login do tipo SQL Server, que são aqueles que criamos somente no SQL Server.

Quando trabalhamos com Windows Authentication é possível criar o acesso para grupos de usuários. O esquema abaixo nos mostra como estão subdivididas as camadas de acesso ao SQL Server.

Quando temos um servidor ingresso em um domino ou não, podemos tanto criar logins tanto utilizando logins do domínio ou usuários locais, quanto, grupos de usuários do AD ou locais.

No nível de instancia temos os temos os logins (AD, Local ou Grupos) e as server roles, onde concedemos os acessos.

No nível de database temos os usuários com ou sem login e as database role.

Até o SQL Server 2008 ou 2008 R2, sempre que precisássemos criar um usuário, primeiro era necessário criar um login na instância e depois associar a uma database. Porém, isso mudou no SQL Server 2012, quando começou a ser possível criar um usuário somente no banco de dados, sem a necessidade de criar um login na instância.

Como podemos observar na imagem acima, estamos em um SQL Server 2008 R2, onde vamos fazer um teste de criar um usuário direto no banco Stackoverflow, sem criar um login.

CREATE USER [tiago]  
WITH PASSWORD='123456'  
    ,DEFAULT_SCHEMA=[dbo]  
GO   

Ao tentar criar o usuário, o SQL retorna um erro de sintaxe incorreta.

Agora, vamos fazer a mesma ação em um SQL Server 2017, porém, vamos criar o usuário sem login na base Northwind.

Para criar um usuário sem login é necessário fazer algumas alterações na base de dados e na instância.

sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'contained database authentication', 1
GO
RECONFIGURE WITH OVERRIDE
GO
ALTER DATABASE Northwind SET CONTAINMENT = PARTIAL

Para ver a documentação completa e as limitações basta acessar o link (https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-databases?view=sql-server-2017).

USE [Northwind]
GO
CREATE USER [tiago] WITH PASSWORD=N'123456', DEFAULT_SCHEMA=[dbo]
GO
USE [Northwind]
GO
ALTER ROLE [db_owner] ADD MEMBER [tiago]
GO

Como podemos observar, o usuário foi criado com sucesso.

Para conectar no Management Studio é necessário informar qual é a database default. Na console de conexão, selecione a opção “Options”.

Nas propriedades de conexão, você deverá informar em qual database você deseja conectar, no caso selecionei “Northwind”

Como podemos ver, conseguimos conectar ao banco de dados e temos acesso somente à database “Northwind”.

Bom, então vimos que a partir do SQL Server 2012 é possível criar um usuário somente no banco de dados sem a necessidade de criar o usuário na instância. Uma grande vantagem de criar os usuários direto no banco de dados é quando nós utilizamos ambiente com o Always On, pois, em caso de failover os usuários já estão na base de dados.

Agora vamos às roles de acesso:

Server Roles

No SQL Server temos 9 roles a nível de servidor (Server Roles). Quando concedemos acessos a essas roles, estamos concedendo acesso a nível de instância, ou seja, as permissões se aplicam a todas as databases.

Para conhecer o nível de acesso de cada role podemos utilizar a procedure de sistema sp_srvrolepermission (‘nome da role’)

Bulkadmin

Essa role é normalmente concedia a usuários que fazem extração de dados ou ETL. Quando trabalhamos com inserção em massa no banco de dados com arquivos externos, além do usuário ter a necessidade de ter permissão de insert na tabela ou no banco de dados, ele também tem a necessidade de ser membro da role Bulkadmin.

EXEC sp_srvrolepermission 'Bulkadmin'

Dbcreator

Como o próprio nome já diz, a role concede a permissão aos seus membros de manipular o status, criar, apagar, renomear e fazer restore das databases. Essa role deve ser concedida com muito cuidado pois, como podemos ver que os membros dessa role podem realizar grandes estragos na instância.

EXEC sp_srvrolepermission 'DBCREATOR'

Diskadmin

Essa role é utilizada basicamente para criar e excluir dispositivos de backups. Ela praticamente não é utilizada no SQL Server. (Para falar a verdade nunca vi um caso prático de utilização dessa role).

EXEC sp_srvrolepermission 'Diskadmin'

Processadmin

Essa role tem o poder de executar kill (matar) nas conexões da instância. Ela, como a diskadmin, praticamente não é utilizada, mas se for, ela deve ser concedida a usuários de confiança.

Através dela podemos até mesmo realizar ataque de negação de serviço, além de poder adicionar outros usuários na role. Então devemos ter muito cuidado ao adicionar membros nessa role.

EXEC sp_srvrolepermission 'Processadmin'

Public

Todo login do SQL Server pertence à role Public, ela tem acesso para conectar na instancia e visualizar os bancos de dados da instância, além de executar instruções de select no banco Master. Devemos tomar muito cuidado pois é bastante comum encontrar ambientes com permissões a Role Public.

Securityadmin

Os membros da role securityadmin gerenciam os logins, podendo criar, dropar e alterar senha. Eles podem ter as permissões de nível de servidor GRANT, DENY e REVOKE. Eles também podem alterar as permissões de nível de banco de dados GRANT, DENY e REVOKE se tiverem acesso ao banco de dados.

Essa role devido ao fato de poder gerenciar logins é um papel poderoso. Ela inclusive pode conseguir se adicionar a role sysadmin ou adicionar outro usuário a role sysadmin. Portanto a participação nessa role deve ser cuidadosamente controlada.

EXEC sp_srvrolepermission 'securityadmin'

Serveradmin

Os membros dessa role podem alterar configurações da instância, dar um SHUTDOWN na instância, ou seja, outra role que você deve evitar conceder acesso. É verdade que é muito raro você encontrar alguém como membro dessa role pois, normalmente as configurações da instância ficam a cargo do DBA, que normalmente é sysadmin.

EXEC sp_srvrolepermission 'serveradmin'

Setupadmin

Os membros da role setupadmin basicamente tem permissão para gerenciar linkedserver. Essa é outra role que praticamente não encontramos em uso, uma vez que normalmente quem cria os linkedserver’s são os DBA’s.

EXEC sp_srvrolepermission 'setupadmin'

Sysadmin

Essa é a role mais importante do banco. Essa é aquela que você nunca, jamais, em hipótese alguma, deve conceder a usuários, usuários de sistemas e consultores externos.

A role sysadmin tem o poder de fazer qualquer ação tanto na instância, quanto nos bancos de dados. Ela pode criar ou dropar usuários, conceder ou remover acessos, enfim ela pode tudo.

É muito comum encontrar em cliente vários usuários com acesso a role sysadmin, inclusive é uma boa prática até mesmo desabilitar o usuário SA.

Para verificar todas as ações do sysadmin, execute o seguinte comando.

EXEC sp_srvrolepermission 'Sysadmin'

Database Roles

As databases roles são as roles de acesso a nível de banco de dados, assim como as server roles, cada database role libera um nível de acesso.

No SQL Server existem 10 databases roles, vejamos o nível de permissão de cada uma.

Db_accessadmin

Os membros da role db_accessadmin tem a permissão de adicionar e remover os usuários em um banco de dados. Essa é um role pouco utilizada, uma vez que os acessos são gerenciados pelo DBA e as aplicações não necessitam gerenciar acesso.

sp_dbfixedrolepermission 'Db_accessadmin'

Db_backupoperator

Essa role tem permissão de executar o backup do banco de dados, porém, somente os backups nativos do SQL Server, se você quiser fazer backup utilizando ferramenta de terceiros você não vai conseguir.

Você deve tomar cuidado com os membros dessa role pois, como o usuário consegue fazer backup da base de dados, ele pode fazer o restore da base em uma instância onde ele tenha acesso full.

sp_dbfixedrolepermission 'Db_backupoperator'

Db_datareader

Os membros dessa role tem a permissão fazer leitura de qualquer tabela ou view da base de dados.

sp_dbfixedrolepermission 'Db_datareader'

Db_datawriter

Os membros dessa role tem a permissão de fazer operações de insert, update e delete em qualquer tabela da base de dados.

sp_dbfixedrolepermission 'Db_datawriter'

DB_ddladmin

Os membros da role tem permissão de criar, alterar e excluir qualquer estrutura do bando de dados. Em resumo, podem executar qualquer comando Data Definition Language (DDL), exceto gerenciar acesso com GRANT, DENY e REVOKE.

sp_dbfixedrolepermission 'DB_ddladmin'

Db_denydatareader

Os membros desta role não podem ler dados de tabelas ou views.

sp_dbfixedrolepermission 'Db_denydatareader'

Db_denydatawriter

Os membros desta role não podem realizar insert, delete ou update em uma tabela.

sp_dbfixedrolepermission 'Db_denydatawriter'

Db_owner

Os membros desta role tem a permissão full no banco de dados, basicamente um sysadmin porém, do banco de dados. Os membros da role db_owner podem fazer select em qualquer tabela, realizar insert, update e delete em qualquer tabela, criar e dropar qualquer objeto do banco, seja tabelas, views, procedures ou functions, executar qualquer procedure ou function e podem gerir os acessos, concedendo Grant, Deny e Revoke.

sp_dbfixedrolepermission 'db_owner'

Db_securityadmin

Os membros desta role podem criar ou apagar logins, além de gerenciar os acessos. Como normalmente quem gerencia os acessos é o DBA e normalmente ele está na role sysadmin ou na role db_owner, essa role é pouco utilizada. Quando você encontrar algum usuários nessa role, vale você tentar entender o motivo e se realmente ele deve ser membro desta role.

sp_dbfixedrolepermission 'Db_securityadmin'

Public

Assim como a server role Public, a database role Public é a role defaul para todo usuário do banco de dados.

Os membros da role Public podem somente conectar no banco e não conseguem executar nenhum comando, a não ser que exista um GRANT implícito para a role Public.

Bom como podemos ver, existem diversos niveis de acessos tanto a nivel de instância (server roles), quanto a nivel de banco de dados (database roles). Conceder os acessos corretos a cada usuário pode fazer que você tenha um grande ganho de segurança.

Bom pessoal, por hoje é isso.

Até o próximo post.

Abraços,

Tiago Neves

Curta a minha página no facebook e fique por dentro das novidades do mundo SQL Server.