Database Security Question

[princess12]

Hi All,



i have created a successfull login system with logon attempts. so after 3 failed attempts, the application quits. my concern is that once application is opened again the user can still attempt to logon. is that ok, is that how it should be. or can i do something like prevent logins until they request for password change or anything similar... if so, how?

p.s this project involves internal users, and does not involve network, it just to prevent other users using other logins.


please provide me with your inputs

thanks for your help

[darwish]

Dear

Yes this is normal.

For more security you can do the following:

1. Lock the account logging In for about 10min. When user try to login display a message that your account is locked due to invalid password provided several times.
2. Lock the account logging In from a certain PC. So here you will lock according to the PC name.

Regards

[princess12]

ive never done this before, could you please give me a template for option 1, an example so i can see what you mean. i understand your idea but not in programming terms.

for example

if userid and password dont match after 3 times
interval = 10 minutes
msgbox "your account is locked due to invalid password provided several times"
end if

thanks for your help

[princess12]

will it work even if application quits?

thanks

[darwish]

OK look what you should do:

In the users table in your database add a column having the time of last logIn.

This means that every time user log In you will update this field for the user logged In with the currentTime (NOW())

THE CONDITION COMES HERE

If user provided invalid password 3 times, you update the time field in the users table with the username provided in the user field and update this field by current time + 10min.

Example: Update Users SET "time Field (new field)" = DateAdd ( n, 10, Now()) WHERE Username = "user trying to login"

In the above statement you added 10 min to the current date (check the following link to know more about how to add specific amount of time to current date and time "http://www.techonthenet.com/excel/formulas/dateadd.php")

Finally your last step comes within the Login attemp.

when the user logs in with his user name you should add 1 condition in the statement that checks for the user and password to logIn

add the following

WHERE "time field (new field added) < NOW();

This means that user will now be able to login if the current time is not greater than the time field updated in the table for his account.

[darwish]

will it work even if application quits?

thanks

Ofcourse since the record is saved in your database (according to my previous comment)

[princess12]

well i given it a go and got update error - syntax is wrong, i cant seem to fix it could you please check my update statement

CurrentDb.Execute "UPDATE tblActivitylog (SET LastLogin = DateAdd ( n, 10, Now()) WHERE Username = '" & Forms!Login.txtUsername & _
" And Password = " & Forms!Login.txtPassword & ";"

thanks

[princess12]

also the last line WHERE "time field (new field added) < NOW();

do i put that with the update statement or that in different section of code

[darwish]

well i given it a go and got update error - syntax is wrong, i cant seem to fix it could you please check my update statement

CurrentDb.Execute "UPDATE tblActivitylog (SET LastLogin = DateAdd ( n, 10, Now()) WHERE Username = '" & Forms!Login.txtUsername & _
" And Password = " & Forms!Login.txtPassword & ";"

thanks

UPDATE tblActivitylog SET LastLogin = DateAdd( "n", "10", NOW()) WHERE Username = '" & Forms!Login.txtUsername & "'

[darwish]

also the last line WHERE "time field (new field added) < NOW();

do i put that with the update statement or that in different section of code

you use that when you try to login

you check if the current time is greater than time last login.

its main purpose is that if the user try Invalid password you now update LastLogin by now +10min

user try to login, you check if LastLogin is less than current time than you can logIn.
If LastLogin is greater than the current time This means that He maid several attemts wrong pasword then you will not accept him to enter the form you delay him 10 min.


Do you understand what logic am I working with?

[princess12]

thanks a lot the issue got sorted, but one more - am nearly there

the updates code works but it says you about to update 0 rows, - it doesnt update the lastlogin


if statement of 'if the user tries to login'
Set rst = db.OpenRecordset("SELECT TOP 1 Logout FROM tblActivityLog WHERE StaffID =" & Me.txtEmployee & " And LastLogin < time() ORDER BY StartDate Desc, Login Desc;", dbOpenDynaset)


thank alot for being patient with me

[darwish]

thanks a lot the issue got sorted, but one more - am nearly there

the updates code works but it says you about to update 0 rows, - it doesnt update the lastlogin


if statement of 'if the user tries to login'
Set rst = db.OpenRecordset("SELECT TOP 1 Logout FROM tblActivityLog WHERE StaffID =" & Me.txtEmployee & " And LastLogin < time() ORDER BY StartDate Desc, Login Desc;", dbOpenDynaset)


thank alot for being patient with me

I didnt understand what you had written but below is a summary of the overall process:

1. When user write his username and password and press the login button the following should happen:
a. create a string that store the password and a string that reads the last login
b. Select * from Users where Username = ' UserTextBox'
c. Assign the Password string = Password Selected and LastLogin String = LastLogin for the username
d. If Created String = password entered by the user in the textbox AND LASTLOGIN < NOW() then
open the Form
ELSE
(this means if the user is wrong)
UPDATE USERS SET LastLogin = DateAdd ( n, 10, Now()) Where User = "username in textbox"
End IF

[princess12]

ok i understood the update bit i got it right. but you know from step a to d. due to complex functionality this section of code below checks for user name against password so i dont know where to add this line --> where LastLogin < time()

"If DLookup("StaffID", "tblStaff", "StaffID = " & Forms!Login!txtEmployee _ & " AND Username = '" & Forms!Login!txtUsername & "'" _
& " AND Password ='" & Forms!Login!txtPassword & "'") Then
Set rst = db.OpenRecordset("SELECT TOP 1 Logout FROM tblActivityLog WHERE StaffID ="
& Me.txtEmployee & " ORDER BY StartDate Desc, Login Desc;", dbOpenDynaset)

<-from set rst, the code selects the top records and enables to log out based on the ID that logged in, it prevents loggin in again until the person logs out,

p.s thanks a lot for your help.