Set or Change Access 2003 user-level security in Access 2010

If you created a database in an earlier version of Access and you applied user-level security to the database, those security settings remain unchanged when you open the file in Microsoft Access 2010. In addition, you can launch the security tools provided by Microsoft Office Access 2003, such as the user-level Security wizard and various user and Group permissions dialog boxes, from Access 2010. This article describes how Access 2003 security features work, and how to start and use these features in Access 2010. The information in this article applies only to databases (. mdb files) that you create in Access 2003 or later in Access. User-level security is not available for databases created in Access 2010 (. accdb files). In addition, if you convert an. mdb file to a new format (. accdb file), Access 2010 Discards your user-level security settings.

[How user-level security works and overviews in Access 2010] 1, how user-level security works in Access 2010 [/page]

1, Access 2010 provides user-level security only for databases that use Access 2003 and earlier file formats (. mdb and. mde files). In Access 2010, if you open a database that was created in a later version of Access and the database has user-level security applied, the security feature is still valid for that database. For example, a user must enter a password to use the database. In addition, you can start and run various security tools provided by Access 2003 and later access, such as the user-level Security wizard and various user and Group permissions dialog boxes. During the operation, keep in mind that these tools are available only when you open an. mdb or. mde file. If you convert a file to an Access 2010 file format, Access deletes all existing user-level security features.

2, Access 2003 user-level security overview

The following sections provide background information about user-level security in Access 2003 and later in Access. If you are familiar with previous security models and user-level security, you can skip these sections and go directly to setting up user-level security or deleting user-level security later in this article.

Basic information for user-level security

User-level security in Access is similar to security on a server-based system that uses passwords and permissions to allow or restrict access to objects in the database by individuals or groups. In Access 2003 or later in Access, when you implement user-level security in an Access database, the database administrator or object owner can control the actions that individual users or groups of users perform on tables, queries, forms, reports, and macros in the database. For example, a group of users can change objects in a database, another group can only enter data into a particular table, and one group can view only the data in a group of reports.

User-level security in Access 2003 and later Access uses a combination of passwords and permissions, which is a set of properties that specify the type of access a user has to data or objects in a database. You can set passwords and permissions for individuals or groups, and the combination of those passwords and permissions becomes a security account that can be used to define the users and groups of users who are allowed access to objects in the database. Accordingly, a combination of users and groups is called a workgroup, and Access stores that information in the workgroup information file. When Access starts, it reads the workgroup information file and determines which users and groups have the appropriate 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 group is 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. Accordingly, the Users group has full permissions for all objects in the database. Additionally, the Admin ID is a member of the Admins group. The Admins group must contain at least one user ID (must have a database administrator), and the admin ID will remain the default database administrator until you change it.

When you start Access 2003 or later, Access assigns the Admin user ID to you, so that you become a member of each default group. This ID and these groups (Admin and users) provide all users with full permissions to all objects in the database, which means that unless you implement user-level security, any user can open, view, and change all the objects in all. mdb files.

One way to implement user-level security in Access 2003 or later in Access is to change the permissions of the Users group and add new administrators to the Admins group. If you do this, Access automatically assigns new users to the Users group. After these steps are performed, the user must log on with a password to open the protected database. However, if you need to implement more detailed security mechanisms (for example, allowing a group of users to enter data and only allow another group of users to read the data), you must create additional users and groups and grant them specific permissions on some or all of the objects in the database. Implementing such user-level security mechanisms is a complex task. To help simplify this process, Access provides the user-level Security wizard, which makes it easier to create users and groups in one step.

The user-Level Security Wizard can help you assign permissions and create user and group accounts. A user account contains a user name and a unique personal ID number (PID) that you can use to manage permissions for users to view, use, or change database objects in an Access workgroup. A group account is a collection of user accounts, so the group account is in a workgroup. Access uses the group name and PID to identify each workgroup, and the permissions assigned to the group apply to all users in the group. For more information about using the wizard, see Setting up user-level security later in this article.

After you complete the wizard, you can manually assign, modify, or delete permissions on the database and its existing tables, queries, forms, reports, and macros from the user and group accounts in your workgroup. You can also set default permissions for any new tables, queries, forms, reports, and macros that access adds to the database for you or another user.

Workgroup and workgroup information files

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

When a user first runs Access 2003 or later, Access automatically creates an Access workgroup information file that is identified by the name and organization information that the user specified when installing access. For Access 2003, Setup adds the relative location of this workgroup information file to the following registry key:

Hkey_current_usersoftwaremicrosoftoffice11.0accessjet4.0enginessystemdb

And

HKEY_USERS. Defaultsoftwaremicrosoftoffice11.0accessjet4.0enginessystemdb

Future users will inherit the default workgroup file path from the values in the HKEY_USERS registry key. Because this information is usually easy to determine, an unauthorized user can also create another version of the workgroup information file. Therefore, these users may obtain an irrevocable Administrator account (members of the Admins group) permissions in the workgroup defined by the workgroup information file. To prevent unauthorized users from obtaining these permissions, you should create a new workgroup information file and specify a workgroup ID (WID), a string of 4 to 20 case-sensitive alphanumeric characters that you must enter when you create a new workgroup information file. Create a new workgroup to uniquely identify the Admin group for the workgroup file. Only users who know WID can create a copy of the workgroup information file. To create a new file, you can use the Workgroup Administrator tool.

Unless a user joins another workgroup by using the Workgroup Administrator tool, any user and group accounts or passwords that you create will be saved in the workgroup information file.

Important Make sure to write down the exact name, organization, and workgroup ID (including whether the letters in these three entries are uppercase or lowercase) and keep them in a secure location. When you must re-create the workgroup information file, you must provide exactly the same name, organization, and workgroup ID. If you forget or lose these entries, you may not be able to access your database.

User-level security can recognize two types of permissions: Explicit permissions and implicit permissions. Explicit permissions are the permissions that are granted directly to the user account, and are not affected by other users. Implicit permissions are permissions granted to a group account. Adding a user to a group gives that user permissions to the group, and removing a user from the group cancels the group's permissions for that user.

When a user attempts to perform an operation on a security-enabled database object, the user's permission set is the intersection of its display and implicit permissions. The user's security level typically has minimal restrictions on the user's explicit permissions and on the permissions of any and all groups to which the user belongs. Therefore, the easiest way to manage a workgroup is to create a new group and assign permissions to that group, rather than assigning permissions to individual users. You can then change the permissions for individual users by adding or removing users from the group. In addition, when you need to grant new permissions, you can grant that permission to all members of the group by an action.

People who can change permissions on database objects include:

Admins Group member of the workgroup information file used when creating the database.

The object owner.

Any user with administrator permissions for the object.

Even though the user may not currently be able to perform an action, they can grant themselves permission to perform this action. You can do this if the user is a member of the Admins group, or is the owner of the object.

The user who creates a table, query, form, report, or macro is the owner of the object. In addition, user groups that can change permissions in the database can also change ownership of these objects or re-create them, both of which can change the ownership of the object. To recreate an object, you can make a copy of the object, or import an object from another database or export the object to another database. This is the simplest way to transfer the ownership of objects, including the database itself.

Note Copying, importing, or exporting does not change the ownership of a query that has the RunPermissions property set to owner. You can change the ownership of a query only if the query's RunPermissions property is set to "owned by users."

3. Security Account

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

In fact, the security mechanisms in Access 2003 and later access are always valid. Before you activate the logon process for a workgroup, all users are implicitly logged on by using the default Admin user account with a blank password when Access starts. In the background, Access uses the Admin account as the Administrator account for the workgroup. For all databases, tables, queries, forms, reports, and macros that you create, access also uses the Admin account in addition to the owner (group or user) of those objects.

Administrators and owners are important because they have the right to be undone:

Administrators (members of the Admins group) always have full permissions to the 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 the database.

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

You can assign any number of user accounts to the Admins group as needed, but only one user account can own the database, and the account that owns the database refers to the user account that is active when the following actions are performed: Creating a Database, or transfer ownership by creating a new database and importing all the objects of a database into the new database. However, group accounts can also have tables, queries, forms, reports, and macros in the database.

Issues to be aware of when organizing security accounts

Only user accounts can log on to Access, and you cannot use group accounts to log on.

Accounts created for database users must be stored in the workgroup information file that these users will join when they use the database. If you create a database with a different file, you should change the file before you create the account.

Make sure that you create a unique password for the administrator and user accounts. Users who can log on with an administrator account can always get full permissions to all the tables, queries, forms, reports, and macros created in the workgroup. Users who can log on with an owner account can always obtain full permissions for the objects they own.

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

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

Set start/delete user-level security

The steps in this section describe how to start and run the user-Level Security wizard. Keep in mind that these steps apply only to databases that have Access 2003 file formats or earlier file formats and open in Access 2010.

Start the user-level Security wizard

Open the. mdb or. mde file that you want to manage.

On the Database Tools tab, in the Administration group, click the arrow under Users and permissions, and then click the User-Level Security wizard.

Follow the steps on each page to complete the wizard. The user-level Security Wizard creates a backup copy of the current Access database with the same name and a. bak file extension, and then applies security measures to the selected objects in the current database.

If the current Access database uses a password to help protect the VBA Code, the wizard prompts you for the password, and the wizard does not successfully complete its operation until you enter the password.

All passwords created through the wizard are printed in the user-Level Security Wizard report that is printed when you complete the wizard. The report should be saved in a secure location. You can use this report to recreate a workgroup file when it is missing or corrupted.

Delete user-level security

To remove user-level security when using Access No. 2010, save the. mdb file as a. accdb file.

Save a copy of the file in Access 2007 format

Click the Files tab. The Backstage view opens.

On the left, click Share.

On the right, click Save Database as, and then click Access Database (*.ACCDB).

The Save As dialog box appears.

Use the Save in list to find the location where you want to save the converted database.

In the Save as type list, select Microsoft Office Access 2007 Database (*.ACCDB).

Click Save.

Object permission Reference

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