Query password change form on load

[enginerdUNH]

Hi,



I am creating a database for members of my town's fire department. I have a login form for each member to login with their own unique username and password. This form has two separate unbound fields for the user to enter both their username and password. On a separate form, new users can set up a "profile" in the database where their first name, last name, username, and password fields are all tied to a member information table which compiles a lot of information from other forms. When the users set up a new profile, the username field auto-populates on update of the member last name field to be MemberFirstName & "." & MemberLastName while the password auto-populates to a default generic password that is the same for all users. Next, when a user logs in for the first time, they will use the default password that was established when they first set up their new profile however the database will check and see that the password needs to be changed and a new form will prompt the user to change their password. What i am having trouble trying to do is figure out the code to make the MemberID, MemberUsername, and MemberPassword query to the user's information from the previous form. After this query, the user would then go ahead and enter the new password into the specified unbound text box and the database would run it's necessary password checks.

[Micron]

Some ideas:
- if those fields are on the fist form, when the 2nd opens, hide the 1st and grab those values from the 2nd form. You could either have additional hidden fields to put the info, or make the new form's fields be those values. I wasn't able to 100% follow your process, so I don't know which. When you no longer need the 1st form, 2nd form code just closes it.
- if the fields are in the table already, don't bother with the 1st form unless you have no other way of getting the user id. Use DLookups to get the values.
- don't bother with a user name type of ID at all - get the Windows user login ID, then it can't be faked - search Environ and fOsUsername as 2 methods you can use.
- you can also pass values to subsequent forms via the OpenArgs property of the DoCmd object (what you use to open the next form) but that's typically reserved for passing 1 value, not several.

[isladogs]

On the same lines as Micron, I would also recommend that you change this to use the network password which can be retrieved using active directory.

The reason for this is that it's a bad idea to store user passwords in the database for security reasons.
If you MUST do so, then the passwords should be encrypted before saving and code added to compare the entered password with the decrypted version of the saved password.

Its extra effort but should avoid any risk of password theft by your users hacking the system

[enginerdUNH]

Thank you both micron and ridders. Micron I believe we’ve had a similar discussion on this in the past and I will mention yet again that I CANNOT use the windows login to control access to the database for multiple reasons.
1. They do not run their computers off of a server
2. The cumputers only have 4 login profiles and the majority of users use the same one.
There probably is other reasons but those are the big ones I can think of. Let me try and give a better explanation of how the user setup/login process works.
1. User clicks a command button on the main switchboard that opens up the user setup form.
2. User enters their first and last name into two separate fields
3. Upon update of the last name field, a username field auto fills to the combination of the first and last name as follows “MemberFirstName.MemberLastName” and the password field autofills to the default new user password.
4. User presses ok to complete new user setup and is taken back to the main switchboard.
5. User now presses member login button and is taken to the login screen.
6. User enters their username and the default password and presses ok.
7. Database sees that the current password equals the temporary password which prompts the change password form to pop-up.
8. The userID, username, and current password autofill when the password change form loads.
9. User chooses a new password and presses ok.
10. Database checks that the password doesn’t match the old password and any other necessary requirements.
11. If all is good, database will allow login access. If not, error message will pop up.

The issue I am having is in step 8 where I cannot seem to get the user ID, username, and password to autofill from the previous form.

[Micron]

I used to think db passwords was a waste of effort when you could simply add a user (windows login name being one of the fields) and lookup the user using the login name as criteria. If not found, they don't get in. After reading the responses in a previous thread where the users were teachers who likely were going to be failing to log off the pc, a db password made some sense. But if a user has Windows authentication, I'm afraid I don't see the authentication route being all that advisable for a shared pc. All I have to do is log on, leave the pc and because you're allowed to use it, you get all my db rights. Maybe you're saying use AD as well as an encrypted password.

I agree the db password should be encrypted, but that would be the least one should do. You have to keep others out of the BE tables (I used a password known to the IT group when linking them). Yet anyone who can get into the encrypted field can use that to thwart security (I'm not going to say how here, but there are 2 ways I can think of). IMO, better to use a routine in vba and shift the Ascii value of the characters to some arbitrary increase and store that value. You can perform that magic on the password input field and compare the result to the table value. Besides, as I mentioned, you need to keep users out of the BE and must compile the FE as a minimum, otherwise you expose the encryption code.

The authentication route might not be suitable for a shared pc environment, especially if multiple users belong to different groups. I'm no Active Directory expert, but it seems to me there's no guarantee the fire department is using it?

[Micron]

then the solution I'd pick would be the first one I gave you.
Since your profile here is Advanced User, I'll presume you know how to reference the fields from the 1st form. If not, provide
- the names of the form and the controls that hold that info
- the names of the 2nd form and the controls you'll either already have to place that info, or the names of any others where you need to store it.
Not sure if you want the info being carried over to show or not

Better yet, compact/repair and zip and post a copy of your db.

[isladogs]

I used to think db passwords was a waste of effort when you could simply add a user (windows login name being one of the fields) and lookup the user using the login name as criteria. If not found, they don't get in. After reading the responses in a previous thread where the users were teachers who likely were going to be failing to log off the pc, a db password made some sense. But if a user has Windows authentication, I'm afraid I don't see the authentication route being all that advisable for a shared pc. All I have to do is log on, leave the pc and because you're allowed to use it, you get all my db rights. Maybe you're saying use AD as well as an encrypted password.

I agree the db password should be encrypted, but that would be the least one should do. You have to keep others out of the BE tables (I used a password known to the IT group when linking them). Yet anyone who can get into the encrypted field can use that to thwart security (I'm not going to say how here, but there are 2 ways I can think of). IMO, better to use a routine in vba and shift the Ascii value of the characters to some arbitrary increase and store that value. You can perform that magic on the password input field and compare the result to the table value. Besides, as I mentioned, you need to keep users out of the BE and must compile the FE as a minimum, otherwise you expose the encryption code.

The authentication route might not be suitable for a shared pc environment, especially if multiple users belong to different groups. I'm no Active Directory expert, but it seems to me there's no guarantee the fire department is using it?

Hi micron.
We've discussed this before and it was my comments about some teachers failing to log off....though its applicable in many situations.
I agree with all you wrote in your reply and was referring to the use of AD ...if the fire department use it.

[enginerdUNH]

then the solution I'd pick would be the first one I gave you.
Since your profile here is Advanced User, I'll presume you know how to reference the fields from the 1st form. If not, provide
- the names of the form and the controls that hold that info
- the names of the 2nd form and the controls you'll either already have to place that info, or the names of any others where you need to store it.
Not sure if you want the info being carried over to show or not

Better yet, compact/repair and zip and post a copy of your db.

Thank you again micron and ridders for your responses. Micron, you don't need to worry about users failing to log off because i have a fail-safe that i will be incorporating that will check and see how long the database has been inactive and after a certain amount of time, log the user out so that nobody can make changes to that person's profile unless he/she logs back in. And i will have all measures in place to protect the passwords from being changed by someone who shouldn't have access to change all that stuff but it should also be noted that the majority of people i am working with are not entirely tech savvy and would have no understanding of how to even hack into the passwords to change them but i do see your point. As for your response about referencing fields from the first form, are you referring to the from where they set up their profile or the form where they are logging in. If the former, that form is not open at all when the user uses the login form. As for the login form, i think that my only problem seems to exist in calling the MemberID as i have successfully called the MemberUsername and MemberPasword from the previous form but i don't know think that i know how to call the MemberID using what i assume would be a DLookup query as this field is not on the login form currently so there's no way to reference it from that form.

[isladogs]

Do bear in mind that if one person is reasonably tech savvy, that's enough to break security of stored passwords.
As well as your existing procedures, you MUST encrypt all passwords saved in your database
If you omit that, you will be in serious trouble when a data breach occurs.

Also how long a period of inactivity do you consider to be safe?

As for your forms, why do you need two?
Why not use hidden controls that are only displayed when the password needs to be changed?

[enginerdUNH]

Do bear in mind that if one person is reasonably tech savvy, that's enough to break security of stored passwords.
As well as your existing procedures, you MUST encrypt all passwords saved in your database
If you omit that, you will be in serious trouble when a data breach occurs.

Also how long a period of inactivity do you consider to be safe?

As for your forms, why do you need two?
Why not use hidden controls that are only displayed when the password needs to be changed?

Thank you ridders for the suggestion to use the same form with hidden controls rather than a completely separate form. I didn’t even consider this as an option and I will work on trying to incorporate this and let you know if I am successful.

As for the encryption and locking of the tables and coding, I have a plan in place to have the tables and coding locked so that nobody except the database administrator (myself) will have access to them.

As for the period of inactivity, I currently have it set to 5 minutes.

[isladogs]

Thank you ridders for the suggestion to use the same form with hidden controls rather than a completely separate form. I didn’t even consider this as an option and I will work on trying to incorporate this and let you know if I am successful.

As for the encryption and locking of the tables and coding, I have a plan in place to have the tables and coding locked so that nobody except the database administrator (myself) will have access to them.

As for the period of inactivity, I currently have it set to 5 minutes.

I know I'm harping on about encryption BUT:
a) no system should be dependent on one user only. There will be times when you are absent (ill/holiday etc) and at some point you may leave
b) you shouldn't have access to user passwords either. That's for your own security as much as everyone else's

There is no point setting up a login system and leaving a back door open no matter how low the risk is in your view
And as I've already stated, if things go wrong and there is a breach, you could be fired or prosecuted
Don't take the risk. Do the job properly

[Micron]

I presume you mean after 5 minutes of inactivity, which seems kind of short to me. What if you're in the middle of something and you have to answer a phone, deal with an inquiring boss or co-worker or need a quick nature break?

You might want to consider a boolean flag that denotes a user has been "locked" out in order to allow processing of any open form, but prevents them from opening any new ones or navigating to a new record without either logging back in or closing/restarting? If you force a db close with unsaved data, Access will attempt to save that data. If it cannot due to data rule violations, you could end up with orphaned or lost records, or corruption.

[isladogs]

Good point re closing down 'nicely' to reduce risk of lost data / corruption

In my equivalent example I set a default inactivity time of 20 minutes (though IT admin can alter it).
If no activity in that time, users get a 30 second warning before it closes (unless they respond in that time)

Any action counts as activity e.g. changing form, using any control
Sitting on an open form without doing anything counts as inactivity