Access 2010: The Missing Manual

Securing Your Database

In most shared databases, different people perform different tasks. The easiest way to keep everyone on the right track is to create several distinct front ends, one for each group of people. This lets you gently guide people along in the tasks they perform.

However, customizing the front end doesn’t restrict the abilities of a determined troublemaker. In a large company that relies on a multiuser database, you don’t just think about guiding people—you also worry about restricting them.

Sadly, Access provides a limited security model. You have the ability to lock strangers out of a database with a password, but you don’t have the more fine-grained features you need to restrict a single user from using certain tables or performing certain actions. As you’ll see in the following sections, you can use some workarounds, but none works as well as the security provided by a server-side database product like SQL Server.

Nostalgia Corner: Workgroup Security Gets Locked Out

Old-hand Access experts may remember that Access 2003 included a much more useful form of user-level security called workgroup security. With workgroup security, Access keeps a separate file that identifies what each user and group is allowed to do with the database. Workgroup security is easier to implement than file-based security, and a lot more flexible. In fact, it seems like the perfect solution.

Unfortunately, workgroup security was never truly secure. Well-published workarounds could be used ...