List of Figures

Chapter 1: The Access Protection and Security Driving Instructions

Figure 1-1: The demonstration database for Access 2002.

Figure 1-2: The permissions on a folder that a normal Access database uses.

Figure 1-3: The invaluable Google Groups tab.

Figure 1-4: Garry's security journey found him working on databases in the Gobi Desert.

Chapter 2: Protecting Your Database with Startup Options

Figure 2-1: Opening the startup options on the Tools menu.

Figure 2-2: The startup options available for Access 97.

Figure 2-3: The Main Switchboard startup form in the Northwind sample database.

Figure 2-4: The Database window makes the data and objects easily accessable to everyone.

Figure 2-5: Selecting the AllowToolbarChanges option will give your users access to most options.

Figure 2-6: The frmStartupProperties enables you to change the startup settings in code.

Figure 2-7: The users group does not have Administrator permission for the database.

Figure 2-8: Opening the Northwind database.

Figure 2-9: Setting up the Autokeys macro to make getting around Access a little easier.

Chapter 3: Using Database Options and Attributes to Protect Data and Objects

Figure 3-1: Choose the database options from the Tools menu.

Figure 3-2: The Access 2002 General tab with the most secure settings selected.

Figure 3-3: The MRU list reveals the location of databases that you have visited.

Figure 3-4: The Summary tab of database properties.

Figure 3-5: The summary information is visible if you hover over the file in Windows XP.

Figure 3-6: The MSysCompactError table shows problems with compacting.

Figure 3-7: The erroneous lines with "###" entries caused the Compact process to fail.

Figure 3-8: This form demonstrates the VBA code to change the General tab options.

Figure 3-9: The Access 2002 Edit/Find tab options, shown with the most secure settings selected.

Figure 3-10: This form demonstrates the VBA code required to change the Edit/Find tab options.

Figure 3-11: When you run an Insert query, you must confirm the action by responding to a prompt.

Figure 3-12: Access 2002 Advanced tab with the most secure settings selected.

Figure 3-13: This form demonstrates the VBA code required to change the Advanced tab options.

Figure 3-14: Right-click a table, query, form, report, macro, or module and choose Properties.

Figure 3-15: Select the Attributes Hidden check box to hide the object.

Figure 3-16: The Access 2002 View tab with the most secure settings selected.

Figure 3-17: The Object Browser makes hidden modules visible. Example is from Access 97.

Figure 3-18: The Project Explorer window enables you to navigate to all modules and code in your project.

Figure 3-19: This form demonstrates VBA code options for system tables, hidden objects, and temporary tables.

Figure 3-20: Hiding a column in a table or a query can help protect your information.

Chapter 4: Providing a Solid Foundation with Good Programming Practices

Figure 4-1: The AutoExec macro in the demonstration database includes a call to a function.

Figure 4-2: The Database Splitter wizard in action. Heed the warnings!

Figure 4-3: Enter the destination of the back-end database.

Figure 4-4: Arrows next to table names denote linked tables.

Figure 4-5: Update the links by clicking the Select All button.

Figure 4-6: The frmRelinkDatabase demonstration form.

Figure 4-7: The frmErrorHandling form demonstrates the error handling for VBA code.

Figure 4-8: When an error occurs, the error handler displays a message box.

Figure 4-9: This message box is a result of no error handling.

Figure 4-10: The Allow Viewing Code After Error check box in Access 97.

Figure 4-11: The disabled Debug button in Access 97.

Figure 4-12: Viewing the code in break mode.

Figure 4-13: Access help has a lot of material on linked tables.

Chapter 5: Backing up and Recovering Your Databases

Figure 5-1: Testing whether you have exclusive access to a database.

Figure 5-2: Backing up files on a regular basis is essential.

Figure 5-3: Adding the database to a .ZIP file.

Figure 5-4: Choosing to compact a database for backup purposes.

Figure 5-5: Selecting a new folder and entering a name that reflects that the database is a backup.

Figure 5-6: The exported comma-delimited file viewed in a text editor.

Figure 5-7: The second window of the Import Text wizard.

Figure 5-8: The Shippers table from the Northwind database in XML format.

Figure 5-9: The text file that can reload the tables into a blank database.

Figure 5-10: The SaveAsText and LoadFromText methods viewed in the Immediate window.

Figure 5-11: The Back Up All Objects to Text form.

Figure 5-12: The message that appears to tell you where the files went and how to recover them.

Figure 5-13: Folder showing all the objects exported to individual files.

Figure 5-14: The Product Sales query after being exported to a backup text file.

Figure 5-15: The VBA recovery file that helps import all the objects into a blank database.

Figure 5-16: The database documentor output shows important database properties.

Figure 5-17: The relationship window, which you can print as part of your backup material.

Figure 5-18: The Import Objects wizard after the Options button is clicked.

Figure 5-19: The Backup menu command.

Figure 5-20: Access 2003 backup wizard in action.

Chapter 6: User and Object Surveillance

Figure 6-1: The Jet User Roster form, which shows users in a database.

Figure 6-2: Warning received when users try to connect to a database that has been locked.

Figure 6-3: Finding a database from which you require a list of users.

Figure 6-4: LDB Viewer, showing a list of users in a database.

Figure 6-5: The Access Workbench, showing a list of computers in a database.

Figure 6-6: The no security details warning from the Access Workbench.

Figure 6-7: The frmUserObjectLogs form shows how to log users and object use.

Figure 6-8: The hidden user logging form.

Figure 6-9: The UserLogs table, which stores logging details at time of entry and exit.

Figure 6-10: A report that shows Windows user ID information.

Figure 6-11: The rptGR8_UserObjectLogs report, showing activity for forms and reports .

Figure 6-12: The Windows XP user ID as shown in fast user switching mode.

Chapter 7: Protecting Your Database with Menus and Toolbars

Figure 7-1: Menus, toolbars, and shortcut menus.

Figure 7-2: Customizing toolbars by using the View menu.

Figure 7-3: Customizing toolbars by right-clicking.

Figure 7-4: Naming a new custom toolbar.

Figure 7-5: Selecting the commands that you need for your toolbar.

Figure 7-6: Adding a separator with the Begin a Group command.

Figure 7-7: Converting a toolbar to a menu bar.

Figure 7-8: Dragging New Menu to the menu bar.

Figure 7-9: Adding a form to a custom menu.

Figure 7-10: A custom menu for the Northwind database.

Figure 7-11: The message invoked when you make a toolbar into a shortcut (pop-up) menu.

Figure 7-12: Displaying shortcut menus with other toolbars.

Figure 7-13: A demonstration of a Form View shortcut menu.

Figure 7-14: Adding custom menus and toolbars to your form.

Figure 7-15: The frmSafeFormMenus form, which changes all form shortcut and toolbar properties.

Figure 7-16: Adding the Protection menu, which includes forms from the demonstration database.

Figure 7-17: The startup properties, which include information on menus and toolbars.

Figure 7-18: The menu bar at the top of the page, which now becomes your custom menu.

Figure 7-19: Fewer menu items available in the built-in menus.

Figure 7-20: The frmDisableMenuItems form, which disables/enables developer commands.

Figure 7-21: The sample software, with disabled commands on the Access built-in menus.

Figure 7-22: - Additional toolbar VBA code in the frmShowMenus form.

Chapter 8: Developer Workgroup Security

Figure 8-1: The design for the developer workgroup strategy.

Figure 8-2: Finding the workgroup administrator in the Security submenu in Access 2002 and Access 2003.

Figure 8-3: The first dialog that appears when you run the Workgroup Administrator.

Figure 8-4: The developer workgroup setup information.

Figure 8-5: Inserting a new Admin user password.

Figure 8-6: Adding a new user, called Developer.

Figure 8-7: A user who doesn't belong to the Admins group.

Figure 8-8: Removing the Admins group from the Member Of list box.

Figure 8-9: Renamed user and security commands

Figure 8-10: Building a developer workgroup file.

Figure 8-11: Clearing the Read Design check box in the Permissions area.

Figure 8-12: Changing the ownership of the form to the Developer.

Figure 8-13: The error message issued when an object cannot be imported into another database.

Figure 8-14: A warning message that appears when the user account cannot convert a database.

Figure 8-15: The Convert/Open Database message, which appears when you open a database with a later version of Access.

Figure 8-16: The Change Owner tab shows that the Developer is the owner of the database.

Figure 8-17: The frmCheckandChangeOwnership demonstration form.

Figure 8-18: The User and Group Permissions dialog.

Figure 8-19: Database Administer permission stops users from changing the startup options on this dialog.

Figure 8-20: Clearing all permissions for all objects for the Admin user.

Figure 8-21: Removing permissions for objects owned by the Users group.

Figure 8-22: The minimum permissions that should be applied to the Users group.

Figure 8-23: Removing design permission from a module in Access 97.

Figure 8-24: The user account that does not have the permissions will receive this error message.

Figure 8-25: Help in Access 2002.

Chapter 9: Security Concerns, Encryption, and Database Passwords

Figure 9-1: Encrypting/ decrypting a database from the Tools menu.

Figure 9-2: Verifying the ownership of the database.

Figure 9-3: Message that appears when you cannot encrypt and decrypt a database.

Figure 9-4: The Accounts and Groups tables from a workgroup file.

Figure 9-5: A password recovery program, which retrieves complex workgroup passwords instantly.

Figure 9-6: Opening the database in Exclusive mode to add a database password.

Figure 9-7: Setting the database password.

Figure 9-8: The change database password command.

Figure 9-9: Removing Administer permission from the Users group.

Chapter 10: Securing Data with Workgroup Security

Figure 10-1: Making sure that the Developer account owns all the tables.

Figure 10-2: Removing all the permissions for the Database object type.

Figure 10-3: The message that results from completely removing the database object permissions.

Figure 10-4: Checking whether all permissions have been removed for the Admin account.

Figure 10-5: Removing permissions from some tables for the Users group.

Figure 10-6: Error message that indicates that the Admin account can no longer open the table.

Figure 10-7: Setting read-only permission for all tables for the Users group.

Figure 10-8: A table, query, or form in read-only mode.

Figure 10-9: Making a new account that will be used to edit data.

Figure 10-10: Adding a new users group to the database.

Figure 10-11: Adding the Editor user to the Full Data Users group.

Figure 10-12: Granting full data permissions to the Full Data Users group.

Figure 10-13: No permission to open the Categories form due to read data permissions.

Figure 10-14: Right-clicking the Access executable to send a shortcut to the desktop.

Figure 10-15: Modifying the target line of the shortcut by right-clicking the shortcut file.

Figure 10-16: The frmCmdLine form, which allows you to mimic shortcut files securely.

Figure 10-17: Page one of the security wizard, where you can select an existing workgroup file.

Figure 10-18: Page two of the security wizard, showing the optional selection of objects.

Figure 10-19: Page three of the wizard, which allows you to choose the different permission schemes.

Figure 10-20: Page four of the wizard, which allows you to assign permissions to the Users group.

Figure 10-21: Page five of the wizard, which allows you to add additional user accounts, passwords, and PIDs.

Figure 10-22: Page six of the wizard, which allows you to allocate users to groups.

Figure 10-23: Saving the unsecured database in the final page of the wizard.

Figure 10-24: A report showing all the security settings and passwords.

Figure 10-25: The report of the workgroup file.

Figure 10-26: The Editor should have no permissions for any table.

Figure 10-27: The Full Data Users group permissions on tables.

Figure 10-28: The Access 97 User-Level Security wizard.

Figure 10-29: The Access 97 Users-Level Security wizard should not look like this.

Figure 10-30: Adding a new group account to the end user's default workgroup file.

Figure 10-31: Adding the Admin account to the group.

Figure 10-32: Multiple users on a single Windows XP home computer.

Figure 10-33: The secure folder where the Access default workgroup file is stored.

Figure 10-34: Other limited accounts cannot look at your personal files.

Figure 10-35: The design view of a RWOP query, showing the Run Permissions properties.

Chapter 11: Object Protection and Security Measures

Figure 11-1: Entering the developer workgroup information into the wizard fields.

Figure 11-2: All users requireRead Design permission for queries.

Figure 11-3: The Open/Run permission for forms.

Figure 11-4: Entering the full path to a database in the Source Database field.

Figure 11-5: A list of tables and queries in the source database.

Figure 11-6: Removing all permissions for the Invoices query for the Users group.

Figure 11-7: Choosing Owners as the query permission property.

Figure 11-8: A form opened as a class object cannot be put in design mode.

Figure 11-9: Protecting the VBA project by a password.

Figure 11-10: Password-cracking software figures out your VBA project password.

Figure 11-11: Changing the location of the database in the Data Link Properties dialog.

Figure 11-12: Changing the System Database property to point to the workgroup file.

Figure 11-13: The Field Pane, showing all tables.

Figure 11-14: The Employees DAP, which now works with user-level security.

Figure 11-15: When users open a data access page, they will need to enter a user name and password to view it.

Chapter 12: Protecting and Securing Your Database with the Operating System

Figure 12-1: Establishing a network share on the /data/ folder.

Figure 12-2: Entering the details for the network share.

Figure 12-3: The network share, now set up and available to be referenced in the Address bar.

Figure 12-4: Adding new users in the Users and Passwords dialog.

Figure 12-5: Selecting the Restricted User option.

Figure 12-6: The new account, which is now a member of the Users group.

Figure 12-7: The first stage in adding a new group of users.

Figure 12-8: Viewing all the groups on your computer.

Figure 12-9: Adding user accounts to the Group that will edit your databases.

Figure 12-10: The new User account is now a member of two groups.

Figure 12-11: Typing the path to the Network share into the Address bar.

Figure 12-12: Choosing properties to change permissions on a folder.

Figure 12-13: Removing permission from the Everyone group.

Figure 12-14: Adding the two groups to the permissions for this folder.

Figure 12-15: The file and folder permissions.

Figure 12-16: The error that appears when a user account cannot open a folder.

Figure 12-17: Setting up an equivalent user account on the peer-to-peer server.

Figure 12-18: Entering and confirming the password on the peer-to-peer server.

Figure 12-19: Joining this new user to the Access Editors group.

Figure 12-20: Modifying folder permissions for the Access Editors group.

Figure 12-21: The Access control settings for the folder.

Figure 12-22: Selecting a user or group.

Figure 12-23: Selections required to stop a group from browsing the contents of a folder.

Figure 12-24: The Access Editors now have a new deny permissions entry.

Figure 12-25: Confirming that the deny permission is going to take priority.

Figure 12-26: The error received when browsing to a folder that has no listing permission.

Figure 12-27: You cannot browse the folder.

Figure 12-28: A shortcut file that points to a database in a protected folder.

Figure 12-29: Setting up password protection on your logon screen saver.