UNDERSTANDING SECURITY IN ACCESS 2000
Access has one of the most advanced security models in the PC database industry. This chapter focuses on helping you understand the security model and features of Access security by describing the security model, presenting the user interface, discussing common problems, and providing useful code examples.
Understanding the Model and the Function of System.mdw
To understand the Access security model, you must first understand the difference between share-level security and user-level security. The easiest way to envision the difference between share-level and user-level security is to make some analogies to some popular network operating systems.
Share-level security (where share refers to a network share such as \Abednegopublic) is the simpler of the two security models. It's simply a password-protection scheme. If you know the password to the resource, you're provided access to that resource. Each resource has its own password in the share-level model. In the networking model, this can become quite cumbersome if you have to remember the name of each share and its password. If you need access to three different file servers, two SQL servers, and five printers, you need to remember 10 passwords! Share-level security is what you find in most applications that provide password protection.
User-level security is an advanced security model adopted from popular network operating systems. In the user-level model, an administrator creates predefined users and groups. Each user gets a unique logon ID and password. The administrator then assigns permissions to each user. To help ease the process of assigning permissions to multiple users, groups can be created and permissions then assigned to groups. Novell NetWare and Microsoft Windows NT networks use this method of security.
The benefit of user-level security is clear: The end users need to remember only their logon names and passwords. From there, they either have permissions to a resource or they don't. The down side is that you need to have a knowledgeable administrator to create, modify, and remove user accounts and permissions. This quite often can become an entire group of positions in large corporations.
Share-Level Security: The Database Password
In Access 95, Microsoft added the most requested security feature from its end-user community: the database password. Figure 21.1 shows the Set Database Password dialog.
Figure 21.1. The Set Database Password dialog lets end users password protect their databases.
Note
All passwords within Access, whether database passwords or user passwords, are case sensitive and up to 14 alphanumeric characters long.
To set the database password, follow these steps when you have the database open:
1. |
From the Tools menu, choose Security and then Set Database Password to open the Set Database Password dialog. |
2. |
Type the database password in the Password text box. |
3. |
Re-enter the password in the Verify text box. |
4. |
Click OK to accept the new password or Cancel to abort database password protection. |
The next time this database is opened, the user is prompted to enter a database password.
Note
To set the database password, you must have permissions to open the database exclusively. For more information, see the section “Setting Database Permissions” later in this chapter.
Caution
If you forget the password, there's no way to reset it. You'll be locked out of the database and lose access to all your data.
Remember that password protection is good only for preventing somebody from opening the database. When a user gets into a password-protected database, there's no other security unless you set up user-level security. If you haven't set up user-level security, anybody could change any of the objects and the data freely. Using just a database password isn't the preferred method for securing a full-fledged application.
User-Level Security: The Real Security System of Access
Since day one, Access has had a user-level security model built into the product. Surprisingly, few people have taken the time to master it. If you, as a developer, take the time to properly learn and implement Access user-level security in your applications, you'll surely be rewarded for these efforts.
Recall that the user-level security model is based on permissions assigned to users and groups. Each user has a unique logon name and password.
Tip
Access security is always turned on. If the Admin user account doesn't have a password, the system will automatically log a person on as the user Admin. This works great when you aren't using security because everyone then has rights to all objects in all versions of Access. In the beginning of the development cycle, this makes it easy to send your application for review without having to worry about security right away.
To see what user you're logged on as, follow these steps:
1. |
From the Tools menu, choose Security and then User and Group Accounts. |
2. |
Click the Change Logon Password tab. |
The current user name is displayed at the top of the dialog. In Figure 21.2, you can see that user Scott is now logged on to this session of Access.
Figure 21.2. In the User and Group Accounts dialog, you can always find out who you're logged on as on the Change Logon Password page.
Users and Groups
By default, Access ships with one user, Admin, and two groups, Admins and Users. The Admin user and Users group accounts are the same across all versions of Access. This is by design so that everybody who ever bought a version of Access can share their databases if they don't want to implement security. Remember, Access security is always active; you just don't see it when you're logging on as Admin with no password.
Versions of Access before Access 95 had two additional default accounts: Guest and Guests. In Access 95, these accounts were removed.
Note
Only members of the Admins group can create users and groups. What's more, only members of the Admins group and the database creator can change permissions on the database object and encrypt the database.
Caution
All user accounts are members of the Users groups—there's no way around this in the user interface. However, in code you can make some very serious mistakes because there's no security error checking, per se. Through code, you can create users that belong to no groups. This isn't advised because everyone should belong to at least one group.
You can never remove the three default accounts created by Access. The Jet database engine will protect these accounts. Furthermore, at least one user must always be in the Admins group. This user can be one that you, as the developer, create. It doesn't have to be the default Admin account. In fact, a database isn't secured if the default Admin account is a member of the Admins group.
For more information about removing users from the Admins group, see the section “Removing Users and Groups” later in this chapter. For more information about creating users (and groups) through the user interface, see the later section “Looking at the Security User Interface.” For more information about creating users and groups through code, see the section “Managing Security Through Code.”
Permissions
Access security is based on a database object granularity scale. This means that you can assign different security permissions on a per-object basis. You can set security permissions on a form in the database window, but you can't set security permissions on a combo box object on a form.
Each user and group can be assigned various permissions for each database object. Table 21.1 shows the permissions that you can assign based on the object type.
Note
To see a table's or query's data, you must select Read Data and Read Design. If you select Read Data through the user interface, Access automatically selects Read Design. However, if you're assigning permissions through code, be sure to assign both.
Note
As of Access 2000, modules are now protected in the Visual Basic Editor with one password for all code in the VBA project.
In addition to object permissions, Access has some additional permissions that you can set at the database level. Table 21.2 explains each of the database permissions.
For assigning permissions through the user interface, see the section “Looking at the Security User Interface” later in this chapter. For examples of how to set permissions programmatically, see the later section “Managing Security Through Code.”
Do I Have Permissions?
Within Access is the concept of implicit and explicit permissions. Users automatically get all the permissions assigned to them for each object. This is known as their explicit permissions. Also, they get all the permissions assigned to any group they belong to. These are implicit permissions because they're tacitly granted to the users by their belonging to those groups.
When an inconsistency exists between explicit (user) permissions and implicit (group) permissions, Access grants the least restrictive permissions. To put it another way, the permissions are additive. The following examples help illustrate this point.
Assuming that the Users group has no permissions to the Customers table, Jennifer might have been given explicit permissions to read the table. That is, her account has both Read Data and Read Design permissions. Jennifer is also a member of the Employees group, which has permissions to Update Data and Insert Data. So Jennifer would be able to read, update, and insert data. Because Jennifer is a member of only the Employees group and no other, however, she can't delete data from the Customers table.
Ryan might have no permissions to the Customers table explicitly assigned to his account. However, Ryan is a member of the Managers group, which has permissions to Read Data (and Read Design), Update Data, Insert Data, and Delete Data. So Ryan can read, update, insert, and delete data in the Customer table.
With the previous examples, you can see why it's beneficial to assign users to groups. If you know you're going to have 30 employees, three managers, and 100 objects in your database, you really don't want to assign 3,300 different sets of permissions when you could assign 200.
Understanding Ownership
Another characteristic of Access security to be aware of is the concept of ownership. The creator of an object is known as that object's owner. The owner of an object has some special privileges that can't be removed unless a member of the Admins group changes the ownership of the object. (For information about changing the ownership of an object, see the section “Looking at the Security User Interface” later in this chapter.)
Owners can always assign permissions to other users and groups for the objects they created. Also, owners can always assign permissions back to themselves if a member of the Admins group removes their permissions. Again, to help illustrate the point, an example is in order.
Suppose that Jennifer creates a new report called MonthlyVideoSales. Jennifer is the owner of the MonthlyVideoSales report. As you recall from the example in the preceding section, Jennifer is a member of the Employees group only—she isn't a member of the Admins group. Again, let's assume that the default Users group has no permissions to any objects. However, she can assign permissions to the MonthlyVideoSales report for other users. And if a member of the Admins group removes all permissions from the MonthlyVideoSales report, she can assign full permissions back to herself. The only way a member of the Admins group can keep Jennifer from reassigning herself permissions is to change the ownership of the MonthlyVideoSales report object from Jennifer to someone else.
Encrypting Your Database for Added Protection
Encryption is the process of scrambling, or encoding, the database file so that normal word processors or file-viewing utilities can't view any of the data or definitions within a database. Figure 21.3 shows the differences between an encrypted database and an unencrypted database.
Figure 21.3. Which would you rather have: an easy-to-read unencrypted database (top), or an encrypted database (bottom)?
As you can see in Figure 21.3, viewing sensitive data is very easy. You can see the salary and address data for three employees in the top window. If user Ryan looks at this database with any text editor, he can see where his salary is in relation to his name. Ryan knows he makes $38,000, so he could quickly deduce that his boss, Scott, makes $45,500, and that Jennifer makes $23,000. Worse yet, nothing can stop him from modifying data.
Any copy of Access can read an encrypted database from another copy of Access. So encryption as the only method of security won't protect you from another user with Access. What encryption does protect you from is the file-viewing utilities.
Caution
Encryption doesn't come without a price. Performance decreases about 5 percent to 15 percent, depending on the database structure and contents, for encrypting a database. Also, compression utilities don't affect encrypted databases. Compression utilities look for common patterns in a file to reduce its size. Encryption, by its very nature, creates randomness, thus preventing compression algorithms from doing their job.
Note
Access uses RSA's RC4 encryption technology to scramble the database. RC4 is an industry-standard encryption algorithm.
For details about how to encrypt a database, see the later sections “Looking at the Security User Interface” and “Managing Security Through Code.”
The System.mdw File
Now that the concepts of users, groups, permissions, ownership, and encryption have been covered, it's time to see how Access implements these theories.
Access uses a two-database security model. Permissions are stored in the same database as the objects they pertain to. Users, groups, and passwords are stored by default in a special database named System.mdw. Passwords are stored encrypted. Also, System.mdw stores individual user settings and group membership.
The System.mdw file is known by many names, all interchangeable, in the Access developer community. Some of the names for the System.mdw file are System.mda, SystemDB, system database, workgroup file, and workgroup information file. The Access 2000 documentation refers to it as the workgroup information file.
Note
If you're familiar with previous Access versions, you'll remember this file having the common Access database library extension of .mda. This was changed in Access 95 to prevent confusion with other libraries. Now the extension is .mdw, for Microsoft Database Workgroup.
Regardless of the name it's known by, it's a very important file. In fact, prior to Access 2000, if you didn't have a valid workgroup information file, Access wouldn't start. In Access 2000, if the mdw file isn't found, Installer will prompt you to repair the application. And this doesn't happen until you open a Jet database. You can technically start Access without an mdw file.
In previous 16-bit versions, Access found the workgroup information file by looking at the SystemDB line in the [Options] section of the Msaccess.ini (v1.x) or Msacc20.ini (v2.0) file. Now the path to the workgroup information file is stored in the Windows Registry. Developers can find the path to System.mdw under the following Registry key:
HKEY_LOCAL_MACHINESOFTWAREMicrosoftOffice9.0AccessJet4.0Engines
You can find out which workgroup information file you're using in several different ways:
Run the Workgroup Administrator's program (Wrkgadm.exe) shortcut, which is located in the Microsoft Access folder.
Use the Registry Editor to navigate the tree given in the preceding path (see Figure 21.4).
Figure 21.4. You can look up the workgroup information file key in the Windows Registry Editor.
Call a system command from VBA to return the path of the workgroup information file. The system command is in the ap_CanNotCreateDatabase() function found at the end of this chapter in the section “Denying Users the Ability to Create Databases.”