Securing Your MS Access Database Front-End

In this post I’d like to discuss what steps you can take to try and secure, as best you can, your MS Access Front-End application.

Password Protect your FE

As I pointed out in my post entitled Why Ms Access Isn’t A Secure Database – Part 2, a MS Access Front-End is not secure and leaves sensitive information completely open to hacking.  As such, like your back-end (BE), you should always Encrypt with Password your front-end (FE).  This will, in the very least, stop prying eyes from being able to harvest BE passwords allowing anyone to have complete reign on your raw data.

Also, be sure to use a different password for the FE than used with the BE.

Distribute a Compiled FE to your End-Users

Another critical step is to always distribute a compiled version (mde/accde) of your FE to your end-users.  By converting your mdb/accdb into an mde/accde, you lock down certain type of objects within your database.  Users can not open Forms or Reports in design view, nor can the view any of the VBA code.  So in essence by distributing a compile version you minimize any opportunity to mess around with the database itself.

Now an mde/accde surprisingly does not lock down tables, queries or macros.

Also note that since all VBA code is locked down and unviewable, any unhandled errors will cause a fatal application crash, hence the crucial importance of bulletproof error handling throughout all of your VBA code.

Hide the Navigation Pane

As noted in the previous section, an mde/accde surprisingly does not lock down tables, queries or macros so it becomes critical to take the necessary steps to do as much as you can to limit users abilities to get into aspects of the databases they shouldn’t be in.  As such, by simply hiding the Navigation Pane, you eliminate your user’s ability to access these database objects.

Hide individual Database Objects

Of lesser importance, if you’ve taken the necessary steps to hide the Nav Pane altogether, changing the individual object Hidden attribute to True (so as to make it a hidden object) can keep most users from viewing objects in the first place unless they purposely go and changes their Navigation Display Options to Show Hidden Objects.

Use a Custom Ribbon

By completely hiding the standard Ribbon and using your own Ribbon, you can control which commands your users have access to and thus minimize any trouble they can get into.

Disable the Shift Bypass

Even if you prepare your database and hide this and that, you must disable the Shift Bypass property so as to close the back door which enables people to get around all of the above steps you’ve taken to harden your application.

Disable Special Keys

Change your database Use Access Special Keys property.

Create an AutoKeys Macro

By creating an AutoKeys macro, you can disable certain built-in key sequences, such as:

Ctrl+G -> Opens VBE
Ctrl+Break
F11 -> Shows/Hides Navigation Pane

In an ideal world we’d also include the following key combinations, but for whatever reason Microsoft does not allow the use of Alt in AutoKeys, go figure?!

Atl+F11 -> Opens VBE
Alt+F1

This step is somewhat redundant since we disabled Access’ Special Keys in the previous step, but I prefer to do both.  It provides an extra layer of security in case someone manages to change the database property itself.

Create an AutoExec Macro

I personally like to use an AutoExec macro which calls my startup procedure.  Typically, as part of my startup procedure I call a procedure that simply sets/resets the various properties we’ve been discussing (Shift Bypass, Hide Nav Pane, Check that we’re running an compiled copy, …)

Lockdown Where Your Database Can Run

If you are lucky enough that you are running your db on say a CITRIX server, you can easily add code, as part of your startup mechanism, that verifies which computer is running the current application.  If it doesn’t match up with the computer/server name it should be running on, simply close the application.

Note: I actually send an e-mail to the db admin when this occurs with certain information (PC name, Username, IP address, Date/Time, …).

Lockdown Which User Can Run Your Database

Another easy process to implement is to have a table in your database that lists all the windows usernames that are allowed running the database.  At startup, retrieve the current user’s network username and then see if it is in your user table.  If it isn’t close the application.

Note: I actually send an e-mail to the db admin when this occurs with certain information (PC name, Username, IP address, Date/Time, …).

Lockdown Folder Permissions

Yes, simple folder permissions are still one of your best friends when trying to harden any application, not just MS Access databases!  By controlling who can access what, you minimize any potentials for disaster.

When it comes to Access, setting up the BE folder to have Travers permission thus allowing user access to the data but not to the folder itself stops people from nosing around and being able to copy everything and walking away with it.  Then you distribute the FE as you always do (one copy per user locally installed on their PC).

Implement Virtual Password Protected Connection (vPPC)

If you truly want to take things to the next level, you may wish to examine and implement Virtual Password Protected Connection (vPPC).  Alan Cossey was kind enough to provide me with a copy to the initial concept which you can download below since all the other download links are no longer valid.  Do note this is offered with no support whatsoever.

Download “Access - vPPC Security Toolkit” vPPCSecurityToolkit.zip – Downloaded 44960 times – 1.24 MB

 

As always, if you have any tips to share please feel free to leave a comment.

28 responses on “Securing Your MS Access Database Front-End”

  1. Merrikay Lee

    Thank you, Daniel. I found the step to make the production front end user-bullet proof. Don’t want them messing with forms or queries…or even knowing they are there. Great explanation.

  3. Ziq

    Dear,
    Daniel,

    Great post!

    I want to secure my database and do not how to.

    After reading your post. I have a better understanding.

  4. Bernard Lambrey

    Is there a way to prevent objects from being imported into another database? As far as I know, without database password (which is tiresome for users), anyone can freely import the objects…

    1. Daniel Pineault Post author

      In a general sense you are correct, but there are things you can do.

      Hide the Nave Pane. If they don’t know the names of your objects they won’t be able to copy them.

      Add a VBA module to all your objects and password protect the VBA project. This will effectively stop the ability to export forms and reports without first unlocking the VBA project.

  5. YZH

    I was thinking that I’m totally secured just by encrypting the backend, disabling special keys, disabling bypass keys, using a custom ribbon, using “CurrentDb.TableDefs(“some MSys tables, USysRibbons and all my other tables”).Attributes = dbHiddenObject” and error handler to hide all errors that displays the path of my backend, but recently I found this “SELECT [Table].* FROM [Table] IN ‘FrontEndDatabase.acccdb path'” when I was trying to run a query in another database and I found that any user having the frontend can display and edit the data by using this code :@…
    Is there any way to resolve this 🙁
    Thank you!

      1. M

        That is what I’m referring too. I’m unable to open it. It is asking for a password. Please assist.

  7. rkb

    Unfortunately, this doesn’t fix a huge flaw in MSAccess design that I discovered recently, that seems a huge oversight to me.

    As someone previously mentioned, a user can export the objects into a new access database, and your solution prevents this. But what it doesn’t prevent, is someone from Importing the objects in a new database by selecting the front end, followed by a handy list of all tables they can import. This literally puts the linked tables into the new database and allows the user to make edits and changes directly from here, as well as view the data in it’s entirety.

    The only saving grace here, being that an ACCDE file will prevent forms and modules coming across, but tables and queries will.

    1. Daniel Pineault Post author

      This true and why other steps need to be implemented to minimize such issues: lock down folder permissions, Password Protect the Back-End.

      That said, yes, you are right the risk still exists. But if your data is that sensitive/important and you can’t trust your users, the Access is the wrong platform in the first place for the back-end and you should switch to SQL Server, Oracle, …

  8. Benson Onyango

    Hi,
    Am glad to read from you. Just to add on what you have already written, its important to make modules and classes private to stop users from using the FE as libraries thereby access the codes. Additionally, set up some ignition key flags variables which when the application starts via Autoexec macro running a startup function enables the forms which are disabled from starting unless the flags are on. To tighten further, the forms can be added other properties like Permission IDs and Interface numbers to map to some security levels which must be met for forms are opened otherwise they are cancelled. If you are using server based BEs (e.g. SQL Server), be careful to use DNSless type of connection without saving the password and at the same the FE should be carefully designed to communicate with server to avail the data when the FE has a session established and some conditions met so that when a hacker tries accessing the linked objects, he/she finds no data.

    1. Daniel Pineault Post author

      You’d have to explain yourself more. That said, if you try and compile your project and errors are flagged, then you need to fix those issues before you will be able to create an accde. There is also a bug in which ACCDEs won’t work if you have empty procedures, see: http://www.devhut.net/2018/10/31/ms-access-bug-vba-code-doesnt-run-as-accde/.

      If you need further help fixing errors, etc. I’d recommend you post a question in a support forum, see: http://www.devhut.net/2010/06/07/ms-access-forums-and-discussion-groups/

  11. Hassan essa

    Hello Daniel, thank you so much for your help and effort to all of us in this most useful web site about MS access stuff, please I have 2 Questions:
    1. What is the most useful licence management software ( free or paid) that you recommend to secure and deploy split MS Access database to customers.
    2.Can we make an .accde files from a split MS Access database and encode it into an .exe application for automated setup for the end users (ex, via install sheild ) without problems?
    and if there are any complications? what is the alternative to deploy split access database in .exe format?

  13. Hussain

    Dear Daniel,
    I have read all the discussion Mr. Alan Cossey had with some other guy called Bert. They came up with a very good idea to avoid having any tables or queries in the front end database file.

    The security hole they could not find a solution for is the [Automation] problem. A hacker could obtain a pointer to the front end [Application] object using the function GetObject(,[FE.accdb]), and through this pointer he can do almost anything to the FE DB file and BE DB file.

    So, my question to you is :
    Do you have a solution to this security hole?
    Is there a way to prevent hackers from using GetObject() function to get a pointer to the Application object in FE.accdb?
    ”
    The vPPC discussion can be found here…
    https://web.archive.org/web/20101217020939/http://www.utteraccess.com:80/forum/Access-2007-security-t1242310.html

  15. Abdullah Khan

    Hi Daniel,

    Thanks for this useful discussion. But do you have any way to protect your BE password from being extracted by some methods like the one you showed in BEPasswordExtractor or other similar methods/codes?

    Best Regards

    1. Daniel Pineault Post author

      You’d need to password protect the Front-end as well.

      There are also other approaches to obfuscate such information. One approach is to not include the password as part of the connection string, but rather add it dynamically through code at runtime. There are Pros and Cons to this approach obviously. That said, there is no one solution for this. I’ve always been surprised that the connection string, or at the very least username/password, weren’t encrypted in some way.

  16. Bob Alston

    REgarding your statement “setting up the BE folder to have Travers permission thus allowing user access to the data but not to the folder itself” I don’t think that works because the user still needs read write permissions to the folder containing the BE database, so it can create, modify and delete the .LDB/.LACCDB files. Right?
    Bob

  17. Eyob Daniel

    Hi i’m eyob
    I’ve some invoice database and i locked some forms not to add or edit a data, also i locked the navigation bar and i have hide all the objects but still someone is changing my data