Login Scripts with security features

use PS_UserData
GO

create table dbo.IP_Block (
	UserIP		varchar(15) not null,
	TryCounter	smallint not null default 0,
	BlockEndDate	datetime default null
)
GO

TABLE [dbo].[Users_Master](
	[RowID]		[int] IDENTITY(1,1) NOT NULL,
	[UserUID]	[int] NOT NULL,
	[UserID]	[varchar](18) NOT NULL,
	[Pw]		[varchar](12) NOT NULL,
	[JoinDate]	[smalldatetime] NULL,
	[Admin]		[bit] NULL,
	[AdminLevel]	[tinyint] NULL,
	[UseQueue]	[bit] NULL,
	[Status]	[smallint] NULL,
	[Leave]		[tinyint] NULL,
	[LeaveDate]	[smalldatetime] NULL,
	[UserType]	[char](1) NULL,
	[UserIp]	[varchar](15) NULL,
	[ModiIp]	[varchar](15) NULL,
	[ModiDate]	[datetime] NULL,
	[Point]		[int] NULL,
	[EnPassword]	[char](32) NULL,
	[Birth]		[varchar](8) NULL
)

alter table Users_Master add [StaticIP]	[bit] NOT NULL default 0;
alter table Users_Master add [TryCounter] [smallint] NOT NULL default 0;
alter table Users_Master add [BlockEndDate] [datetime] default NULL;

-- remove char(32) column
alter table Users_Master drop column EnPassword
GO
-- insert a binary(20) column to store digests (MSSQL 2008 doesn't support hash mechanism larger than 160 bits)
alter table Users_Master add HashPw binary(20) NULL
GO
-- compute & insert SHA1 digest for existing passwords
update Users_Master set HashPw=HashBytes('SHA1', Pw)
go

-- [strike]from now, you should remove the Pw column,[/strike]
-- be sure to update the registration script before to do it
-- [strike]!!! alter table Users_Master drop column Pw !!![/strike]

-- EDIT: removing the column is a BAD idea, or the next script must be changed since
-- the posted version does select the column, it's safer to simply empty it, so:
-- update Users_Master set Pw=null
-- go

TABLE PS_UserData.dbo.UserLoginLog (
	[RowID] [int] IDENTITY(1,1) NOT NULL,	-- useless, yet said
	[SessionID] [bigint] NOT NULL,		-- the session ID received by usp_Try_GameLogin_Taiwan
	[UserUID] [int] NOT NULL,		-- the unique user ID conditionally settled during login
	[UserIP] [varchar](15) NOT NULL,	-- the client IP received by usp_Try_GameLogin_Taiwan
	[LoginTime] [datetime] NOT NULL,	-- the date/time of login process
	[LogoutTime] [datetime] NULL,		-- the date/time of logout process (not used there)
	[LoginType] [smallint] NOT NULL,	-- type of operation: 0: log-in, 1: log-out
	[ErrType] [smallint] NOT NULL,		-- our @result variable indicating the reason of failure
	[ErrMsg] [varchar](300) NULL,		-- not used there
	[PCBangRowID] [int] NULL		-- not used there
)

USE [PS_UserData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter Proc [dbo].[usp_Insert_LoginLog_E]
	@sessionID bigint,
	@userUID int,
	@userIP varchar(15),
	@logTime datetime,
	@logType smallint,
	@errType smallint

AS

set NOCOUNT ON

insert into PS_UserData.dbo.UserLoginLog (SessionID, UserUID, UserIP, LoginTime, LoginType, ErrType)
	values (@sessionID, @userUID, @userIP, @logTime, @logType, @errType)

set NOCOUNT OFF

USE [PS_UserData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

--	ALTER Proc [dbo].[usp_Try_GameLogin_Taiwan]

ALTER Proc [dbo].[usp_Try_GameLogin]

	@UserID 	varchar(18),
	@InPassword	varchar(32),
	@SessionID 	bigint,
	@UserIP 	varchar(15),
	@UserUID 	int = 0,
	@LoginType 	smallint = 1, 
	@LoginTime 	datetime = NULL

AS

SET NOCOUNT ON

DECLARE
	@result		smallint	= null,	-- processing result value, use as a flag to follow the login sequence
	@leave 		tinyint,		-- user Leave state
	@status 	smallint,		-- user Status
	@password	varchar(32),		-- plain password (may not exist)
	@hash_pass	binary(32),		-- digest of password (may not exist)
	@staticIP	bit		 = 0,	-- does static IP used?
	@validIP	varchar(15)	= NULL,	-- optional unique IP allowed to log-in
	@companyIP 	varchar(15),		-- optional checking of the IP of users-with-rights
	@blockDate	datetime	= NULL,	-- possible date of end of ban for UserID or IP
	@ipCounter	smallint	= NULL,	-- number of consecutive login attempts for a given IP
	@accCounter	smallint	= 0,	-- number of consecutive login attempts for a given account
	@TryMaxCtr	smallint		-- the maximal allowed consecutive login attempts (to be tuned acc. yours needs/wishs)

set @UserIP	= ltrim(rtrim(@UserIP))
set @LoginTime	= GetDate()

--------------------------------------------------
--	SETUP PARAMETERS

--	set the class C address (the 3 first digit of an IPv4 address) that admins shall use
--	set @companyIP	= '127.0.0'
--	set @companyIP	= '192.168.0'
--	or set it to NULL to disable the test
	set @companyIP	= NULL

--	maximal allowed consecutive login attempts failures (to be tuned acc. yours needs/wishs)
	set @TryMaxCtr	= 5

--------------------------------------------------

--	check whether the IP is already banned
--	* BlockEndDate is not null if IP was banned, and null otherwise
--	* ipCounter is read for later use

select @blockDate=BlockEndDate,@ipCounter=TryCounter from IP_Block where UserIP=@UserIP
--	does IP currently banned?
if @blockDate is not null
begin
--	check whether ban is still running (not expired)
	if @blockDate > @LoginTime
	begin
	--	set result to 'Account (actually IP) blocked'
		set @result = -9
	--	suspend this thread/request for 5mn to avoid DoS attacks
		waitfor delay '00:05'
	end
	else
	--	ban is expired, unblock the IP, reset its try counter
		update IP_Block set TryCounter=0,BlockEndDate=NULL where UserIP=@UserIP
end

if @result is null
begin
--	try to select a record with given UserID (= account name)
	select @UserUID=UserUID,@password=Pw,@hash_pass=HashPw,@status=Status,@leave=Leave,
			@staticIP=StaticIP,@validIP=UserIp,@blockDate=BlockEndDate,@accCounter=TryCounter
		from Users_Master where UserID=@UserID
	
--	does record found?
	if @UserUID = 0
	begin
	--	no account exist for the given name (UserID)
	--	could be an alias mistyping or an attack to discover a valid UserID
		
		if @ipCounter is null
		--	first invalid login attempt from that IP, create a new entry
			insert into IP_Block (UserIP,TryCounter,BlockEndDate) values (@UserIP, 1, NULL)
		else
		begin
		--	increase try counter
			set @ipCounter = @ipCounter + 1
		--	does maximal number of consecutive login failures reached?
			if @ipCounter >= @TryMaxCtr
			--	ban this IP for one hour (to be tuned acc. yours needs/wishs)
				update IP_Block set TryCounter=@ipCounter,BlockEndDate=DateAdd(hour, 1, @LoginTime) where UserIP=@UserIP
			else
				update IP_Block set TryCounter=@ipCounter where UserIP=@UserIP
		end
	--	store 'account doesn't exist' info
		set @result = -3
	end
	else --	UserUID is valid, so UserID was a valid user account name
	begin
	--	does account currently banned?
		if @blockDate is not null
		begin
		--	check whether ban is still running (not expired)
			if @blockDate > @LoginTime
			begin
			--	set result to 'Account blocked'
				set @result = -9
			--	suspend this thread/request for 3mn to avoid DoS attacks
				waitfor delay '00:03'
			end
			else
			--	ban is expired, unblock the account, reset its try counter
				update Users_Master set TryCounter=0,BlockEndDate=NULL where UserID=@UserID
		end

	--	does account closed?
		if @result is null and @leave = 1
		begin
			set @result = -3		--	account doesn't exist
		end

	--	does static IP to be used?
		if @result is null and @staticIP=1
		begin
		--	check used IP against allowed one
			if @validIP <> @UserIP
				set @result = -10	--	account restricted
		end

	--	optional IP tests on admin. accounts (don't use it if your admins can log from any IP)
		if @result is null and @companyIP is not null and (@status & 0x70) <> 0
		begin
		--	extract the 3 first decimal numbers of the given IP
			declare @partialIP varchar(15)
			set @partialIP = reverse(@userIP)
			set @partialIP = reverse(SubString(@partialIP, CharIndex('.', @partialIP) + 1, 32))
		--	compare the class C addresses
			if @partialIP <> @companyIP
				set @result = -11	--	account restricted
		end
	
	--	do all tests passed?
		if @result is null
		begin
			--	verify password, or hash of password
			declare @verifResult bit = 0
			
			if @hash_pass is not null
			begin
			--	verify hash of password
				if HashBytes('SHA1', @InPassword) = @hash_pass
					set @verifResult = 1
			end
			else
			begin
			--	verify plain password
				if @InPassword = @password and @password is not null
					set @verifResult = 1
			end

		--	update try counter of account, and opt. date of ban, if verification failed
			if @verifResult = 0
			begin
			--	increase account try counter
				set @accCounter = @accCounter + 1
			--	block account if nbr of consecutive login failure reached the limit
				if @accCounter >= @TryMaxCtr
				begin
				--	block account for 1hr (or whatever delay you want)
					set @blockDate = DateAdd(hour, 1, @LoginTime)
					set @result = -5	--	account blocked
				end
				else
				begin
					set @blockDate = NULL
					set @result = -15	--	account restricted
				end
			end
			else
			begin
			--	password verification was successful, reset the account try counter
				set @accCounter = 0
				set @blockDate = NULL
				set @result = 0
			end
		--	update account details, block it if required
			update Users_Master set TryCounter=@accCounter,BlockEndDate=@blockDate where UserID=@UserID
		end
	end
end

-- log the login details (@LogType = 0 for login (1 for logout))
exec usp_Insert_LoginLog_E @SessionID, @UserUID, @UserIP, @LoginTime, 0, @result

--	for all errors return 'account restricted' msg
--	(don't give accurate answers regarding validity of UserID to not help hackers)
if @result < 0
	set @status = -7

--	select expected response fields
select @status as Status, @UserUID as UserUID

SET NOCOUNT OFF