Securing Data with PID Authentication | Real World Microsoft Access Database Protection and Security

This section of the chapter shows you how to use more than one workgroup file to counteract two of the greatest issues with workgroup security:

Anyone with access to a workgroup file can use password-cracking software to extract the user accounts and their passwords. We will counter this by restricting the workgroup file that the person uses to one or a minimum number of Group accounts so that the effect of the password-cracking software is minimized.
Logging on to an Access workgroup account is fraught with many perils , from users forgetting passwords to users passing passwords of different accounts from person to person. We will counter this management problem by transferring the onus of authentication to the more secure authority offered by recent Windows operating systems.

To fix these problems, we are going to adopt modified versions of a technique that Microsoft suggested. This technique is discussed in the Access 2000 help guide under the topic "Set Up More Than One Workgroup to Manage the Same Secure Database," and in the Access Security FAQ "Item 33" ("I Want Users in Other Groups Besides the Admins Group to Be Able to Administer the Database and Add Accounts"). You will find a link for this page in the "Further Reading" section at the end of the chapter.

I have called this technique "PID authentication" because it involves setting up an equivalent Group account name with its unique PID into a different work-group from that used to assign the permissions in the database. You can also use this technique with User accounts, but that is generally harder to maintain than the Group accounts.

The technique can be summarized as follows :

Grant the permissions for objects (tables) to a Group account by using the Developer account in the developer workgroup file (discussed in Chapter 8).
In a second workgroup file, add the same Group account and its PID.
Make a User account a member of that Group in the second workgroup file.

Now I will show you how you can use this technique to allow a user to log on to a database by using the anonymous Admin account. By combining this approach with the file security offered by the latest Windows operating systems, you can achieve a highly secure authentication of a person. This approach mimics the same Windows authentication system used by SQL Server.

Anonymous Windows Authentication

Anonymous Windows Authentication (AWinA) is a secure system that involves the DBA setting up a workgroup file in such a way that the Windows User account can open a secured database without having to log on. To set up this system, you will need a computer running the Windows 2000 or Windows XP operating system and you will need Access 2002 or later. Then, for this technique to work properly and securely, you need to establish Windows User accounts with passwords. Another important criteria is that the disk partition needs to be formatted by using NTFS, a subtlety that I discuss in Chapter 12. Because you want the user to be blissfully unaware of the process, I advise that you make these changes when the user is not watching.

Note

I include the following anonymous Windows environment information with the aim of providing information for a developer or small team of developers who uses a peer-to-peer network to manage his or her development. If you work on a domain that uses Windows Server 2000 or later, your users will also use the same Documents and Settings folders but in a number of subtly different ways, the descriptions of which are beyond the scope of this book. Therefore, I suggest that you read this information and then ask your network administrator how these files are managed in your network environment.

The Anonymous Environment

To set up AWinA, open Access 2002/2003 on your end user's computer and confirm that the current workgroup file is the default workgroup file. You can do this by opening any database, switching to the Visual Basic Editor and typing ? syscmd(13) into the Immediate window. If the path includes the Application Data folder like the one below, then you are ready to set up anonymous Windows authentication. The path that I would expect to see would be similar to this one:

 C:\Documents and Settings\Mr Robinson\Application Data\Microsoft\Access\System.mdw

where the Windows User account on my computer is Mr Robinson. This path establishes that you are using a workgroup file that is stored in an area protected to all but the Windows User called Mr Robinson. Now you are in a position to alter this local workgroup file so that Mr Robinson can open the database as a member of the Full Data Users group.

Before we do that, let's confirm that the anonymous Admin account cannot open the database. To do this, log on to your secured database as the anonymous Admin user. If you find that the Admin user either is blocked from opening the secured database or only has the permissions that you intentionally granted to the ubiquitous Users group, then that is good. This situation is what we want all the Windows users on your network to encounter before we start to establish AWinA for a particular Windows User account.

Setting up AWinA for a Windows User Account

Now I will show you how to modify the user's personal workgroup file so that it has a hidden Group account.

Choose Tools ˜ Security ˜ User and Group Accounts.
Select the Groups tab (as shown in Figure 10-30) and click the New button.

Figure 10-30: Adding a new group account to the end user's default workgroup file.
Add a new Group (Full Data Users) by using exactly the same name and PID that you used when securing the database manually or with the User-Level Security wizard, and click OK.
Select the Users tab and make sure that you have selected the Admin user. Now choose Full Data Users in the Available Groups list and click the Add button (as shown in Figure 10-31).

Figure 10-31: Adding the Admin account to the group.

That is all you need to do to set up the User and Group accounts. If you are worried about whether the user will explore this workgroup file, you will want to set the Access startup options (Chapter 2) to turn off the full menus so that the Security command is hard to find. That said, even if outsiders were to process the contents of the workgroup file with password-cracking software, they would discover nothing that would change their permission levels for the database.

Now try this account on your workgroup-secured database, and you will find that it works perfectly for the permissions that you have set up for the Group that you just added. Because the Admin account doesn't have a password, there's no reason to log on to a workgroup, because it happens automatically for Admin users who have no passwords.

Bingo: Simple, Secure Access

When I first worked out that I could secure a database with AWinA, I was flabbergasted. Here I was after many months into research on Access security, and the best method of security that I could devise for data was to allocate permissions by using the Admin account from a workgroup file with no security at all. The reason I was fooled was that I had always been under the misapprehension that in some way a Group account SID was constructed by using the PID of the workgroup itself. I never actually considered that permissions to the objects in a secure database could be granted to any old user who joined an authorized group in any old work-group file. Anyway, you can, and after careful consideration, I believe that AWinA is the best combination of security for Access data and simplicity for the user.

To understand why this happens, let me review the Access security model. Access uses SIDs to check users' permissions. Each database has a table called MSysACEs that keeps permissions information for all the objects in the database. Before a user account uses a database object, the Access Jet engine scans the MSysACEs table by using the current user SID and the SIDs of the groups that the user belongs to, to see if the action is allowed.

But where do these SIDs come from? Group account SIDs are generated from the group account name and the personal identifier when the group is established. They are not created from any information in the workgroup file. This means that creating a group account in any workgroup with the same PID will give you the same permissions that the equivalent group account had in the original work-group file. Exactly the same principle applies to a workgroup user account, which means that you can re-create your Developer account in any workgroup file and the permissions that the account had in the secured database will be exactly the same.

User Story

One of the first secure applications that I had to write was a land rights system built for a mining company using an Informix database. I developed a very functional and neat Informix 4GL interface that ran on text-based terminals. Everyone who required access to the application had first to log on to UNIX by using an individual account. The Informix security implementation involved granting permission to each UNIX account to insert, update, and delete data for each table in the database. Therefore, when a person started the Informix 4GL application, the database engine would check that the UNIX account had permissions on the table before any changes were allowed. This was a great system to administer because the user only ever had to log on to UNIX and the database security would take its cue from the UNIX account name. This type of security interface is far friendlier to users than a system like the Access workgroup security system, where logon details are required for each user and each database. I like Anonymous Windows Authentication for this reason ”it emulates this process.

Returning to Anonymous Windows Authentication

So why do PIDs and all that other technical stuff make a local workgroup file with a hidden group account so effective? Because if it is used in the correct way in a version of Windows 2000 or Windows XP Home or Professional, it can be protected by Windows security itself. That is, if the user logs on through a valid secure Windows account, the following points are true:

Windows passwords and screen-saver passwords (discussed in Chapter 12) will help to protect the Access session and the data that is left exposed by an unattended computer.
The workgroup file is protected in a folder that only the Windows User account or the computer's administrator can access.
The actual workgroup file itself is quite hard for an ordinary user to find because it is kept deep in a hidden folder.
If a person were to take the database and the workgroup file off-site, that person would only have the same permissions that they had on-site.
If the person didn't realize that there was a workgroup file, he or she could take the database off-site and then find that they had no permissions to do anything. This scenario is quite likely when the Admin account doesn't have a password and the user doesn't get a logon prompt.

Of course, this assurance comes with one big caveat. Everyone must log on to his or her own Windows account on the computer and the concept of the walkup computer that anyone can use with no permissions must be forgotten. The same can also be said about Windows accounts in a domain, and I encourage you to discuss with your system administrator how the Windows server manages the \documents and settings\ folders.

When a person leaves the company, you must take care to remove the Windows User account from the computer(s) that that person logged on to. Obviously, if you are using Windows Server, you need to remove the account from the active directory. Also, you must make it clear from the onset that computer accounts are not for sharing.

Now I will show you how the Windows User account ensures that you have a secure workgroup file.

The Secure Personal Workgroup File

In Windows 2000 and Windows XP, every Windows User account (see Figure 10-32) has personal secured folders that other users of the computer and the network cannot access.

Figure 10-32: Multiple users on a single Windows XP home computer.

As it happens, Microsoft Access 2002 and 2003 store their default workgroup files in these same secure personal folders, which allows you to use the Windows User security to your own advantage. In Figure 10-33, I have opened the folder where these versions of Access store the default workgroup file. As you can see, the path to this file is quite long, and, with the file being stored in a hidden Windows folder, it will be quite hard for the Windows User account owner to find the file.

Figure 10-33: The secure folder where the Access default workgroup file is stored.

Now, if another limited Windows User account tries to look at the files in someone else's personal folders, that account won't be allowed access (as shown in Figure 10-34).

Figure 10-34: Other limited accounts cannot look at your personal files.

Anonymous Windows Authentication Pro

If you are using Access 97 or later, you can set up a workgroup file in the user's personal folders. You can then use a more secure version of the previous technique by creating a new workgroup file in the Windows personal folders and then opening the database by using a shortcut that also opens the workgroup file. The command line for this process would look similar to this one (all in one line):

 C:\Program Files\Microsoft Office\msaccess.exe c:\data\northwind..mdb  /wrkgrp    "C:\Documents and Settings\Contractor 1\Application    Data\Microsoft\Access\system.mdw"

Once you have established the workgroup file, you can then add the group account and join the anonymous Admin account to the group account to start using the AWinA system. This version of AWinA is better because the user has only temporary access to the workgroup file. Subsequently, permissions are revoked if the user opens the secured database without using the shortcut file or joining the workgroup file.

In summary, I like both of the anonymous Window authentication techniques because users will protect their own Windows User account with more vigor than they would their Access workgroup accounts. In addition, Windows provides good security, and because users don't have to enter a user name and password to open the database, they will probably be blissfully unaware of any security at all. Therefore, if they were to steal a secured database, they would not actually be able to open it.

The Developer's Challenge

By using the instructions for adding Groups and Users by using VBA code in Chapter 8, you could add the AWinA Group account and then join Admin to that group as part of an automated installation process for a new user.

Dual Workgroups

Dual workgroups are another variation of the technique that involves adding a Group to an alternative workgroup that I have called PID Authentication. This variation works as follows:

A new workgroup file is set up with a different workgroup ID.
A new administrator account is set up and added to the Admins group.
The anonymous Admin user is removed from the Admins group.
The new administrator then sets up the group accounts by using the same name and PID as the original workgroup file that was used to secure the database.
Each user account is added to the group account just as if it were a member of the original workgroup.

You can read more about this process in the Access 2000 (or later) help under the topic "Set Up More Than One Workgroup to Manage the Same Secure Database" or in the Access Security FAQ "Item 33."

Things to Watch for in PID Authentication

When setting up AWinA or dual workgroups, there are a number of things to be aware of:

The developer account must own all the objects in the database.
The anonymous Admin account must have no permissions or own any objects.
The ubiquitous Users group should have no permissions unless you specifically want any Access user to have that permission.
No groups should have Administrator permissions on any objects in the database.
Keep your developer workgroup file hidden.
Developers should have a secure copy of the workgroup and secure documentation of the name and PID of the workgroup. DBAs and IT managers should pester the developer for the same information or you'll risk losing the workgroup security information.
If you are using the AWinA system with Windows XP or Windows 2000, you must use NTFS for your hard drive partitions. If you use FAT32, all users will have access to all files on your hard drive, regardless of their account type (Administrator or Limited/Restricted).

Now we will see what you can do to protect databases that you want to distribute to other people or companies, or even make available for download from the Internet.