Verify password does not match previous 10

[ChrisLayfield]

I have compiled this code from plenty of previous posts and it nearly does everything I need. The last part I am missing for compliance is to verify that the employee's new password does not match any of the previous 10 passwords. Does anyone have any suggestions for efficient code or a sample someplace?

'------------------------------------------------------------
' cmdSavePassword_Click
'
'------------------------------------------------------------
Private Sub cmdSavePassword_Click()
On Error GoTo cmdSavePassword_Click_Err
    On Error Resume Next
    
Dim db As Database
Dim i As Integer
Dim iCount As Integer
Dim intUppercase As Integer
Dim intLowercase As Integer
Dim intNumeric As Integer
Dim intSpecialChar As Integer
Dim CriteriaCheck As Integer
intUppercase = 0
intLowercase = 0
intNumeric = 0
intSpecialChar = 0
CriteriaCheck = 0
iCount = 0
Set db = CurrentDb
    
' Test Password Length
    If Nz(Me.txtPassword, "") = Nz(Me.txtConfirm, "") Then
        If Len(Me.txtConfirm) < 8 Then
            Me.txtPassword = Null
            Me.txtConfirm = Null
            Me.txtPassword.SetFocus
            MsgBox "Password must be at least 8 characters", vbOKOnly
        End If
    End If
    
For i = 1 To Len(Me.txtPassword)
' Test Password for Numeric Character
    If Asc(Mid(Me.txtPassword, i, 1)) >= 49 And _
        Asc(Mid(Me.txtPassword, i, 1)) <= 57 Then
            intNumeric = 1
    End If
    
' Test Password for Uppercase Letter
    If Asc(Mid(Me.txtPassword, i, 1)) >= 65 _
        And Asc(Mid(Me.txtPassword, i, 1)) <= 90 Then
            intUppercase = 1
    End If
' Test Password for Lowercase Letter
    If Asc(Mid(Me.txtPassword, i, 1)) >= 97 _
        And Asc(Mid(Me.txtPassword, i, 1)) <= 122 Then
            intLowercase = 1
    End If
' Test Password for Special Characters Letter
    If Asc(Mid(Me.txtPassword, i, 1)) >= 33 _
        And Asc(Mid(Me.txtPassword, i, 1)) <= 47 Then
        intSpecialChar = 1
    ElseIf Asc(Mid(Me.txtPassword, i, 1)) >= 58 _
        And Asc(Mid(Me.txtPassword, i, 1)) <= 64 Then
        intSpecialChar = 1
    ElseIf Asc(Mid(Me.txtPassword, i, 1)) >= 91 _
        And Asc(Mid(Me.txtPassword, i, 1)) <= 96 Then
        intSpecialChar = 1
    ElseIf Asc(Mid(Me.txtPassword, i, 1)) >= 123 _
        And Asc(Mid(Me.txtPassword, i, 1)) <= 126 Then
        intSpecialChar = 1
    End If
Next i
' Verify Password Meets Complexity Requirements
CriteriaCheck = intNumeric + intUppercase + intLowercase + intSpecialChar
If CriteriaCheck < 3 Then
    MsgBox "The password does not meet the complexity requirements, please re-enter", vbOKOnly
    Me.txtPassword = Null
    Me.txtConfirm = Null
    Me.txtPassword.SetFocus
    Exit Sub
End If
' Verify Password is Unique (Not Used in Last 10 Changes)

'Test Password for Identical Entry
    For i = 1 To (Len(Me.txtPassword) - 1)
        If Asc(Mid(Me.txtConfirm, i, 1)) <> Asc(Mid(Me.txtPassword, i, 1)) Then
            iCount = iCount + 1
        End If
    Next
    
' Save Password or Reject
    If iCount < 1 Then
        db.Execute "INSERT INTO tbl_EmployeePasswords (EmployeeID, Password, CreationDate) VALUES ('" & Forms!frmNCSLogin.cbxEmployeeID & "', '" & Forms!frmNCSPasswordChange.txtConfirm & "',  Date());"
        Forms!frmNCSLogin.txtPassword = Null
        MsgBox "Enter new password for login"
        DoCmd.Close acForm, "frmNCSPasswordChange", acSaveNo
    Else
        Cancel = True
        Me.txtPassword = Null
        Me.txtConfirm = Null
        Me.txtPassword.SetFocus
        MsgBox "Password doesn't match confirmation", vbOKOnly
    End If
    
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If
    
cmdSavePassword_Click_Exit:
    Exit Sub
cmdSavePassword_Click_Err:
    MsgBox Error$
    Resume cmdSavePassword_Click_Exit
iCount = 0
End Sub

 

[vbaInet]

Are the passwords case-sensitive or not?

 

[ChrisLayfield]

Here is the msgbox which contains the password requirements:

Private Sub Form_Load()
    MsgBox "Password must be an 8 character combination of at least three of the following: " & _
        Chr(13) & " - Upper case letters" & _
        Chr(13) & " - Lower case letters" & _
        Chr(13) & " - Numeric character" & _
        Chr(13) & " - Special Character (!@#$%^&*()_+|~-=\`{}[]:" & Chr(34) & ";'<>?,./)", vbOKOnly
End Sub

 

[DCrake]

First thing I noticed is that you are not encrypting the saved password. you need to do this.

On you question though.

Firstly you need to be recording all the users previous paswords in an archive file, simply employee id and password.

Then create a query to display the top ten passwords for a given employee from the archive table.

Secondly you would use a DLookup() to check for matching password in this query. and respond accordingly.

 

[ChrisLayfield]

David - Thanks for the encryption advice, I am hoping to encrypt the entire database when everything is done though I still have to learn how to do that.

For the archive file - I have a table that stores all of the passwords by userID...with what you siad should that table only house the current ID/password and then a different table to house the historical stuff?

Thanks for the path though

 

[DCrake]

Yes hold the current one in the employee table and when a employee changes their password copy the old one to the archive table and replace the exising one with the new one in the employee details table. That way you can differeciapte between what is current and what is new. You could also check that the employees new pasword does not match any other employees password that is currently in use.

Remember
Passwords are like toothbrushes

Keep them to yourself
change them regularly
Use them all the time

 

[DCrake]

Regarding encryption.

Encrypting the whole database is one route I have never gone down. AFAIK is reduces performance by at least 20% locally. simply encrypt/decrypt the password as and when needed. There are a few examples on the forum.

 

[vbaInet]

If you haven't found it, here's on by DCrake:

http://www.access-programmers.co.uk/forums/showthread.php?t=193131

 

[ChrisLayfield]

vbaInet - thanks for that link, I added it and it is aces (thanks DCrake for the programming)

I thought over the encryption and looking at the regulations I need to meet and the data contained in this db, I think I am good with only encrypting the passwords.

 

[vbaInet]

Glad you were able to use what was kindly provided by DCrake.