Microsoft Office 2000 Resource Kit

User-level security is the most flexible and secure method of protecting sensitive data, code, and object design in a database application developed in Microsoft Access. In addition, user-level security is the only form of Access security that allows you to establish different levels of access to sensitive data and objects.

Unlike the security models of most other database systems, the primary form of security in Access is user-level security rather than share-level security. Access user-level security is similar to the security used in most network environments, such as Microsoft Windows NT Server versions 3.51 and 4.0. With user-level security, users are authenticated before they open a database. Users log on to the system with a name and a password, which are compared to a database of user account information in a workgroup information file or system database.

How user-level security works

When a user opens a secured database, Access determines the user’s level of access to an object (including the database itself) by checking the set of permissions assigned to that user for that object. Different users and groups can have different permissions for the same objects. When a user tries to perform an action on an object — such as opening a form, running a query, or modifying the data in a table — Access checks to see whether the user, or any of the groups to which the user belongs, has the necessary permissions to carry out the operation.

In contrast, database systems that provide share-level security associate passwords with specific objects, and users must supply passwords to gain access to the objects. A user’s level of access is determined by the kind of password that user has been given. For example, managers might be given an update password for a table, allowing them to change records, and general staff members might be given a read password, allowing them to view records but not modify them.

You can create a similar system with Access user-level security by creating a Managers group with Update Data permission and a Staff group with only Read Data permission and then assigning each user to the appropriate group. Users do not supply passwords when accessing the objects, because the members are identified as members of the appropriate group when they log on to a system that has Access user-level security.

Storing workgroup and permission information

Access stores information about users and groups in a database called a workgroup information file, commonly referred to as the system database. A workgroup information file stores the following information:

Each workgroup information file defines a workgroup and can be used by many Access databases. A workgroup is a group of users in a multiuser environment who share data and the same workgroup information file. You manage users, their passwords, and the groups they are assigned to in the User and Group Accounts dialog box (Tools menu, Security submenu).

Permissions that you assign to users and groups for the objects in a database are stored in hidden system tables within the database. Even if a new workgroup information file is created, the permissions associated with the objects in a database do not change. You assign permissions on the Permissions and Change Owner tabs in the User and Group Permissions dialog box; this information affects the objects in the open database, but not the workgroup information file.

The following illustration shows how these elements of Access user-level security are related.

The location of the workgroup information file is specified in the Windows registry. You can create a new workgroup information file or specify which file to use with the Workgroup Administrator program or the /wrkgrp command-line option.

Creating user and group accounts

You use the User and Group Accounts dialog box (Tools menu, Security submenu) to create new user and group accounts for your workgroup. When you create a new user or group account, you supply a name for the user or group as well as a personal identifier (PID). Access sends the user or group name and the associated PID to an encryption program to generate the SID for that account. If you send the same user name and PID back into the encryption program, you get the same SID. This gives you the ability to re-create user accounts if your workgroup information file becomes corrupted or is lost.

Access uses the SID to uniquely identify and validate users and groups. When a user requests access to objects, Access uses the user’s or group’s SID for identification (instead of the user name and password).

Saving names and personal identifiers

Be sure to write down the exact, case-sensitive names and PIDs that you use to create user and group accounts and keep them in a safe place. If your workgroup information file is damaged or lost and you need to re-create it, you must reenter the exact names and PIDs to re-create identical SIDs for the user and group accounts.

Use the Workgroup Administrator program to create an identical workgroup information file to contain these accounts by reentering the name, organization, and workgroup ID (WID) that you used when you created it originally.

An Access workgroup information file contains the following default user and group accounts:

Admin account

Admin is the default user account. Before user-level security is established, all users are automatically logged on using the Admin user account. Therefore, the Admin user owns and has full permissions on all objects created in the database. Because the Admin user’s SID is identical across all installations of Access and Visual Basic, all objects that the Admin user owns or has permissions on are open to anyone using another copy of Access or Visual Basic for Applications (VBA).

When you establish user-level security, make sure that the Admin user does not own or have any permissions on objects that you want to secure. For assistance in securing objects, run the User-Level Security Wizard provided with Access.

Admins account

Admins is the workgroup administrator’s group account. Before user-level security is established, the default Admin user is the only member of the Admins group. Members of the Admins group have the power to perform actions such as changing permissions, modifying user and group memberships, and clearing users’ passwords.

Members of the Admins group that was in use when you created the database can also grant permissions on any object in that database. For example, if a user is a member of the Admins group, that user might not have permissions to open a particular table, but can nevertheless grant himself or herself permission to open that table. It is recommended that an Admins group have at least one member at all times.

Caution Do not delete the last member of the Admins group. Only the owner of a database object has irrevocable permissions for that object. If the owner’s account ever becomes corrupted or deleted, and cannot be re-created because you don’t have that user’s name and PID, there is no way to recover the permissions for that object. In other words, there is no member of the Admins group to fall back on.

Users account

Users is the default group account comprising all user accounts. Access automatically adds user accounts to the Users group when you create them. Any permissions assigned explicitly to the Users group are available to all users in all installations of Access and Visual Basic for Applications. This is true because, like the SID for the Admins group, the SID for the Users group is identical in all workgroup information files.

By default, the Users group has full permissions for all newly created objects. When you establish user-level security, make sure that the Users group does not own or have permissions on objects that you want to secure.

Creating a secure workgroup information file

The Access Setup program uses only the user’s name and the company name provided during Setup to create the SID for the Admins group of the default workgroup information file. Because these two values are available from the Access Help menu, unauthorized users trying to breach security could re-create an identical Admins account by using the Workgroup Administrator program to create a new workgroup information file with these values, and then add themselves to the Admins group.

When establishing user-level security, use the Workgroup Administrator program or the User-Level Security Wizard to create a new workgroup information file, making sure to enter the WID, which is a value known only to you. This step ensures that the new workgroup information file contains a new Admins group with a unique, secure SID.

Because the SID of the Admins group is based on the exact, case-sensitive values you type in the Name, Organization, and Workgroup ID boxes, record and keep these values in a safe place. If the workgroup information file is lost or damaged, you can re-create it with an identical Admins group by reentering these three values. Members of this re-created Admins group can grant themselves permissions on all objects that were created when the original workgroup information file was in effect.

Setting security for replicated databases

Replicated databases use the same security model as nonreplicated databases. Users’ permissions on database objects are determined at the time they start Access and log on. You must make sure the same security information is available at each location where a replica is used.

You can do this by making the identical workgroup information file available to users at each location where a replica is used. Although the workgroup information file cannot be replicated, it can be manually copied to each location.

Alternatively, you can create a new workgroup information file at each location and then re-create the required user and group accounts needed by entering the same user and group names with their associated PIDs.

Note Modifications to permissions are considered design changes and can be made only in the Design Master replica.

By default, Administer permission is granted to the Users group, the Admins group, and the creator of the database. If security is to be maintained, you must restrict this permission to selected users.

Setting permissions

Access permissions fall into one of two categories. Explicit permissions are those you explicitly grant to an individual user. When an explicit permission is granted to a user, no other users are affected. Implicit permissions, by contrast, are granted to a group. Because a group contains more than one user, all users in a group receive the permissions granted to the group. If a user is added to a group, that user automatically inherits all the implicit permissions of that group.

The easiest way to administrate a secure workgroup is to create new groups and assign permissions to the groups, rather than to individual users. Later you can change individual users’ permissions by adding or removing them from groups. In a simple situation, you might want to use only the default groups to define permissions — assigning administrators to the Admins group and all other users to the Users group. If you do this, keep in mind that because the Users group is identical across all installations of Access, any permissions that you assign to the Users group are available to all users of Access.

For a higher level of security, revoke all permissions for the Users group, create your own groups, and assign permissions to them. There is no need to create an alternative to the Admins group, as long as you enter a secure WID when you create a new workgroup information file. By doing so, you ensure that the Admins group is unique to that file.

When a user tries to perform an operation on an object, the user’s security level is determined by the least restrictive of the permissions explicitly granted to the user and the permissions implicitly granted to the groups to which the user belongs. For example, if a user has explicit Read permission for a table but no Write permission, and that same user also belongs to a group that has Write permission for the table, the user can write to the table because of the higher level of the implicit Write permission.

You can set permissions on all of the objects in the database except for modules and the links to data access pages. Access provides no form of security for data access page links other than control over who can open a database, but you can password-protect all of the modules stored in a database by locking the Visual Basic for Applications project.

Permissions for a database can be changed only by the owner of the database or by members of the Admins group.

Revoking permissions

Even though users might not be able to perform an action at a given moment, they might be able to grant themselves permissions to perform the action. This is true if the user is a member of the Admins group of the workgroup information file in use when the database was created, or if the user is the owner of an object.

The following table summarizes the permissions that you can revoke or assign as they are identified in the User and Group Permissions dialog box.

Some permissions automatically include other permissions. For example, the Update Data permission for a table automatically includes the Read Data and Read Design permissions because they are needed to modify the data in a table. The Modify Design and Read Data permissions include the Read Design permission. For macros, the Read Design permission includes the Open/Run permission.

This permission	Permits a user to perform these actions on database objects
Open/Run	Open a database, form, or report; or run a macro.
Open Exclusive	Open a database in exclusive mode.
Read Design	View tables, queries, forms, reports, and macros in Design view.
Modify Design	View and change the design of tables, queries, forms, reports, and macros; or delete these objects.
Administer	For databases, set database password, replicate a database, and change startup properties. For tables, queries, forms, reports, and macros, have full access to these objects and data, including ability to assign permissions.
Read Data	View data in tables and queries.
Update Data	View and modify data in tables and queries, but not insert or delete data.
Insert Data	View and insert data in tables and queries, but not modify or delete data.
Delete Data	View and delete data in tables and queries, but not modify or insert data.

In most cases, you must assign Read Data and Read Design permissions for the table in the back-end database, and Modify Design permission for the table link defined in the front-end database so that users can access a linked table. If you want to restrict all access to the back-end tables but still allow users to view the data and relink the tables, remove all permissions on the back-end table and use queries in the front-end database with their RunPermissions property set to Owner’s.

Note When you edit an object and save it, the object retains its assigned permissions. However, if an object is saved with a new name, the associated permissions are lost, and you have to reassign them.

Why can’t I revoke Administer permission for the Admins group?

In the Admins group, the Administer permission — the right to change permissions — cannot be revoked by using the Access user interface. Even if you clear the Administer check box on the Permissions tab in the User and Group Permissions dialog box (Tools menu, Security submenu) for the Admins group on an object, the permission remains. You can use DAO code to revoke the Administer permission, but this is not recommended because there is no way to grant the permission back again.

Instead, make sure that you are using a workgroup information file created with a secure WID, which ensures a unique and secure Admins group, and then make sure that only the appropriate users are assigned to the Admins group.

Ownership

Understanding the concept of ownership is crucial to understanding the Access security model. The user who creates an object owns that object. This ownership grants that user special privileges for that object; thus, that user can always assign or revoke permissions for that object. The owner of an object can always grant permissions on the object, even if a member of the Admins group has revoked his or her explicit permissions on it.

Before you establish user-level security, the default Admin user is the owner of the database and all the objects in it. To effectively manage security, you need to change the ownership of the database and all the objects in it. You can do this for all objects except the database itself on the Change Owner tab in the User And Group Permissions dialog box (Tools menu, Security submenu). However, you can’t change the owner of a database on the Change Owner tab in the same way.

When you are using the Access user interface, the only way to transfer the ownership of all the objects and the database itself is to log on as the user who you want to own the database, create a new blank database, and then import all of the objects. However, when you run the User-Level Security Wizard to secure your database, it transfers ownership of the database and all of its objects by using code.

Transferring ownership of an object to another user can be achieved by logging on as that user and re-creating the object. To re-create an object, make a copy of the object, or import or export it to another database. To re-create an object, you must have Read Design permission for the object and, if the object is a table or query, you must also have Read Data permission.

Note These procedures don’t change the ownership of queries whose RunPermissions property is set to Owner’s. You can change ownership of a query only if you own the query, or if its RunPermissions property is set to User’s.

Using Security Features in Access

Setting User-Level Security