Hackers and Peepers and Thieves, Oh My!
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
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
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
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
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)
Set prp = dbs.CreateProperty("StartupShowDBWindow", dbBoolean, True, True)
Once created, you can read and change their values using the
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
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.
See All Articles by Columnist Danny J. Lesandrini