Programmable security and user lockouts

[PerkinsSlave]

Morning,

I have created a security log-in for a database. I would like to lock users out after four unsuccessful log-in attempts.

I have the following tables and forms:

- tblUserIDs contains UserID, Password, SecurityLevel, and AccountLocked

The default for account locked, which is a Yes/No, is unchecked. Once it's checked, the account should be locked out. Here's the code once the user enters their ID and password and presses the Enter button:



Private Sub cmdEnter_Click()

Dim AccType As String
Dim lcnt As Long
Dim lockout As Integer

lockout = 0

lcnt = DCount("[AutoNumber]", "tblUserIDs", "[UserID] = '" & Me.txtUserID & "' and [Password] = '" & Me.txtPassword & "'")

If lcnt <> 1 Then
lockout = lockout + 1
If lockout >= 5 Then
MsgBox "Your account has been locked out. Please contact the system administrator.", vbCritical, "Login Error"
Me.txtUserID = Null
Me.txtPassword = Null
Me.txtUserID.SetFocus
Exit Sub
Else
MsgBox "Invalid user ID or password, please try again.", vbExclamation, "Login Error"
Me.txtUserID = Null
Me.txtPassword = Null
Me.txtUserID.SetFocus
Me.chkChangePassword.Value = 0
Exit Sub
End If
Else

AccType = Nz(DLookup("SecurityLevel", "tblUserIDs", "[UserID] = '" & Me.txtUserID & "'"), "")
If AccType <> "" Then ' if the security level has been set, then:
If Me.chkChangePassword.Value = -1 Then
gblUserID = Me.txtUserID ' attach the user id to the global string
gblAccType = AccType ' attach the account type to the global string
Call LogSignIns(Now()) ' log this sign-in
DoCmd.Close
DoCmd.OpenForm "sl-frmChangePassword"
Else
gblUserID = Me.txtUserID ' attach the user id to the global string
gblAccType = AccType ' attach the account type to the global string
Call LogSignIns(Now()) ' log this sign-in
DoCmd.Close
DoCmd.OpenForm "frmMain Menu"
End If
Else ' if the security level has NOT been set, then:
MsgBox "Your account has been locked out. Please contact the system administrator.", vbCritical, "Login Error"
Me.txtUserID.SetFocus
Exit Sub
End If
End If

End Sub



My first thought was to set a counter. As you can see, the counter resets everytime you enter the sub. That part is fine. What I would like to do is, once the counter is > 4, set the checkbox.

I can make that happen, but it won't save to the table. What I can't figure out is how to change the text box in the underlying table without changing the rest of the record, and without creating a new record.

I was thinking some kind of SQL statement, or else maybe using DAO commands such as .Edit, but I can't figure out how to make it work. Any ideas? Or does anyone have a security sub they'd like to share? This one does work perfectly, except for the password lockout.

Thanks!!

 

[PerkinsSlave]

Well, this isn't too encouraging... no replies!

Can anyone tell me how to, in a form SUB, write to the fourth column of data in a table without overwriting the first three columns?

 

[Dan Aragon]

Fellow TwinsCitian....

Unfortunately, this type of security is pretty easy to get around, so hopefully this isn't for a high security type of application!

I briefly read through your routine and you asked how to write to a specific field in a table.

There are a couple of ways of doing this. This is probably most straightforward. You need to open the recordset first in code, find the row of data you want to change, then specify the field you want to edit, something like:

dim rst as Recordset
set rst = Currentdb.OpenRecordset("yourTable")

with rst
   'Search for the User ID in yourTable from the txtUserID field on your form
   .FindFirst txtUserID
   'Open the table for editing
   .Edit
   'Sets the Lockout field to -1 in yourTable
   !Lockout = -1
   'Save the change
   .Update
End With

Make sense? The other way is instead of using "!Lockout=...", you would use ".Fields("Lockout")=..."

 

[PerkinsSlave]

Thanks for the reply, Dan. I actually figured out how to do this with SQL statements. I am selecting the table and updating the particular field where the userid's match.

This is database is not in need of a high level of access control, but I would like to minimize access attempts. Aside from disabling the shift bypass ability and encrypting the database so it can't be examined with a text editor, what else would you suggest?

Thanks again,
Paul