The Developer Workgroup File Strategy

As I researched this book, I had to wrestle with the question, "Is it possible to secure an Access security database?" On the positive side, it seems that even the most modest security such as startup options seems to be enough to deter users from straying into the parts of Access that we have decided that they shouldn't be. On the negative side, I came across issues like the fact that Access workgroup and password protection clearly have not been improved much over the years , which has allowed companies and individuals that want to profit from understanding the format of Access databases to create password-cracking software and sell database recovery and security consulting services (discussed in Chapter 9). Another obvious problem is that a lot of people seemed to be confused by workgroup security, which you can verify any day of the week by visiting the Microsoft Access security newsgroup at http://communities.microsoft.com/newsgroups/ .

So, should we persist with adding security to the database or should we just shrug our shoulders and hope that the database gets upgraded to an enterprise level database such as SQL Server? The answer is yes, it is worth persisting , and in the next few chapters, I will show you how you can tackle the password crackers and Access flaws head-on. In the process, I hope to make it easy enough for you to understand what you need to do and to decide whether it is worth doing. The first critical step that you cannot avoid is that you need to implement workgroup security so that users cannot get their hands on the workgroup file that secures the database. I have called this the developer workgroup strategy, and I encourage you to walk through the steps with sample databases so you can understand the basic concepts.

User Story  

Workgroup security was all too hard, which equaled fewer dollars. I personally stayed away from workgroup security for two or three years because I thought it was a little complex. I even came up with a strategy for convincing my clients to put security off until phase two of Access projects in the hope that the problem would go away. It was only when I realized that I was probably limiting my income by not understanding workgroup security that I finally got into it. And guess what ”it's not rocket science!

So, how do you get started with workgroup security? Guess what ”you already have. Whenever you open an Access database, the first thing that happens is that the Access Jet engine opens a workgroup file. If no one has modified the workgroup file, Access then logs on by using Admin, a workgroup user account. This Admin account (like all other Access user accounts) has a security identifier (SID) that is passed to the Access database. From then on, the Access Jet engine uses that SID to define ownership of new objects and to verify your permissions for all objects in the database. What is important to remember is that the SID for the Admin account is always the same, no matter which version of Access you are using or which computer you are starting Access from. For this reason, I will now refer to this account as "the anonymous Admin user" to emphasize its universality.

You are going to use the universal SID of the anonymous Admin account in two ways when securing the database. First, you must secure the objects and the interface in such a way as to ensure that no account other than the developer can modify how your database works. Second, you will allow any users to log on anonymously to your database by using the Admin account and the interface that you provide. Because most users will probably log on to the database through their default workgroup file, they will be logging on by using the anonymous Admin account. This approach removes all the headaches of passwords from the DBA and stops those silly user habits, such as writing down the password and sticking it on the monitor and telling friends that they can log on with their account.

Let's review this basic concept in a diagram, because you are going to use this simple process to secure your databases. In Figure 8-1, you can see that the developer workgroup strategy allows two types of users to log on to the (network) database. The users open the database and log on through the default workgroup file ( system.mdw ) on their own PC. Because these workgroup files haven't been modified, the user logs on by using the Admin account. The developer though will log on through the developer workgroup file ( developer.mdw ) using either a developer account or the Admin account to test the database as a user. The developer can also use the original system.mdw file on his or her computer to further test end-user security.

Figure 8-1: The design for the developer workgroup strategy.

The specific features of the default system.mdw workgroup file include the following:

• Every workgroup file has an Admin account. This account has the same security identifiers, regardless of which computer generates the workgroup file.

• Every workgroup file has a Users group account. This group account has the same security identifiers, regardless of which computer generates the workgroup file. All Access users are members of the Users group, and this group cannot be deleted.

• Unless you specify otherwise , Access will open a workgroup file that it creates the first time you use it. Even if you delete this file, Access will create a new default workgroup file in its place.

• If you do not specify a password for the Admin account, the workgroup file opens automatically.

• Because this file is on the local drive, performance will be good and network traffic will be limited to the database.

Note  

I'll repeat this fundamental concept: Everyone will log on as Admin unless you do something about it. Logging on as Admin requires no work on the part of the user.

What is a Workgroup File?

A workgroup file contains a list of user names and passwords and a list of groups. Both the users and groups will have a special identifier (SID) that is created by combining the personal identifier (PID) that you enter at the time that you create the user account and the user name . All this information is stored in a workgroup file that is actually an encrypted Access database. Included in the workgroup file will be the Admin account and the Users group. Both the Admin account and the Users group have identical SIDs for every installation of Access on the planet.

Which Workgroup File? Which User?

From the moment that you start working with workgroup file security, you need to know two things before you make any changes:

• Which workgroup file did I use to open the database?

• Which user account did I log on under?

You can find the answers to these questions through VBA code in the Immediate window (open by pressing CTRL+G) or by adding code similar to the following to an Access form or procedure:

 MsgBox "My current workgroup file is " & SysCmd(acSysCmdGetWorkgroupFile) 

or

 MsgBox "My current workgroup file is " & DBEngine.SystemDB 

My favorite code, which I generally type straight into the Immediate window ("13" here being the constant acSysCmdGetWorkgroupFile ), is

 ? SysCmd(13) 

To find the user account that you logged on as, use the CurrentUser property of the Application object, as follows :

 MsgBox "My current user account is " & Application.CurrentUser 

Alternatively, type the following into the Immediate window:

 ? CurrentUser 

Tip  

If you are using Access 97, try choosing Tools ˜ Security ˜ User-Level Security Wizard. This dialog provides both the user and the workgroup files.

Another thing that I find myself looking for is the location of all the workgroup files on my computer or our network. Take it from me ”you and your company do not want to have too many such files. To take a look, open Windows Explorer and search for all files that have a file type of *.MDW .

The Workgroup File Database

Each workgroup information file contains an MSysAccounts table that stores all user and group account names, encrypted user passwords, and SIDs. If you are curious about workgroup files, use Access to open one by using the file type of *.MDW . After the special database is opened, choose Tools ˜ Options and select the View tab. Now select the System Objects check box. Have a look at both the tables and queries in the database. The queries may prove useful in determining the status of users and groups.