Введение

Во второй части этой серии я хотел бы уделить внимание некоторым дополнительным функциям, которые я тестировал, чтобы сделать код более полезным для разработчика. Эти функции также должны работать во всех версиях SQL Server 2005 и последующих версиях.

Бэкграунд

Исходный код был прерван на моем сервере БД дома и регулярно блокировал нарушающие IP-адреса. Тем временем я немного подумал о дополнительных функциях, которые сделают код более полезным для разработчика.

Одним из вариантов использования, которое пришло в голову, является клиентское или мобильное приложение, которое подключается к базе данных SQL Server и где пользователи имеют собственные имена пользователей и пароли. В случае, когда пользователь случайно вводит свой пароль неправильно слишком много раз, нам нужен простой способ разблокировать их в брандмауэре. Обычно пользовательский интерфейс аутентификации пользователя включает функцию сброса пароля, поэтому мы хотим, чтобы простой способ использовать некоторые простые коды для их разблокировки автоматически.

Кроме того, я подумал, что было бы неплохо регистрировать события block / unblock, чтобы я мог получить статистику по IP и видеть, были ли какие-либо повторные нарушители

Использование кода

Я добавил две таблицы для сбора дополнительных данных о неудачных попытках входа в систему. BlockedClientDtl захватывает ID пользователя, используемый для каждой неудачной попытки, и EventLog записывает блокировку или разблокировку действий, выполняемых хранимой процедурой CheckFailedLogins. Я также модифицировал BlockedClient для записи определенного значения datetime для разблокирования в случае, если мы хотим изменить способ его вычисления (например, по IP-адресу клиента, возможно, для увеличения времени блокирования для повторных нарушителей).

CREATE TABLE BlockedClient

(

   IPAddress VARCHAR(15) NOT NULL PRIMARY KEY,

   LastFailedLogin DATETIME,

   UnblockDate DATETIME,

   FailedLogins INT,

   FirewallRule VARCHAR(255)

);

CREATE INDEX IX_BlockedClient_UnblockDate ON BlockedClient(UnblockDate);

CREATE TABLE BlockedClientDtl

(

   IPAddress VARCHAR(15) NOT NULL,

   Attempt INT NOT NULL,

   LogDate DATETIME,

   UserId VARCHAR(255),

   Message VARCHAR(512),

   PRIMARY KEY(IPAddress, Attempt),

   FOREIGN KEY(IPAddress) REFERENCES BlockedClient ON DELETE CASCADE

);

CREATE INDEX IX_BlockedClientDtl_UserId_LogDate ON BlockedClientDtl(UserId, LogDate);

CREATE TABLE EventLog

(

   LogId BIGINT NOT NULL PRIMARY KEY IDENTITY,

   LogDate DATETIME DEFAULT GETDATE(),

   IPAddress VARCHAR(15),

   Action VARCHAR(20),

   EventDesc VARCHAR(512)

);

CREATE INDEX IX_EventLog_IP_LogDate ON EventLog(IPAddress, LogDate);

Идея с BlockedClientDtl заключается в том, чтобы запускать запросы, подобные следующим, когда пользователь запрашивает сброс пароля:

DELETE FROM BlockedClient

WHERE EXISTS (SELECT * FROM BlockedClientDtl

             WHERE BlockedClientDtl.IPAddress = BlockedClient.IPAddress

               AND BlockedClientDtl.UserId = 'JDOE')

Когда пользователь JDOE завершает сброс пароля, мы можем удалить все записи в BlockedClient, связанные с идентификатором пользователя (и, возможно, только в течение определенного периода времени, отсчитанного назад, на основе BlockedClientDtl.LogDate).

Наконец, вот обновленный код нашего CheckFailedLogins SP:

CREATE PROCEDURE CheckFailedLogins

AS

BEGIN

   SET NOCOUNT ON;

   DECLARE @UnblockDate DATETIME

   DECLARE @LookbackDate DATETIME

   DECLARE @MaxFailedLogins INT

   DECLARE @FailedLogins TABLE

   (

        LogDate datetime,

        ProcessInfo varchar(50),

        Message text

   );

   DECLARE @FailedLoginClientDtl TABLE

   (

     IPAddress VARCHAR(15),

       LogDate DATETIME,

       UserID VARCHAR(128),

       Message VARCHAR(1000)

   );

   SELECT @LookbackDate = dateadd(second, -ConfigValue, getdate())

   FROM Config

   WHERE ConfigID = 1

   SELECT @MaxFailedLogins = ConfigValue

   FROM Config

   WHERE ConfigID = 2

   SELECT @UnblockDate = CASE WHEN ConfigValue > 0 THEN DATEADD(hour, ConfigValue, getdate()) END

   FROM Config

   WHERE ConfigID = 3

   INSERT INTO @FailedLogins -- Read current log

   exec sp_readerrorlog 0, 1, 'Login failed';

   INSERT INTO @FailedLoginClientDtl

   SELECT ltrim(rtrim(substring(CONVERT(varchar(1000), Message),

       charindex('[CLIENT: ', CONVERT(varchar(1000), Message)) + 9,

       charindex(']', CONVERT(varchar(1000), Message)) - 9 - charindex('[CLIENT: ', CONVERT(varchar(1000), Message))))) as IPAddress,

       LogDate,

       CASE WHEN charindex('Login failed for user ''', CONVERT(varchar(1000), Message)) > 0 THEN

       ltrim(rtrim(substring(CONVERT(varchar(1000), Message),

       charindex('Login failed for user ''', CONVERT(varchar(1000), Message)) + 23,

       charindex('''. Reason:', CONVERT(varchar(1000), Message)) - 23 - charindex('Login failed for user ''', CONVERT(varchar(1000), Message))))) END AS UserId,

       Message

   FROM @FailedLogins

   WHERE (Message like '%Reason: An error occurred while evaluating the password.%' -- Some filter criteria

           OR Message like '%Reason: Could not find a login matching the name provided.%'

           OR Message like '%Reason: Password did not match that for the login provided.%'

           OR Message LIKE '%Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.%')

       AND LogDate >= @LookbackDate

   INSERT INTO BlockedClient(IPAddress, LastFailedLogin, UnblockDate, FailedLogins)

   OUTPUT INSERTED.IPAddress, 'Block', 'Blocked client ' + INSERTED.IPAddress + ' after ' + CONVERT(VARCHAR(10), INSERTED.FailedLogins) + ' failed login attempts.'

   INTO EventLog(IPAddress, Action, EventDesc) -- Record block event

   SELECT IPAddress,

       MAX(LogDate) AS LastFailedLogin,

       @UnblockDate,

       COUNT(*) AS FailedLogins

   FROM @FailedLoginClientDtl d

   WHERE NOT EXISTS (SELECT * FROM Whitelist l -- Check against whitelist

                     WHERE l.IPAddress = d.IPAddress)

     AND NOT EXISTS (SELECT * FROM BlockedClient c -- ignore already blocked clients

                     WHERE c.IPAddress = d.IPAddress)

     AND IPAddress <> '<local machine>' -- ignore failed logins from local machine

   GROUP BY IPAddress

   HAVING COUNT(*) >= @MaxFailedLogins -- Check against number of failed logins config

   INSERT INTO BlockedClientDtl(IPAddress, Attempt, LogDate, UserId, Message)

   SELECT IPAddress,

     Attempt,

       LogDate,

       UserID,

       Message

   FROM

   (

       SELECT IPAddress,

           ROW_NUMBER()OVER(PARTITION BY IPAddress ORDER BY LogDate) AS Attempt,

           LogDate,

           UserID,

           Message

       FROM @FailedLoginClientDtl d

       WHERE EXISTS (SELECT * FROM BlockedClient c

                                   WHERE c.IPAddress = d.IPAddress)

   )AS t

   WHERE NOT EXISTS (SELECT * FROM BlockedClientDtl dtl

                     WHERE t.IPAddress = dtl.IPAddress

                       AND t.Attempt = dtl.Attempt)

   DELETE FROM BlockedClient -- Delete entries older than the delete config set if > 0

   OUTPUT DELETED.IPAddress, 'Unblock', 'Unblocked client ' + DELETED.IPAddress  + '.'

   INTO EventLog(IPAddress, Action, EventDesc) -- Record unblock event

   WHERE UnblockDate < getdate()

END

Для операций вставки и удаления на BlockedClient я использовал специальные таблицы INSERTED и DELETED, доступные при использовании условия OUTPUT для вставки записей в таблицу EventLog, чтобы я мог видеть историю событий block/unblock

Дальнейшие улучшения

Возможно, мы могли бы проявить смекалку с  конкретными параметрами блока клиента, соблюдая шаблоны в EventLog.  Возможно, поэтапно увеличивать время блокировки IP для каждого раза, когда она происходит. В любом случае хорошо иметь видимость этих событий, если мы хотим  продвинуться глубже или устранить неожиданное поведение.

К сожалению, мы достигли пределов информации, которая может быть получена из sp_readerrorlog. В третьей части этой серии я перейду к совершенно другому способу реализации, который использует брокер услуг, доступный только в стандартном и корпоративном выпусках. Мы сможем получить гораздо больше информации о событиях входа в систему с этим подходом и напрямую инициировать клиентские блоки на этих событиях. С учетом сказанного, я хотел бы охватить все функции, которые будут работать в Express-изданиях SQL Server, чтобы все больше людей могли их использовать и, как мы надеемся, достичь немедленных улучшений в обеспечении безопасности своих баз данных.

No Comments