Set or change Access 2003 user-level security in Access 2007

If you created a database in a previous version of Access and you applied user-level security to that database, those security settings remain in place when you open that file in Microsoft Office Access 2007. In addition, you can start the security tools provided by Microsoft Office Access 2003 — the User-Level Security Wizard and the various user and group permission dialog boxes — from Office Access 2007. This article explains how the Access 2003 security features work, and it explains how to start and use them in Access 2007.

Note: The information in this article applies only to a database created in Access 2003 or earlier versions (an .mdb file). User-level security is not available for databases created in Office Access 2007 (.accdb files). Also, if you convert your .mdb file to the new format (an .accdb file), Office Access 2007 discards your user-level security settings.

How user-level security behaves in Access 2007

Access 2007 provides user-level security only for databases created in Access 2003 and earlier versions (.mdb and .mde files). In Access 2007, if you open a database created in an earlier version of Access, and that database has user-level security applied, that security feature will work as designed for that database. For example, users must enter a password to use the database.

In addition, you can start and run the various security tools provided by Access 2003 and earlier versions, such as the User-Level Security Wizard and the various user and group permission dialog boxes. As you proceed, remember that those tools become available only when you open an .mdb or .mde file. If you convert the files to the Office Access 2007 file format, Access removes all existing security features.

Top of Page

Understand Access 2003 user-level security

The following sections provide background information about user-level security in Access 2003 and earlier versions. If you are already familiar with the previous security model and user-level security, you can skip these sections and go directly to Set user-level security or Remove user-level security, later in this article.

The basics of user-level security

User-level security in Access 2007 resembles the security mechanisms on server-based systems — it uses passwords and permissions to allow or restrict the access of individuals, or groups of individuals, to the objects in your database. In Access 2003 or earlier versions, when you implement user-level security in an Access database, a database administrator or an object's owner can control the actions that individual users, or groups of users, can perform on the tables, queries, forms, reports, and macros in the database. For example, one group of users can change the objects in a database, another group can only enter data into certain tables, and a third group can only view the data in a set of reports.

User-level security in Access 2003 and earlier versions uses a combination of passwords and permissions — a set of attributes that specifies the kinds of access that a user has to the data or objects in a database. You can set passwords and permissions for individuals or groups of individuals, and those combinations of passwords and permissions become security accounts that define the users and groups of users who are allowed access to the objects in your database. In turn, the combination of users and groups is known as a workgroup, and Access stores that information in a workgroup information file. At startup, Access reads the workgroup information file and enforces the permissions based on the data in the file.

By default, Access provides a built-in user ID and two built-in groups. The default user ID is Admin, and the default groups are Users and Admins. By default, Access adds the built-in user ID to the Users group because all IDs must belong to at least one group. In turn, the Users group has full permissions on all the objects in a database. In addition, the Admin ID is also a member of the Admins group. The Admins group must contain at least one user ID (there must be a database administrator), and the Admin ID is the default database administrator until you change it.

When you start Access 2003 or earlier versions, Access assigns the Admin user ID to you and thus makes you a member of each default group. That ID and those groups (Admin and Users) give all users full permissions on all the objects in a database — this means that any user can open, view, and change all the objects in all .mdb files unless you implement user-level security.

One way to implement user-level security in Access 2003 or earlier versions is to change the permissions for the Users group and add new administrators to the Admins groups. When you do so, Access automatically assigns new users to the Users group. When you take those steps, users must log in with a password whenever they open the protected database. However, if you need to implement more specific security — allow one group of users to enter data and another to only read that data, for example — you must create additional users and groups, and grant them specific permissions to some or all of the objects in the database. Implementing that type of user-level security can become a complex task. To help simplify the process, Access provides the User-Level Security Wizard, which makes it easier to create users and groups in a one-step process.

The User-Level Security Wizard helps you to assign permissions and create user and group accounts. User accounts contain user names and unique personal ID numbers (PIDs) needed to manage a user's permissions to view, use, or change database objects in an Access workgroup. Group accounts are a collection of user accounts that, in turn, reside in a workgroup. Access uses a group name and PID to identify each work group, and the permissions assigned to a group apply to all users in the group. For more information about using the wizard, see Set user-level security, later in this article.

After you complete the wizard, you can manually assign, modify, or remove permissions for user and group accounts in your workgroup for a database and its existing tables, queries, forms, reports, and macros. You can also set the default permissions that Access assigns for any new tables, queries, forms, reports, and macros that you or another user add to a database.

Understand workgroups and workgroup information files

In Access 2003 and earlier versions, a workgroup is a group of users in a multiuser environment who share data. A workgroup information file contains the user and group accounts, passwords, and permissions set for each individual user or group of users. When you open a database, Access reads the data in the workgroup information file and enforces the security settings that the file contains. In turn, a user account is a combination of user name and personal ID (PID) that Access creates to manage the user's permissions. Group accounts are collections of user accounts, and Access also identifies them by group name and a personal ID (PID). Permissions assigned to a group apply to all users in the group. Those security accounts can then be assigned permissions for databases and their tables, queries, forms, reports, and macros. The permissions themselves are stored in the security-enabled database.

The first time a user runs Access 2003 or earlier versions, Access automatically creates an Access workgroup information file that is identified by the name and organization information that the user specifies when he installs Access. The setup program adds the relative location of this workgroup information file to the following registry keys:

HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Access\Jet\4.0\Engines\SystemDB

and

HKEY_USERS\.DEFAULT\Software\Microsoft\Office\11.0\Access\Jet\4.0\Engines\SystemDB

Subsequent users will inherit the default workgroup file path from the value in the HKEY_USERS registry key. Because this information is often easy to determine, it is possible for unauthorized users to create another version of this workgroup information file. Consequently, unauthorized users could assume the irrevocable permissions of an administrator account (a member of the Admins group) in the workgroup defined by that workgroup information file. To prevent unauthorized users from assuming these permissions, create a new workgroup information file, and specify a workgroup ID (WID), a case-sensitive alphanumeric string from 4 to 20 characters long that you enter when you create a new workgroup information file. Creating a new workgroup uniquely identifies the Admin group for this workgroup file. Only someone who knows the WID will be able to create a copy of the workgroup information file. To create the new file, you use the Workgroup Administrator tool.

Note: Office Access 2007 does not provide the Workgroup Administrator tool. For information about using Workgroup Administrator, see the Help for Access 2003.

Any user and group accounts or passwords that you create are saved in this workgroup information file, unless the user joins a different workgroup by using the Workgroup Administrator tool.

Important: Be sure to write down your exact name, organization, and workgroup ID — including whether letters are uppercase or lowercase (for all three entries) — and keep them in a secure place. If you must re-create the workgroup information file, you must supply the exact same name, organization, and workgroup ID. If you forget or lose these entries, you might lose access to your databases.

Understand how permissions work and who can assign them

User-level security recognizes two types of permissions: explicit and implicit. Explicit permissions are those permissions that are granted directly to a user account; no other users are affected. Implicit permissions are the permissions granted to a group account. Adding a user to that group grants the group's permissions to that user; removing a user from the group takes away the group's permissions from that user.

When a user attempts to perform an operation on a database object that employs security features, that user's set of permissions are based on the intersection of that user's explicit and implicit permissions. A user's security level is always the least restrictive of that user's explicit permissions and the permissions of any and all groups to which that user belongs. For this reason, the least complicated way to administer a workgroup is to create new groups and assign permissions to the groups, rather than to individual users. Then you can change individual users' permissions by adding or removing those users from groups. Also, if you need to grant new permissions, you can grant them to all members of a group in a single operation.

Permissions can be changed for a database object by:

Members of the Admins group of the workgroup information file in use when the database was created.
The owner of the object.
Any user who has Administer permissions for the object.

Even though users might not be able to currently perform an action, they might be able to grant themselves permissions to perform the action. This is true if a user is a member of the Admins group, or if a user is the owner of an object.

The user who creates a table, query, form, report, or macro is the owner of that object. Additionally, the group of users that can change permissions in the database can also change the ownership of these objects, or they can re-create these objects, both of which are ways to change ownership of the objects. To re-create an object, you can make a copy of the object, or you can import it from, or export it to, another database. This is the easiest way to transfer the ownership of objects, including the database itself.

Note: Copying, importing, or exporting doesn't change the ownership of a query that has its RunPermissions property set to Owner's. You can change ownership of a query only if its RunPermissions property is set to User's.

About organizing security accounts

An Access 2003 workgroup information file contains the following predefined accounts.

Account	Function
Admin	The default user account. This account is exactly the same for every copy of Access and other programs that can use the Microsoft Jet database engine, such as Visual Basic for Applications (VBA) and Microsoft Office Excel 2003.
Admins	The administrator's group account. This account is unique to each workgroup information file. By default, the Admin user is a member of the Admins group. There must be at least one user in the Admins group at all times.
Users	The group account comprising all user accounts. Access automatically adds user accounts to the Users group when a member of the Admins group creates them. This account is the same for any workgroup information file, but it contains only user accounts created by members of the Admins group of that workgroup. By default, this account has full permissions on all newly-created objects. The only way to remove a user account from the Users group is for a member of the Admins group to delete that user.

In effect, security in Access 2003 and earlier versions is always active. Until you activate the logon procedure for a workgroup, Access invisibly logs on all users at startup by using the default Admin user account with a blank password. Behind the scenes, Access uses the Admin account as the administrator account for the workgroup. Access uses the Admin account in addition to the owner (group or user) of any databases and tables, queries, forms, reports, and macros that are created.

Administrators and owners are important because they have permissions that can't be taken away:

Administrators (members of the Admins group) can always get full permissions for objects created in the workgroup.
An account that owns a table, query, form, report, or macro can always get full permissions for that object.
An account that owns a database can always open that database.

Because the Admin user account is exactly the same for every copy of Access, the first steps in helping to secure your database are to define administrator and owner user accounts (or use a single user account as both the administrator and owner accounts), and then to remove the Admin user account from the Admins group. Otherwise, anyone with a copy of Access can log on to your workgroup by using the Admin account and have full permissions for the workgroup's tables, queries, forms, reports, and macros.

You can assign as many user accounts as you want to the Admins group, but only one user account can own the database — the owning account is the user account that is active when the database is created or when ownership is transferred by creating a new database and importing all of a database's objects into it. However, group accounts can own tables, queries, forms, reports, and macros within a database.

Considerations when organizing security accounts

Only user accounts can log on to Access; you can't log on by using a group account.
The accounts that you create for users of the database must be stored in the workgroup information file that those users will join when they use the database. If you are using a different file to create the database, change the file before creating the accounts.
Make sure to create a unique password for your administrator and user accounts. A user who can log on by using the administrator account can always get full permissions for any tables, queries, forms, reports, and macros that were created in the workgroup. A user who can log on by using an owner account can always get full permissions for those objects owned by that user.

After you create user and group accounts, you can view and print the relationships between them. Access prints a report of the accounts in the workgroup that shows the groups to which each user belongs and the users that belong to each group.

Note: If you are using a workgroup information file created with Microsoft Access 2.0, you must be logged on as a member of the Admins group to print user and group information. If the workgroup information file was created with Microsoft Access 97 or later, all users in the workgroup can print user and group information.

Top of Page

Set user-level security

The steps in this section explain how to start and run the User-Level Security Wizard. Remember that these steps apply only to databases created in Access 2003 or earlier versions and opened in Office Access 2007.

Start the User-Level Security Wizard

Open the .mdb or .mde file that you want to help protect.
On the Database Tools tab, in the Administer group, click the arrow below Users and Permissions, and then click User-Level Security Wizard.
Follow the steps on each page to complete the wizard.

Note:

The User-Level Security Wizard creates a back-up copy of the current Access database with the same name and a .bak file name extension, and then employs security measures for the selected objects in the current database.
If your current Access database helps protect VBA code by using a password, the wizard prompts you for the password, which you must enter for the wizard to complete its operation successfully.
Any passwords that you create through the wizard are printed in the User-Level Security Wizard report, which is printed when you finish using the wizard. You should keep this report in a secure location. You can use this report to re-create your workgroup file if it is lost or corrupted.

Top of Page

Remove user-level security

The only way to remove user-level security while working in Office Access 2007 is to convert the .mdb file to an .accdb file. You can convert a file or save a copy of the file in the Access 2007 format. The steps in this section explain how.

Note: You cannot convert an earlier version database if the database is encrypted. You must have sufficient permissions to administer the database before you can decrypt it. Typically, this means you must belong to the Admins group of the workgroup that was used to secure the database.

Convert the file

Click the Microsoft Office Button and then click Convert.

The Save As dialog box appears.
Use the Save In list to find a location in which to save the converted database.
In the Save as type list, select Microsoft Office Access 2007 Database (*.accdb).
Click Save.

Save a copy of the file in the Access 2007 format

Click the Microsoft Office Button .
Point to Save As, and then click Access 2007 File Format.

The Save As dialog box appears.
Use the Save In list to find a location in which to save the new file.
In the Save as type list, make sure Microsoft Office Access 2007 Database (*.accdb) appears.
Click Save.

Top of Page

Object permissions reference

The following table lists the permissions that you can set for a database and the objects in the database, and it describes the effect or result of using each permission setting.

Permission	Applies to these objects	Result
Open/Run	Entire database, forms, reports, macros	Users can open or run the object, including procedures in code modules.
Open Exclusive	Entire database	Users can open a database and lock out other users.
Read Design	Tables, queries, forms, macros, code modules	Users can open the listed objects in Design view. Note: Whenever you grant access to the data in a table or query by assigning another permission, such as Read Data or Update Data, you also grant Read Design permissions because the design must be visible to correctly present and view the data.
Modify Design	Tables, queries, forms, macros, code modules	Users can change the design of the listed objects.
Administer	Entire database, tables, queries, forms, macros, code modules	Users can assign permissions to the listed objects, even when the user or group does not own the object.
Read Data	Tables, queries	Users can read the data in a table or query. To grant users permissions to read queries, you must also give those users permissions to read the parent tables or queries. This setting implies Read Design permission, which means that users can read your table or query design in addition to the data.
Update Data	Tables, queries	Users can update the data in a table or query. Users must have permissions to update the parent table or queries. This setting implies both Read Design and Read Data permissions.
Insert Data	Tables, queries	Users can insert data into a table or query. For queries, users must have permissions to insert data into the parent tables or queries. This setting implies both Read Data and Read Design permissions.
Delete Data	Tables, queries	Users can delete data from a table or query. For queries, users must have permissions to delete data from the parent tables or queries. This setting implies both Read Data and Read Design permissions.

Top of Page

Microsoft Office Tutorials

Thursday, March 16, 2017