Securing Your Access Database

This post is particularly for any of you that use Access in a multi-user environment.

Selecting the Right Security
When developing in Access to serve multiple users, it is important to consider what level of security you need before you get too far down the path. If you need comprehensive, air-tight security, Access is not the right way to go. Think of Access as a small safe where you keep valuables. You can lock it, potentially bolt it down to the floor, but a skilled thief will simply pry it from the floor, take it home, and figure out how to crack it open with enough time. So if you are highly concerned about data security, look to a more heavy duty platform like MySQL, SQL Server, or Oracle on a centralized database server. If you are satisfied that your employees are not likely to (a) attempt to compromise the data in your database, or (b) take the time to become savvy enough to overcome some basic security measures, Access is just fine.

Securing Your Access Database
Since there are many resources to help you with the necessary tasks of securing your database, I'll just hit the highlights before listing further reading.

A) Limit User Navigation

Be sure to give user's access only to the forms, queries, and reports that are essential to their duties. Hide the database window on startup. And if you choose not to complete (D) below, make sure to eliminate the option to open forms in Design View. Pre-2007 Access formats will allow you to establish workgroup security, which you can then use to limit users to specific objects in the database. If you plan to take advantage of the ACCDB or ACCDE formats in Access 2007, you'll need to think of clever ways to achieve the same result.

B) Lock Down Special Keys

Next eliminate the ability for someone to use Special Keys at startup (Startup>>Options on pre-2007 versions). This will ensure they cannot hit Ctrl-G, as an example, to enter the VB Editor and create some malicious SQL routine.

C) Password Protect

Password protect the database, and if needed, put it in a file server directory that is also protected based on the user's Windows login. This is a key step to prevent someone from accessing your database via an ODBC connection.

D) Convert to MDE

This final step can mean you need to be more careful about error handling, and also adds some steps to deploying updates. But converting to an MDE file means users will be unable to delete/add/change forms and reports, or expose your Visual Basic code.

Further Reading

There are quite a few resources out there on the details of securing an Access 2007 database. The two that really give you a full picture are:
How I Use Microsoft Access User-Level Security by Jack MacDonald
Real World Microsoft Access Database Protection and Security by Garry Robinson