The Protection-Related Database Options


The Protection- Related Database Options

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).

Table 3-1: Database options and value recommended for protection

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


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

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

Now I will discuss those particular General tab options in the sections that follow.

Recently Used File List


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.


Figure 3-3: The MRU list reveals the location of databases that you have visited.
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.

Removing Personal Information


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.


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

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.

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

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.

Compact on Close


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.

start sidebar
Compacting Regularly Is Very Important

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.

end sidebar
 

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.

click to expand
Figure 3-6: The MSysCompactError table shows problems with compacting.
start sidebar
Dealing with an Auto Compacting Error

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.

click to expand
Figure 3-7: The erroneous lines with "###" entries caused the Compact process to fail.
end sidebar
 
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.

Changing the General Tab Options by using VBA Code

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).

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

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.

The Edit/Find Tab


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.

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

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.

Changing the Edit/Find Settings by using VBA Code

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).

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

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 

Temporarily Turning Off the Confirm Prompts for Action Queries

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.

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

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 Advanced Tab


The only option on the Advanced tab (shown in Figure 3-12) that relates to your database's security is Default Open Mode.

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

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.

Changing the Default Open Mode by using VBA Code

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).

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

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 

Time to Find the Magic Wand

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.




Real World Microsoft Access Database Protection and Security
Real World Microsoft Access Database Protection and Security
ISBN: 1590591267
EAN: 2147483647
Year: 2003
Pages: 176

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net