Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

Improve Security in Access Databases                          Part 1 (of 2)

This article is a companion to the following items at this website:

   Access File Security ,  Purpose of System Tables ,    Security Challenges

 

As I have written elsewhere:

Access databases can NEVER be made 100% secure

A capable and determined hacker can break any Access database given sufficient time and motivation.

However, by erecting various barriers, it is certainly possible to make the process so difficult and time consuming that it isn't normally worth attempting.

 

This article summarises some of the main steps that developers should take to improve security – it is not necessarily complete

Make sure you make several backups as you add each step as its only too easy to lock yourself out!

 

The second part of this article explains how to do each step with an example application containing the code needed

 

Remember that Access apps (or any applications) are only as secure as the weakest part of the security used

 

 

1.  Split your databases

    In a multiuser environment, Access applications should ALWAYS be split with:

    a)  Shared backend file(s) on the server and only containing tables

         Users should connect to the backend files on a LOCAL area network (LAN)

              - NOT on a WIDE area network (WAN) as it is likely to be slow and cause issues

       NEVER use a cloud based location such as OneDrive or Dropbox as this increases the risk of corruption significantly

        Similarly users should NEVER run a split database when connected to the server using a WIRELESS connection

 

   b)  All forms, reports, macros and module code in a frontend database

       Each user needs to have their own copy of the frontend database located on their own workstation    

       It is ESSENTIAL that users do NOT share the same copy of the frontend as corruption WILL occur

   

 

2.  Backend files (BE)

    a)  Hide all tables

    b)  Hide the navigation pane

    c)  Disable various Access options e.g. UNTICK all the following:

         - allow full menus

         - allow default shortcut menus

         - use Access special keys (this disables the SHIFT bypass – but see below)

    d)  Do ONE of the following:

         - Hide the ribbon

         - Remove Privacy Options from the File menu (otherwise users can undo all the above)

    e)  Encrypt using a strong password & do NOT store this in anywhere in the application (FE or BE)

   

    NOTE: There is no benefit in using an ACCDE file if your backend database only contains tables

 

 

3.  Frontend file (FE)

    a)  Do everything listed above in the frontend as well – no ribbon / no nav pane / shift bypass disabled etc

    b)  ALWAYS use an ACCDE so your code is compiled and therefore not available to end users

    c)  Rename as ACCDR and add code to prevent it being used if the file type is changed back again

    d)  Use strong encryption with a different password to those used in the backend file(s)

    e)  A VBA project password can also be used but it is easy to disable these. DO NOT rely on this alone!

    f)  It is strongly recommended that users login with user name & password:

        - if possible do NOT store the passwords in the database - use Active Directory

        - If you must store passwords in the DB, make sure you use strong 128-bit encryption such as RC4 (see below)

          (rather than just encoding as that is usually very easy to decode) and DO NOT:

                 - store the encryption cipher anywhere it can be viewed

                 - include decryption code in the DB

                 - if you must store the encryption cipher in the DB, encrypt that as well (using a different method)

 

 

4.  Other security features      

    These include:

    - Add registry strings that are checked by the FE & if altered, prevent it running

    - Using activation type code to ‘lock’ the application to a particular workstation and prevent copying

    - Prevent users having direct access to the folders containing BE files

    - Consider removing the taskbar whilst the application is in use but, if so, ensure that is reversed afterwards

 

    IMPORTANT:

    It is possible for knowledgeable users to do ALL the following (and more) from an external application such as Notepad/Excel or another Access database

    - re-enable the Shift Bypass

    - view the connection strings to the BE tables (which exposes the BE password!)

    - change the start up form e.g. to bypass the user login form

    - bypass everything on first opening if the location isn’t trusted

 

   I am deliberately NOT going to explain how to do any of these things here

 

 

5.  Security Challenges

    All the above security features (and more) are included in my various security challenges 

 

    Their purpose is partly fun but also to encourage users to improve their own security by looking into weaknesses in other apps

    Each challenge is meant to be solvable so there are deliberate built-in weaknesses (as well as a couple of unintentional flaws included by mistake!)

 

 

6.  RC4 Encryption

    This code is freely available online and provides very strong 128-bit encryption (good enough for use in Access)

    However, it is no longer considered secure enough for commercial databases storing information such as credit card data etc

    For further details, see https://en.wikipedia.org/wiki/RC4

 

 

7.  File-based or server-based databases

    Access is a file-based application which is fundamentally why it can never be 100% secure

    If you have mission critical data, an Access BE is not the right solution.

    Using a server-based BE such as SQL Server will significantly improve the security of your data.

    However, it does require someone with a knowledge of SQL Server to maintain it.

 

 

Colin Riddington      Mendip Data Systems        Updated 20/03/2019

* Required

To provide feedback on this article, please enter your name, e-mail address and company (optional) below, add your comment and press the submit button. Thanks

Click this link to download this article as a PDF file:    Improve Access File Security

 

Click this link for a detailed explanation of the various steps involved and an example application: Securing Your Database

 

 

I would be grateful for any feedback on this article including details of any errors or omissions

Difficulty level :   Advanced

Return to Home Page Return to Access Articles 1 2 Return To Top
Page 1 of 2