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.
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
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: trgAudit_Login
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
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: 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
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: 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
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: trgAudit_Login5
trgAudit_Login6
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
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!
Dirceu Resende
Arquiteto de Banco de Dados e BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.
Comentários (0)
Carregando comentários…