Trigger DDL Logon – Bloqueando acesso de SQL Logins através do SSMS

Bom dia pessoal,

Vamos falar de um assunto um tanto corriqueiro nas instituições hoje. Sabe aquele problema: “Poxa! O cara do desenvolvimento logou com o usuário da aplicação (SQL Login) no SQL Server Management Studio (SSMS), apagou registros de uma tabela de produção importantíssima e tive subir um point in time de uma base de 100 GB, que gerou aquele estresse!”.                                                                                                                                                     Uma solução simples seria a mudança da senha de tal usuário:

ALTER LOGIN NomeLogin WITH PASSWORD = 'SenhaForte', CHECK_POLICY =OFF, CHECK_EXPIRATION = OFF

Mas como existem várias aplicações em suas “string connections” utilizando aquela senha, seria uma tarefa um tanto inviável.                                                                                                                                                                                                                                                                             O que fazer então?  Vou ficar a míngua do desenvolvedor? Oh! E agora quem poderá me defender?

Eu! O SQL Colorado!!!!!!!!! O desenvolvedor, não contava com a minha astúcia!!!!! kkkkkkkkkkkkkkkkkkkkkkkk

CHAUP

Deixando de lado as brincadeiras vamos a solução: Trigger DDL!                                                                                                                                                                                                                       Passo a passo!

01. Crie a tabela dbo.TBAuditoriaUsuarios:

USE master /*Ou o banco administrativo que desejar*/
GO
CREATE TABLE [dbo].[TBAuditoriaUsuarios](
 [IDAuditoriaUsuarios] [int] IDENTITY(1,1) NOT NULL,
 [SPID] [int] NULL,
 [DataEvento] [smalldatetime] NULL,
 [NomeHost] [varchar](300) NULL,
 [NomePrograma] [varchar](300) NULL,
 [NomeLogin] [varchar](50) NULL,
 CONSTRAINT [PK_IDAuditoriaUsuarios] PRIMARY KEY CLUSTERED
([IDAuditoriaUsuarios] ASC)ON [PRIMARY])

02. Crie a Trigger DDL -> TR_USERS_AUDIT :

CREATE TRIGGER [TR_USERS_AUDIT]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN

/*No ORIGINAL_LOGIN você vai definir a nomenclatura padrão dos "Logins" do seu ambiente*/
IF (ORIGINAL_LOGIN() LIKE 'User%') AND APP_NAME() LIKE 'Microsoft SQL Server Management Studio%' 

 BEGIN

 ROLLBACK;

 /*Coleta na tabela dbo.TBAuditoriaUsuarios a tentativa de acesso*/
 INSERT INTO master.dbo.TBAuditoriaUsuarios
 SELECT @@SPID,GETDATE(),HOST_NAME(),APP_NAME(),ORIGINAL_LOGIN()

 /*Mensagem de Erro que aparecerá na sp_readerrorlog*/ 

 PRINT 'A Trigger DDL "TR_USERS_AUDIT" bloqueou o acesso do HostName '+HOST_NAME()+ ' logando com o Login '+ORIGINAL_LOGIN()

 END

END

/*
ENABLE TRIGGER [TR_USERS_AUDIT] ON ALL SERVER
DISABLE TRIGGER [TR_USERS_AUDIT] ON ALL SERVER
*/

All right! Agora faça os testes em seu ambiente:

CREATE LOGIN UserTeste WITH PASSWORD = '123', CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Agora aparecerá a seguinte mensagem de erro no SSMS:

Erro Acesso

Confira o acesso indevido na tabela de auditoria dbo.TBAuditoriaUsuarios.

Apague o usuário de teste UserTeste:


DROP LOGIN UserTeste;

No exemplo acima a Trigger DDL Logon bloqueou o acesso de usuários que começam com ‘User%’ no programa ‘Microsoft SQL Server Management Studio%’.

Pessoal, basicamente é isso!!! Agradeço a todos!!

Jesus bless everybody!!!!

Anúncios

5 comentários sobre “Trigger DDL Logon – Bloqueando acesso de SQL Logins através do SSMS

  1. Bem legal. Tem nego que é mala, mesmo às vezes não sendo permitido esse comportamento em produção (e faz todo sentido, imho), loga com o usuário da aplicação “só no sapatinho” e se não tiver auditoria a chance de dar problema é grande/alta, é mais questão de tempo mesmo pro telefone tocar.
    De várias triggers anti-login essa é sem dúvida uma das mais úteis.
    []’s

  2. Outra coisa legal que não coloquei no blog desta trigger é que ela está na severidade 20. Seria interessante habilitar a severidade 20 no Agent e colocar para enviar o e-mail (Operador) para você. O bom é pegar na hora o carinha tentando logar.Ai valida na tabela master.dbo.TBAuditoriaUsuarios e na sp_readerrorlog… kkkkkkkkkkkkkk

  3. Renata

    Precisava acrescentar o IP da maquina no log, como posso fazer?

    INSERT INTO master.dbo.TBAuditoriaUsuarios
    SELECT @@SPID,GETDATE(),HOST_NAME(),APP_NAME(),ORIGINAL_LOGIN()

    1. Olá Renata!!! Então você pode utilizar o seguinte parâmetro abaixo:

      SELECT
      CONNECTIONPROPERTY(‘local_net_address’) AS ip_instancia_sql,
      CONNECTIONPROPERTY(‘client_net_address’) AS ip_host_name_origem

      Espero que tenha ajudado!!! =)

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 )

Foto do Google+

Você está comentando utilizando sua conta Google+. 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 )

Conectando a %s