En esta publicación, le demostraré algunas características interesantes de los activadores de inicio de sesión, como crear un registro de auditoría para cada usuario que se conecta a su base de datos, bloquear conexiones provenientes de un usuario/IP/nombre de host y bloquear conexiones en un momento determinado.
AVISO
En primer lugar, me gustaría advertirle sobre el PELIGRO al utilizar un activador de inicio de sesión. Tenga en cuenta que este disparador se ejecutará cada vez que se abra una nueva conexión en la base de datos. Además de la posibilidad de ralentizar el proceso de inicio de sesión, según su código de activación, si el usuario no tiene permiso para realizar ninguna operación de activación o incluso si el activador tiene un error, puede evitar que TODOS los usuarios de la instancia se conecten a SQL Server.
En otras palabras, TEN MUCHO CUIDADO, ya que puedes detener la instancia si activas un disparador sin antes probarlo muy bien. Antes de habilitar un activador de inicio de sesión, siempre recomiendo activar la conexión DAC para garantizar que pueda conectarse a la instancia si el activador tiene problemas. Otro consejo es dejar siempre un usuario en la lista de excepciones (como “sa” en el ejemplo) en caso de que el disparador tenga un error, será más fácil conectarse a la instancia para eliminar el disparador.
Si ya creaste el disparador, está impidiendo que los usuarios se conecten, no has activado la conexión DAC y ya estás teniendo problemas para iniciar sesión en la instancia, una alternativa es agregar el parámetro -f al iniciar el servicio SQL Server para Inicie SQL Server con una configuración mínima., suelte el disparador y reinicie el servicio sin -f.
Auditoría y registro de inicios de sesión realizados
En este fragmento de código, demostraré cómo crear un registro de conexión para auditar a los usuarios que se conectan a su base de datos.
Agregué algunos filtros para evitar el inicio de sesión de usuarios del sistema (Ej: SA), conexiones provenientes de software que se conectan constantemente a la base de datos (Ej: RedGate SQL Prompt e Intellisense de Managment Studio).
También agregué una función para intentar identificar el nombre del usuario de AD que inicia sesión en la base de datos utilizando un usuario de SQL. No conozco una manera de hacer esto al 100%, así que recupero el último usuario de AD que se conectó a ese nombre de host donde está el usuario de SQL.
Finalmente, agregué otro filtro para evitar registrar múltiples líneas repetidas, verificando si ya se ha registrado una conexión con el mismo usuario, nombre de host y SPID en un intervalo de 1h.
Implementación: Ver código fuente
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
Recuerde revisar cuidadosamente el nombre de las tablas en este activador cuando lo implemente en su entorno. De lo contrario, probablemente creará un activador "con errores" e impedirá que los usuarios inicien sesión en su instancia, como se ve en el siguiente mensaje de error:
Otra observación en este código es que este disparador escribe datos en algunas tablas de la base de datos, es decir, el usuario que se conecta a la base de datos necesitará permisos para escribir datos en esta tabla, además de tener el usuario creado en la base de datos de esa tabla. Por este motivo, agregué el comando de concesión a la tabla para el rol público.
Otra forma de evitar esto es usar la cláusula EXECUTE AS ‘login_com_permissao’, de modo que el disparador se ejecutará con el permiso de ese usuario EXECUTE AS, pero registrará los datos del usuario real que está contando, evitando la necesidad de crear todos los usuarios en la base de datos y liberar los permisos, quedando así:
CREATE TRIGGER [trgAudit_Login] ON ALL SERVER
WITH EXECUTE AS 'dirceu.resende'
FOR LOGON
[...]
PD: si va a seguir este enfoque, recuerde elegir un usuario con todos los permisos necesarios para las operaciones de este activador (por ejemplo, usuario miembro del rol de administrador de sistemas). De lo contrario, su disparador presentará errores e impedirá nuevas conexiones a la base de datos (lea CAOS)
Evitar que ciertos usuarios inicien sesión
Ahora les demostraré cómo limitar el acceso de algunos usuarios específicos a la base de datos. Esto también se puede aplicar a IP o nombres de host específicos, creando una lista de permitidos o denegados.
Implementación: Ver código fuente
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
Impedir el inicio de sesión en un momento determinado
En este extracto a continuación, demostraré cómo bloquear conexiones fuera del horario comercial.
Implementación: Ver código fuente
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
Limitar el número máximo de conexiones de usuario
En el fragmento de código a continuación, demostraré cómo limitar la cantidad de conexiones simultáneas de los usuarios. Puede cambiar el código para limitarlo solo a ciertos usuarios según sus necesidades.
Tenga en cuenta que en este activador, agregué una excepción para no limitar las conexiones de los usuarios que son administradores de sistemas, como es el caso de los administradores de bases de datos. Esto también se puede utilizar en otros desencadenantes, si lo encuentra interesante.
Implementación: Ver código fuente
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
Evitar que los usuarios de SQL inicien sesión con SSMS
Ahora le demostraré cómo evitar que los usuarios con autenticación de SQL Server se conecten al entorno mediante SQL Server Management Studio o SQLCMD. Esto es muy útil en entornos donde los desarrolladores y DBA acceden a la base de datos utilizando usuarios con autenticación de Windows y las aplicaciones usan usuarios con autenticación de SQL Server.
En este escenario, es muy común que los desarrolladores utilicen al usuario de la aplicación para aplicar comandos en entornos de producción, donde su usuario no tiene permisos de escritura. Este activador podría ser una buena opción para evitar que intenten utilizar estos cambios utilizando el usuario de la aplicación en lugar de preguntarle al DBA.
Implementación: Ver código fuente
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
¡Eso es todo, amigos!
¡Hasta el próximo post!
Dirceu Resende
Arquitecto de Bases de Datos y BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.
Comentários (0)
Carregando comentários…