Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2007
    Location
    Auburn, Kansas, USA
    Posts
    37

    Unanswered: Form to change password for user loged into system

    I am trying to figure out the best way to change a user’s password in a database. Here is how I have the Database set up

    I have a few Tables, Queries and Forms setup for this

    Tables
    tblEmployees - Linked Table for all using database to use
    EmpID - AutoNumber - Primary Key
    EmpName - Text - Stores User's Name, appears in pull down list for Logon Form
    EmpPassword - Text - Stores User's Password
    EmpAccess - Text - Stores User's Access Level to Database

    tblCurrentUser - Local table to store user information currently logged onto that machine to run menus and logs
    CurUserKey - AutoNumber - Primary Key
    CurUserName - Text - Stores Current User’s Name
    CurAccessLevel - Text - Stores Current User’s Access Level

    Queries
    CurrentUserInfo – Query that uses the tables tblEmployees and tblCurrentUser with a 1 to 1 join on EmpName to CurUserName to pull up the EmpName, EmpPassword and EmpAccess for error checking in the MenuChangePass form.

    Forms
    MenuLogon - Menu that uses a pull down for available users and then an unbound field to enter a password, When Ok is clicked, in VB it makes sure the user’s Password matches what is in the table tblEmployees, deletes all records in table tblCurrentUser using a SQL statement, writes the user that just logged into the system to tblCurrentUser, writes the user that logged into the system into an activity log and then open the main menu form.

    MenuChangePass - Menu to change the currently logged in user’s password. Consists of 3 unbound fields to enter 1: old Password 2: New password and 3: confirm new password. When a user enters their information and then hits ok in, VB it makes sure that there is information in all 3 fields, that the Old Password is the same as the password in tblEmployees, that the New Password and Confirmed Password are the same and that the New Password is not the same as the Old Password. If all these conditions are not satisfied, the form displays a warning message box telling you what was wrong, blanks out all 3 fields and lets you try again.

    NOW TO THE PROBLEM
    My problem is that once all conditions are satisfied, through VB how so I get the New Password from the MenuChangePass Form to overwrite the EmpPassword entry in tblEmployees. I tried to do a SQL statement to change the password in the Query but get an error message:

    Run-time error '2465':

    CSPTrack Software can't fine the field '|' referred to in your expression


    The code Used for this is
    Code:
            StrUser = DLookup("CurUserName", "tblCurrentUser", 1)
            StrUserLevel = DLookup("AccessLevel", "tblCurrentUser", 1)
            NewPassword = Me.NewPass1
     
            strSql = "INSERT INTO CurentUserInfo  " & vbCrLf & _
            "( EmpName, EmpPassword, Access ) " & vbCrLf & _
            "SELECT  " & vbCrLf & _
            "'" & [StrUser] & "' AS EmpName" & vbCrLf & _
            "'" & [StrPassword] & "' AS EmpPassword,  " & vbCrLf & _
            "'" & [NewUserLevel] & "' AS Access;"
    Would it be easier to just change the record for the user in tblEmployees with a SQL Statement or can you do this in the query? It would be very easy to add the record number of the user into query so I have the record number to change in the SQL statement. Just figuring out the best way to accomplish this task is my current issue.

    Wow that was a long one...

    Any help would be much appreciated.

    -Tony

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I would just execute an UPDATE query against tblEmployees.

    Also, as a security thought, part of security is knowing a valid login name, so I wouldn't have the names available from a drop down list. Depends on how secure you want to be. In any case, no "roll-your-own" security will be as secure as the built in security available.
    Paul

  3. #3
    Join Date
    Apr 2007
    Location
    Auburn, Kansas, USA
    Posts
    37
    Thanks for the quick reply Paul,

    Could you please direct me in the right direction for proper use of the UPDATE statement. I am very green in the gills on the SQL and VB side of things. I really just need to go and purchase of the GOD books on VB and SQL if they are going to have a computer tech write databases as complex as this one is getting.

    I agree with the security risk, but since they have to log onto the domain to even be able to reach the datafile that has the tables with the usernames and passwords stored in it and since the MDB file that stores all of the reports, queries and forms is installed into there local folders for that user I don't really see a problem with the pulldown list for the users. I however can change it to use an unbound box for the username, a pulldown was quicker though.

    -Tony

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    adding paranoia to the mix: plaintext passwords in a table are too easy!

    as a minimum, store only hash(password & salt), and verify against recreated hash(user-input password & salt) each startup.

    'salt' varies from nothing, thru something application unique, to something user + application unique (and ultimately to application-instance + user unique)

    google 'rainbow tables' for why salt
    google 'capicom' for how hash

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Apr 2007
    Location
    Auburn, Kansas, USA
    Posts
    37
    Ok so let me run this past ya and see if I have it right.

    I could do something like this?

    Code:
    ' Set Up Variables to be written to tblEmployees
    
            StrUser = DLookup("Username", "UserInfo", 1)
            StrNewPass = Me.NewPass1
    
    ' SQL Statement To write to tblEmployees
    
            strSql = "UPDATE tblEmployees SET empPassword = '" & [EmpPassword] & "'" & vbCrLf & _ 
            "WHERE EmpName = " & [StrUser] & ";"
    
    ' Execute the SQL Statement
    
            CurrentDb.Execute strSql
    Something sorta like that?

    -Tony

  8. #8
    Join Date
    Apr 2007
    Location
    Auburn, Kansas, USA
    Posts
    37
    Quote Originally Posted by izyrider
    adding paranoia to the mix: plaintext passwords in a table are too easy!

    as a minimum, store only hash(password & salt), and verify against recreated hash(user-input password & salt) each startup.

    'salt' varies from nothing, thru something application unique, to something user + application unique (and ultimately to application-instance + user unique)

    google 'rainbow tables' for why salt
    google 'capicom' for how hash

    izy

    WHOA there! laugh... I am not getting paid to do this one. I think using the windows security will be enough for me and them. If they want something that secure they better contract someone that knows just what they are doing and does it for a living. I know PC based security but as for all that... wow... no thanks. I can't wait until this thing is done so I can go back to playing with my Parts and Pieces.

    -Tony

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Something like that, yes. You'll probably need single quotes around the user name in the WHERE clause, and I'd just get the name from the form, not via DLookup.

    Edit: also, I think you'd want to set the field to StrNewPass.
    Paul

  10. #10
    Join Date
    Apr 2007
    Location
    Auburn, Kansas, USA
    Posts
    37
    Quote Originally Posted by pbaldy
    Something like that, yes. You'll probably need single quotes around the user name in the WHERE clause, and I'd just get the name from the form, not via DLookup.
    Laugh... ya I saw that as I hit the Submit Reply button and the form its self does not use a pulldown list to select the user... The User is stored in a table when they first login, this way it will only change the password of the current user that is loged in on that machine.

    -Tony

  11. #11
    Join Date
    Apr 2007
    Location
    Auburn, Kansas, USA
    Posts
    37
    That worked great guys, thanks for steering me in the right direction! I can’t express enough how much I appreciate your quick replies and helpful suggestions. This is truly how a Forum should work! (un like some of the others I was a member of...) I will have to ask again if I have any other questions or brain farts.

    -Tony

    BTW - Both of those sites were very helpful on SQL (even though one was from Microsoft...) and have been filed away in my favorites for later reference.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •