密码策略重试次数与锁定持续时间

今天分享密码策略的重试次数与登录失败锁定持续的小功能。

软件中，需要一个管理介面，可让系统管理设置相关的参数，这些设置将保存于数据库中，这两个参数初始化为0，如果大于0，说明管理员已经启用此功能。如下：

密码策略重试次数与锁定持续时间

从功能上看，只是涉及至验证用户与登录时需要实现在功能，Insus.NET不想改动太多地方，特别是程序部分，因此Insus.NET只是修改用户登录验证的一个存储过程：

逻辑太约分几步，首先是密码参数表获取上图中两个参数：

View Code

DECLARE @NumberOfRetries TINYINT = [ dbo ]. [ udf_GetPasswordParameterValue ]( 1)
DECLARE @LockoutDuration TINYINT = [ dbo ]. [ udf_GetPasswordParameterValue ]( 2)

从用户表中，获取当前登录的用户名的登录失败次数与及锁定时间：

View Code

DECLARE @LoginFailures TINYINT, @LockoutDate DATETIME
SELECT @LoginFailures = [ LoginFailures ], @LockoutDate = [ LockoutDate ] FROM [ dbo ]. [ Users ] WHERE [ Account ] = @Account

宣告一个锁定过期时间，即是锁定时间加上密码参数表设定的密码锁定持续时间

View Code

DECLARE @ExpirationDate DATETIME = DATEADD(minute, @LockoutDuration, @LockoutDate)

写一个判断，判断用户是否被锁定，如果用户在锁定期间，用户还不断重次登录，此时不管密码正确与否，均以当前时间更新用户锁定时间。其中有一个SQL日期时间比较自定义函数[dbo].[udf_DateTimeCompare]，可以参考：http://www.cnblogs.com/insus/archive/2011/06/24/2089005.html

View Code

IF [ dbo ]. [ udf_DateTimeCompare ]( @ExpirationDate, CURRENT_TIMESTAMP) > 0
BEGIN
     UPDATE [ dbo ]. [ Users ] SET [ LockoutDate ] = CURRENT_TIMESTAMP   WHERE [ Account ] = @Account
     RAISERROR(N ' 用户名已被锁定。 ', 16, 1)
     RETURN
END

用户在没有锁定情况之下，如果用户名与密码正确，允许用户顺利登录，还在更新锁定时间与登录失败次数字段，初始化为NULL和0。

View Code

IF EXISTS( SELECT TOP 1 1 FROM [ dbo ]. [ Users ] WHERE [ Forbidden ] = 0 AND [ Account ] = @Account AND [ Password ] = @Password)
BEGIN
UPDATE [ dbo ]. [ Users ] SET [ LockoutDate ] = NULL, [ LoginFailures ] = 0 WHERE [ Account ] = @Account
SELECT [ UsersId ], [ WorkNumber ], [ Account ] FROM [ Users ] WHERE [ Account ] = @Account
END

如果用户输入的密码不正确

判断是否设置与启用了用户登录重试次数要求，可以从密码参数表的密码重试次与锁定持续时间均大于0

View Code

IF @NumberOfRetries > 0 AND @LockoutDuration > 0

如果登录失败重试且是最后一次，更新登录失败次数以及锁定时间，反之，仅更新登录失败次数。

View Code

IF @NumberOfRetries - @LoginFailures = 1
             UPDATE [ dbo ]. [ Users ] SET [ LoginFailures ] = [ LoginFailures ] + 1, [ LockoutDate ] = CURRENT_TIMESTAMP   WHERE [ Account ] = @Account
         ELSE
             UPDATE [ dbo ]. [ Users ] SET [ LoginFailures ] = [ LoginFailures ] + 1 WHERE [ Account ] = @Account

较完整的存储过程(仅供参考)：

usp_Users_LoginVerifyAndGetInfor

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Insus.NET
-- Create date: 2009-04-06
-- Update date: 2012-02-07
-- Description:    Verify user login and get infor
-- =============================================

ALTER PROCEDURE [ dbo ]. [ usp_Users_LoginVerifyAndGetInfor ]
(
     @Account nvarchar( 30),
     @Password nvarchar( 30)
)
AS
DECLARE @NumberOfRetries TINYINT =   [ dbo ]. [ udf_GetPasswordParameterValue ]( 1)
DECLARE @LockoutDuration TINYINT =   [ dbo ]. [ udf_GetPasswordParameterValue ]( 2)

IF NOT EXISTS( SELECT TOP 1 1 FROM [ dbo ]. [ Users ] WHERE [ Account ] = @Account)
BEGIN
     RAISERROR(N ' 用户名或密码错误。 ', 16, 1)
     RETURN
END

IF EXISTS( SELECT TOP 1 1 FROM [ dbo ]. [ Users ] WHERE [ Forbidden ] = 1 AND [ Account ] = @Account)
BEGIN
     RAISERROR(N ' 用户名已荒废或被禁用。 ', 16, 1)
     RETURN
END

DECLARE @LoginFailures TINYINT, @LockoutDate DATETIME
SELECT @LoginFailures = [ LoginFailures ], @LockoutDate = [ LockoutDate ] FROM [ dbo ]. [ Users ] WHERE [ Account ] = @Account
DECLARE @ExpirationDate DATETIME = DATEADD(minute, @LockoutDuration, @LockoutDate)

IF [ dbo ]. [ udf_DateTimeCompare ]( @ExpirationDate, CURRENT_TIMESTAMP) > 0
BEGIN
     UPDATE [ dbo ]. [ Users ] SET [ LockoutDate ] = CURRENT_TIMESTAMP   WHERE [ Account ] = @Account
     RAISERROR(N ' 用户名已被锁定。 ', 16, 1)
     RETURN
END

IF EXISTS( SELECT TOP 1 1 FROM [ dbo ]. [ Users ] WHERE [ Forbidden ] = 0 AND [ Account ] = @Account AND [ Password ] = @Password)
BEGIN
     UPDATE [ dbo ]. [ Users ] SET [ LockoutDate ] = NULL, [ LoginFailures ] = 0 WHERE [ Account ] = @Account
     SELECT [ UsersId ], [ WorkNumber ], [ Account ] FROM [ Users ] WHERE [ Account ] = @Account
END
ELSE
BEGIN
     IF @NumberOfRetries > 0 AND @LockoutDuration > 0
     BEGIN
         IF @NumberOfRetries - @LoginFailures = 1
             UPDATE [ dbo ]. [ Users ] SET [ LoginFailures ] = [ LoginFailures ] + 1, [ LockoutDate ] = CURRENT_TIMESTAMP   WHERE [ Account ] = @Account
         ELSE
             UPDATE [ dbo ]. [ Users ] SET [ LoginFailures ] = [ LoginFailures ] + 1 WHERE [ Account ] = @Account
     END

     RAISERROR(N ' 用户名或密码不正确。 ', 16, 1)
     RETURN
END

秒客网

密码策略重试次数与锁定持续时间

相关文章