Access Cookbook

Secure Your Access Database

Problem

Youve created an Access database that you’d like to secure. The database contains some sensitive data to which you wish to limit access. You’d like to be able to create different classes of users, so that some users have no access to this data, others can read the data but can’t change it, and still others can modify the data. How can you accomplish this?

Solution

The Microsoft Jet database engine, which Access uses to store and retrieve its objects and data, employs a workgroup-based security model that allows you to secure your Access databases, assigning permissions to users and groups. Access supports two mechanisms for securing your database: the database password feature and user-level security. The database password feature is an all-or-nothing proposition—users who know the password aren’t restricted in any way once they’re in the database. If you want to assign varying permissions to different users, you’ll need user-level security. User-level security is fairly complex—it doesn’t work if you leave out a step. It consists of creating a new workgroup file (which holds user, group, and password information) and then using this new workgroup file to secure the database. There is a Security Wizard built into Access that will help you secure your database, but you can also manually perform the process, which will help you understand what’s happening.

User-level security relies on a special database, called a workgroup file, to store users, the groups to which they belong, and their passwords. When you install Access, you are automatically hooked up to a default workgroup file called System.mdw. To secure your database, you will need to create your own unique workgroup file.

Every Access workgroup file includes two built-in groups: the Users group, which contains every user; and the Admins group, the members of which automatically get permission to administer security. There is also one built-in user, Admin. The Admin user starts out in the Admins group, but don’t let the name Admin confuse you. You can remove Admin from the Admins group and take away all its administrative privileges, as long as some other user is left in Admins to act as the administrator. The Admin user has the same identity in every Access workgroup file, so any privileges that you give Admin will be available to anyone.

Securing a database involves adding a new member to the Admins group, removing the Admin user from that group, removing permissions from the Admin user and from the Users group, and assigning permissions to the custom groups that you define. The steps that follow show you how to implement user-level security in your Access database:

1. Create a new, unique workgroup file. In Access 2002, this capability is built into the product, but in older versions you must run a separate utility called the Workgroup Administrator (Wrkgadm.exe). Write down the Name, Organization, and Workgroup ID strings that will be requested when you create your new workgroup file, and store them in a safe place. These strings will be encrypted to form the unique identity of your new workgroup file—if the original ever becomes lost or corrupted, it can be reconstructed as long as you input the identical strings. Each database “knows” the workgroup file it was secured with by this unique token (the Workgroup ID, or WID) and will not recognize a workgroup file that has a different WID. This means that you’ll be permanently locked out of your database if you lose these strings. Also, upgrading a secured Access database to a newer version of Access is almost impossible if you don’t have this information, because the recommended upgrade path is to recreate the workgroup file in the new version of Access and then upgrade the secured database. Figure 4-19 shows the Workgroup Administrator dialog with the new workgroup information.

   

   Figure 4-19. The Workgroup Administrator dialog

2. The Workgroup Administrator automatically switches you to the new workgroup file, so you can simply close when you’re finished. The Workgroup Administrator will create the necessary entries in the registry, making the new workgroup file the default. Start Access and load your database.

3. You will be logged on as a user named Admin. Use the Security menu options to set a password for the Admin user. This causes Access to prompt for a logon name and password the next time you try to open a database using this workgroup file.

4. Create a new user, which is the account you will use to secure the database. Add this new user to the Admins group, to make it the administrator. None of the user accounts has any built-in capabilities. You also need to write down the strings used for the Name and Personal Identifier (PID). Part of recreating a workgroup file is recreating the key accounts stored in it. The PID is not a password—it is encrypted along with the name string to create a System Identifier, or SID. The SID is the token used when assigning permissions and when distinguishing users from each other. The name alone isn’t secure, although Access won’t let you have duplicate names in the same workgroup file.

5. Quit Access entirely and restart, logging on as the new user account that you created in Step 4. Don’t type anything in the Password dialog—you haven’t set one for this account yet.

6. Remove the Admin user from the Admins group so that Admin is a member of only the Users group. Every user is automatically added to the Users group, which is similar to Everyone in Windows. You can’t delete any of the built-in users or groups (Admin, Admins, and Users), but you can move users in and out of various groups. Access requires that there always be one member of the Admins group (that would be you). Later you’ll create additional groups, assigning permissions to the groups for various database objects. Users then inherit permissions from their group membership. You’ll probably want to remove all permissions from the Users group, since permissions granted to Users are granted to all.

7. At this point you’ll want to secure the database. You can either run the Security Wizard or manually secure it. If you manually secure it, you’ll create a new database (this is how you transfer ownership of the database) and then import all of the objects. Next, remove all permissions for the Users group and the Admin user. The Admins group has full permissions by default—only the Admins group can work with users and groups and has irrevocable administrative permissions on the database. If you use the Security Wizard, it will also remove all permissions from the Admin user and the Users group and encrypt the new database (you can do this manually if you choose).

8. You need to create your own custom groups and assign the desired level of permissions to these groups. Every user is required to be a member of the Users group (otherwise, a user would not be able to start Access), so grant to Users only those permissions that you want everyone to have. Members of the Admins group have irrevocable power to administer database objects, so make sure to limit membership in the Admins group to only those users who are administrators.

9. Create your own users and assign them to the groups that reflect the level of permissions you want them to have. Do not assign permissions directly to users, because that is extremely difficult to administer; users inherit permissions from the groups of which they are members, and keeping track of the permissions assigned to a group is much easier than keeping track of the separate permissions of individuals. If a user is a member of multiple groups, that user will have all the permissions granted to any of those groups plus any permissions assigned specifically to the user (this is known as the “least-restrictive” rule). There is no way to deny permissions to a user if that user is a member of a group that has been granted those permissions. If you need to create specific permissions for only a single user, create a group for that user and assign the permissions to the group; then add the user to the group. The reason for this becomes clear when you consider that the user may leave unexpectedly, and you may have to set up permissions for the replacement on short notice.

10. Test security by logging on as users with varying levels of permissions. Try to do things that a user at that level shouldn’t be able to do. The only way you’ll be able to see if your database security is working is to bang on it and try to break it.

Discussion

The Microsoft Jet database engine, which Access uses to store and retrieve its objects and data, employs a workgroup-based security model. Every time the Jet database engine runs, it looks for a workgroup file, which holds information about the users and groups of users who can open databases during that session. The default workgroup file, System.mdw, is identical across all installations of Access. That’s why it’s important not to skip the first step of creating a new workgroup file.

The workgroup file contains the names and security IDs of all the groups and users in that workgroup, including passwords. Each workgroup file contains built-in groups (Admins and Users) and a generic user account (Admin). You can’t delete any of the built-in accounts, but you can add your own group and user accounts.

The built-in accounts each have their own characteristics and properties:

• The built-in Admins group is always present, and its users have administration rights that cannot be revoked. You can remove rights from the Admins group through the menus or through code, but any member of Admins can assign them right back. Access ensures that there is always at least one member in the Admins group to administer the database. The Admins group is the only built-in account that has any special properties.

• The default user account, Admin, is a member of the Admins group in an unsecured database and is the only user account present in the default system.mdw workgroup file. It has no special properties of its own; all of its power is inherited through membership in the Admins group.

• The Users group is a generic group to which all users belong. You can create users in code and not add them to the Users group, but they won’t be able to start Access—internal tables and system objects are mapped to the permissions of the Users group. Other than the fact that all users must belong to the Users group, it has no special properties.

Permissions to various Access objects can be assigned directly to users (explicit permissions) or to groups. Users inherit permissions from the groups to which they belong (implicit permissions). It’s always a good idea from an administrative point of view to assign permissions only to groups, and not to users, which could become endlessly complicated.

Access employs the least-restrictive rule: users have the sum total of their explicit and implicit permissions. In other words, if a user belongs to a group that has full permissions and you make that user a member of a group that has restricted permissions, the user will still have full permissions because he is a member of the unrestricted group.

User and group information, including passwords, is saved in the workgroup file, or System.mda/mdw, which validates user logons at startup. Permissions to individual objects are saved in the database itself. You can give the groups and users within a workgroup various levels of permission to view, modify, create, and delete the objects and data in a database. For example, the users of a particular group might be permitted to read only certain tables in a database and not others, or you could permit a group to use certain forms but not to modify the design of those forms.

Most Access database applications consist of a frontend with linked tables against a backend database. You need to secure both the frontend and the backend using the same workgroup file.

Access user-level security works best when securing data—if you want to secure your code, the best solution is to compile your application as an MDE. This prevents anyone from viewing or altering the design of forms, reports, or module code. It also prevents users from creating new Access objects, but it has no effect on data objects (tables and queries). You’ll need to save a backup copy of the original .mdb file if you want to make alterations later—there’s no way to decompile an MDE to recover the source code and source objects.

Also bear in mind that security in an Access database is mainly good for deterrence only. In any situation in which the physical files are exposed, it is impossible to guard against determined hackers. An additional weakness is that the network share where the Access .mdb and .mdw files are located also needs to have read, write, and delete permissions, which means you can’t prevent users from deleting or copying the .mdb and .mdw files. The only alternative is to create an n-tier application where the middle-tier objects alone have access to the physical files. However, this means that you need to write the application “unbound,” since the users will no longer be directly connected to the database. When you get to that point, you’ll probably be considering SQL Server or another database platform that is capable of scaling to support more users and larger volumes of data.