I was asked to review Garry Robinson's new book, Real World Microsoft Access Database Protection and Security published by Apress, because I write reviews, not because I'm a security expert. In fact, my natural overabundant optimism made it difficult for me to rise to the level of paranoia required to really embrace all the security measures suggested in the book. After finishing the read, I have concluded that a little paranoia and cynicism might just save my hide some day.

One reason I was really looking forward to Garry's book was that it sounded like it was going to be more than just a verbose regurgitation of the Access Developer's Handbook security chapter. I was not disappointed. In fact, the author made a point of introducing fresh new ideas into the topic of Access security, even differentiating security methods from protection techniques.

One thing is evident: Garry Robinson has done his research! In addition to having a firm grasp on Access development and knowledge of the Access help files, he reveals some development gems that, if not undocumented, are at least hard to find. By the third chapter, I had learned something new that could change the way I develop access applications. More on that later ... it's my favorite tip!

Speaking of hackers, Garry adds the caveat: "I have been open about the issues caused by password-cracking software and quiet about some of the little issues that cause a threat. So, if you are thinking of using this book as a guide to help you pilfer someone's database asset, you will be disappointed."

Protection verses Security

Early on, a distinction is made between protection and security, but as the chapters unfold, the line between them seems to blur. The word security is generally applied to the steps involved in effectively enabling Access security whereas protection includes things like compiling your apps into MDE format, obfuscating the location of files and implementing operating system security to keep unwelcome guests from even browsing the files.

The second chapter immediately launches into one of the most important of these protection issues and it happens to be the easiest to implement: startup options. If you are not already aware of the effects of hiding the database window, suppressing full menus, disabling the bypass key and the like, then this is an important chapter for you. If you are familiar with these options, were you aware these properties could be set through code? If so, did you know that the last parameter of the CreateProperty method is an option that flags the property in such a way that only users who have admin permissions may change it?

Set Property = Object.CreateProperty Name, Type, Value, DDL

The object mentioned above is a Database object and the following code is used to create startup properties:

Dim dbs As DAO.Database, prp As DAO.Property
Set dbs = CurrentDb 

Set prp = dbs.CreateProperty("StartupForm", dbText, "frmMain", False)
dbs.Properties.Append prp

Set prp = dbs.CreateProperty("StartupShowDBWindow", dbBoolean, True, True)
dbs.Properties.Append prp

Once created, you can read and change their values using the simpler syntax:

dbs.Properties("StartupForm") = "frmNewStartForm"
dbs.Properties("StartupShowDBWindow") = False

The download that comes with the book includes an Access database with code to handle the above-described settings more gracefully than what I have published here. If, for example, the property does not exist and you attempt to read or write to it, you get an error. If, on the other hand, you try to create it and it does exist, that also generates an error. Garry's code takes care of all this, encapsulated into a form that may be imported into any Access database and used with your applications.

Another trick that falls more in the protection category than security is the ability to hide database objects, such as tables. This is the tip alluded to above that is my favorite. Keep in mind that it only works for versions 2000 and greater, but it is a nice line of defense. Access 97 did expose an attribute for tables called dbHiddenObject, but as Garry points out, setting this attribute to true would flag your table as temporary and it would be deleted during the next compact.

Newer versions of Access actually expose an application method for hiding and showing objects programmatically. Of course, if users can get to the Options form from the menu, they can simply toggle the Show Hidden Objects option to true and see your hidden forms, queries, etc., but the menu piece of the protection puzzle is covered in a later chapter. Here are some examples of reading and writing hidden attributes.

Application.SetHiddenAttribute acForm, "frmMain", True
fIsHidden = Application.GetHiddenAttribute acReport, "rptSales"

This chapter mentions two other really cool features that work with all versions of Access, but apply only to tables. One way to hide a table is to name it with the prefix of Usys. Therefore, if you create a new table and name it UsysCustomers, it will disappear from the database window.

The other cool trick is to set the attributes of a table to dbSystemObject. Doing so causes the table to be treated like the Msys system tables. Not only does this hide the object when the Show System Objects option is cleared, it causes the tables to be non-updateable from the database window. You cannot add or edit records in any table that has this code applied to it:

dbs.TableDefs("tblOrders").Attributes = dbSystemObject

The strange but useful oddity to this setting is that, while it is not editable from the database window, it still works fine when bound to a form. This is a simple and effective way to protect data in tables from power users who may like to browse data casually. This was the tip I mentioned above as my favorite and it says a lot about the depth of Garry Robinson's knowledge and/or research on the subject.

The next few chapters discuss topics that one may or may not ordinarily associate with security. For example, Garry looks at each of the following from a security and protection standpoint.

• Use of AutoExec and AutoKeys macros
• Splitting data from client
• Implementation of error handling
• Compacting and backing up your database
• Exporting and importing objects to and from text files
• User surveillance techniques
• Effective use of menus and toolbars

Security at its best

From what I gleaned, I am going to split the security topic into two segments: Access Workgroup Security practices and Windows Operating System security issues. As mentioned above, the suggestions given for using Access security are not topics I have seen covered in many of the other Access programming books I have read. Garry describes the process he has developed over the years to create a secure environment for the user while simplifying the logon process. He calls this the Anonymous Windows Authentication Method which, when used with Windows 2000 or Windows XP group accounts, provides a simple and secure way for users to log on to your databases.

Since this is the meat and potatoes of the book, I will refrain from stealing Garry's thunder by reproducing his logic here in this article. Even if I wanted to, I realize that my lack of expertise with Access Workgroup Security coupled with the lack of space for this article would make that impossible. Being relatively unfamiliar with Access security, I had to read over this section twice and even then, I did not really begin to understand it until I hashed it out with some members of my local Access user group.

Should you buy this book?

If you need a solid method for securing your Access databases that goes beyond what you might glean from the help file or from basic Access books, then you need this book, Real World Microsoft Access Database Protection and Security. Peter Vogel, who wrote the book's forward, said it is a volume he will keep near at hand. If that is how Peter feels, then how much more so will this book deserve shelf space at my programmer's nook?

If, however, you do not have pressing Access security needs you may still find this book extremely beneficial. While writing this review I flipped through the book looking for my notes and points I had highlighted with a red marker. They were numerous and it was difficult choosing what I wanted to highlight (and/or reveal) here in this article. Because the book explores all aspects of security, not just creating a workgroup file and setting permissions, we get a glimpse into some of the really clever ways to use Microsoft, things that Garry Robinson has picked up in the decade or so he has been programming with Access. While not a verbose tome, weighing in at less than 500 pages, it is packed with valuable information that is sure to enhance every aspect of the Access database applications you create.