Access and Security

Since Access’s growth from a home and boutique database package to a powerful tool that, in many cases, can be used as the backbone database of a website, the whole issue of security has been studied carefully. Let’s first take a look at some of the simpler security techniques that can be used.

The User Interface

One of the simplest security techniques is to turn the Access user interface off. This can be done from within Access by going to Tools | Startup, as shown in Figure 16-1. This allows you to shut off the Database window and status bar, set a custom menu bar as the default, set a customized opening splash screen, and so on.

Figure 16-1: The Startup dialog box

For smaller and less secure applications, this could be a very easy and nonprogrammatic way of securing them. It could restrict users’ movements enough that they could not create a problem.

Setting a Password

Also for smaller and less secure database applications, you can set a password that would be required before obtaining unrestricted use of the database. This can be used for a two-level security system: those who have full access to the database, and those who have access only to the application.

Those who have the password can access virtually anything in the database; and those who do not can only access the application with its programmed restrictions.

If you have TheCornerBookstore database open, try setting a database password by selecting Tools | Security | Set Database Password. If you do that, you may end up with a message like this:

In order to set the password, you need to open the database in an Exclusive mode. You can do this by first closing the database and then selecting File | Open. In the opening dialog box, click once on thecornerbookstore.mdb. In the lower-right corner, click on the down arrow of the Open button and select Open Exclusive:

Once that is completed, you can then select Tools | Security | Set Database Password, and you will be presented with the following dialog box:

You can now set the password you want to use. Access will store it in an encrypted format. Once it is set, you then have the option, by using Tools | Security | Unset Database Password, to shut that password off. You need the existing password for permission to unset it.

Creating a Password Programmatically

Up to this point in the book, the SQL code we have been using has been pretty generic in that it will run in any database environment. However, each database package has a small number of proprietary SQL statements that you can use.

Since Access is built on the Jet database engine, this is called Jet SQL in Access. One of the statements you can use is ALTER DATABASE PASSWORD. Because of this, you can write a fairly simple VBA program to set or change the password. The keyword NULL can be used to represent the old password. As an example:

Sub changePassword()   Dim con1 As ADODB.Connection   Dim strPassword As String   strPassword = "ALTER DATABASE PASSWORD [smith] NULL;"   Set con1 = New ADODB.Connection   With con1   = adModeShareExclusive   .Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _   "Data Source=C:\BegVBA\thecornerbookstore.mdb;"   .Execute (strPassword)   End With End Sub 

This code assumes that you are accessing a remote database.

In this code, we made the new password the word “smith.” As mentioned previously, NULL is used as the second argument because there is no password to replace.

As before, we had to set the mode of the connection to Exclusive (adModeShareExclusive).

Let’s say we wanted to change the password of the database from smith to jones. Our code would need to reflect two things. First of all, the SQL string would look as follows:

strPassword = "ALTER DATABASE PASSWORD [jones] [smith];"

Again, notice that the first argument represents the new password, and the second argument represents the old password. However, you cannot change the password unless you open the database with the existing password. In order to do that, you need to add another line to the connection.

  With con1   .Mode = adModeShareExclusive   .Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _   "Data Source=C:\Books\Access\chap15\thecornerbookstore.mdb; " & _   "Jet OLEDB:Database Password=smith;"   .Execute (strPassword)   End With

Finally, if you want to remove the password entirely, change the SQL string as follows:

strPassword = "ALTER DATABASE PASSWORD NULL [smith];"

As you can see, it is relatively easy to write VBA code to handle passwords.

Protecting the VBA Code

Beginning with Access 2000, you can now password protect your code modules. We can take a quick look at how to do that by going to the Visual Basic Editor and right-clicking on the project name in the Project window. Select TheCornerBookstore (or whatever your project name is) Properties. After you click on the Protection tab, you should see a dialog box like Figure 16-2.

Figure 16-2: The Project Properties Protection dialog box

Notice the Lock Project for Viewing option. This is a source of confusion for a lot of developers. If you leave that option turned off, the password you enter in the Password text box controls access only to the Project Properties dialog box; anyone can view and edit the VBA code. If you check the Lock Project for Viewing box, users will also need the password to get into the code.

This box will not take effect until you completely close the project and reopen it. If you want to remove the password, just open the dialog box and clear the password fields.

Compiling to an .mde File

If you really do not want anyone to get into the code, convert the file to an .mde file. This compiles all the modules into machine code (0’s and 1’s) and completely eliminates the editable code.

If you distribute the project, this technique offers some additional advantages over the usual compiled code. Your project size is much smaller, and because the code is compiled, it will run much faster. The downside is that you cannot edit the code in the .mde format. If you make any changes, you must go back to the original files, change them, and then recompile to the .mde format. Depending on how much code you have, this can be a time-consuming process.

As with many of the other security features, the original file must be opened in Exclusive mode. If your database is saved in the Access 2000 format, and you have either Access 2002 or 2003, you must first convert the 2000 database to the 2002/2003 format before you can compile it.

Once the database is converted and opened in an Exclusive mode, you can compile the database in Access by selecting Tools | Database Utilities | Make MDE File. Give the file a new name, and it will take some time to make the conversion.

As an interesting exercise, compare the size and speed of the .mde and .mdb files.