Real World Microsoft Access Database Protection and Security

501 Pages·2004·18.958 MB·English

by  Garry Robinson (auth.)

Real World Microsoft
Access Database Protection
and Security
GARRY ROBINSON
APress Media, LLC
Real World Microsoft Access Database Protection and Security
Copyright © 2004 by Garry Robinson
Originally published by Apress in 2004
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any
means, electronic or mechanical, including photocopying, recording, or by any information
storage or retrieval system, without the prior written permission of the copyright owner and
the publisher.
ISBN 978-1-59059-126-0 ISBN 978-1-4302-0793-1 (eBook)
DOI 10.1007/978-1-4302-0793-1
Appendix C reprinted with permission from From Access to SQL Server, ISBN 978-1-59059-126-0
published by Apress. Copyright© 2000 by Russell Sinclair.
Trademarked names may appear in this book. Rather than use a trademark symbol with every
occurrence pf a trademarked name, we use the names only in an editorial fashion and to the
benefit of the trademark owner, with no intention of infringement of the trademark.
Technical Reviewer: Frank Rice
Editorial Board: Dan Appleman, Craig Berry, Gary Cornell, Tony Davis, Steven Rycroft,
Julian Skinner, Martin Streicher, Jim Sumser, Karen Watterson, Gavin Wray, John Zukowski
Assistant Publisher: Grace Wong
Project Manager: Sofia Marchant
Development Editor: Tracy Brown Collins
Copy Editor: Kristen Imler
Production Manager: Kari Brooks
Production Editor: Janet Vail
Proofreader: linda Seifert
Compositor: Susan Glinert Stevens
Indexer: Kevin Broccoli, Broccoli Information Services
Cover Designer: Kurt Krames
Manufacturing Manager: Tom Debolski
In the United States: phone 1-800-SPRINGER, email orders@springer-ny. com, or visit
http://I'A-IW.springer-ny.com. Outside the United States: fax[phone], email
orders@springer. de, or visit http: I !"tMW. springer. de.
For information on translations, please contact Apress directly at 2560 Ninth Street, Suite 219,
Berkeley, CA 94710. Phone [phone], fax [phone], email info@apress. com, or visit
http:/ l"tMW.apress.com.
The information in this book is distributed on an "as is" basis, without warranty. Although every
precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall
have any liability to any person or entity with respect to any loss or damage caused or alleged to
be caused directly or indirectly by the information contained in this work.
To my father, Keith, I hope that I will be as inspirational
to my sons, Sean and ]ames, as you have been to me.
Contents at a Glance
Foreword ................................................................................................................ xiii
About the Author ................................................................................................. xv
About the Technical Reviewer ..................................................................... xvi
Acknowledgments ................................................................................................ xvii
Chapter 1 The Access Protection and Security
Driving Instructions ............................................................ 1
Chapter 2 Protecting Your Database
with Startup Options .......................................................... 23
Chapter 3 Using Database Options and Attributes
to Protect Data and Objects ...........................................
49
Chapter 4 Providing a Solid Foundation
with Good Programming Practices .................................. 81
Chapter 5 Backing Up and Recovering Your Databases ............. 109
Chapter 6 User and Object Surveillance ....................................... 161
Chapter 7 Protecting Your Database with Menus
and Toolbars .......................................................................... 197
Chapter 8 Developer Workgroup Security ....................................... 233
Chapter 9 Security Concerns, Encryption,
and Database Passwords .................................................... 287
Chapter 10 Securing Data with Workgroup Security ................... 307
Chapter 11 Object Protection and Security Measures ............... 363
Chapter 12 Protecting and Securing Your Database
with the Operating System ............................................. 403
Appendix A Specific Access Security Information .....................
453
Appendix 8 Registering the Access Workbench ..............................
459
Appendix C Why Migrate from Access to SQL Server? ................. 461
Index ...................................................................................................................... 475
v
Contents
Foreword ..............................................................................................................
xiii
About the Author .............................................................................................
xv
About the Technical Reviewer ............................................................
xvi
Acknowledgments ............................................................................................
xvii
Chapter 1 The Access Protection and Security
Driving Instructions ....................................................
1
What's This Book Going to Do for You? ....................................................
1
The Overall Road Map ......................................................................................... 4
Your Personal Driving Instructions .......................................................... 7
About the Book and the Demonstration Material ................................
10
Secure Things that You Can Do Now ........................................................... 14
Does Upgrading Assist in Security? ........................................................ 17
Further Reading .................................................................................................. 19
Security: The Journey Never Ends ............................................................. 20
What's Next? ........................................................................................................ 22
Chapter 2 Protecting Your Database
with Startup Options ..................................................
23
Do I Need to Read this Chapter? ...............................................................
24
Manually Changing Startup Options ........................................................
25
Programming the Startup Options ............................................................... 30
The AllowBypassKey Property ........................................................................
42
Using Homemade Accelerator Keys
in Restricted Environments ...................................................................
45
Further Reading ............................ :. ....................................................................
47
Reflecting on this Chapter ..........................................................................
47
What's Next? ........................................................................................................ 48
vii
Contents
Chapter 3 Using Database Options and Attributes
to Protect Data and Objects ................................
49
How Will This Chapter Help Me? ................................................................. 50
The Protection-Related Database Options .............................................. 52
Hiding Objects in the Database Window .................................................. 67
Further Reading .................................................................................................. 79
Reflecting on This Chapter .......................................................................... 79
What's Next? ......................................................................................................... 80
Chapter 4 Providing a Solid Foundation
with Good Programming Practices .....................
81
Is This Chapter Worth Reading Now? .........................................................
82
The AutoExec Macro Provides a Flexible Springboard ......................
83
Splitting Your Database to Make It Easier to Protect ................. 87
Error Handling Shields Your Code ........................................................... 97
Further Reading ................................................................................................ 105
Reflecting on This Chapter ........................................................................ 107
What's Next? ....................................................................................................... 107
Chapter 5 Backing Up and Recovering
Your Databases ...............................................................
109
What You Must Know About Database Backups and Recoveries .........
110
Backing Up Multi-User Databases .............................................................
112
Normal Backups ................................................................................................
120
Creating Compressed (.ZIP) Archives ....................................................
122
Compacting Databases .....................................................................................
124
Saving Tables to Text Files ......................................................................
128
Exporting and Recovering Programming Objects ...............................
140
Size Does Matter: How the Text Backup Files Will Help .............
152
Backing Up Other Information ....................................................................
153
Backing Up Your Database by Using Access 2003 ...............................
156
Further Reading ................................................................................................
158
Reflecting on This Chapter ........................................................................
159
What's Next? ....................................................................................................... 159
viii
Contents
Chapter 6 User and Object Surveillance ........................... 161
Will Surveillance Really Help? ............................................................... 162
The Jet User Roster Form ............................................................................ 163
The LDBView Utility ....................................................................................... 169
The Access Workbench ..................................................................................... 173
Logging Windows Users, Computers, and Access Accounts ............. 176
Recording When Objects Are Opened ...................................................... 182
Retrieving the Computer Name, Windows User ID,
and Access Workgroup ID by Using VBA Code ................................ 187
Checking for Users Who Skip the Startup Sequence ...................... 191
Further Reading ................................................................................................ 193
Reflecting on This Chapter ........................................................................ 195
What's Next? ...................................................................................................... 196
Chapter 7 Protecting Your Database
with Menus and Toolbars ........................................
197
Will Menus and Toolbars Help Protect My Database? ...................... 198
Building Toolbars, Menus, and Shortcut Menus ................................. 199
Integrating Menus into Your Forms and Reports .............................. 212
Modifying the Built-In Menus and Toolbars ....................................... 224
Further Reading ................................................................................................ 229
Reflecting on This Chapter ........................................................................ 231
What's Next? ...................................................................................................... 232
Chapter 8 Developer Workgroup Security ...........................
233
Why Is Developer Workgroup Security So Important? ...................... 234
The Developer Workgroup File Strategy ................................................ 240
Preparing Your Developer Workgroup File ............................................ 245
The Developer Workgroup File Is Ready ................................................ 254
Securing Your Database with Your Developer
Workgroup File ........................................................................................... 261
One Secure Object Marks a Developer's Territory .......................... 262
Transferring Ownership of the Database
and All Its Objects ................................................................................ 267
Maintaining Object Ownership and Change of Ownership ............... 269
Setting Database Permissions .................................................................... 273
Further Reading ................................................................................................ 282
Reflecting on This Chapter ........................................................................ 284
What's Next? ...................................................................................................... 286
ix
Contents
Chapter 9 Security Concerns, Encryption,
and Database Passwords ...........................................
287
What Is It That We Should Be Concerned About? ............................... 288
Are Password Retrieval Programs All Bad? .......................................... 289
Importing Objects from Other Databases .............................................. 289
Database Encryption ....................................................................................... 291
Workgroup Security Files ............................................................................. 295
Database Passwords .......................................................................................... 298
VBA Project Passwords ................................................................................... 305
Further Reading ................................................................................................ 305
So What Are the Best Access Security Strategies? ........................ 305
Reflecting on This Chapter ........................................................................ 306
What's Next? ....................................................................................................... 306
Chapter 10 Securing Data with
Workgroup Security ..................................................... 307
What Do I Need to Know About Securing My Data? ............................ 308
Trusting Your Workgroup File to Other Users ................................... 310
Trusting Your Users by Adopting the Same Workgroup File ........... 318
Opening Databases with Shortcut Files ................................................ 323
Time for Reflection ....................................................................................... 330
Security Wizards .............................................................................................. 333
Securing Data with PID Authentication ................................................ 345
Protecting Data Outside the Company Network ................................... 355
Server-Based Databases ................................................................................. 359
Further Reading ................................................................................................ 360
Reflecting on This Chapter ........................................................................ 361
What's Next? ....................................................................................................... 362
Chapter 11 Object Protection
and Security Measures ............................................. 363
Have I Done Enough to Protect the Objects in My Database? .... 364
A Workgroup Security Refresher ............................................................... 365
Protecting Linked Tables' Paths with Remote Queries .................. 371
Protecting Your Query Design .................................................................... 374
Hiding Your Query (Design) in Forms and VBA Code ........................ 376
Protecting Forms by Using VBA Code ....................................................... 377
Protecting Reports by Using VBA Code .................................................. 381
Protecting Macros by Using VBA Code .................................................... 382
Protecting VBA Code by Using a Password ............................................ 382
X
Contents
Using Data Access Pages with Workgroup Security .......................... 385
Access Data Projects .....................................................................................
390
The MOE Database Format Revisited .........................................................
391
Further Reading ................................................................................................
399
Reflecting on This Chapter ........................................................................
400
What's Next? ......................................................................................................
401
Chapter 12 Protecting and Securing Your Database
with the Operating System ...................................
403
Why Is Operating System Security Important
for My Database? .......................................................................................
404
Overview of Applicable Operating System Security ........................
406
Proof-of-Concept Operating System Security .....................................
409
The Access Protected Folder Strategy .................................................. 427
The All-Important NTFS Format .................................................................
439
Protecting Against Unattended Computers ............................................ 441
Issues of Relevance in Windows XP ......................................................... 443
Checklist of Other Security-Related Issues ..................................... 446
Further Reading ................................................................................................ 449
Reflecting on This Chapter ........................................................................ 450
Is That the End of the Journey? .............................................................
451
Appendix A Speci fie Access
Security Information ................................................
453
Appendix 8 Registering the Access Workbench .................
459
Appendix C Why Migrate from Access
to SQL Server? ...............................................................
461
What Is Migrating? ......................................................................................... 462
Access versus SQL Server ............................................................................
464
Reasons to Migrate .........................................................................................
469
Reasons Not to Migrate .................................................................................
471
Migration Considerations ............................................................................ 472
Index ...................................................................................................................
475
xi