User Login With Block Condition and Attempted Condition

Background

In our simple life we have many accounts, like bank account, insurance account, Facebook account, Gmail account and so on. I forget my password regularly but thank you developers for providiing me a password recovery capability. Without a password recovery capability I would have no way to remember my password. In this article I have a single table tbl_ALogin.

select * from tbl_ALogin

LoginID UserName   Password  Salt  LoginDateTime   LastLoginTime   IsBlocked   AccessCounter
1   abc   74e9f6722b6df40630d8d7249078453ad1af05c8    a8947f78d0ca294c 2012-11-14 10:03:53.070 2012-11-14 10:03:53.070 0   0
5   cde    62ee78aa3cb90bde955166e6478d4f807f6dea0c   90f62957f3e9bab 2014-12-20 12:46:46.610 2012-11-14 13:34:18.623 0   0

We can see the preceding table has two columns, one is "IsBlocked" and the other is "AccessCounter". These columns provide access and not access permission. 

 

I assume cde user name has the password admin123.

 

For example, now enter the wrong password.

 

The following shows what has changed in the table after the wrong password. 

LoginID UserName   Password  Salt                     LoginDateTime                            LastLoginTime   IsBlocked   AccessCounter
1   abc   74e9f6722b6df40630d8d7249078453ad1af05c8    a8947f78d0ca294c 2012-11-14 10:03:53.070 2012-11-14 10:03:53.070 0   0
5   cde    62ee78aa3cb90bde955166e6478d4f807f6dea0c   90f62957f3e9bab 2014-12-20 12:46:46.610 2012-11-14 13:34:18.623 0   1

Where is the check in Stored Procedure access counter:

if (DATEDIFF(n,@logintime,getdate())<30)
set @counter=@counter+1

 If we enter the wrong password again then the access counter is incremented.  

LoginID UserName   Password  Salt                     LoginDateTime                            LastLoginTime   IsBlocked   AccessCounter
1   abc   74e9f6722b6df40630d8d7249078453ad1af05c8    a8947f78d0ca294c 2012-11-14 10:03:53.070 2012-11-14 10:03:53.070 0   0
5   cde    62ee78aa3cb90bde955166e6478d4f807f6dea0c   90f62957f3e9bab 2014-12-20 12:46:46.610 2012-11-14 13:34:18.623 0   2

 If we enter the wrong password then again our account is blocked as in the following:

  if(@counter>2)
begin
update tbl_ALogin set isblocked=1,AccessCounter=@counter,Logindatetime=getdate() where LoginID=@tmpuserid
set @Succ=-1
end

After the blocked account the following shows how it is displayed: 

LoginID UserName   Password  Salt                     LoginDateTime                            LastLoginTime   IsBlocked   AccessCounter
1   abc   74e9f6722b6df40630d8d7249078453ad1af05c8    a8947f78d0ca294c 2012-11-14 10:03:53.070 2012-11-14 10:03:53.070 0   0
5   cde    62ee78aa3cb90bde955166e6478d4f807f6dea0c   90f62957f3e9bab 2014-12-20 12:46:46.610 2012-11-14 13:34:18.623 1  3

The following is the complete procedure of the user login. 

ALTER PROCEDURE [dbo].[proc_UserLogin]
-- Add the parameters for the stored procedure here
    @username varchar(100),
    @password varchar(50),
    @succ int out,
    @LoginID int out

AS
BEGIN

 -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
 declare @tmpuserid int
 declare @isblocked bit
 declare @logintime datetime
 declare @validpassword varchar(50)
 declare @counter int
    -- Insert statements for procedure here
select @tmpuserid=LoginID,@validpassword=Password,@isblocked=isblocked,@logintime=Logindatetime,@counter=isnull(AccessCounter,0)  from tbl_AdminLogin where username=@Username
--select @tmpuserid=LoginID,@validpassword=Password,@isblocked=isblocked,@logintime=Logindatetime,@counter=isnull(AccessCounter,0)  from tbl_AdminLogin where username='admin'

     -- print DATEDIFF(n,@logintime,getdate())
      if (@isblocked=1 and (DATEDIFF(n,@logintime,getdate())>30) and @validpassword=@Password)
            begin
                        update tbl_AdminLogin set isblocked=0,Logindatetime=getdate(),AccessCounter=0 where LoginID=@tmpuserid
                        set @succ=1
                        set @LoginID=@tmpuserid
            end
      else
          begin
                 if (@isblocked=1 and @validpassword<>@Password)
                        set @Succ=-1
                  else
                      if(@isblocked=1)
                         set @Succ=-1
                                    else
                                         begin
                                                if (@isblocked=0 and @validpassword=@Password)
                                                       begin
                                                            update tbl_AdminLogin set isblocked=0,Logindatetime=getdate(),AccessCounter=0 where LoginID=@tmpuserid
                                                            set @succ=1
                                                            set @LoginID=@tmpuserid
                                                      end

                                              else
                                                  begin
                                                        if (DATEDIFF(n,@logintime,getdate())<30)
                                                               set @counter=@counter+1

                                                            if(@counter>2)
                                                             begin
                                                               update tbl_AdminLogin set isblocked=1,AccessCounter=@counter,Logindatetime=getdate() where LoginID=@tmpuserid
                                                               set @Succ=-1
                                                            end
                                                           else
                                                                begin
                                                            update tbl_AdminLogin set AccessCounter=@counter  where LoginID=@tmpuserid
                                                            set @succ=-2
                                                              end
                                                 end
                                          end

          end
       -- print @succ

END