Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server - How to use auditing to map actual required permissions on a user

Post Views 3,109 views
Esse post é a parte 10 de 21 da série Security and Auditing
Reading time 6 minutes

Hey guys!
Nesse artigo, vou demonstrar como utilizar auditoria para mapear permissões necessárias reais um usuário, identificando tudo o que esse usuário fez efetivamente no banco de dados durante o tempo observado, para gerar um script concedendo apenas as permissões que ele realmente precisa, eliminando assim, a necessidade de ter usuários de integrações e outros usuários, que não o principal da aplicação, com permissões em todos os objetos do banco, como db_owner, db_datareader e/ou db_datawriter, por exemplo.

Durante o atendimento a clientes, é muito comum presenciar situações onde usuários de integrações entre sistemas ou processos são criados no banco de dados exclusivamente para esse processo (boa prática), mas mesmo precisando acessar/alterar poucas tabelas, eles acabam recebendo permissões em todos os objetos desse database. Ainda mais em usuários de integração, que nem sempre são desenvolvidos pela equipe interna e em muitos casos, o sistema desses usuários de integração não está nem na responsabilidade do time de TI local e podem ser acessados via internet, por exemplo, uma brecha nesse sistema externo pode acabar sendo catastrófico para a sua empresa.

Com o objetivo de mitigar esse problema, vou demonstrar a vocês como logar tudo o que esses usuários fazem no banco de dados e permitir que vocês apliquem somente as permissões necessárias. Para essa necessidade, vou utilizar o recurso de Server Audit, disponível desde o SQL Server 2008 na versão Enterprise e a partir do SQL Server 2012 na versão Standard. Também vou utilizar o recurso Database Audit, disponível na versão Standard apenas a partir do SQL Server 2016 SP1.

Em resumo, para conseguir executar os scripts desse artigo, você precisará atender uma das condições abaixo:

  • SQL Server 2012 ou acima, edição Enterprise, Datacenter (2008) ou Developer
  • SQL Server 2016 SP1 ou acima (qualquer edição, até Express)

Criando a tabela para armazenar o histórico de acessos

Após a introdução acima, vamos agora começar a monitorar os acessos às tabelas. Antes de mais nada, vou criar a tabela que vai armazenar o histórico dos acessos coletados pela auditoria.

Criando a Server Audit filtrando os usuários

O segundo passo para conseguir auditar os acessos realizados por determinados usuários, vamos criar um server audit na instância que será utilizado no próximo tópico para capturar esses eventos. Vale lembrar que o recurso de Server Audit está disponível desde o SQL Server 2008 na versão Enterprise e a partir do SQL Server 2012 na versão Standard. Além disso, só é possível utilizar filtros no Server Audit a partir do SQL Server 2012.

No exemplo abaixo, estou definindo que a auditoria irá criar até 16 arquivos de 10 MB cada. A medida que os arquivos vão sendo preenchidos, novos arquivos com os dados coletados serão criados até atingir o limite estabelecido (16 arquivos). Quando todos os arquivos já tiverem sido criados e já estão totalmente preenchidos, aí os arquivos mais antigos começam a ser sobrescritos com as novas informações. Por este motivo, é importante ter uma rotina para coletar dados dos arquivos e armazenar em tabelas.

Além disso, a partir do SQL Server 2012, podemos aplicar diversos filtros para refinar nossas buscas, como capturar os dados dos usuários que terminem com ‘%User’ ou que começem com LS_% e também ignorar o Intellisense do SSMS e ferramentas da RedGate, além de não coletar dados de usuários que estejam no domínio “MEUDOMINIO” e também não coletar dados do usuário “usrDirceuResende”.

Nessa etapa que podemos filtrar os nomes de usuários, softwares, IP’s ou Hostnames que serão utilizados para refinar as pesquisas para retornar apenas os dados desejados.

Criando a Database Audit capturando os acessos

No terceiro passo para montar nosso monitoramento, vamos agora criar uma database audit para cada banco de dados que você queira monitorar as permissões. No exemplo abaixo, vou monitorar os eventos de INSERT/DELETE/UPDATE/SELECT/EXECUTE realizados por qualquer usuário do banco (public).

Vale lembrar que, embora a Database Audit pareça capturar os eventos de todos os usuários, na Server Audit nós já limitamos os usuários que devem ser retornados e esse filtro é respeitado também no Database Audit.

O código abaixo vai percorrer todos os databases que não estejam na lista de exceções (master, tempdb, msdb e model) e vai criar a database audit.

Criando a Stored Procedure para armazenar os dados coletados

No 4º passo da rotina, vamos criar a Stored Procedure responsável por ler os dados dos arquivos de auditoria e grava os dados na tabela criada no passo 1. Lembre-se de criar um job no SQL Agent para executar a procedure abaixo a cada X minutos.

Consultando os dados coletados

E por fim, no 5º passo dessa rotina, vamos ler os dados coletados e armazenados na tabela dirceuresende.dbo.Auditoria_Acesso e vamos identificar os acessos realizados por cada usuário em cada database. Isso o ajudará a identificar o que esses usuários estão acessando atualmente no banco de dados. Acabou a desculpa para todos os usuários serem db_owner das databases.

Caso você queira também gerar os scripts para conceder as permissões atuais, você pode utilizar esse script:

Result:

Pronto! Rotina de auditoria de acessos implementada. O próximo passo agora, é utilizar os dados coletados para conceder as permissões que os usuários efetivamente utilizaram durante o tempo em que o Audit ficou habilitado e remover permissões elevadas desses usuários, como sysadmin e db_owner.

Observation: Um ponto muito importante que deve ser analisado, é a quantidade de registros que essa tabela irá manter. Em ambientes que possuem muitos usuários simultaneos, essa coleta de dados de acesso pode acabar gerando um volume de dados muito grande e consumindo muito espaço em disco. Por conta disso, é sempre importante acompanhar o volume de dados e criar uam rotina para limpeza da tabela, mantendo apenas X dias de histórico.

Além disso, não preciso nem falar que recomendo que você TESTE bastante antes de aplicar em produção né ? Dependendo da quantidade de transações por segundo, isso pode até gerar um impacto de performance ao ativar essa feature (embora eu já tenha testado em ambientes bem críticos e não tenha percebido nenhuma mudança).

Bom pessoal, espero que tenha ajudado vocês a ter uma forma de validar todos os objetos acessados por determinados usuários, auxiliando vocês no mapeamento das permissões necessárias.
Um grande abraço e até mais!