Access login to be case sensitive

[Emmanuel]

i have a login form which logs in perfectly but i recently noticed that it still logs the user in without considering the case sensitivity of the password.
Would be glad if someone could be of help.

This is the code behind my login button on the form



"Dim FIRST_NAME As Variant, access_level As Variant

If Trim(Me.txt_username.Value & vbNullString) = vbNullString Then
MsgBox prompt:="Username should not be left blank.", buttons:=vbExclamation, title:="SYSTEM"
Me.txt_username.SetFocus
Exit Sub
End If

If Trim(Me.txt_password.Value & vbNullString) = vbNullString Then
MsgBox prompt:="Password should not be left blank.", buttons:=vbExclamation, title:="SYSTEM"
Me.txt_password.SetFocus
Exit Sub
End If

' RETREIVE FROM SAVED QUERY
' ASSUMES EVERY USER GIVEN A NON-NULL ACCESS LEVEL
FIRST_NAME = DLookup("FirstName", "tbl_login", "UserName = '" & Me.txt_username & "' and password = '" & Me.txt_password & "'")
access_level = DLookup("access_level", "tbl_login", "UserName = '" & Me.txt_username & "' and password = '" & Me.txt_password & "'")



If IsNull(FIRST_NAME) = True Then
MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, title:="SYSTEM"
Me.txt_username.SetFocus
Else
MsgBox prompt:="Welcome, " & FIRST_NAME & ".", buttons:=vbOKOnly, title:="SYSTEM"

'arnelgp
'save the first_name and access_level to Tempvars
TempVars("First_Name") = FIRST_NAME
TempVars("Access_Level") = access_level
TempVars("user") = Me.txt_username.Value


' CONDITIONALLY OPEN FORMS
Select Case access_level
Case "Administrator"


DoCmd.OpenForm "ABC"


Case "Accounts"
DoCmd.OpenForm "XYZ"

End Select
End If

[orange]

Review strComp Binary comparison should achieve what you ask.

[Emmanuel]

Review strComp Binary comparison should achieve what you ask.

So how do i apply it to my code?
Would be glad if i could get a practical example using my code

Am thinking of modification in the dlookup line in the code. But I don't know how to go about it

[Micron]

You could compare as in If strComp(access_level,Me.txt_password,0) = 0 Then (good to go) but in such a way that you can know there is a value in Me.txt_password

If IsNull(FIRST_NAME) = True Then
   MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, title:="SYSTEM"
   Me.txt_username.SetFocus
   Exit Sub
End If

If strComp(access_level,Me.txt_password,0) = 0 Then
   MsgBox prompt:="Welcome, " & FIRST_NAME & ".", buttons:=vbOKOnly, title:="SYSTEM"
Else
   Msgbox "some other message"
   Exit Sub
End If

As others here probably know, I'm not a fan of all this password maintenance, resetting and stuff. Instead I'd just have user details in a table, including Windows user login name and pc name they happen to be on). When anyone opens the db if they're not found in the table they don't get in. In the past, I had people call me asking why they can't open the db. They had copied it from the network figuring they could just open it and poke around. If they had got in, it would have closed on them anyway because their copy location would not match the location of the authorized version.

EDIT - the pc name wasn't in the user table. It was stored in the login table record.

[Emmanuel]

You could compare as in If strComp(access_level,Me.txt_password,0) = 0 Then (good to go) but in such a way that you can know there is a value in Me.txt_password

If IsNull(FIRST_NAME) = True Then
   MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, title:="SYSTEM"
   Me.txt_username.SetFocus
   Exit Sub
End If

If strComp(access_level,Me.txt_password,0) = 0 Then
   MsgBox prompt:="Welcome, " & FIRST_NAME & ".", buttons:=vbOKOnly, title:="SYSTEM"
Else
   Msgbox "some other message"
   Exit Sub
End If

As others here probably know, I'm not a fan of all this password maintenance, resetting and stuff. Instead I'd just have user details in a table, including Windows user login name and pc name they happen to be on). When anyone opens the db if they're not found in the table they don't get in. In the past, I had people call me asking why they can't open the db. They had copied it from the network figuring they could just open it and poke around. If they had got in, it would have closed on them anyway because their copy location would not match the location of the authorized version.

EDIT - the pc name wasn't in the user table. It was stored in the login table record.

Thanks for your feedback.
This method will have to require me changing the whole code since the check on user details is on the dlookup line of the code.

I strongly believe that modifying that line with the StrComp might fix the issue.

I tried your code but user details won’t login

[Micron]

This method will have to require me changing the whole code since the check on user details is on the dlookup line of the code.

Not sure what that means, or if you realize that suggestion would be integrated into what you have, not replace what you have.

[Emmanuel]

Not sure what that means, or if you realize that suggestion would be integrated into what you have, not replace what you have.

I modified the line of the code at the section you sent me but the login details didn’t login

[Emmanuel]

Not sure what that means, or if you realize that suggestion would be integrated into what you have, not replace what you have.

So do you suggest integrating to what I have?
If that’s the case, which line of the code should I place it?

[Micron]

Look at post 4. I showed you how to modify the prior If block and where to put the strComp test.
Best if you don't simply say things like 'I used your code but it didn't work' but instead, show us - and please use code tags (# button on posting toolbar) to maintain indentation and readability as I did.

[orange]

Here's a mock up to simulate testing for a case sensitive password match

' ----------------------------------------------------------------
' Procedure Name: testStrcomp
' Purpose: Routine to demo the use of strComp with binary compare.
' in this sample
'   the username is not required to be case sensitive
'   the password is case sensitive.


' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 04-May-23
' ----------------------------------------------------------------
Sub testStrcomp()
          Dim accesslevel As String
10        Dim EnteredUsername As String: EnteredUsername = "Joe User"  'different capotalization since NOT
20        Dim storedUserName As String: storedUserName = "Joe USER"    'case sensitive

30        Dim storedPassword As String: storedPassword = "$MyPasSwOrd"  'the valid stored password

          Dim enteredPassword(2) As String  ' 3 attempts to enter a password
          
          Dim i As Integer

          'represents 3 entered passwords
40        enteredPassword(0) = "smypassWord"  'invalid no $ improper capitalization
50        enteredPassword(1) = "$MyPasSwOrd"  'Valid
60        enteredPassword(2) = "$MyPasswOrd"  'one s is not capitalized

          'access_level = DLookup("access_level", "tbl_login", "UserName = '" & Me.txt_username & "' and password = '" & Me.txt_password & "'")

          'simulate the test
          'password entered must match storedPassword CASE SENSITIVE

70        For i = LBound(enteredPassword) To UBound(enteredPassword)
80            If (EnteredUsername = storedUserName And StrComp(storedPassword, enteredPassword(i), vbBinaryCompare) = 0) Then
90                Debug.Print i & "  Valid login for " & EnteredUsername & " and  Password "
100           Else
110               Debug.Print i & "  !!! INValid login for " & EnteredUsername & " mismatched  Password ---validPwd " _
                      & storedPassword & " vs " & enteredPassword(i)
120           End If
130       Next i
End Sub

'RESULT
0 !!! INValid login for Joe User mismatched Password ---validPwd $MyPasSwOrd vs smypassWord
1 Valid login for Joe User and Password
2 !!! INValid login for Joe User mismatched Password ---validPwd $MyPasSwOrd vs $MyPasswOrd


NOTE: Username is not case sensitive so Joe User matches Joe USER

[isladogs]

I was approached by the OP via a PM at Access World Forums where he goes by the name of @Tiprof.
I asked him to post as a forum thread so others could participate/benefit & he then referred me to this post

My response below was written before seeing this thread and much of it covers the same ground as previous answers...

--------------------------------------------------------------------------------------------------

By default, Access is case insensitive.

It is certainly possible to do a case sensitive check, by using StrComp(EnteredPasswordString, StoredPasswordString, vbBinaryCompare). The output will be -1, 0 , 1 or Null depending on the two string values. Output = 0 means both are IDENTICAL including the case of each character and passes the check. Any other output indicates differences so the check should fail

For more details, see the MS Help article on the StrComp function

However, there is a better approach with an important additional step that will also make your passwords more secure
Passwords should NEVER be stored unencrypted in a database. If anyone gains access to user passwords, you will have major data protection issues. Don't risk it!

Ideally, check the passwords against the active directory which is designed to be secure. If so, no login form is normally needed.

However, if you do want to utilise a login system, encrypt the passwords with a suitable encryption method like RC4.
See my PasswordLogin example database

To do this, each password is stored encrypted using the function RC4(PasswordString, RC4_Key) where RC4_Key is the encryption string. This has the benefit of giving different results depending on the case used. For example:

?RC4("opensesame", "isladogs")
ÌÂ~—KkÙ±

?RC4("OpenSesamE", "isladogs")
ìÂ~·KkÙ‘

?RC4("OPENSESAME", "isladogs")
ì0â^·kKù.‘

?RC4("oPeNsEsAmE", "isladogs")
Ì0Â^—kkù‘

If you look carefully, all of these are subtly different
Then your password check process should do a binary compare of the encrypted version of the entered password with the stored password.

EDIT:
I have just used the above approach to update my example app and make the password validation checks case sensitive

[Emmanuel]

I have been able to fine a fix with this code:

FIRST_NAME = DLookup("FirstName", "tbl_login", "StrComp(username, '" & Me.txt_username.Value & "', " & vbBinaryCompare & ") = 0")
access_level = DLookup("access_level", "tbl_login", "StrComp(password, '" & Me.txt_password.Value & "', " & vbBinaryCompare & ") = 0")

If IsNull(FIRST_NAME) = True Then
MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, title:="SYSTEM"
Me.txt_username.SetFocus
Exit Sub
End If

If access_level = DLookup("access_level", "tbl_login", "StrComp(password, '" & Me.txt_password.Value & "', " & vbBinaryCompare & ") = 0") Then

MsgBox prompt:="Welcome, " & FIRST_NAME & ".", buttons:=vbOKOnly, title:="SYSTEM"

Else
MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, title:="SYSTEM"
Exit Sub

End If

Thanks to you all for your suggestions