Access is a flexible software product, as is illustrated by the Options dialog box, with its comprehensive list of more than 50 different settings. Because this book focuses on issues of security, I have identified and will discuss only those options that either relate to security or will protect your database from inappropriate use.
Choose Tools ˜ Options to open the Options dialog box. This dialog box contains many options spread out over 10 different tabs. I will discuss the different options that we are interested in by grouping them into their respective tabs. Table 3-1, which gives you an overview of these protection-related options, lists the option, the tab that it can be found on, the version of Access when it was first introduced, whether the option is localized to the Windows user (WinUser) or to the current access database (CurrentDB), and the recommended protection value (if any).
Tab | Option | Version | Applies To | Recommended Value |
---|---|---|---|---|
General | Recently Used File List | 2000 or later | WinUser | Cleared [*] |
Compact on Close | 2000 or later | CurrentDB | Cleared | |
Remove Personal Information | 2002 or later | CurrentDB | N/A [**] | |
Edit/Find | Confirm Record Changes | 97 or later | WinUser | Selected |
Confirm Document Deletions | 97 or later | WinUser | Selected | |
Confirm Action Queries | 97 or later | WinUser | Selected | |
Advanced | Default Open Mode | 97 or later | WinUser | Shared |
View | Show Hidden Objects | 97 or later | WinUser | Cleared |
Show System Objects | 97 or later | WinUser | Cleared | |
[*] Unless your Windows account is secured (Window XP or Windows 2000) [**] This value is only a temporary measure. |
In the sections that follow, I describe each of these tabs in more detail. Those discussions will involve the specific options available, what value you need to specify to protect your database, and how the developer can interrogate and manipulate the option by using VBA code.
The General tab applies to Access 2000. Changes to the General tab options take effect immediately (without your having to close and reopen the database) and apply to all databases that your computer opens. To view the options grouped under the General tab, choose Tools ˜ Options ˜ General, as shown in Figure 3-2. On that form, I have deliberately cleared the following protection-related options for better security:
Recently Used File List
Compact on Close
Remove Personal Information from this File
Now I will discuss those particular General tab options in the sections that follow.
The Recently Used File List option allows you to hide directory path links to the Access databases that you have used most recently (shown in Figure 3-3). As I explain in Chapter 12, hiding the location of your Access database is an important part of your security.
Note | This option is sometimes referred to as MRU, which means "most recently used." |
Files used most recently are a security issue because people can observe this path while you are demonstrating your database or if Access is open when you are away from your desk.
I read an article once that described a person who, while demonstrating an Excel spreadsheet to a supplier, inadvertently showed the names of other suppliers on the spreadsheet names. The lesson here is to consider turning off the most recently used option if you work at the same location as your users or if you are visiting a client who may have more than a passing interest in file names or directory locations.
Tip | In Access 97, you can manipulate this list only by changing the Windows registry by using VBA code. I am not sure that this risk is worth that level of sophistication. If you do have this problem, open a few sample databases just before you head to that important meeting, and the list will not reveal anything. |
Most recently used lists appear in the Task pane in Access 2002. These lists do not appear if you turn this option off.
If you are using Windows XP and Access XP, you will find that you can keep your most recently used lists protected by setting up a new Windows account on your computer. This operating system protection works especially well with Access 2002 because each user has his or her own MRU lists when he or she starts Access. You can also protect yourself by introducing a password window. Both these protection methods are covered in more detail in Chapter 12. Also in Chapter 12, you will find out how to remove the Windows XP recent document list, which can also reveal the location and names of databases.
Now we will look at the personal information that is stored in the database options, then find out how the database options allow you to remove it.
Microsoft Access allows you to store basic information to describe your database: who built it, where it originated, who developed it, and various other things. This information is readily accessible on the Summary tab (File ˜ Database Properties ˜ Summary), as shown in Figure 3-4. This descriptive information is stored in the Summary and Custom tabs, and you should be aware of this information before you distribute your database.
In Access 2002 or later, if you select the Remove Personal Information from this File option, Access erases the author, manager, and company descriptions from your database. The remainder of the Summary or General tab properties stay the same. If you use Microsoft Word regularly, you will find that these properties are very similar to the document properties.
You can also read this summary information directly from Windows Explorer. In Windows XP, you can hover your pointer above the file to see the summary properties (shown in Figure 3-5). To change the properties, right-click a database and choose the Summary tab. Keep in mind that on different computers and different operating systems, the properties that you can view and change will vary. I guess it's one of life's little mysteries.
Generally , database properties don't represent a huge security risk, and you may find that adding your company and personal information will assist in establishing ownership if the database ends up in the wrong place. For example, a former employee sends a database to a database recovery company to recover the data. In this case, a company address in the database properties would allow that recovery company to verify that the person asking for the recovery was bona fide. Though the database properties don't necessarily represent a security risk, they may represent a privacy issue or an embarrassing reference to a former employee.
In Access 2000, Microsoft introduced the Compact on Close option to ensure that your database is always in good condition. Compact on Close applies only to the current database, and the last person to leave the database will compact it. If you select the Compact on Close check box, the database will ”theoretically ”compact when the last person closes it.
Compacting an Access database makes a copy of the file and rearranges how the file is stored on your disk. As part of this process, data and objects are stored in optimal order and all temporary information is removed from the database. Remember to compact both the front-end and the back-end databases.
Unfortunately, this option doesn't always work properly due to issues like data corruption or problems with ownership of files. As a result, temporary files with names such as db1.mdb and db2.mdb are left in the same directory as the database. In addition, if you move your database, you might inadvertently leave these temporary databases in the old folder. If these temporary files aren't "cleaned up" on a regular basis, they pose a reasonable security risk by exposing your data to anyone who has access to the location that contains the temporary files. Any security that you add to your database after the temporary databases are created will not be reflected in the databases that failed the compacting process.
Tip | Do not use the Compact on Close option. Instead, find another way to compact your database. |
A system table called MSysCompactError generally documents any compacting errors (shown in Figure 3-6). You will need to be able to view system tables by choosing Tools ˜ Options ˜ View, as explained later in this chapter.
One of the sites where I work is a remote Australian copper mine. When I took over the geological database, a number of the tables were poorly set up and suffered from a lack of rules and database integrity. The database itself suffered from a lack of basic maintenance, such as compacting and repairing, and, even worse , ran Access 2 on two old PCs. After cleaning up the database and upgrading it to Access 2000, I decided to implement the Compact on Close option on the database. When I returned to the site a few months later, I found nine stray versions of the database, from db1.mdb to db9.mdb, in the same directory as the database itself. These temporary databases were all roughly the same size as the primary database and consumed more than 200 MB of disk space.
On investigating the problem, I found a new system table called MSysCompactError in the database (shown in Figure 3-6). The error descriptions in this table led me to the biggest table (50,000 records) in the database (called BoggerSamples). In that table, I found a number of dubious records (shown in Figure 3-7), which I removed. I imagine that these rubbish entries stopped the automatic compacting. This example illustrates the problematic nature of allowing Compact on Close to run on any user's PC because no user ever reports the problem. I resolved to turn off the Compact on Close option and instead built a simple menu option into the database to compact it. To complete the exercise, I showed the newly appointed DBA how to compact the database before I left the site.
Caution | In Access 2000, a new database property called Auto Compact Percentage was also added to the database to allow for a more controlled and less frequent compacting of the database. This property never worked and was abandoned in Access 2002. |
To change the General tab options by using VBA code, you will need to use the Microsoft Access application object's GetOption and SetOptions methods. To find examples of the code, choose Chapter 3 in the download database and select the form called frmGeneralOptions (shown in Figure 3-8).
Now if you want to find out the current values of the General tab options discussed, have a look at the code snippet from the Refresh button's onClick event. In this case, the values of the options returned are True or False, which is particularly suited to display in a check box.
Me!chkEnableMRU = Application.GetOption("Enable MRU File List") Me!chkAutoCompact = Application.GetOption("Auto Compact")
As you are probably going to want to update the option values in code, you will need to look at the Finish button's onClick event. In this case, I will demonstrate the full subroutine so that you can also see how to open the Database Options dialog box with VBA code.
Private Sub updGeneralProps_Click() ' Update the General options and close the form. Application.SetOption "Enable MRU File List", Me!chkEnableMRU Application.SetOption "Auto Compact", Me!chkAutoCompact If chkViewCode Then ' The next line displays the current subroutine. DoCmd.OpenModule "Form_" & Me.Name, Me.ActiveControl.Name & "_Click" Else ' Close this form and display the database options dialog box. On Error Resume Next DoCmd.Close acForm, Me.Name RunCommand acCmdOptions End If End Sub
As you can see from these code samples, viewing and changing a database option really only takes one line of code. Sometimes I find it just as easy to enter a simple one-line expression like the following directly into the immediate window. If you want to do this, open the immediate window (press CTRL+G) and enter the line of code in that window. For example, you could turn off the MRU list by typing the following into the Immediate window on a new line:
Application.SetOption "Enable MRU File List", false
Now I will discuss the database options that allow you to specify whether the users are allowed a second chance when they delete some data or remove an object.
Three Confirm options are on the Edit/Find tab (shown in Figure 3-9). Selecting these options will ensure that the users are prompted before these actions occur. These prompts notify users before they accidentally run queries on multiple records, accidentally delete objects, or change records without confirming the action. These Confirm options apply only to the current computer but will apply to every database that you open. To make a safer database, select all three options.
Now I will discuss how to change the Edit/Find settings by using VBA code, and then show you how I occasionally use VBA code to control when the user has to confirm an action query.
To change the Edit/Find tab options by using VBA code, you will need to use the Access application object's GetOption and SetOptions methods. To find examples of the code, choose Chapter 3 in the download database and select the form called frmEditFindOptions (shown in Figure 3-10).
Now if you want to find out the current values of the Edit/Find tab options discussed, have a look at the following code snippet from the Refresh button's onClick event. In this case, the values of the options returned are True or False, which are particularly suited to appear in a check box.
Me!chkRecordChanges = Application.GetOption("Confirm Record Changes") Me!chKDocDelete = Application.GetOption("Confirm Document Deletions") Me!chkActionQueries = Application.GetOption("Confirm Action Queries")
As you are probably going to want to update the option values in code, you will need to look at the Finish button's onClick event. In this case, the following code snippet shows you how to update the option by using the value from the check box:
Application.SetOption "Confirm Record Changes", Me!chkRecordChanges Application.SetOption "Confirm Document Deletions", Me!chKDocDelete Application.SetOption "Confirm Action Queries", Me!chkActionQueries
If your users have selected the Confirm Action Queries option, which they should, the prompt that appears (shown in Figure 3-11) when they run action queries from your application may annoy them.
I find it best to turn off the prompt in each individual subroutine or function and, at the end of the subroutine, turn the prompt on again so that action query prompts always appear (as in Figure 3-11). To clear confirm action queries in your VBA code, you need to change the SetWarnings method from True to False. The following code example shows you how to stop an append message from appearing. You will notice that the SetWarnings method is set back to True just before the subroutine exits. In other words, it should happen after error handling and after the normal execution of the code. To demonstrate this process, open the form frmEditFindOptions . In the onClick event of button A, you will see the following code:
Private Sub actionQuery_Click() Dim sqlStr As String ' Demonstration of how to turn action query messages on and off. On Error GoTo actionQuery_Click_Error: sqlStr = "INSERT INTO MyTable ( Field1, Field2 ) " & _ "values ( 'Test 1' , 'Test 2' );" DoCmd.SetWarnings False DoCmd.RunSQL sqlStr actionQuery_Click_Exit: DoCmd.SetWarnings True Exit Sub actionQuery_Click_Error: MsgBox Err.Description GoTo actionQuery_Click_Exit End sub
Note | It is best to test your action queries thoroughly before disabling confirm prompts. |
Now I will show you how you can change the default mode in which users open their databases.
The only option on the Advanced tab (shown in Figure 3-12) that relates to your database's security is Default Open Mode.
If the Default Open Mode option is set to Exclusive, that computer will be able to open databases in exclusive mode whenever possible. This situation is generally one that you want to avoid, unless you are the administrator or the developer. This option only affects the way a database is opened on a single workstation; it does not affect the default setting for users on other computers. Another consequence of opening a database in exclusive mode is that an .LDB file will not be generated, which can be a useful pointer to a database that's being locked in exclusive mode. In Chapter 6, I discuss more about surveillance with some references to the .LDB files.
Note | In Access 2000 and 2002, only if you are the only user of the database, it is possible to switch from shared ownership to exclusive ownership if you open a form, report, or module in design view. |
If you set up Access workgroup security in your multi-user environment, you can ensure that users don't open a database exclusively by denying them Open Exclusive permission for that database. For more information on removing permissions, see Chapters 8 and 10 on workgroup security.
Tip | In Chapter 10, you will find out how you can set up your own desktop shortcuts so that you can open your database in exclusive or read-only mode, even if you specify shared mode as your default. |
Now I will show you how you can use VBA code to change the default open mode for all databases that users open.
To change the default open mode on the Advanced tab by using VBA code, you will need to use the Microsoft Access application object's GetOption and SetOptions methods. To find examples of the code, choose Chapter 3 in the download database and select the form called frmAdvancedOptions (shown in Figure 3-13).
Now if you want to find the current values of the Advanced tab options discussed, have a look at the following code snippet from the Refresh button's onClick event. In this case, the values of the options returned are integer constants. To show the correct integer constant, I have set up two options in a frame, and one option is selected according to the value returned from the GetOption method.
Const OPENEXCLUSIVE = 1 Const OPENSHARED = 0 Private Sub refrAdvancedProps_Click() 'Update the Option box frame to reflect the current system settings If Application.GetOption("Default Open Mode for Databases") = OPENSHARED Then fraPrefOpen = OPENSHARED ElseIf Application.GetOption("Default Open Mode for Databases") = _ OPENEXCLUSIVE Then fraPrefOpen = OPENEXCLUSIVE End If End Sub
As you are probably going to want to update the option values in code, you will need to look at the Finish button's onClick event. In this case, the following code snippet shows you how to update the option by using the value from the option frame.
If fraPrefOpen = OPENEXCLUSIVE Then Application.SetOption "Default Open Mode for Databases", OPENEXCLUSIVE ElseIf fraPrefOpen = OPENSHARED Then Application.SetOption "Default Open Mode for Databases", OPENSHARED End If
We've come to the end of the discussion about options that protect or expose your database. Now we will look at one of the oldest tricks that Access magicians have up their sleeves ”the ability to hide important objects in the Database window so that the casual explorer won't be tempted to modify an object or view the contents of a table.