PW encryption / hashing

[player1up]

Is anyone here working on password encryption for the users_master table?
I just want to make sure I'm not duplicating effort.
I seem to only able to find bits an pieces about it with the search function.

[JohnHeatz]

I don't think there is anyone actively working on it, and if there is, I don't know about it.

Now, as this is not a release...

#Moved to the right section

[DotG]

i think ive read about it somwhere in here... but i dont remember where and who did it >.<

i think it was either abrasives or EarthCrushs release, but im not sure about that

Edit: Found it!

[player1up]

Ugh!, Thanks for the move John, I've got a lot of stuff going on and brain.exe must be set to low priority Also, could you add the how to password hashing link to the index?



And thanks to DotG, I posted and being the OCD person I am, I decided to jump headlong into it on my own. I did get the pwd's encrypted on my own and recreate the process to replicate this on the "prod" db server. However I did it using certificates and symmetric keys, almost the same end result though.

I say almost because I added a column to the users_master table to hold the new encrypted pw, modified the game login sp, set the new column to the encrypted value of the pw column and then nulled the pw column...all so users that were currently logged in wouldn't be affected by the change and their "pw" would remain the same.

and to everyone else, Please, hash/encrypt your users passwords

[ShaiyaOmega]

I did this by sending registration data to a stored procedure.

--Procedure: dbo.Create_Shaiya_Account

/* DROP PROCEDURE dbo.Create_Shaiya_Account
GO */

CREATE PROCEDURE dbo.Create_Shaiya_Account
@UserID varchar(18), 
@Pw varchar(18), 
@Administrator tinyint = 0
AS
SET NOCOUNT ON

DECLARE @username VARCHAR(18);
DECLARE @password VARCHAR(50);
DECLARE @admin TINYINT;
DECLARE @salt VARBINARY(40);
DECLARE @hash VARBINARY(40);

SET @username = @UserID;
SET @password = @Pw;
SET @admin = @Administrator;
SET @salt = CONVERT(VARBINARY,CAST(RAND() * 1000000000 AS INT));
SET @hash = CONVERT(VARBINARY,HASHBYTES('SHA1',CONVERT(VARCHAR,@salt) + @password));

-- Add admin user
IF(@admin = 1)
BEGIN
 INSERT INTO [PS_userdata].[dbo].[Users_Master]
 ([UserID],[Pw],[Admin],[AdminLevel],[Leave],[Status],[UserType],[Salt],[CreatedDate])
 VALUES(@username,@hash,1,255,0,16,'A',@salt,GETDATE());
END
-- Add regular user
ELSE IF(@admin = 0)
BEGIN
 INSERT INTO [PS_userdata].[dbo].[Users_Master]
 ([UserID],[Pw],[Admin],[AdminLevel],[Leave],[Status],[UserType],[Salt],[CreatedDate])
 VALUES(@username,@hash,0,0,0,0,'N',@salt,GETDATE());
END

SELECT
 [UserUID],
 [Status],
 [Leave]
FROM Users_Master AS UM
WHERE UM.UserID = @username
 AND UM.Pw = CONVERT(VARBINARY,HASHBYTES('SHA1',CONVERT(VARCHAR,UM.Salt) + @password));

 INSERT INTO [PS_GameData].[dbo].[UserMaxGrow]
 ([ServerID],[UserUID],[Country],[MaxGrow],[Del])
 VALUES(1,(SELECT UserUID FROM [PS_UserData].[dbo].[Users_Master] WHERE UserID = @username),2,3,0)

SET NOCOUNT OFF
GO

EXEC sp_addextendedproperty
  N'MS_Description', N'Username',
  N'SCHEMA', N'dbo',
  N'PROCEDURE', N'Create_Shaiya_Account',
  N'PARAMETER', N'@UserID'
GO

EXEC sp_addextendedproperty
  N'MS_Description', N'Password',
  N'SCHEMA', N'dbo',
  N'PROCEDURE', N'Create_Shaiya_Account',
  N'PARAMETER', N'@Pw'
GO

EXEC sp_addextendedproperty
  N'MS_Description', N'User Is Admin 1 for Yes 0 for No',
  N'SCHEMA', N'dbo',
  N'PROCEDURE', N'Create_Shaiya_Account',
  N'PARAMETER', N'@Administrator'
GO

This works pretty well for creating accounts.

For the login i use this.

--Procedure: dbo.usp_Try_GameLogin_R

/* DROP PROCEDURE dbo.usp_Try_GameLogin_R
GO */

CREATE PROCEDURE dbo.usp_Try_GameLogin_R
@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 

@Leave TINYINT,
@Status SMALLINT,
@TempIP VARCHAR(15),
@Check INT

SET @Status = -1
SET @LoginTime = GETDATE()
SET @UserIP = LTRIM(RTRIM(@UserIP))
--------------------------------------------------

SELECT
 @UserUID=[UserUID],
 @Status=[Status],
 @Leave=[Leave]
FROM Users_Master AS UM
WHERE UM.UserID = @UserID
 AND UM.Pw = CONVERT(VARBINARY,HASHBYTES('SHA1',CONVERT(VARCHAR,UM.Salt) + @InPassword));

-- No account or incorrect username and password
IF(@@ROWCOUNT = 0)
BEGIN
 SET @Status = 1
END
-- User already logged in
ELSE IF( @Leave = 1 )
BEGIN
 SET @Status = -7
END

-- BlockUser Check
IF( (@Status >= 2) AND (@Status <= 6) )
BEGIN
 -- Get Block Limit Date AND Replace date text
 DECLARE @BlockEndDate datetime
 SELECT @BlockEndDate = BlockEndDate FROM Users_Block WHERE UserUID = @UserUID
 IF ( @@ROWCOUNT <> 0 )
 BEGIN
 -- Block Release
 IF ( @BlockEndDate <= @LoginTime )
 BEGIN
 SET @Status = 0
 UPDATE Users_Master SET Status = @Status WHERE UserUID = @UserUID
 END
 END
END

-- Select 
SELECT @Status AS Status, @UserUID AS UserUID

-- Log Insert
IF( @Status = 0 OR @Status = 16 OR @Status = 32 OR @Status = 48 OR @Status = 64 OR @Status = 80 )
BEGIN
 EXEC usp_Insert_LoginLog_E @SessionID=@SessionID, @UserUID=@UserUID, @UserIP=@UserIP, @LogType=0, @LogTime=@LoginTime, @LoginType=@LoginType
END 
SET NOCOUNT OFF
GO

NOTE some of the procedures might be different or differently named on your server as i have extensively edited mine. for example the dbo.usp_Try_GameLogin_R of mine used to be Try_GameLogin_taiwan or something so i edited it in the exe files to point to the proper SP.

Thanks to abrasive for the original idea.