Results 1 to 8 of 8
  1. #1
    SemiAuto40 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    41

    Password protect front end and back end multiple user - how? Access2010

    I am to the point of deploying my DB (Access2010) but I need to split the DB into FE and BE. I don't want to leave the back end vulnerable. Currently I have multiple users and passwords for each hashed and stored in a table. My DB is more protected now because of my login form and password scheme. It does not seem to make sense to have the backend so visible and accessible to fools. Every thing I read says split the database - but no one gives any info on how to secure it except for a single password. My DB (as stated above) has multiple passwords and access levels for each user. Isn't there a way to protect the backend? What is everyone doing?



    Thanx for your replies.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    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
    tanvi is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    100
    There are two methods to protect the backend database:

    1) I just put a simply little "autoexec" macro that runs in the back end..

    I have that macro launch a msgbox command that says you don't have
    permissions. The user clicks ok, and they are kicked out.


    2) If you open the frontend and delete the table links, and then recreate them,
    you'll be prompted for the backend password - that will get stored with the
    link, and the user won't be asked again. You could do this on your copy of
    the frontend, and then distribute the updated frontend to the users.

  4. #4
    SemiAuto40 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    41
    Quote Originally Posted by tanvi View Post
    There are two methods to protect the backend database:

    1) I just put a simply little "autoexec" macro that runs in the back end..

    I have that macro launch a msgbox command that says you don't have
    permissions. The user clicks ok, and they are kicked out.


    2) If you open the frontend and delete the table links, and then recreate them,
    you'll be prompted for the backend password - that will get stored with the
    link, and the user won't be asked again. You could do this on your copy of
    the frontend, and then distribute the updated frontend to the users.

    What I am trying to do is maintain separate passwords for users on the front end and have the back end so that everyone can't look through it.

  5. #5
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Sorry for the late reply, but I've recently started looking at this issue myself. And what it comes down to is that it is EXTREMELY difficult to lock down an Access Database!

    I've sketched together a rough idea of how to do it, but since I don't have a copy of 2010 at work yet (it's coming though!) I can't do any VBA programming/testing.

    Basically, it works like this: You have your Front-end DB and two (count them TWO) Back-end DBs.
    • For the Front-end DB, I strongly recommend using a freshly decompiled ACCDE - compiled ones tend to have readable code imbedded inside them, making some of the VBA Code accessible by opening the Front-end in a Text/Binary File editor. The only Tables you should include in this file are Temporary ones that ONLY include non-proprietary/confidential data for the same reason you should de-compile it (so no Sales/Purchase Orders Tables folks!).
    • The FIRST Back-end DB should contain ONLY the Users Table. This Table should have an additional (encrypted) field containing the password used to access the SECOND Back-end DB (be sure to use the Cryptographic API to encrypt it, don't just "hide" the text behind a so-called PASSWORD datatype). Although the second DB's password is the same for each user, it will be encrypted using each user's password. Thus, you'll need to store it separately for each user.
    • The SECOND Back-End DB should contain the remaining security-related Tables (Groups, Permissions, Memberships, etc.) as well as all your data Tables. This DB (the entire file) should be encrypted with the password hidden inside the FIRST Back-end DB.


    Then it works like this:
    • The user opens the Front-end DB and gets a Form asking for their username/password.
    • Then the system attempts to decrypt the encrypted Field in the FIRST Back-end DB using the user-supplied password.
    • If successful, then it uses VBA to create temporary links between the Front-end and SECOND Back-end DBs that last until the Front-end DB is closed.
    • If unsuccessful, then no harm. The user gets an error message and has to try again.

  6. #6
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    I maintain a database that is split amongst almost a dozen different front ends. All the login is handled by the built in ms access accounts and groups.

    Long story short though. This method of security only works as long as the user does not hold shift when starting the db. If they do then there is nothing you can do about it. I am currently considering on moving the database to Infopath or even making a .net website.

  7. #7
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Yeah, on anything before Access 2007, real security was a technical and literal impossibility. In fact, even WITH the new encryption capabilities of Access 2007+, effective security is still somewhat limited...

    You can't salt passwords (because the salt itself would have to be unencrypted and accessible to the user - and thus, pointless) and users have direct, physical access to the back-end database. That means that even if the file itself is encrypted, all they need to do is copy it to a thumb drive and take it home to decrypt at their leisure. And with the advent of cloud computing and multiple core processors, it's actually possible to brute-force some of these encryption methods now (not easy or fast, but still possible).

  8. #8
    rankhornjp is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jul 2011
    Posts
    46
    Quote Originally Posted by Perceptus View Post
    I maintain a database that is split amongst almost a dozen different front ends. All the login is handled by the built in ms access accounts and groups.

    Long story short though. This method of security only works as long as the user does not hold shift when starting the db. If they do then there is nothing you can do about it. I am currently considering on moving the database to Infopath or even making a .net website.
    Change the extension on the front-end to .accdr (forces it to run in run-time mode, disabling edit functions). I'm not saying this is fool-proof, but at least you know that MOST people won't know how to access it.

    You can change the back-end as well...

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

Similar Threads

  1. Replies: 1
    Last Post: 06-14-2012, 01:07 PM
  2. password protect form
    By nkuebelbeck in forum Forms
    Replies: 2
    Last Post: 01-15-2012, 01:02 PM
  3. Password Protect Forms
    By jlclark4 in forum Security
    Replies: 3
    Last Post: 01-25-2011, 04:26 PM
  4. readonly password protect
    By mlgehle in forum Security
    Replies: 1
    Last Post: 03-13-2010, 08:59 PM
  5. Password Protect Forms
    By Robert M in forum Programming
    Replies: 3
    Last Post: 01-15-2010, 01:50 PM

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