Password Login Screen with session login recording

[DCrake]

This demo has been expanded from my earlier version to include the creation of a login session. Whereby once a user logs in a new session is created and is subsequently closed when the user logs out.

As this is not split you will only ever see yourself as being logged in but the logic is there to be used.

Only Deliliah and Samson have passwords set they are Del and Samson respectively.

Note a simple encrypter/decrypter is used to store the passwords in a table.

If you feel so inclined any feed back regarding this and any other samples and help given can be done via my website feedback page. It's a bit lean at the moment. Need to build it up a bit.

 

[isladogs]

Following a request by MarcusGR in this thread https://www.access-programmers.co.uk/forums/showthread.php?t=302223, I have updated this utility to ACCDB format & made various changes so it will now work in both 32-bit & 64-bit Access

In doing so, I have
a) Replaced the code used to get user name / computer name with a different version using WScript. As a result, the API declarations are no longer needed
b) Added Option Explicit to each code module and declared any variables as necessary
c) made cosmetic changes to the appearance of the forms

The password encoding is done using a XOR function and is very weak/easily decoded.
I have left that as it is but would suggest stronger encryption e.g. using RC4 if passwords are to be stored in a database

NOTE: I didn't check the code for the password expiry. If it doesn't work, please PM me as, like all threads started by David Crake, the thread is now closed.

UPDATE:
The attached file has a serious error in that new users cannot be added.
Use the updated version attached to post #4 instead

 

[isladogs]

As previously stated, whilst I would prefer not to have user passwords stored within a database, if it has to be done the passwords need to be encrypted properly rather than just encoded

I've now done an updated version of this with stronger password encryption based on the RC4 function (previously XOR encoding)

I've also added passwords to all except the last record which is the default 'not set'.
Compare the old XOR & new RC4 versions of each password
XOR could be decoded without much difficulty whether or not the 'cipher' is known
RC4 almost impossible to crack in everyday use unless you know the cipher.

Of course, 'del' isn't a strong enough password for any method of encryption though I expect that was David's point originally.

I would insist on passwords of a minimum length such as 8 characters including at least one of each of these: capital letter, small letter & number

Of course, a skilled hacker could still eventually crack the passwords ...which is why its better not to store these in the same database

The new code is in modEncryption and I've used 'isladogs' as the RC4 cipher.

NOTE: In a production database, I would store the cipher itself in a table, encrypted using a different system!

UPDATE:
The attached file has a serious error in that new users cannot be added.
Use the updated version attached to post #4 instead

FURTHER UPDATE 17/02/2020 - please use latest version v5.3 in post #21

 

[isladogs]

OOPS!
There was a serious error in my two previous versions in that new users could not be added to the system
Many thanks for user MarcusGR for spotting this

Whilst fixing this I took the opportunity to check the rest of the original code and found that various parts didn’t work properly.
For example, the code to force users to update passwords after a set expiry date wasn’t working.

As a result, this ended up as a major rewrite. Many changes made including:

1. Tables tblUsers & tblPermissions have been merged

2. Added form to enter new user info including various optional settings:
a. User access level (1/2/3) with default = 1 (standard user)
b. Password expiry (days) with default=0 (never expire)
c. Change own password (yes/no) with default=No

3. All forms changed to border style = None

4. Form frmLogin changed to Shortcut menu = Yes to allow right click menu during development purposes
This should be reset to No for production use

5. Login button now remains disabled until the password has been validated

6. All various logic permutations completely rewritten removing all recordset code and checked
Amongst other things, the code now checks for:
a. Password expiry date (if any)
b. Users logged in on another computer – blocked for logging in again
c. Users still logged on current computer – previous session ended so new login possible
d. New password cannot match previous password

As supplied, there are 6 users:

To facilitate logic checking:
Billy is not active so won’t appear in drop down list
Jill has no password set so you will be prompted to enter a password
Tommy has a password which expired on 5 Oct 2018 so you will be prompted to enter a new password
The others can login normally (Delilah / Jack / Samson)

This version uses RC4 to encrypt the passwords..
I have not updated the XOR version but that code remains (disabled) if anyone wants to revert to using a weak form of password encoding.

I hope this updated version is useful to others.
It has no API calls so should work in both 32-bit & 64-bit Access
Developed in A2010 but should work in any version from A2007 onwards

I would be grateful for feedback on this. As this old thread is now officially closed, please contact me via PM if you find any issues or just to let me know its working as intended
Many thanks

FURTHER UPDATE 17/02/2020 - please use latest version v5.3 in post #21

 

[isladogs]

Following a comment by MarcusGR in another thread, please note that users need to click Enter after entering their password.

The password is checked and, if it matches, the Login button is then enabled. This is because the code used to check the password runs in the after update event rather than the on change event.

If you think users won't realise this, do one of the following:
a) Change the scrolling text caption to:

..........Please enter your user name and password below then press ENTER

Use spaces at the start (instead of ....).
The spaces are important to separate each scrolling iteration

b) Just replace the scrolling text with static text with no leading spaces
To disable the scrolling, set the timer interval to 0.
To remove it completely also remove the Form_Timer event code

HTH

 

[isladogs]

Many thanks to Diogo Cuba for reminding me via PM of a bug in this application.
The first time you log in an invalid use of null error occurred in DoLogin event of frmLogin

'Create a login event for this user
         lngUserID = DMax("LoginID", "tblLoginSessions") + 1

The error was because I had emptied the table and adding 1 to null is still null!
The fix is very simple:

 'Create a login event for this user
         'v5 21/11/2018 - added Nz to manage case where no record exists
         lngUserID = Nz(DMax("LoginID", "tblLoginSessions") + 1, 1)

I had noticed the issue back in November last year but forgot to upload the fix at that time

Modified version attached - there may be other changes but as it was done 2 months ago I can't remember!

UPDATE 17/02/2020 - please use latest version v5.3 in post #21

 

[DBApprentice]

Many thanks to Diogo Cuba for reminding me via PM of a bug in this application.

Glad I could help, Colin!

 

[Stoic]

@isladogs thanks for this platform. I really like it and would like to use it in my database, but I would like for users to enter their user ID and not select from a combo box. Therefore I would like to change the combo box to a text box, but changing it produces an error that points to Column 2.

I appreciate your help on this, kindly. I must admit, I am not an advanced access database developer.


Thanks,

 

[isladogs]

Hi Stoic
This is a moderated area and I've only just seen/approved your post.
The user name combo has 7 columns, all of which are referenced in the code.
To replace it with a textbox would require a significant rewrite of the code.
Whilst I have no objection to anyone doing so, I don't have the time to spare for this at the moment.

One possible approach to minimise the coding needed would be to keep the combo but hide it.
Add an unbound text box txtUser then add an after update event to set the hidden combo value to that

Private Sub txtUser_AfterUpdate()
Me.cboUser=me.txtUser
CboUser_AfterUpdate

End Sub

If the user enters an incorrect username, that should be covered by the cboUser NotInList event which already exists

Note this is untested code so may need tweaking. Good luck

 

[Stoic]

Hi Stoic
This is a moderated area and I've only just seen/approved your post.
The user name combo has 7 columns, all of which are referenced in the code.
To replace it with a textbox would require a significant rewrite of the code.
Whilst I have no objection to anyone doing so, I don't have the time to spare for this at the moment.

One possible approach to minimise the coding needed would be to keep the combo but hide it.
Add an unbound text box txtUser then add an after update event to set the hidden combo value to that

Private Sub txtUser_AfterUpdate()
Me.cboUser=me.txtUser
CboUser_AfterUpdate

End Sub

If the user enters an incorrect username, that should be covered by the cboUser NotInKist event which already exists

Note this is untested code so may need tweaking. Good luck

Thank you so very much. It worked just as I wanted. You are the best.
Thanks again.

 

[isladogs]

Excellent. Glad I could help.

 

[xyba]

Excellent. Glad I could help.

Hi
I've tried to incorporate you forms and tables into one of my db's (great work btw) but, when I open the login form and select my name from the combo I get the following error.

Error13 in cboUser_AfterUpdate procedure: type mismatch

I haven't changed any formats in either frmLogin or tblUsers so I'm confused. Would you know why I get this error?

 

[isladogs]

Hi xyba.
You posted to a moderated area and I've only just seen your posts . I've deleted the duplicate post. Please see sticky thread about reporting your own posts to moderated areas.

Anyway, a type mismatch occurs when you try to link a number datatype to a text field or other similar situations.

In the example attached, the PK field is text but many user tables have a number or autonumber field for UserID. Have a look at the code and your data and hopefully the error will be obvious.

If not, to save issues with moderated posts, I suggest you post a new thread with a link to this one. Try to give additional details and someone (possibly me) will be able to assist. Good luck

 

[Stoic]

Excellent. Glad I could help.

Thanks again for your help. I would like to direct users to specific forms based on access level. I have the following code:

If DLookup("[AccessLevel]", "tblUsers", "[UserName] = " & Forms!frmLogin!cboUser) = 2 Then
    MsgBox "You have access to Human Resources Section!", vbOKOnly
    DoCmd.OpenForm "frmAdminCorner"
    End If

this is how I have it inserted within your code:

Private Sub DoLogin()

On Error GoTo Err_Handler

        'Create a login event for this user
         'v5 21/11/2018 - added Nz to manage case where no record exists
         lngUserID = Nz(DMax("LoginID", "tblLoginSessions") + 1, 1)
         
        'flag that the user is currently logged in
        Call CreateSession(lngUserID)
        Call LogMeIn(lngUserID)
        'Open the main form and close this one

If DLookup("[AccessLevel]", "tblUsers", "[UserName] = " & Forms!frmLogin!cboUser) = 2 Then
    MsgBox "You have access to Human Resources Section!", vbOKOnly
    DoCmd.OpenForm "frmAdminCorner"
    End If

        DoCmd.Close acForm, "frmLogin"
        
Exit_Handler:
    Exit Sub
    
Err_Handler:
    MsgBox "Error " & Err.Number & " in DoLogin procedure: " & Err.Description
    Resume Exit_Handler
    
End Sub

It is not working. I must be missing out on something.

Please help

 

[isladogs]

Stoic
A reminder that this is a moderated area which is why your three duplicate posts didn't appear.
I've deleted the duplicates.
Please see sticky thread above about reporting your own posts.

I'm answering on my tablet with no access to the original app. Will respond when I can view it.

 

[isladogs]

Your code assumed number delimiters. Unless you've altered the form, the combo is bound to a text field so text delimiters needed.

Just downloaded the app to my tablet. This should work for you

Private Sub DoLogin()

On Error GoTo Err_Handler

        'Create a login event for this user
         'v5 21/11/2018 - added Nz to manage case where no record exists
         lngUserID = Nz(DMax("LoginID", "tblLoginSessions") + 1, 1)
         
        'flag that the user is currently logged in
        Call CreateSession(lngUserID)
        Call LogMeIn(lngUserID)
        'Open the new form and close this one
        If DLookup("AccessLevel", "tblUsers", "UserName='" & GetUserName & "'") = 2 Then
            DoCmd.OpenForm "frmAdminCorner"
        Else
            DoCmd.OpenForm "frmSessions"
        End If
        DoCmd.Close acForm, "frmLogin"
        
Exit_Handler:
    Exit Sub
    
Err_Handler:
    MsgBox "Error " & Err.Number & " in DoLogin procedure: " & Err.Description
    Resume Exit_Handler
    
End Sub

The If ...Else..End If is needed so the code has 'somewhere to go' if AccessLevel isn't 2

 

[Stoic]

Your code assumed number delimiters. Unless you've altered the form, the combo is bound to a text field so text delimiters needed.

Just downloaded the app to my tablet. This should work for you

Private Sub DoLogin()

On Error GoTo Err_Handler

        'Create a login event for this user
         'v5 21/11/2018 - added Nz to manage case where no record exists
         lngUserID = Nz(DMax("LoginID", "tblLoginSessions") + 1, 1)
         
        'flag that the user is currently logged in
        Call CreateSession(lngUserID)
        Call LogMeIn(lngUserID)
        'Open the new form and close this one
        If DLookup("AccessLevel", "tblUsers", "UserName='" & GetUserName & "'") = 2 Then
            DoCmd.OpenForm "frmAdminCorner"
        Else
            DoCmd.OpenForm "frmSessions"
        End If
        DoCmd.Close acForm, "frmLogin"
        
Exit_Handler:
    Exit Sub
    
Err_Handler:
    MsgBox "Error " & Err.Number & " in DoLogin procedure: " & Err.Description
    Resume Exit_Handler
    
End Sub

The If ...Else..End If is needed so the code has 'somewhere to go' if AccessLevel isn't 2

Hi, thank you so very kindly. It works great. Just a little background, I am working on a school database, and so this will help a lot. I am hoping to include an audit trial to know who initiated an entry, who updated an entry, who change an entry and who deleted an entry.

If you have any database developed on this, I will be glad if you could share a copy with me.

Thank you very much, I am grateful.

 

[isladogs]

Hi Stoic
There are various threads related to audit trails here at AWF but I don't have a specific example. Suggest you use the advanced search feature.
However I have several commercial schools apps available at my website (see link below). Several such as School Data Analyser have free DEMO versions that can be downloaded as ACCDE files complete with data for a fictitious school.

In answer to your other point about new threads, all members have the ability to start a new thread. If you don't know how, have a look in the FAQ section

 

[jdraw]

stoic,

Re auditing, I showed a sample use of data macros late in this thread . It was a demo during a "learning experience".

 

[isladogs]

Minor update to version 5.2 to fix date error when new user enters a password.
In certain cases this caused errors 3075/3061

Many thanks to tihmir for alerting me to the issue earlier today.
Considering how often its been downloaded, I'm surprised the error wasn't spotted before!

NEW VERSION 5.2
I've made two changes to frmLogin in event code for cmdLogin_Click & txtConPWD_AfterUpdate.
No other changes / new features

UPDATE 17/02/2020 - please use latest version v5.3 in post #21 which fixes a further date error