SQL Server - How to implement login auditing and control (Logon Trigger)

Olá pessoal,
Boa tarde!

Neste post irei demonstrar pra vocês, alguns recursos legais de triggers de logon como criar um log de auditoria para cada usuário que se conecta no seu banco de dados, bloquear conexões vindas de um usuário/IP/Hostname e bloquear conexões em um determinado horário.

AVISO

Antes de mais nada, gostaria de alertá-los sobre o PERIGO ao se utilizar trigger de logon. Tenha em mente, que essa trigger será executada toda vez que uma nova conexão for aberta no banco de dados. Além da possibilidade de deixar o processo de logon mais lento, de acordo com o código da sua trigger, caso o usuário não tenha permissão para executar alguma operação da trigger ou mesmo caso a trigger tenha algum erro, você pode impedir que TODOS os usuários da instância se conectem ao SQL Server.

Ou seja, TOMEM MUITO CUIDADO, pois você pode parar a instância caso ative uma trigger sem antes testá-la muito bem. Antes de habilitar uma trigger de logon, sempre recomendo ativar a conexão DAC, para garantir que você consiga se conectar na instância caso a trigger apresente problemas. Uma outra dica, é sempre deixar um usuário na lista de exceções (como o “sa” do exemplo) para o caso da trigger ter algum erro, ser mais fácil conectar na instância para dropar a trigger.

Para saber fazer isso, visite o meu artigo Habilitando e utilizando a conexão remota dedicada para administrador (DAC) no SQL Server.

Se você já criou a trigger, ela está impedindo a conexão de usuários, você não ativou a conexão DAC e já está com problemas para se logar na instância, uma alternativa é adicionar o parâmetro -f na inicialização do serviço do SQL Server para iniciar o SQL Server com configuração mínima, dropar a trigger e reiniciar o serviço sem o -f.

Auditando e registrando Logins realizados

Neste trecho de código irei demonstrar como criar um log de conexões para auditoria dos usuários que se conectam na sua base de dados.

Adicionei alguns filtros para evitar logar os usuários de sistema (Ex: SA), conexões provenientes de softwares que ficam constantemente conectando no banco (Ex: RedGate SQL Prompt e o Intellisense do Managment Studio).

Adicionei também um recurso para tentar identificar o nome do usuário do AD que está logando no banco de dados usando um usuário SQL. Não conheço uma maneira de fazer isso de forma 100%, então eu recupero o último usuário AD que se conectou nesse hostname em que está o usuário SQL.

Por último, adicionei um outro filtro para evitar gravar várias linhas repetidas, verificando se já foi gravado em um intervalo de 1h, uma conexão com o mesmo usuário, hostname e SPID.

Implementação:
Visualizar código-fonte

Resultados:

Lembrem-se de revisar muito bem o nome das tabelas dessa trigger quando for implementar no seu ambiente. Caso contrário, você provavelmente vai criar uma trigger “bugada” e vai impedir o logon dos usuários na sua instância, conforme a mensagem de erro abaixo:

Uma outra observação nesse código, é que essa trigger grava dados em algumas tabelas no banco, ou seja, o usuário que for conectar no banco precisará de permissões para gravar os dados nessa tabela, além de ter o usuário criado no database dessa tabela. Por este motivo, adicionei o comando de grant na tabela para a role public.

Uma outra forma de contornar isso, é utilizar a cláusula EXECUTE AS ‘login_com_permissao’, de modo que a trigger será executada com a permissão desse usuário do EXECUTE AS, mas vai gravar os dados do usuário real que está se contando, evitando a necessidade de ter que criar todos os usuários no database e liberar as permissões, ficando desta forma:

PS: Se for seguir essa abordagem, lembre-se de escolher um usuário com todas as permissões necessárias pelas operações dessa trigger (ex: usuário membro da role sysadmin). Caso contrário, sua trigger irá apresentar erros e impedir novas conexões ao banco de dados (leia-se CAOS)

Impedindo o Login de determinados usuários

Agora vou demonstrar a vocês, como limitar o acesso de alguns usuários específicos no banco de dados. Isso pode ser aplicado também, a IP’s ou Hostnames específicos, criando uma lista de permitidos ou de negações.

Implementação:
Visualizar código-fonte

Resultados:

Impedindo login em um determinado horário

Neste trecho abaixo, vou demonstrar como bloquear conexões fora de horários comerciais.

Implementação:
Visualizar código-fonte

Resultados:

Limitando o numero de conexões máximas do usuário

No trecho de código abaixo, irei demonstrar como limitar o número de conexões simultâneas dos usuários. Você pode alterar o código para limitar apenas para determinados usuários, dependendo da sua necessidade.

Reparem que nessa trigger, eu coloquei uma excessão para não limitar as conexões de usuários que sejam sysadmin, que é o caso dos DBA’s. Isso pode ser utilizado nas outras triggers também, se você achar interessante.

Implementação:
Visualizar código-fonte

Resultados:

Impedindo o Login de usuários SQL com SSMS

Agora vou demonstrar a vocês, como impedir que usuários com autenticação SQL Server se conecte no ambiente utilizando o SQL Server Management Studio ou o SQLCMD. Isso é muito útil em ambientes onde os desenvolvedores e DBA’s acessam o banco utilizando usuários com autenticação Windows e as aplicações utilizam usuários com autenticação SQL Server.

Nesse cenário, é muito comum que os desenvolvedores utilizem o usuário da aplicação para aplicar comandos em ambientes de produção, onde o usuário deles não possui permissões de escrita. Essa trigger pode ser uma boa pedida para impedir que eles tentem utilizar essas alterações utilizando o usuário da aplicação ao invés de solicitar ao DBA.

Implementação:
Visualizar código-fonte

And that's it, folks!
Até o próximo post!