Preparing Your Developer Workgroup File | Real World Microsoft Access Database Protection and Security

The first thing that you need to understand before preparing and changing workgroup files is finding the program that allows you to do it. Believe me, this step was tricky until Access 2002 came along. Anyway, the following sections describe the steps for finding the Workgroup Administrator programs.

The Workgroup Administrator for Access 97 and 2000

For Access 97 and 2000, search for a shortcut file in Windows Explorer called MS Access Workgroup Administrator.lnk . In Access 97, this shortcut points to a stand-alone program called WRKGADM.EXE , which will probably be in the C:\WINDOWS\system32\ directory. In Access 2000, this shortcut points to a file that is also called WRKGADM.EXE , and you will probably find this in the \Office\1033\ subdirectory in the Access 2000 installation folder. Once you have found the shortcut file, drop a copy of the shortcut file onto your desktop or onto the Windows Start menu because it is likely that you will use it a lot.

The Workgroup Administrator for Access 2002 or Later

For Access 2002 and 2003, you will find the Workgroup Administrator conveniently accessible by choosing Tools ˜ Security ˜ Workgroup Administrator (as shown in Figure 8-2). You do not have to have a database open for this option to appear. In these versions of Access, the Workgroup Administrator is part of the Access executable and thus will be subject to your menu management practices (discussed in Chapter 7). Access 2002 and later are the only versions that allow you to change workgroups while you are using Access.

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

Creating and Joining Workgroup Administration Files

Once you start the Workgroup Administrator, you will encounter the dialog shown in Figure 8-3. In this case, I am using the Access 97 Workgroup Administrator, and the currently selected (default) workgroup file on my computer is shown.

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

Joining an Existing Workgroup File

If you want to join another workgroup file, click the Join button in the Workgroup Administrator dialog, shown in Figure 8-3. This action takes you to a second dialog. Click the Browse button to navigate to the new workgroup file, click OK a couple of times, and you will return to the dialog shown in Figure 8-3. This dialog then shows what workgroup file you will use the next time you join Access. In Access 2002 or later, when you click Exit, Access will actually change the workgroup file immediately. In Access 97 or 2000, you have to close Access to open the new default workgroup file.

Note	Another way to join a workgroup file is to use shortcut files, which allows you to join to a different workgroup temporarily, as you open the database. This workgroup file association only lasts for the current Access session. Shortcut files are discussed in detail in Chapter 10.

Creating a Workgroup File

If you intend to create a new workgroup file, it is a good idea to write down the name and location of the current workgroup file so that you can rejoin it later. To create a new workgroup file, click the Create button in the Workgroup Administrator dialog. The Workgroup Owner Information dialog opens, shown in Figure 8-4. In this dialog, you need to enter your name , organization, and a special workgroup identifier. Then you need to write these details down in a very safe place because you will need the information in these three fields to re-create the workgroup file. I personally like to cut and paste the information into a Word document as well, just in case I get the upper- and lowercases wrong. Taking a picture of the screen by pressing ALT+PRINT SCREEN and pasting it into the Word document is also a good idea. For this book, I have used the following details:

Name: Real World
Organization: Microsoft Access Database
Workgroup ID: Protect and Secure

Figure 8-4: The developer workgroup setup information.

Note

To help you with the exercises in this book, I have set up a table in Appendix A with all the identifiers and passwords that you will need. If you like, you can also enter your own information in that same table, maybe including password hints, so that you don't have to write down the exact security information. That way, whenever you forget a password or a PID, you can always refer to the back of this book.

Now save the workgroup security file into a location that will be safe from prying eyes. Remember that the whole point of this file is to keep it secure from your database users. Also, please do not save the file as system.mdw , or you will get the new file confused with the default file on all computers. When these steps are complete, you will have joined that new workgroup.

No Logon Dialog ”What Sort of Security is This?

Now, if you close Access and open it again with a database of your own choosing, you will notice that the database just opens. That's because one of the first things that Access does when it uses a workgroup file is check whether the Admin account has a password. Because this workgroup file is new, the password is blank, so Access opens the database by using the Admin user . Therefore, the first thing that you need to do with this new workgroup file is add a password to the Admin account. To do this, choose Tools ˜ Security ˜ User and Group Accounts. Make sure that the current user is Admin, and select the Change Logon Password tab as shown in Figure 8-5. Leave the Old Password field blank and type the new password into the New Password and Verify fields. I suggest that you make this password very easy to remember, because you will only be using it for testing purposes. Now close Access and open the database again.

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

Once you start Access again, enter your user name and password into the Logon dialog that will appear. In Access 97, this dialog appears as soon as Access starts. In Access 2000 and later, you will find that the Logon dialog appears when you are actually opening a database. Once you have successfully logged on, you will continue with this user and workgroup until you close Access again.

Setting up the Developer User Account

Now that you have your own workgroup file, you must undertake a few basic tasks to start to secure the workgroup file.

Adding a New User

The first thing that you need to do is add a developer user account:

Choose Tools ˜ Security ˜ User and Group Accounts.
The User and Group Accounts dialog appears, as shown in Figure 8-6. Click the New button in the User area.

Figure 8-6: Adding a new user, called Developer.
In the next dialog, enter the user's name (Developer) and a personal identifier (RealWorldDeveloper). Click OK.

The name of the new user will appear in the User drop-down list of the User and Group Accounts dialog shown in Figure 8-6. Also in the User and Group Accounts dialog in the Group Membership area, you will find that the new user has been made a member of the Users group. That's it. You now have your user. Shut down and open Access, and this time, log on as your new user (Developer). Because you have not added a password, you will not need to enter one. And because you are not going to distribute your developer workgroup file, having no password will prove to be a good little time-saver.

Caution

Write down both the user name and personal ID for this account because you will need this information to re-create the user.

Now that you have logged on, try choosing Tools ˜ Security ˜ User and Group Accounts again, and you will find that Access has worked out that the new account (Developer) is not a member of the Admins group. In addition, you will find that the new account does not have access to the New, Delete, and Clear Password buttons (as shown in Figure 8-7). The only thing that you can do with this user is change passwords and print lists of users and groups. These attributes are exactly what we are looking for in our anonymous Admin user. The next two steps in the process show you how to swap the administrator permissions from the anonymous Admin user account to the new Developer account.

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

Note	When you are looking at the object permissions for Groups, do not confuse the anonymous Admin user with the Admins (administrators) group.

Note	In Chapter 11, I show you how you can prepare a developer workgroup file by using the User-Level Security wizard.

Making Your New User an Administrator

To secure the workgroup:

Close and start Access and log on as the Admin user.
Open the User and Group Accounts dialog. Choose the new user (Developer) in the User Name drop-down list.
In the Available Groups list, choose Admins, then click Add.

The new Developer account can now add and delete user accounts and groups and arrange users into groups whenever they are joined to this workgroup file.

The Admins Group

The Admins group account is unique to each workgroup information file. By default, the Admin user belongs to the Admins group. Another requirement is that at least one user must be a member of the Admins group at all times. The Admins group is created from the owner information when the file is created. The Admins group in the default workgroup file on your computer is created with the company name and user name provided when you install Access. You can find out your company and user name by choosing Help ˜ About Microsoft Access.

Removing Administrator Permissions From the Admin Account

To make the workgroup safer, you should revoke administrator privileges from the Admin account:

Select the Admin account in the User Name drop-down list (see Figure 8-8).

Figure 8-8: Removing the Admins group from the Member Of list box.
Select Admins in the Member Of list box.
Click Remove to transfer the Admins group from the Member Of list to the Available Groups list (as shown in Figure 8-8).

The Admin User Ends up in the Users Group

After completing these steps, the Admin account is still a member of the Users group, another important anonymous entity that is common to all databases. This Users group account, which is controlled by the Jet database engine, will always hold all the users in the workgroup. This Users group uses security identifiers that are identical across all workgroups, and the group cannot be deleted, which means that any object permissions that are granted to the Users group apply to any Access user, including the anonymous Admin account. Importantly, this account by default has full permissions on all newly created objects. Given these parameters, it is obvious that the Users group must be managed carefully if you are going to produce a secure database.

Now, if you close Access and log on again as Admin, you will find that the Admin account cannot make any changes, apart from changing its own password. You now have a workgroup file with which you can apply some security to your database. Finally, you may find it a good idea to burn your developer workgroup file onto a CD-ROM for even more backup.

Tip	If you feel a little apprehensive about building and saving a developer workgroup file, use the instructions from this section to re-create the file. That way, you can tell your boss how to re-create the workgroup file if something goes wrong.