Real-World Access Security

<< Click to Display Table of Contents >>

Navigation:  Protection and Security >

Real-World Access Security

Danny Lesandrini          

For months I looked forward to the arrival of this new book. When I finally got a copy, I devoured it in a single weekend and I wasn’t disappointed with what I read. Garry Robinson’s new book is a must have for all serious Access developers: This 492-page book is well worth its $59.99 list price. Here, Danny Lesandrini offers his review.

 
frontcover_zoom40
Find out more about the book and where to buy it here

Application security is an enigma. At least it is to me, and when I mentioned Garry Robinson’s new book, Real World Microsoft Access Database Protection and Security, to members of the Denver Area Access User Group, I got the feeling that there are others who feel the same way. Sure, we’ve all secured a database before, some with more finesse than others. But when it comes down to it, our ears perk up when we hear other developers share tips and tricks for securing and protecting databases.  

Why a book about Access security?

I suppose I should be a little embarrassed to admit this, but I was unaware how easy it is to crack a “secured” Microsoft Access database. Garry claims that his book will “tell you what Access security is, how you apply it, and how to determine whether it works.” Garry also suggests, “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 reassess the database security you have been involved in.”

What Garry asserted in his book was confirmed by my friends in the user group: For less than $60, you can buy a hacker utility that allows you to peek into Access workgroup files and undermine Access’ security. While I figured security could be breached, I had no idea it was so easy.

While I’ve implemented Access security, I confess that I fall into the category of those in need of a “wake-up call.” Sure, I’ve read the chapters from the Developer’s Handbook about setting up security in your databases, and I’ve even played around with code to modify users and permissions. But I’m an experienced Access developer, and I wasn’t expecting to find anything new in Garry’s book. I was pleasantly (or should that be, unpleasantly?) surprised.

In addition to discussing strategies to fully secure your Access workgroup files, Garry takes a holistic approach to the problem, enumerating just about every possible security breach from file locations exposed in shortcuts to the danger of unattended computers. Each potential problem is described and a solution or workaround is proposed. Even if you skipped the chapters on hardcore security, the suggestions on implementing protection would be enough to ward off 80 percent of your security problems.

Protection from a fresh perspective

Before launching into a detailed discussion of how to implement Access user security, the book identifies numerous “low risk” steps that you can quickly apply to your database that deliver a great deal of protection for a very small amount of effort. These include suggestions on how to do the following: • Hide important menu commands. • Create custom toolbars. • Prevent people from importing your objects. • Protect your code. • Obfuscate the location of your database. • Use error handling to avoid a security breach.

Many of these points have little to do with what I traditionally think of as Access security issues. I guess that’s why I liked the book. For example, Garry revealed code for setting a table attribute that would cause it to behave like a system table. By applying the following code to your tables, users will be unable to add or edit records by double-clicking on a protected table in the database window. They will, however, be able to add and edit data through your custom forms that use these tables as their data sources. Is that cool or what?

Dim dbs As DAO.Database, tdf As DAO.TableDef  

Set dbs = CurrentDb  

Set tdf = dbs.TableDefs("tblOrders")  

tdf.Attributes = dbSystemObject

The sample database that comes with the book includes code to help you determine whether someone is currently attached to the data file, as well as a suggestion for how to automatically log out users for nightly maintenance. While none of this is rocket science, these are wildly popular questions among Access power users, popping up almost weekly at the Microsoft Access newsgroups.

Another nice little piece of code that’s too verbose to reproduce here (another reason to go out and get your own copy of the book) is Garry’s section about backing up your data and client objects. In addition to explaining the new backup feature in Access 2003, he goes into detail discussing some code for exporting all data as text and/or XML. This is a unique approach to data backups but one that certainly wouldn’t fall susceptible to data corruption, as is sometimes the case with mdb files. Sure, it’s a beltand-suspenders approach, but when it comes to security and protection, paranoia is the key.

The same chapter describes how you can automate the export of all database objects (such as queries, forms, reports, macros, and modules) as text files. Again, backing up your client objects to text files isn’t a bad idea when you consider how often mdb files become corrupt. More than once I’ve had to revert to a backup copy of a form when the one I was working on flipped out for no apparent reason. For the life of me I couldn’t understand why it happened, but I’m sure the restore process would have been simpler had I followed the backup method described in Garry’s book.

Garry also mentions how using this method once helped him find bloated objects in one very large database file. After analyzing the text files created by his backup process, he was able to identify the problem and shrink a 100MB mdb file down to 20MB. I just didn’t expect to find valuable tips and tricks like this in a book about Access security. Perhaps that’s why I consider the book to be such a value.

Real-world security

Let me stress this: This book looks at “real-world” security. Years ago, implementing basic workgroup security was enough to protect your database investment. Now, in the real world, hackers have created an environment where you need to be more savvy and proactive. While I’m not ashamed to say I’m not especially savvy when it comes to Access security, I can be proactive. Without stealing Garry’s thunder, here’s what his new book suggests as the basic first steps in securing your database:

Build a developer’s workgroup file that’s never distributed to your users.

Produce a second workgroup file that doesn’t include admin information.

Secure your database files by using the operating system security (discussed in Garry’s article in the January 2003 issue of Smart Access).

Implement Anonymous Windows Authentication (AWinA).

Set permissions in such a way as to prevent the database from being imported elsewhere.

Set the anonymous Admin account to read-only access.

Transfer ownership of the database and all its objects.

Consider the costs and benefits of database encryption.

Each of these suggestions is examined in considerable detail. Sometimes, the explanation gets rather longwinded, but Garry assures us that these methods are more difficult to explain than they are to implement. Securing your database can take as little as a few hours and is well worth the effort.

Final thoughts

This book, Real World Microsoft Access Database Protection and Security, was written with one objective in mind, and it remained true to that goal. The author states:

Protecting and securing your database is a matter of balancing software technology and people processes. If you trust the people who use your database, then data protection is a straightforward computer process. If you cannot afford to trust your users, you have to make it as difficult as you can for users to thwart your security and protection measures, but at the same time make it as easy as possible to use your database.

Simple protection. That’s the objective of this book and an objective achieved.

Find out more about the book and where to buy it here