SQL Server – Como implementar auditoria e controle de logins (Trigger de Logon)

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.

Caso você queira saber sobre Auditoria de manipulação (Criação, alteração e exclusão) de objetos, veja mais no post Como criar uma trigger de Auditoria para logar a manipulação de objetos no SQL Server

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:
trgAudit_Login

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:
trgAudit_Login5

trgAudit_Login2

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:
trgAudit_Login5

trgAudit_Login3

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:
trgAudit_Login5

trgAudit_Login6

É isso aí, pessoal!
Até o próximo post!

SQL, sql server, sql server 2008, sql server 2008 R2, Oracle, Oracle Database, Oracle 11g, Oracle 10g, Oracle 12c, MySQL, Firebird, Consultoria, Consultor, Programador, Programação. Desenvolvedor, Analista de Sistemas, DBA, Criação de website, Criação de Sistema Web, Vitória, Vila Velha, Guarapari, Espírito Santo, ES, Consultoria SQL em VItória, Treinamento, Curso, Prestação de serviço, prestar serviço, freelancer, freela, banco de dados, consultoria em banco de dados, consultor de banco de dados

2 comentários em “SQL Server – Como implementar auditoria e controle de logins (Trigger de Logon)

  1. Olá esta aparecendo erros no código SQL

    Server: Msg 156, Level 15, State 1, Line 2
    Incorrect syntax near the keyword ‘ON’.
    Server: Msg 156, Level 15, State 1, Procedure trgAudit_Login, Line 2
    Incorrect syntax near the keyword ‘ALL’.
    Server: Msg 195, Level 15, State 1, Procedure trgAudit_Login, Line 10
    ‘ORIGINAL_LOGIN’ is not a recognized function name.
    Server: Msg 195, Level 15, State 1, Procedure trgAudit_Login, Line 50
    ‘EVENTDATA’ is not a recognized function name.
    Server: Msg 170, Level 15, State 1, Procedure trgAudit_Login, Line 53
    Line 53: Incorrect syntax near ‘.’.
    Server: Msg 156, Level 15, State 1, Line 2
    Incorrect syntax near the keyword ‘TRIGGER’.

    Qual a correção ?

Deixe uma resposta