Results 1 to 13 of 13
  1. #1
    ndesforges is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    5

    Question Password protect/encrypt a field in an Access form

    Hello to all!

    This is my first post on this forum and I am very inexperienced with VBA programming, and so please take it easy on me!

    Situation :
    I have a MS Access form connected to a SQL Server database through a ODBC driver. The text boxes on the form are connected to the DB fields. I would like two of the textboxes on the form to be password protected and make encrypt/decrypt the DB fields they are linked to in the process.

    Ex:
    When the form opens, the text boxes have the input mask "password" and the DB fields are encrypted (let's call this the locked state). But, if you press their respective button, a prompt opens and asks for a password. If the password entered is incorrect, the popup reopens with a warning that the password entered is incorrect, however, once that password is correctly entered, the dialog is closed, the DB fields are decrypted and the input masks on the text boxes are cleared until the form is closed again (let's call this the unlocked state. Once the form is closed again, the text boxes and DB fields should be put back to the way they were before, in a "locked" state.

    P.S.


    1. When the fields are "unlocked" they should be visible and modifiable but only until the form is closed.
    2. If the fields could auto "lock" after 5 minutes of inactivity that would be fantastic!
    3. The Form itself is already programmed to require the SQL credentials to unlock, the two fields in question here just need a bit more protection as so only a few people can access it and modify it and to prevent accidental modifications


    4. If it is simpler to make it so all of this happens on a seperate (let's call it "read & modify" form) then I would like to also know how to add a bouton to the main form that opens up this new form.


    Thank you so much in advance to anyone and everyone that helps me with this inquiry.
    Last edited by ndesforges; 10-18-2019 at 10:47 AM.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Wouldn't it be far simpler to hide the fields but not the labels rather than encrypting/decrypting data? You are playing around with source table values just so someone can't see them (it reads like the fields are bound). I've never tried overlaying a solid rectangle on a form field, but I suppose it would work to hide the field yet present an object that gives the appearance that there is something under it. As for making this time sensitive, easy enough to do by starting the form timer (by altering its value from 0 to some other value) when the data has been "uncovered".

    Usually it is better to make data visible to a user based on their profile; e.g. make them part of a user group and grant permissions based on the group a user belongs to. Thus if I'm important enough to know the password, I'm important enough to see the data when I open the form. Your approach seems more complicated than it needs to be, and the work to accomplish it is only applicable to one form. A user group approach can be applied to any form, field, report, ability to add/edit or whatever - in the entire database.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I agree with Micron that this is unnecessarily complex.
    For standard users the two fields should be hidden (or masked).
    For users with appropriate permissions, you could have a button to show/edit the unencrypted fields.
    The easiest way is probably for a hidden textbox placed in the same location to be made visible for a specified time and then hidden again
    Or the button could be toggled so a second click hides the unencrypted fields again.

    However, if you wish to persevere with you approach, you may wish to look at my example Encrypted Split No Strings database which takes the idea to the limit so that all the data is encrypted.
    http://www.mendipdatasystems.co.uk/e...-db/4594566347
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    ndesforges is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    5
    Hello,

    Thanks for your input, it is really appreciated!

    How would you suggest I implement the solution you have provided? Again, I do not have much experience in VBA programming or DB management so although I understand roughly what you are suggesting I implement, I don`t have much of a clue as to how I should implement such a solution.

    Again, thanks a lot for your time and I appreciate your answer!

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi
    Its not clear which of us you are asking or indeed which solution.

    This could be a complex task if your knowledge of VBA coding is fairly limited. Therefore you need a simple solution, at least for now.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    ndesforges is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    5
    Hello,

    My question was in way directed to you both as I seem to understand, you both seem to think that going with the user permission is a much simpler idea. I love it, if it can save me a headache I will gladly follow your lead!

    I am now addressing isladogs :

    "For standard users the two fields should be hidden (or masked).
    For users with appropriate permissions, you could have a button to show/edit the unencrypted fields.
    The easiest way is probably for a hidden textbox placed in the same location to be made visible for a specified time and then hidden again
    Or the button could be toggled so a second click hides the unencrypted fields again."

    I would like to know what would go into adding such a feature. I am guessing I would have to restrict the access to those fields within SQL Server Mannagement Sudio, but I don't know the exact route to take. I am then guessing that I'd have to do something on the MS Access front... But like I said, VBA is not something I am all that familiar with and so I am not sure as to what I need to do exactly. I understand the logic behind the programming, but I am not familiar with the code itself.

    Thanks for the quick answers, I really appreciate it!

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Do your users login to the FE with a user name / password or is the user name extracted using Active Directory?
    Are your users assigned to different groups e.g. Standard user, admin etc.

    If the answer to both the above is Yes, this will be easy.
    If not then you will need to make some changes before that idea can be implemented.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    To me it boils down to whether or not db users share a Windows login (sharing a pc doesn't matter) or if they must login to Windows thus cannot use the db under someone else's Windows login.
    If the former, a password approach is pretty much essential for this and each user needs to log in and out of the db. If the latter, you can make use of an API to get the Windows login name and then no passwords, password maintenance, resetting or login forms are needed for the db.
    Last edited by Micron; 10-21-2019 at 12:25 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    ndesforges is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    5
    As information to both of you : They connect to the DB via a ODBC pilot using SQL Server user credentials. Each of them have the same amount of control over the interaction of the DB (read&write). No Active Directory is being used!

    isladogs :
    Does this answer your question? And if so, what should I do next?

    Micron : As long as the .accdb application is on the PC, the ODBC pilot has been programmed and the user knows his password, they have access to the DB. This being said, they obviously need to login to their windows user profile in order to launch the Access application, but the windows login credentials do not affect in any way the access to the DB under the SQL Server environment.

    I Hope this answers both your questions. I look forward to moving on to the next step.

    As always, very appreciated!

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    My question has nothing to do with whatever protections are imposed on the data source. I think you are saying that every user must log in to Windows to do anything on a pc. In that case, one approach I was alluding to is not having passwords to get into the database. The dba (database administrator) adds user records to tblUsers. Upon startup, code gets the Windows login name via fOsUserName (Google it). The login name is part of the user record in tblUsers. If not found, they don't get in - db closes. You'd also have a table of groups and the userID from tblUsers is a foreign key. This is how you tell what group a person belongs to. When a form opens, it checks what group they belong to and you set up the form accordingly. That's a high level of what the approach is about. I suppose the only difference between this and having passwords is the password management side of things. You'd still need tblUsers and tblGroups.

  11. #11
    ndesforges is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    5
    Micron, if I seem to understand what you are suggesting, that would mean to completely change the way the user connects to the DB from the SLQ Server Authentication, that is currently being used, to a from of Windows Authentication that I would have to program into the DB?

    I also do not see how that would help me with my original task which is to protect two fields from the rest of the DB users but allow one person to access them...

    Maybe I simply don't understand what you are trying to explain either... but if that is the case, some more clarifications would be greatly appreciated.

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    completely change the way the user connects to the DB from the SLQ
    Server Authentication, that is currently being used, to a from of
    Windows Authentication that I would have to program into the DB
    Maybe I will have to bow out because I thought you were referring to ODBC connected tables that were linked but required a password. I have virtually no experience with setting up SQL authentication or management so can't relate to what you're asking.

    Again, I'm saying that if I have to log into Windows you can get my Windows login ID and use that to
    a) allow access to the Access db (wherein it matters not how you connect to any data source), and therefore
    b) decide who gets to see what. That was mentioned in prior posts. That is how you hide (or whatever) forms/reports/buttons/form fields, etc from users - based on their user profile.

  13. #13
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I also never use SQL Server authentication but I'm not sure that part is relevant here.
    Even if users don't log in to your app, the network user names can be used to identify who is logged on.
    You need a table of users and permission levels or at leat a list of users with elevated privileges when using your app.
    Then write code in your app such as

    Code:
    If UserLevel = "Amin" Then
    'do something
    Else
    'do something else
    End if
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 29
    Last Post: 07-27-2019, 12:39 AM
  2. Password protect back end
    By School Boy Error in forum Access
    Replies: 5
    Last Post: 11-23-2018, 09:00 AM
  3. Password Protect Field
    By KMan in forum Access
    Replies: 1
    Last Post: 06-20-2016, 07:56 AM
  4. Password protect a form
    By recon2011 in forum Forms
    Replies: 10
    Last Post: 11-18-2012, 04:36 PM
  5. password protect form
    By nkuebelbeck in forum Forms
    Replies: 2
    Last Post: 01-15-2012, 01:02 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums