Results 1 to 10 of 10
  1. #1
    avicknair is offline Novice
    Windows 8 Access 2013
    Join Date
    Oct 2015
    Posts
    3

    Password Protected Query Database

    Hello, I am relatively new to access and was hoping to find some help for an upcoming project that I am performing at my workplace. I am tasked with creating a table with data of all employees and their assets (phones, computers, aircards, etc.) This information is all being put together so that my IT manager can easily use a form to search by name or location. I have gotten as far as creating a form and a query so that I can search by keywords to narrow down the long list of employees.

    The issue at hand is that we want this to be a database used by the entire company. We would like any employee to be able to open the program and search for someone to find things like their phone number, email, etc (a sort of address book). That part is taken care of. What I am looking to do is have the more classified info (phones, computers, aircards, etc.) to be ONLY accessible to my IT manager. I am looking for a way to set up a password in my form and then input the correct formula (whether that is an IF/THEN statement or something along those lines) in the query so that when he types in the password, all of the classified info will be shown.



    Apologies if my terminology is not correct, I have only just started to delve into what seems to be the deep rabbit hole of Microsoft Access. If any clarification is needed, please do not hesitate to leave a question.

    Thank you for reading!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Are you aware that holding down shift key when opening db will bypass most option settings and code will not execute? Access was really meant to be a single user db. It can serve as multi-user but does not have robust security features.

    Password encrypted database is common topic. Did you search?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    ummmmm..... it is definitely not correct to state that Access was meant to be single user........ It is totally a multi user database. As to security: Access is not designed to be Fort Knox - so if your threat is dedicated crackers you want to use the SQL Server product set but you step up to a whole higher level of cost. Access offers reasonable security in terms of typical users a variety of ways. Simple vba can be applied to control visibility or events by requiring a coded password to allow things to be seen or to happen. One can password protect the file itself. One can apply masks. So there are a variety of ways to do things....and it all depends on your situation.

    if your user base is casual - make 1 form without the fields for them, make a second form with the fields for your boss....and give him a separate front end file or put in a simple password method so that form only opens if the correct password is in a text box.....

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Avicknair: I agree with NTC in that Access is fine for multi-user and multi-level for most situations but is not bullet proof. You can enhance the level of protection by doing all or most of these things (not necessarily in the order listed), but I could probably circumvent everything using Access except possibly opening the code project if it's password protected (but I've never tried )

    1) Split the db into front and back files (look up split databases if you don't know what this means)
    2) Password protect the back end THEN link the tables to the front. I would put the BE in a different folder than the FE. You will have to share this password with IT manager or whoever to help ensure it remains available. When linking, you will be prompted to supply the password.
    3) Disable shift bypass key on startup, but provide an "Easter egg" to allow you to cycle it. I use an About form with something like the version info which when double-clicked cycles the property and changes the colour of the text so it is obvious only to those who know. Don't make it something that entices anyone to click on it. This is another fact you'd share with IT guy.
    4) Creating user levels (User; Admin; etc) in a table is useful for governing what is visible to those who open any form or report or tries to invoke any action. I think the best way is to create a custom User object on startup and assign properties such as FName, LName; LoginID; Level ComputerID and reference them as needed. In older versions of my db's I also used these to control what a user sees in the menus. Code to get PC ID and network login ID is readily available, but I would not use the Environ variable.
    5) Go to Options: hide navigation pane; disable special keys; don't allow table design changes in datasheet view - before step 6. You will have to reset some of these settings to be able to alter the template file when creating an updated version, then set them back again.
    6) Make your front end into an mde or accde (compile it) as the case may be, when you're ready to release it. I keep one or two versions of the 'template' file, identified by date (myDB09/21/2015.accdb). I always create new FE with same name as last to keep user shortcuts valid. I haven't tried in a long time so I can't remember if a form or other object can even be opened in design view - I think not.
    7) Not sure I would ever password protect the code project (in VBE). It would be disasterous to forget this password, and code is not viewable in a compiled db anyway.
    8) Being able to code affords more protection because my only macro is an AutoExec which calls the startup code. If a user can get around the bypass (thus not run startup code) and expose the nav pane, all the db objects are visible. However, if they try to open a form or report, it would fail because the user property (whether an object or just a DLookup) cannot be resolved - unless they're opening in design view.**

    Comment: Maybe not related to security, but I also have checked the db startup location and not allowed a user to enter if the db is not in the prescribed folder. For whatever reason, people like to grab copies and put them on their desktop. They'd have access to the tables, but would never get a FE update. To reiterate, a skilled Access user could circumvent many of these protections, but it is not them that you are trying to thwart, is it?

    **Update: to refresh my memory, I created an new accde. You cannot open a form or report in compiled db, but you can open a query or table.

  5. #5
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Quote Originally Posted by June7 View Post
    Are you aware that holding down shift key when opening db will bypass most option settings and code will not execute?
    Are you aware that the shift-bypass can be disabled for a database?

    Are you aware that a compiled databases (mde/accde) locks all forms, reports, and modules for design view and that there is a way to hide the tables and queries (even the show hidden setting will not make them visible)?

  6. #6
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Quote Originally Posted by avicknair View Post
    Hello, I am relatively new to access and was hoping to find some help for an upcoming project that I am performing at my workplace. I am tasked with creating a table with data of all employees and their assets (phones, computers, aircards, etc.) This information is all being put together so that my IT manager can easily use a form to search by name or location. I have gotten as far as creating a form and a query so that I can search by keywords to narrow down the long list of employees.

    The issue at hand is that we want this to be a database used by the entire company. We would like any employee to be able to open the program and search for someone to find things like their phone number, email, etc (a sort of address book). That part is taken care of. What I am looking to do is have the more classified info (phones, computers, aircards, etc.) to be ONLY accessible to my IT manager. I am looking for a way to set up a password in my form and then input the correct formula (whether that is an IF/THEN statement or something along those lines) in the query so that when he types in the password, all of the classified info will be shown.

    Apologies if my terminology is not correct, I have only just started to delve into what seems to be the deep rabbit hole of Microsoft Access. If any clarification is needed, please do not hesitate to leave a question.

    Thank you for reading!
    I have built an equipment tracker in Access to do everything you need. I know it is defensively possible to do.

    As Micron pointed out you want to split the database into a front end (app) and a back end (data).

    I would create two front ends sharing (linked) the same back end. One front end for employees (address book) and a second one for IT that has all the stuff only they need.

    If the concurrent used count will be over 15 then you will probably what o start out with an SQL Server back end.

    This will also allow you to create yet another front end that runs in a web browser. You can use ASP, ASP.NET or even PHP to build a web front. You can use the Access front ends and the web front end all at the same time. The nice thing about a web front end is that it will no require Access to be installed. Also the Web Front end is totally locked down and the data is very secure.
    Last edited by HiTechCoach; 10-29-2015 at 12:48 PM.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Yes, I know those can be accomplished. Just wanted OP to be alerted that there was more to consider in attempting to secure Access db.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Quote Originally Posted by June7 View Post
    Yes, I know those can be accomplished. Just wanted OP to be alerted that there was more to consider in attempting to secure Access db.
    I agree that there is more to securing your data that just a password.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If the concurrent used count will be over 15 then you will probably what o start out with an SQL Server back end
    And if that's not going to happen in the business, then a FE for every user is the alternative. You can even prevent users from using an outdated FE by keeping the 'version' number in a table and having the FE determine if a custom db property matches. They would then have to get the current version. Distributing FE's can be by simple batch file.
    Are you aware that the shift-bypass can be disabled for a database?
    Still not bullet proof because I can reset this remotely. No doubt we're all on the same page in that much can be done to keep prying eyes out, but the higher the level of protection you create, the higher the skills of an Access programmer you can find. Access is a great tool AFAIC: you just need to understand its limitations.

  10. #10
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Quote Originally Posted by Micron View Post
    And if that's not going to happen in the business, then a FE for every user is the alternative. You can even prevent users from using an outdated FE by keeping the 'version' number in a table and having the FE determine if a custom db property matches. They would then have to get the current version. Distributing FE's can be by simple batch file.
    Weather you use an SQL server back end or not, you can still use an Access Front end. If there is a high user count (over 15) I was suggesting using an Access front end with an SQL Server Back end. If the user count is 75-100 or more then then using an SQL Server Back end with the Access Front end will provably be a requirement for performance.

    Quote Originally Posted by Micron View Post
    Still not bullet proof because I can reset this remotely. No doubt we're all on the same page in that much can be done to keep prying eyes out, but the higher the level of protection you create, the higher the skills of an Access programmer you can find.
    I have a way too that has stopped anyone from remotely resetting the bypass. At least no one has been able to spend enough time to crack it so far. And that is the key current key to computer security today.

    Quote Originally Posted by Micron View Post
    Access is a great tool AFAIC: you just need to understand its limitations.
    The limitation to computer security in general are passwords.

    Even SQL Servers (mySQL, MS SQL, oracle etc) can be cracked with enough time.

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

Similar Threads

  1. Replies: 3
    Last Post: 12-15-2014, 06:43 PM
  2. Replies: 2
    Last Post: 09-21-2012, 05:12 PM
  3. linking table in a password protected database
    By bdaniel in forum Programming
    Replies: 1
    Last Post: 01-04-2012, 01:25 AM
  4. Replies: 9
    Last Post: 12-02-2011, 08:52 AM
  5. Replies: 4
    Last Post: 09-14-2011, 12:33 AM

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