Database Passwords | Real World Microsoft Access Database Protection and Security

Setting up a single password that controls who can open a database is easy compared to other Access protection and security measures. Once a database password is set, all users must enter that password to open the database.

Unfortunately, database passwords have some problems, such as:

It is another password for all users to remember.
All users have to be informed of the new password if it changes.
Passwords waste time and add to general technological stress.
If someone knows the database password, that person can take the database to another location and the password will remain the same.
Password retrieval programs can be used by anyone who has access to your database.

There are ways that you can stop the user from having to enter a database password. These methods involve using the OpenCurrentDatabase method in Access 2002, linked tables, and programming by using either the DAO or ADO libraries. I discuss these useful alternatives later on, but first I will show you how to set up a password.

Adding a Database Password to Your Database

To add a password, you first need to open the database in exclusive mode. Choose File ˜ Open, find the file, and for Access 2000 or later, choose Open Exclusive from the list of Open options (as shown in Figure 9-6). In Access 97, select the Exclusive check box before clicking the Open button.

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

Now choose Tools ˜ Security ˜ Set Database Password, as shown in Figure 9-7.

Figure 9-7: Setting the database password.

Enter and confirm the password in the next dialog. I suggest that you use a strong password that should include a combination of upper- and lowercase alphanumeric characters and nonalphanumeric characters. This combination will make it a little harder for someone to guess the password manually. Whatever you do, make sure that you use a password of eight characters or more because the unlicensed version of the password retrieval programs will reveal the first few characters of a password.

Caution

Due to the poor encryption used in Access database passwords, never use the same password you use for important things like your online bank account.

How is a Database Password Implemented?

The database password is always stored (in an encrypted form) in the same location in the header of the database file. This is one reason why the passwords have proved so easy to crack. It is worth knowing that if a database header becomes corrupted, Access might erroneously report the database as being password protected instead of being corrupted. Corruption like this regularly occurs when someone opens and saves the database by using Microsoft Word or a text editor.

Protect the Password with Workgroup Security

Any user who knows the database password and has access to the security menu can remove or even add a database password, as shown in Figure 9-8.

Figure 9-8: The change database password command.

To stop users from changing the database password, remove the Administer permission from the anonymous Admin user account and the Users group. If you have changed the ownership of the database to someone other than the anonymous Admin account, you will only need to remove the Administer permission from the Users group (as shown in Figure 9-9). Fortunately, if a password is set, the user must know the password to remove it and change it. This action will stop a user from jumping onto a machine where the database is open and removing the password.

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

Database Passwords and Linked Tables

To link a table in a back-end database that is password-protected, you must supply the correct password. Once you supply the correct password, Access stores the password with the information that defines the link to the table. When a user opens the linked table, Access uses the stored password to open the database where the table is stored. If someone changes the password in the back-end database, the next time that someone opens the front-end database, the link will fail. In this instance, you need to delete and re-create the linked table, because the built-in linked table manager will not work.

Access stores the database password information for the linked table in an unencrypted form. In my view, this is not such a big deal, because the user does not have to know the database password to use the table. In this scenario, if the user were smart enough to work out that the link to the table included a password, the user would probably be smart enough to find a password retrieval program.

Opening a Database with a Password in Access 2002

One problem with database passwords is the fact that users know both the password and the location of the database. In Access 2002, a new argument was added to the OpenCurrentDatabase method that allows you to use the Automation command to open a password-protected database. If you were to include the downloadable form frmLaunchPassword in a separate database, this software will discretely open your password-secured database, as shown in the following VBA code:

 Private Sub cmdDBPassword_Click() Dim appAccess As Access.Application Const DBPATH = "C:\data\dbpwdishello.mdb" Const DBPWD = "hello" On Error GoTo cmdDBPassword_error    ' Create new instance of Access.    Set appAccess = CreateObject("Access.Application")    ' Open database in Access window.    appAccess.OpenCurrentDatabase DBPATH, False, DBPWD cmdDBPassword_exit:    ' Remove comment from following line when code is working    ' to close the database that this form resides in.    ' DoCmd.Quit acQuitSaveAll    Exit Sub cmdDBPassword_error:    MsgBox "Problem opening database. Please inform your database administrator", _           vbCritical, "Database Could Not Be Opened"    GoTo cmdDBPassword_exit End Sub

To make this code work for your database, import the frmLaunchPassword form into a blank Access 2002 database and make it your startup form. Now, change the constants at the top of the VBA procedure to suit your database and password. Finally, convert the database into MDE format (discussed in Chapter 11) to protect the code and the database password. You are now ready to deliver this database as the application that your users open. Now the database location and password should always be a mystery to your users.

Tip	If you use this technique to launch your database, the most recently used (MRU) list does not reveal the location of the database. You can use the OpenCurrentDatabase method in Access 97 and 2000, but you cannot use the database password argument.

Better Situations in Which to Use a Database Password

Database passwords provide a modest deterrent for all the honest people in the world, but they really are not very secure. Consider these situations if you're contemplating using a password:

On databases used by three or fewer people, where there are other people from whom you need to protect the database.
To protect a back-end database that is interfaced by a front-end database that has linked tables.
To protect a database that you emailed to a third party. At least that way, if the third party saves the database to the hard drive, another person at that location will not open it easily. Remember that in that case, the other person probably will not know the value of the information, so the password should be sufficient to stop further investigation or interest. In this scenario, you might also want to rename the database to deter users from opening it because a database such as Info1.mdb will be far less appealing than a database called Salaries.mdb .
As an alternative to opening the database and entering the password, you can use ADO and DAO to make a connection to the database. With that connection, you can use a recordset to extract the information to another database or program to display. For this procedure, you will probably need to use the OpenDatabase method for DAO connections or the Jet OLEDB:Database Password property in the ADODB.Connection object. For more on this topic, see the section "Further Reading" at the end of this chapter for a link to an article that I wrote on using ADO database passwords.

Personally, I like the approach of starting the database by using the OpenCurrentDatabase method or using linked tables for some front-end databases. Otherwise, the user is stuck with the bothersome password dialog, and you must assess the modest risk that the database will end up in enemy hands. If, after reading this section on database passwords, you are not sure about database passwords, you may want to try a form based on the following idea.

An Alternative for Back-End Databases

Rather than use a database password on your back-end databases, why not set up a simple form with a warning message to tell people that they do not have the permission to enter this database? You may even like to set up your own password on the form as described in "How to Create a Password-Protected Form or Report," Microsoft Knowledge Base Article No. 209871. If the user does not have the password, then use the following VBA code to close the database.

 DoCmd.Quit

You will need to use workgroup security to protect this form, as described in Chapter 8. Also, remember to protect all other avenues into the database such as startup options and protected menus . Now I will briefly discuss the viability of VBA project passwords.