Login Stored procedures and test database

Use Master
go
Create database LoginTest
Go
Use LoginTest
go
Create table SecurityQuestion
(
  QuestionKey int identity(1,1) primary key,
  Question nvarchar(255) not null
)

go

Create table UserLogin
(
   UserKey int identity(1,1) primary key,
   UserName nvarchar(50) not null,
   UserEmail nvarchar(255) not null,
   UserRandomInt int not null,
   UserPassword varBinary(500) not null,
   UserDateEntered Date not null,
   UserDateLastModified Date not null,

)
alter table UserLogin
add constraint unique_UserName unique(userName)

alter table UserLogin
add constraint unique_Email unique(useremail)

go

Create Table UserSecurityQuestion
(
 UserKey int Foreign Key references UserLogin(UserKey) not null,
 QuestionKey int Foreign Key references SecurityQuestion(QuestionKey) not null,
 UserAnswer NVarchar(255) not null,
 Constraint PK_UserSecurityQuestion primary key(UserKey, QuestionKey),
 
)

Go
Create table LoginHistory
(
 LoginHistoryKey int identity(1,1) primary key,
 UserKey int foreign key references UserLogin(userKey),
 LoginHistoryDateTime datetime default GetDate()
)

Go

Insert into SecurityQuestion(Question)
values('Where were you when you got your first traffic ticket?'),
('What is your least favorite book?'),
('What acloholic drink made you the sickest?'),
('What food do you truely hate?')

go
create function fx_hashPassword
(@password nvarchar(50), @RandomInt int)
returns varbinary(500)
As
begin
Declare @Combined nvarchar(60)
Declare @hashed varbinary(500)
Set @Combined = @password + cast(@randomInt as Nvarchar(10))
Set @hashed = HASHBYTES('sha2_512', @combined)
return @hashed
End
go

Select dbo.fx_hashPassword('mypass','1342567901')

Go

Create function fx_getRandomInt()
returns int
As
Begin
Declare @intNumber int
set @intNumber=DatePart(NanoSecond, GetDate())
return @intNumber
End

go

--password, all the info for login table
--userKey as output
--write to userlogin table
--write security question table
--write to login history table
--put in transaction

Alter proc usp_NewLogin
@userName nvarchar(50),
@Password nvarchar(50),
@userEmail nvarchar(255),
@securityQuestion int,
@answer nvarchar(255)
As
--declare internal variables
Declare @intRandom int
Declare @hash varbinary(500)
Declare @Date Date
--check to see if user exists
If Exists
 (Select userKey from userLogin
 where userName=@userName
 And UserEmail=@userEmail)
Begin
Print 'user already exists'
return -1
End

--get random seed 
select @intRandom=dbo.fx_getRandomInt()
--get hash of password
select @hash = dbo.fx_hashPassword(@password, @intRandom)

Set @date =GetDate()
--Begin transaction
Begin tran
Begin try
--insert int userLogin
Insert into UserLogin(UserName, UserEmail, UserRandomInt, UserPassword, UserDateEntered, UserDateLastModified)
Values(@username, @userEmail, @intRandom, @hash, @date, @date)

Declare @UserKey int
Set @UserKey= Ident_Current('UserLogin')
--insert into userSecurityQuestion
Insert into UserSecurityQuestion(UserKey, QuestionKey, UserAnswer)
Values(@UserKey, @SecurityQuestion, @answer)
--Insert into LoginHistory
Insert into LoginHistory(UserKey, LoginHistoryDateTime)
values(@UserKey, @Date)
commit tran
Return @userKey
End Try
Begin Catch
Rollback tran
return 0
End Catch

Exec usp_NewLogin
@userName='George', 
@Password='P@ssw0rd1', 
@userEmail='George@gmail.com', 
@securityQuestion=3, 
@answer='whiskey'

Select * from SecurityQuestion

Select * From userLogin
Select * From UserSecurityQuestion
Select * From LoginHistory



--existing login
--intake password username
--get the salt that goes with the username
--(-1) if no username
--rehash the text password with the salt
--compare the hashes
--if they match the login is successful return user key
--if they fail return 0
go
Alter proc usp_Login
@Password nvarchar(50),
@userName nvarchar(50)
As
Declare @intRandom int
Declare @Newhash varbinary(500)
Declare @DBHash varbinary(500)
Declare @UserKey int

Select @UserKey= userKey, @intRandom=UserRandomInt, @DBHash=userPassword from UserLogin
Where UserName=@userName

if @IntRandom is null
 Begin
  Print '-1'
  Return -1
 End
Select @newHash=dbo.fx_hashPassword(@password, @intRandom)

if @DBHash=@Newhash
 Begin

 insert into LoginHistory(UserKey, loginHistoryDateTime)
 Values(@UserKey,GetDate())

 print cast(@UserKey as nvarchar(10))
    Return @UserKey
 end
Else
   Begin
  print '0'
  Return 0
   End


Exec usp_login
@Password='P@ssw0rd1', 
@userName='spconger'

Select * From LoginHistory


/*
login and validate
Get new Password
Rehash the passord
Update the Login table
*/
go

Create proc usp_ChangePassword
@userName nvarchar(50),
@password nvarchar(50),
@newPassword nvarchar(50)
As
Declare @intRandom int
Declare @Newhash varbinary(500)
Declare @DBHash varbinary(500)
Declare @UserKey int

Select @UserKey= userKey, @intRandom=UserRandomInt, @DBHash=userPassword from UserLogin
Where UserName=@userName

if @IntRandom is null
 Begin
  Print '-1'
  Return -1
End
Select @newHash=dbo.fx_hashPassword(@password, @intRandom)

if @DBHash=@Newhash
 Begin
 Declare @newRandom int
 Set @newRandom=dbo.fx_getRandomInt()
 Declare @UpdateHash varbinary(500)
 Set @UpdateHash=dbo.fx_hashPassword(@newPassword, @NewRandom)

 update UserLogin 
 Set UserRandomInt=@newRandom,
 UserPassword=@updateHash
 Where userKey=@UserKey


 insert into LoginHistory(UserKey, loginHistoryDateTime)
 Values(@UserKey,GetDate())

 print cast(@UserKey as nvarchar(10))
    Return @UserKey
 end
Else
   Begin
  print '0'
  Return 0
   End

   Select * from userLogin

   exec usp_ChangePassword
   @userName = 'spconger', 
   @password='P@ssw0rd1', 
   @newPassword='P@ssw0rd2'

   Exec usp_login
@Password='P@ssw0rd2', 
@userName='spconger'