Visualizações: 8.293 views
Tempo de Leitura: 8 minutosOlá 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 |
USE [master] GO IF ((SELECT COUNT(*) FROM sys.server_triggers WHERE name = 'trgAudit_Login') > 0) DROP TRIGGER [trgAudit_Login] ON ALL SERVER GO CREATE TRIGGER [trgAudit_Login] ON ALL SERVER FOR LOGON AS BEGIN SET NOCOUNT ON -- Não loga conexões de usuários de sistema IF (ORIGINAL_LOGIN() IN ('sa', 'AUTORIDADE NT\SISTEMA', 'NT AUTHORITY\SYSTEM') OR ORIGINAL_LOGIN() LIKE '%SQLServerAgent') RETURN -- Não loga conexões de softwares que ficam se conectando constantemente IF (PROGRAM_NAME() LIKE 'Red Gate%' OR PROGRAM_NAME() LIKE '%IntelliSense%' OR PROGRAM_NAME() = 'Microsoft SQL Server') RETURN IF (OBJECT_ID('Auditoria.dbo.Logins') IS NULL) BEGIN -- DROP TABLE Auditoria.dbo.Logins CREATE TABLE Auditoria.dbo.Logins ( Id_Auditoria INT IDENTITY(1,1), Dt_Evento DATETIME, SPID SMALLINT, Ds_Usuario VARCHAR(100) NULL, Ds_Usuario_Original VARCHAR(100) NULL, Ds_Tipo_Usuario VARCHAR(30) NULL, Ds_Ip VARCHAR(30) NULL, Ds_Hostname VARCHAR(100) NULL, Ds_Software VARCHAR(500) NULL ) CREATE CLUSTERED INDEX SK01 ON Auditoria.dbo.Logins(Id_Auditoria) END DECLARE @Evento XML, @Dt_Evento DATETIME, @Ds_Usuario VARCHAR(100), @Ds_Usuario_Original VARCHAR(100), @Ds_Tipo_Usuario VARCHAR(30), @Ds_Ip VARCHAR(30), @SPID SMALLINT, @Ds_Hostname VARCHAR(100), @Ds_Software VARCHAR(100) SET @Evento = EVENTDATA() SELECT @Dt_Evento = @Evento.value('(/EVENT_INSTANCE/PostTime/text())[1]','datetime'), @Ds_Usuario = @Evento.value('(/EVENT_INSTANCE/LoginName/text())[1]','varchar(100)'), @Ds_Tipo_Usuario = @Evento.value('(/EVENT_INSTANCE/LoginType/text())[1]','varchar(30)'), @Ds_Hostname = HOST_NAME(), @Ds_Ip = @Evento.value('(/EVENT_INSTANCE/ClientHost/text())[1]','varchar(100)'), @SPID = @Evento.value('(/EVENT_INSTANCE/SPID/text())[1]','smallint'), @Ds_Software = PROGRAM_NAME() -- Identifica o usuário original caso seja um usuário SQL IF (LEFT(@Ds_Tipo_Usuario, 7) != 'Windows') BEGIN SELECT @Ds_Usuario_Original = ( SELECT A.Ds_Usuario FROM Auditoria.dbo.Logins A JOIN ( SELECT Ds_Hostname, MAX(Id_Auditoria) AS Id_MAX FROM Auditoria.dbo.Logins WITH(NOLOCK) WHERE Ds_Tipo_Usuario LIKE 'Windows%' GROUP BY Ds_Hostname ) B ON A.Ds_Hostname = B.Ds_Hostname AND A.Id_Auditoria = B.Id_MAX ) END -- Evita gravar várias vezes um mesmo login DECLARE @Dt_Ultima_Data DATETIME SELECT @Dt_Ultima_Data = MAX(Dt_Evento) FROM Auditoria.dbo.Logins WHERE Ds_Usuario = @Ds_Usuario AND SPID = @SPID IF (DATEDIFF(SECOND, ISNULL(@Dt_Ultima_Data, '1990-01-01'), @Dt_Evento) > 1) BEGIN INSERT INTO Auditoria.dbo.Logins SELECT GETDATE(), @SPID, @Ds_Usuario, @Ds_Usuario_Original, @Ds_Tipo_Usuario, @Ds_Ip, @Ds_Hostname, @Ds_Software END END GO ENABLE TRIGGER [trgAudit_Login] ON ALL SERVER GO USE [Auditoria] GO GRANT SELECT, INSERT ON dbo.Logins TO [public] GO |
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:
|
|
CREATE TRIGGER [trgAudit_Login] ON ALL SERVER WITH EXECUTE AS 'dirceu.resende' FOR LOGON [...] |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
USE [master] GO IF ((SELECT COUNT(*) FROM sys.server_triggers WHERE name = 'trgBloquear_Login') > 0) DROP TRIGGER [trgBloquear_Login] ON ALL SERVER GO CREATE TRIGGER [trgBloquear_Login] ON ALL SERVER FOR LOGON AS BEGIN -- Não elimina conexões de usuários de sistema IF (ORIGINAL_LOGIN() IN ('sa', 'AUTORIDADE NT\SISTEMA', 'NT AUTHORITY\SYSTEM')) RETURN DECLARE @Evento XML, @Dt_Evento DATETIME, @Ds_Usuario VARCHAR(100), @Ds_Usuario_Original VARCHAR(100), @Ds_Tipo_Usuario VARCHAR(30), @Ds_Ip VARCHAR(30), @SPID SMALLINT, @Ds_Hostname VARCHAR(100), @Ds_Software VARCHAR(100) SET @Evento = EVENTDATA() SELECT @Dt_Evento = @Evento.value('(/EVENT_INSTANCE/PostTime/text())[1]','datetime'), @Ds_Usuario = @Evento.value('(/EVENT_INSTANCE/LoginName/text())[1]','varchar(100)'), @Ds_Tipo_Usuario = @Evento.value('(/EVENT_INSTANCE/LoginType/text())[1]','varchar(30)'), @Ds_Hostname = HOST_NAME(), @Ds_Ip = @Evento.value('(/EVENT_INSTANCE/ClientHost/text())[1]','varchar(100)'), @SPID = @Evento.value('(/EVENT_INSTANCE/SPID/text())[1]','smallint'), @Ds_Software = PROGRAM_NAME() IF (@Ds_Usuario IN ('Usuario_Teste')) BEGIN PRINT 'Usuário não permitido para logar neste servidor. Favor entrar em contato com a equipe de Banco de Dados' ROLLBACK END IF (@Ds_Tipo_Usuario = 'SQL Login') BEGIN PRINT 'Usuários SQL não são permitidos nesse servidor. Favor entrar em contato com a equipe de Banco de Dados' ROLLBACK END END GO ENABLE TRIGGER [trgBloquear_Login] ON ALL SERVER GO |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
USE [master] GO IF ((SELECT COUNT(*) FROM sys.server_triggers WHERE name = 'trgBloquear_Login_Horario') > 0) DROP TRIGGER [trgBloquear_Login_Horario] ON ALL SERVER GO CREATE TRIGGER [trgBloquear_Login_Horario] ON ALL SERVER FOR LOGON AS BEGIN -- Não elimina conexões de usuários de sistema IF (ORIGINAL_LOGIN() IN ('sa', 'AUTORIDADE NT\SISTEMA', 'NT AUTHORITY\SYSTEM')) RETURN IF (DATEPART(WEEKDAY, GETDATE()) IN (0, 7)) BEGIN PRINT 'Conexões aos fins de semana não são permitidas neste servidor' ROLLBACK RETURN END IF (DATEPART(HOUR, GETDATE()) >= 18 OR DATEPART(HOUR, GETDATE()) < 8) BEGIN PRINT 'Conexões antes das 8h e depois das 18h não são permitidas neste servidor' ROLLBACK RETURN END END GO ENABLE TRIGGER [trgBloquear_Login_Horario] ON ALL SERVER GO |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
USE [master] GO IF ((SELECT COUNT(*) FROM sys.server_triggers WHERE name = 'trgBloquear_Login_Sessoes') > 0) DROP TRIGGER [trgBloquear_Login_Sessoes] ON ALL SERVER GO CREATE TRIGGER [trgBloquear_Login_Sessoes] ON ALL SERVER WITH EXECUTE AS SELF FOR LOGON AS BEGIN -- Não elimina conexões de usuários de sistema IF (ORIGINAL_LOGIN() IN ('sa', 'AUTORIDADE NT\SISTEMA', 'NT AUTHORITY\SYSTEM')) RETURN -- Verifica se o usuário é sysadmin DECLARE @IsSysAdmin int EXECUTE AS CALLER SET @IsSysAdmin = ISNULL(IS_SRVROLEMEMBER('sysadmin'), 0) REVERT IF (@IsSysAdmin = 0) BEGIN IF (( SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND login_name = ORIGINAL_LOGIN() AND [program_name] NOT LIKE 'Red Gate%' AND [program_name] NOT LIKE '%IntelliSense%' ) > 2) BEGIN PRINT 'Número máximo de conexões atingidas para este usuário neste servidor' ROLLBACK RETURN END END END GO ENABLE TRIGGER [trgBloquear_Login_Sessoes] ON ALL SERVER GO |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
USE [master] GO IF ((SELECT COUNT(*) FROM sys.server_triggers WHERE name = 'trgEvita_Conexao_SQL') > 0) DROP TRIGGER [trgEvita_Conexao_SQL] ON ALL SERVER GO CREATE TRIGGER [trgEvita_Conexao_SQL] ON ALL SERVER WITH EXECUTE AS 'dirceu.resende' FOR LOGON AS BEGIN SET NOCOUNT ON -- Não loga conexões de usuários de sistema IF (ORIGINAL_LOGIN() IN ('dirceu.resende', 'sa', 'AUTORIDADE NT\SISTEMA', 'NT AUTHORITY\SYSTEM') OR ORIGINAL_LOGIN() LIKE '%SQLServerAgent') RETURN -- Não loga conexões de softwares que ficam se conectando constantemente IF (PROGRAM_NAME() LIKE 'Red Gate%' OR PROGRAM_NAME() LIKE '%IntelliSense%' OR PROGRAM_NAME() = 'Microsoft SQL Server') RETURN DECLARE @Evento XML, @Ds_Tipo_Usuario VARCHAR(30) SET @Evento = EVENTDATA() SET @Ds_Tipo_Usuario = @Evento.value('(/EVENT_INSTANCE/LoginType/text())[1]','varchar(30)') -- Identifica o usuário original caso seja um usuário SQL IF (LEFT(@Ds_Tipo_Usuario, 7) != 'Windows' AND (PROGRAM_NAME() LIKE 'Microsoft SQL Server Management Studio%' OR program_name() LIKE 'sqlcmd%')) BEGIN SELECT 1/0 END END GO ENABLE TRIGGER [trgEvita_Conexao_SQL] ON ALL SERVER GO |
É isso aí, pessoal!
Até o próximo post!
Portuguese 
English
Fala grande Dirceu!
Primeiramente obrigado pelo seu tempo disponibilizado para realizar esse trabalho de compartilhar conhecimento, sensacional!
Antes de implementar a trigger de logon, geralmente eu crio uma trigger que somente monitora, quem e como estão chegando na instância, assim, eu consigo ter uma percepção mais ampla do que ocorre antes que a trigger que irá bloquear os acessos seja de fato implementada!
O que me ocorre, é que, em alguns casos, quando eu implemento a trigger de monitoração ela gera alguns bloqueios em alguns acessos, e essa trigger só monitora e armazena as informações do logon em uma tabela!
Por qual motivo isso ocorre?
Obrigado, pelo post.
Valeu, Akira!! Obrigado
Obrigado amigo, encontrei a solução perfeita para o que precisava.
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 ?
Alex, obrigado pela visita. O comando CREATE TRIGGER pode ser utilizado a partir do SQL Server 2008. Caso a sua versão seja anterior a esta, não vai funcionar mesmo.. 🙁