在本系列文章的第二部分，探讨了一些额外的功能，这些功能旨在使代码对开发者更加有用。据所知，这些功能将在SQL Server2005及更高版本上工作。
背景：原始代码一直在家里的数据库服务器上运行，定期阻止违规的IP地址。与此同时，考虑了一些额外的功能，这些功能将使代码对开发者更加有用。
一个用例是客户端或移动应用程序连接到SQL Server数据库，用户拥有自己的用户名和密码。如果用户不小心多次输入错误的密码，希望有一个简单的方法在防火墙中解封他们。通常，用户认证UI包括一个密码重置功能，因此希望有一种简单的方法来附加一些简单的代码以自动解封他们。
此外，认为记录封禁/解封事件也很好，这样可以通过IP获取统计数据，看看是否有任何重复的违规者。
添加了两个表来捕获有关失败登录尝试的额外数据。
BlockedClientDtl 捕获每次失败尝试使用的UserID。EventLog 记录由 CheckFailedLogins 存储过程执行的封禁/解封操作。还修改了 BlockedClient 以记录一个特定的解封日期时间值，以防想要修改这是如何计算的（例如，通过客户端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完成密码重置时，可以删除与UserID关联的所有BlockedClient条目（并且可以选择仅基于BlockedClientDtl.LogDate回溯一定时间）。
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 <> '' -- 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上的插入和删除操作，使用了OUTPUT子句可用的特殊表INSERTED和DELETED，以便将记录插入EventLog表中，这样就可以看到封禁/解封事件的历史记录。
可以通过观察EventLog中的模式，创造性地获取特定于客户端的封禁参数。也许每次他们被封禁时，逐步增加IP的封禁时间。无论如何，如果要深入了解或排除意外行为，拥有这些事件的可见性是很好的。