Skip to content

Garry Robinson's Access Security Book

This is the book you need for all those situations when Access security is required.

access archon #120


 


Introduction

I have always hated Access security.  It is a pain to secure a database, and a nightmare to modify a secured database – and, after all that trouble, the database really isn’t that secure, as there are any number of utilities available to decipher passwords for secured Access databases.  So I was very glad to receive a new book by Garry Robinson, Real World Microsoft Access Database Protection and Security.  Long-time WAW readers may recall Garry’s guest article (Access Archon #90, Tricky Queries to Impress Your Boss) in WAW 3.18.


Book Description

For those situations when Access security is required, this is the book you need.  Garry covers all versions of Access from 97 to 2003, and every aspect of database security – not just the standard user-level security applied with the Security Wizard, but many other techniques that (when used in combination) can make an Access database much more secure than you might have thought was possible.  Among other topics, the book covers user and object surveillance, developer workgroup security, encryption, database passwords, workgroup security, object protection, and operating system security.

With permission from Garry and his publishers Apress, here are some quotes from Chapter 1 of his book, to give you an idea of what it covers:

This book describes the issues and demonstrates best practice protection and security for Access. I will discuss internal Access security and protection and folder permissions of modern operating systems. Where possible, I will show you strategies to combine both of these security measures to create a number of layers of defense for your database. If you properly adopt and use the protection methods outlined in this book, you should be able to thwart all but the most determined and skilled users and hackers.

Good Access security will require you to set up many roadblocks of different types to keep your database villain at bay. Among the many things that you need to do to secure your database:

·         Hide the important Access menu commands that expose the objects in your database.

·         Stop people from importing your objects into another database.

·         Stop people from getting to your treasured code and objects.

·         Make sure that people use the user interface that you create for the database.

·         Keep the workgroup file that maintains your internal security from your database users.

Unfortunately, if you implement each of these measures on its own, you will be able to lay claim to having only good quality protection and, arguably, some security. To make any security fail-safe, you need to combine different internal protection measures and touch that up with operating system security, all that while understanding the flaws of an old and popular product such as Access.

This book tells you what Access security is, how you apply it, and how to determine whether it works. The book is very pragmatic because in most cases, Access security isn’t very secure anymore, and labeling it that way is misleading, because it is only really offers protection. It is only when you set it up in a certain way and then combine different Access security and protection techniques that you end up with best-practice security for your database. If you then store your databases in operating system-protected folders and open the databases by using secured shortcut techniques, you can lay claim to a secure system because people will have great difficult copying the database; and then when they do, they will be unlikely to unravel your internal security.

If you are a seasoned Access programmer and feel that you have security covered, let this book be the wake-up call that makes you re-assess the database security that you have been involved in. If there is one message that you take from this book, be it that you need to schedule regular reviews of your database security because no matter how good your skills, things can change over time, especially where humans are involved. This means that many Access users and developers are aware of its vulnerabilities and therefore may be able to use those on your database. Worse still, some companies have developed password-cracking software that decodes the built-in Access security. As a result, Access developers have to be extra diligent when it comes to protecting their databases, and this book shows you how to shore up your defenses.

The following sections from Garry’s book give you some techniques for making Access databases more secure with relatively little effort:


Secure Things that You Can Do Now

To get the ball rolling, two relatively simple things that you can consider now will provide some good security without any complications. All the other security procedures that I discuss in the book start off as being protection against an ordinary user. It is only when you combine a number of different protection techniques and set them up in a certain way that you have security for your database. Here is an overview of the straightforward secure things that you can easily do and descriptions of where they are applicable.


MDE Format: Gold Standard Security for Objects

·         If you want to secure the forms, reports, and modules in your database, there is no better and quicker way to do so than to convert your database to the compiled format, called the MDE format. Before you do that, you need to

·         Split your database into two databases. The first will hold all your software (queries, forms, reports, macros, and modules) and the second will hold all your data and relationships. …

·         Make sure that users are not doing legitimate development in the live front-end database.

·         You will need to have well-established control over who develops the front-end database and where that development takes place before you embark on using the MDE format.

·         Ensure that development takes place on a computer drive to which the database users do not have access. To make an MDE-format database, all you have to do is choose Tools | Database Utilities | Make MDE, and you will have your MDE-format database.

If you are unsure whether the MDE format database will suit your needs, use it yourself as your production version. Once you have completed your test, direct a small number of users to try the MDE version. Generally, switching over to the MDE format is quite simple as long as you have good control of front-end database development. …

CAUTION The MDE format will not provide any security for your tables, queries, or macros.


Use the Operating System to Restrict Who Uses the Database

To preclude some of your fellow network users from opening your database, your best option is to use the operating system to restrict the people who will actually have access to the database. To give you some understanding of how this process works, I will give you a brief rundown here:

1.        Place your database and related files in the specific database folder.

2.        Create a new group of users (which I call “Access Editors”) by using a Windows Server or Windows Peer-to-Peer computer.

3.        Make any Windows user whom you want to open the database a member of this new group.

4.        Remove all permissions for users and groups that currently have operating system permissions to open and use the files in the special database folder.

5.        Grant permissions to the new user group (Access Editors) so that they can create, modify, and delete any files in that special database folder. …

 

Although this sample is not complete, I thought it was important enough to demonstrate the permissions in the Windows professional and server operating systems. Using this method to reduce the number of people using the database is very important because the Windows operating system’s security is the result of much past and ongoing research for Microsoft. You can find out more about the operating system permissions in Chapter 12, particularly how to set up folders so that users cannot browse the folder contents. To supplement this security, this book discusses how you can combine using the Windows operating system with the Access internal security tools whenever it can


Supporting Files

The zip file containing this article, in Word format may be downloaded from the Access Archon page of my Web site.  It is accarch120.zip, which is the last entry in the table of Access Archon columns for Woody’s Access Watch.

Garry Robinson is the editor of the VB123.comWeb site.

About this author