TRIGGER DE LOGON

E ai galerinha, tudo bem com vocês?

Recentemente tive um grande problema de performance devido à conexões de pessoas físicas utilizando os logins de aplicação pelo SSMS e conectando diretamente em PRODUÇÃO.

Para resolver esse problema podemos utilizar a funcionalidade conhecida como TRIGGER DE LOGON.

Uma trigger de logon é disparada quando ocorre uma tentativa de autenticação em uma instância de SQL Server, mas antes da conexão ser efetivamente estabelecida.

Quando algum usuário tentar conectar em uma instância de banco de dados SQL Server, a trigger de logon será disparada e executará algum procedimento programado pelo DBA antes do usuário conseguir finalizar a conexão com a instância.

Vamos imaginar o seguinte cenário para facilitar o entendimento.

Temos uma aplicação chamada App1 que utiliza o login APP para conectar em uma instância de banco de dados SQL Server.

O login APP não pode ser utilizado através da ferramenta SSMS, mesmo que um usuário físico saiba a senha deste login.

Se houver uma tentativa de conexão com o login APP através da ferramenta SSMS, o usuário receberá um erro e a conexão será finalizada.

Para que isso seja possível utilizaremos o seguinte script:

USE [master]
GO
CREATE TRIGGER [TR_LOGON_AUDIT]
ON ALL SERVER
FOR LOGON
AS
BEGIN

IF (ORIGINAL_LOGIN() = ‘APP’) AND APP_NAME() LIKE ‘Microsoft SQL Server Management Studio%’
BEGIN
ROLLBACK
END
END

Como podem perceber, verificamos se o resultado da função ORIGINAL_LOGIN() é igual ao login APP e também se o resultado da função APP_NAME() é igual à  ‘Microsoft SQL Server Management Studio’.

Caso essas condições sejam verdadeiras forçamos o processo de ROLLBACK na conexão e a mesma é finalizada.

Espero que tenham gostado da dica galerinha.

Grande abraço e até o próximo post.

Anúncios
  1. Gustavo Silva
    05/06/2013 às 8:14

    AAAAAEEEEEWWWW Tio!!! A tão falada trigger de logon… Muito legal tio!!! Parabéns!!!

  2. Ariel
    05/06/2013 às 11:45

    Muito boa a dica, apenas um alerta para o pessoal que quiser testa-la/implementa-la: Muito cuidado se algum erro de lógica no código ocorrer seu serviço ficará indisponível para novas autenticações, ou seja, “quem ta dentro fica, quem ta fora não entra”, e a unia maneira que conheço de reverter isto, caso eu ou alguém com a permissão CONTROL SERVER não estiver autenticado é realizar um restore do master.

  3. Anderson Souza
    09/10/2013 às 14:24

    Vitor, implementei uma opções a mais em sua trigger de logon e tudo blz, o problema esta quando vou implantar a mesma no servidor B e tento gravar os dados no servidor A através de linked server, o mesmo indica o erro 3969 (Distribution Transaction is not supported), você teria alguma ideia de como contornar isso?
    obrigado.

    • Tiago
      21/10/2013 às 13:03

      Fala cara, eu implementei isso aqui na empresa também, só fiz umas alterações para liberar os acessos para usuários que são DBA e inserir em uma tabela para auditoria, todo mundo que for bloqueado. Nunca tive problemas com linked server, e olha que no meu ambiente são muitas instancias e todas de produção tem trigger de logon.. Tenta dar uma revisada no código 😀
      Abraço

  4. Filippe Salmi
    14/11/2013 às 15:11

    aeeee vitão, sensacional essa dica… vou aplicar aqui na empresa tbm… abraços

  1. 05/06/2013 às 9:05

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: