Among the objectives, we can list:
- the protection of user's accounts against piracy by attacks (dictionary based or brute force)
- the limitation of the impact of massive attacks on the server login.
This will be achieved by the implementation of a try counter verification associated to existing accounts as well as IP and by several checks that can enforce security.
First of all, let check how in game login is managed.
- the (client) game.exe contains the IP address of the server to which to send a request log-in.
- the game interface prompts for an user ID (an alias) and a password and send this information to the server.
- the ps_login.exe service manages the incoming login request with a call to the stored procedure usp_Try_GameLogin_Taiwan
- depending of the values returned by this proc. a session is started for the given account and the client follows the game loading or session is discarded and the client notifies the user with an error message.
The first point tells us that (obviously) IP of servers are public data, any hacker is so able to send thousands of login requests to a server w/o using the slow game interface.
The call to the SP usp_Try_GameLogin_Taiwan, and its format "%s, %s, %I64d, %s", shows that login relies on 4 parameters only (userID, password, session identifier and IP of client) and expects 2 response values: the 'status' of the user and its unique identifier.
Any changes to the login procedure shall only comply with these 2 requirements; it means that the structure, content, location, in short all details, of the bases and tables used to perform the login can be freely changed.
And indeed several variants are yet in the fields.
Several bases released at Elite PvP contain the OMG_GameWEB base (with a GameAccountTBL table), we will not use it there.
The PS_UserData base is likely used by all existing login processes, we will use it.
Still this base contains several tables that are or are not used, we can also note that some information (basically the end of ban date associated to an account) is stored in a specific table (Users_Block) while it can (could) be stored in the Users_Master table.
OOH the base does not contain any table to record IP that can not be linked to an account, obviously these cases (ie login attempts containing an not-existing UserID) shall be managed, so now need an additional table:
Code:
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
- UserIP is the IP of the computer sending the login request (this IP can be forged (fake) but we have no way to know it so let's forget that point).
- TryCounter is the number of consecutive login attempt failures for this IP
- BlockEndDate is the date/time at which the blocking of the IP will ends, it is null if the IP is not blocked
Now, for attempts with a valid UserID we will need similar fields. Don't forget that some users use the same name in game and in forum, in these cases the hacker already knows half of the data, so blocking an account after too many password verification failure does make sense.
We assume that the Users_Master table is defined as follows:
Code:
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 )
- RowID is (as for most of AG tables) an useless identity key
- UserUID is the User Unique IDentifier, this value shall be returned by the procedure (this field should be a primary (opt. identity) key)
- UserID is the account name to be verified against the received value
- Pw is the account password to be verified against the received value (it is assumed to be in plain characters)
- JoinDate is the date of creation of the record
- Admin is a binary indicator (0 or 1) indicating whether the user has privileged rights
- AdminLevel is another flag for privileged rights
- UseQueue is ... unknown and not used
- Status is a mask describing privileged rights
- Leave indicates if the user has left the server (if set to 1)
- LeaveDate is the date of account closure
- UserType is usually 0 for normal user, 'A' for admins
- UserIp is "an" IP used by the user, we will give it more specific sense below
- ModiIp is a modified IP ?!?, we won't use it
- ModiDate should be the date of modification of the ModiIP field
- Point is the item-mall balance of points for this account
- EnPassword is certainly suppose to contain an encrypted password, but a hash process is *not* an encryption (so wrong name) and its type - char(32) - is not the best chioce, it should be a binary(20) to store SHA-1 digests (or (64) for up to SHA2-512 digests).
- Birth a birth date ?!?
Let's add some columns to store our additional information:
Code:
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;
- TryCounter is the number of consecutive login attempt failures for this account
- BlockEndDate is the date/time at which the blocking of the account will ends, it is null if the account is not blocked, this field replaces the need for the Users_Block table
The binary field StaticIP will allow the implementation of an additional security feature: the login from an unique IP.
Admins as well as normal users may have static (constant) IP, when it's the case, the UserIP field will store that IP and we will check it against the transmitted one during login. Of course for normal users an opt-in process shall be available from your registration / management pages, and an email-based mechanism to change the IP or to disable the feature will also be needed.
If StaticIP is not set (if it contains '0'), the UserIP field is not used by the login script (it can contain registration IP or other info depending on your scripts).
Optionally, you can also want to replace the plain passwords with digest of these passwords, I won't go through the full process to do so - it was recently discussed in a recent thread - I simply list the impact on the Users_Master table:
Code:
-- 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
Code:
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 )
This procedure is not unique, some variants use the current date to compute a date-based name of table, other configs can include a single table (with is cleaned during weekly or monthly based maint.).
The following definition uses a single table, make sure your script is valid & complies with the parameters sent at the end of the updated login script.
Code:
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
the login sequence is:
1) check if the client IP is banned,
if currently banned hold the request for 5mn (dramatically reduce ways to do DoS attacks)
otherwise, update & check login attempts, block if required
2) select user's details
2a) if no record found, it's an attack or a alias mistyping
2b) if record found:
3) check if account is currently blocked
if currently banned hold the request for 3mn (dramatically reduce ways to do DoS attacks)
otherwise, update & check login attempts, block if required
4) check if account is not closed
5) if static IP is defined, check if the used one is the expected
6) if a company IP is defined, check that admins logs from a valid sub-net
7) verify password or hash of password
8) adjust account try counter & block status accordingly
9) log login attempts details
10) return expected data to the ps_login.exe service
set the name of the SP to "usp_Try_GameLogin_Taiwan" only after required tests & eventually updates.
Code:
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
Shaiya Private Server -> Shaiya PServer Guides & Releases
