LLI Wiki — My Digital Knowledge Base

Trigger de connexion avec liste noire d'adresses IP

Refuser les connexions SQL Server venant d'adresses listées dans une table, avec journalisation des tentatives refusées.

Un trigger de connexion au niveau serveur peut refuser les connexions venant d'adresses clientes figurant dans une table de liste noire. Cet exemple crée une base d'audit avec un journal des connexions et une liste noire, puis un trigger de connexion qui annule les connexions issues des adresses listées et enregistre chaque tentative refusée. Les membres de la rôle sysadmin et un login applicatif sont exemptés.

Créer les objets d'audit

USE AuditDb;
go
use [AuditDb]
GO
GRANT CONNECT TO [guest]
GO
DROP TABLE AuditDb.dbo.logIP
go
DROP TABLE AuditDb.dbo.blackListIP
go
--DELETE AuditDb.dbo.logIP
go
CREATE TABLE AuditDb.dbo.blackListIP (
  IP NVARCHAR(15),
  CONSTRAINT PK_blackListIP PRIMARY KEY CLUSTERED (IP)
); 
GO
GRANT SELECT ON AuditDb.dbo.blackListIP TO PUBLIC;
go
CREATE TABLE AuditDb.dbo.logIP (
  IP NVARCHAR(15), login NVARCHAR(200),timeStamp DATETIME2 DEFAULT GETDATE(), Host_Name   NVARCHAR(128), status VARCHAR(20)
); 
GO
GRANT INSERT ON AuditDb.dbo.logIP TO PUBLIC;

go
--SELECT * FROM AuditDb.dbo.logIP
--SELECT * FROM AuditDb.dbo.blackListIP

-- Exemple : ajout d'une adresse à la liste noire (192.0.2.10 est une adresse de documentation)
INSERT INTO AuditDb.dbo.blackListIP (IP) VALUES ('192.0.2.10');
go

Créer le trigger de connexion

ALTER TRIGGER [tr_logon_CheckIP]  
ON ALL SERVER 
FOR LOGON 
AS
BEGIN
  IF IS_SRVROLEMEMBER('sysadmin') <> 1
  BEGIN
  IF ORIGINAL_LOGIN() <> 'app_readonly'
  BEGIN
   DECLARE @IP NVARCHAR(15);
   DECLARE @USER nvarchar(200);
   DECLARE @Status NVARCHAR(15);
   DECLARE @hostName NVARCHAR(200);
   SET @Status = 'OK' 
   SET @USER = ORIGINAL_LOGIN()
   SET @hostName = HOST_NAME()
   SET @IP = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'));
   IF EXISTS(SELECT IP FROM AuditDb.dbo.blackListIP WHERE IP = @IP)
   BEGIN
    SET @Status = 'Denied'
    ROLLBACK;
    INSERT AuditDb.dbo.logIP (ip, LOGIN, HOST_NAME, STATUS)
    SELECT @IP, @USER, @hostName, @Status
   END 
   --INSERT AuditDb.dbo.logIP (ip,LOGIN ,HOST_NAME,status) 
   --select @IP,@USER,EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','NVARCHAR(128)'),@Status
  END; 
  END
END; 
GO

Remarques

Mastodon LLIMastodon SocialFosstodon