About user-level security (MDB)

 Note   The information in this topic applies only to a Microsoft Access database (.mdb).

Microsoft Access user-level security (user-level security: When using user-level security in an Access database, a database administrator or an object's owner can grant individual users or groups of users specific permissions to tables, queries, forms, reports, and macros.) is very similar to the security mechanisms seen on server-based systems. By using passwords and permissions (permissions: A set of attributes that specifies what kind of access a user has to data or objects in a database.), you can allow or restrict the access of individuals, or groups of individuals, to the objects in your database. Security accounts define the users and groups of users allowed access to the objects in your database. This information, known as a workgroup (workgroup: A group of users in a multiuser environment who share data and the same workgroup information file.), is stored in a workgroup information file (workgroup information file: A file that Access reads at startup that contains information about the users in a workgroup. This information includes users' account names, their passwords, and the groups of which they are members.).

ShowAbout user-level security

The best method of helping secure a database is called user-level security. The two main reasons to use user-level security are to:

  • Prevent users from inadvertently breaking an application by changing tables, queries, forms, reports, and macros on which the application depends.
  • Help protect sensitive data in the database.

Under user-level security, users type a password when they start Microsoft Access. Access then goes out and reads a workgroup information file, where each user is identified by a unique identification code. Within the workgroup information file, users are identified as authorized individual users, and as members of specific groups, by their personal ID and password. Microsoft Access provides two default groups: administrators (named the Admins group (Admins group: The system administrator's group account, which retains full permissions on all databases used by a workgroup. The Setup program automatically adds the default Admin user account to the Admins group.)) and users (named the Users group (Users group: The group account that contains all user accounts. Access automatically adds user accounts to the Users group when you create them.)), but you can define additional groups.

Although setting up user-level security on most databases can be a daunting task, the User-Level Security Wizard makes it easy to employ innovative security features for your Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose.) in a one-step process. Furthermore, by implementing common security schemes, the User-Level Security Wizard may even eliminate the need to use the Security command on the Tools menu.

ShowIf the wizard doesn't start

This could be due to the fact that Access is running in sandbox mode but Microsoft Jet 4.0 SP8 or later is not installed on your computer. Jet 4.0 SP8 or later is required for Access to be fully functional when sandbox mode is enabled.

For more information about installing the Jet upgrade, see the Office Online article About Microsoft Jet 4.0 SP8 or later.

For more information about sandbox mode, see the Office Online article About Microsoft Jet Expression Service sandbox mode.

The User-Level Security Wizard will help you to assign permissions and create user (user account: An account identified by a user name and personal ID (PID) that is created to manage the user's permissions to access database objects in an Access workgroup.) and group accounts (group account: A collection of user accounts in a workgroup, identified by group name and personal ID (PID). Permissions assigned to a group apply to all users in the group.). However, after running 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 Microsoft Access assigns for any new tables, queries, forms, reports, and macros that are created in a database.

Permissions are granted to groups and users to regulate how they are allowed to work with each table, query, form, report, and macro in a database. For example, members of the Users group might be allowed to view, enter, or modify data in a Customers table but not to change the design of that table. Members of the Users group might be allowed to only view data in a table containing order data but be totally denied any access to a Payroll table. Members of the Admins group have full permissions on all of a database's tables, queries, forms, reports, and macros. You can have more detailed control by creating your own group accounts, assigning appropriate permissions to those groups, and then adding users to those groups.

If you only need an administrators group and users group for your security purposes, you don't need to create additional groups; you can use the default Admins and Users groups. In this case, you only need to assign the appropriate permissions to the default Users group, and add any additional administrators to the default Admins group. Any new users you add are automatically added to the Users group. Typical permissions for the Users group might include Read Data and Update Data for tables and queries, and Open/Run for forms and reports.

If you need more detailed control of different groups of users, you can create your own groups, assign different sets of permissions to those groups, and then add users to the appropriate groups. To simplify the management of permissions, it is recommended that you only assign permissions to groups (not users), and then add users to the appropriate groups.

For example, you could help protect an Orders database by creating a Managers group for managers, a Sales Reps group for sales representatives, and a Staff group for staff employees. You can assign the least restrictive set of permissions to the Managers group, a more restrictive set of permissions to the Sales Reps group, and the most restrictive set of permissions to the Staff group. When you create a user account for a new employee, you add that account to the appropriate group. The employee then has the permissions associated with that group.

ShowAbout workgroups and workgroup information files

A Microsoft Access workgroup is a group of users in a multiuser environment who share data. If user-level security is defined, the members of a workgroup are recorded in user (user account: An account identified by a user name and personal ID (PID) that is created to manage the user's permissions to access database objects in an Access workgroup.) and group accounts (group account: A collection of user accounts in a workgroup, identified by group name and personal ID (PID). Permissions assigned to a group apply to all users in the group.) that are stored in a Microsoft Access workgroup information file. Users' passwords are also stored in the workgroup information file. These 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 Microsoft Access after Microsoft Office setup, Access will automatically create a Microsoft Access workgroup information file that is identified by the name and organization information that the user specifies. The relative location of this workgroup information file is then added 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 HKEY_USERS registry key. Because this information is often easy to determine, it's possible for unauthorized users to create another version of this workgroup information file and consequently assume the irrevocable permissions (permissions: A set of attributes that specifies what kind of access a user has to data or objects in a database.) of an administrator account (a member of the Admins group (Admins group: The system administrator's group account, which retains full permissions on all databases used by a workgroup. The Setup program automatically adds the default Admin user account to the Admins group.)) in the workgroup defined by that workgroup information file. To prevent this, create a new workgroup information file, and specify a workgroup ID (WID (workgroup ID: A case-sensitive alphanumeric string that is 4 to 20 characters long and that you enter when creating a new workgroup information file by using the Workgroup Administrator. This 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.

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

 Note   In Access 2002 or later, the Workgroup Administrator can now be run from the Security submenu on the Tools menu.

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 have to re-create the workgroup information file, you must supply exactly the same name, organization, and workgroup ID. If you forget or lose these entries, you can't recover them and might lose access to your databases.

ShowAbout how permissions work and who can assign them

There are two types of permissions (permissions: A set of attributes that specifies what kind of access a user has to data or objects in a database.): explicit and implicit. Explicit permissions are those permissions that are granted directly to a user account (user account: An account identified by a user name and personal ID (PID) that is created to manage the user's permissions to access database objects in an Access workgroup.); no other users are affected. Implicit permissions are those permissions that are granted to a group account (group account: A collection of user accounts in a workgroup, identified by group name and personal ID (PID). Permissions assigned to a group apply to all users in the group.). 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 them 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:

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, which is another way to change ownership of the objects. To re-create an object, you can make a copy of the object, or import it 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.

ShowAbout organizing security accounts

A Microsoft Access workgroup information file contains the following predefined accounts.

Account Function
Admin The default user account (user account: An account identified by a user name and personal ID (PID) that is created to manage the user's permissions to access database objects in an Access workgroup.). This account is exactly the same for every copy of Microsoft Access and other applications that can use the Microsoft Jet database engine (Microsoft Jet database engine: The part of the Access database system that retrieves and stores data in user and system databases. It can be thought of as a data manager upon which database systems, such as Access, are built.), such as Microsoft Visual Basic for Applications (VBA) (Visual Basic for Applications (VBA): A macro-language version of Microsoft Visual Basic that is used to program Windows applications and is included with several Microsoft applications.) and Microsoft Excel.
Admins The administrator's group account (group account: A collection of user accounts in a workgroup, identified by group name and personal ID (PID). Permissions assigned to a group apply to all users in the group.). This account is unique to each workgroup information file. By default, the Admin user is in the Admins group (Admins group: The system administrator's group account, which retains full permissions on all databases used by a workgroup. The Setup program automatically adds the default Admin user account to 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. Microsoft 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 Microsoft Access is always active. Until you activate the logon procedure for a workgroup, Microsoft Access invisibly logs on all users at startup by using the default Admin user account (Admin account: The default user account. When you install Access, the Setup program automatically includes the Admin user account in the workgroup information file that it creates.) with a blank password. Behind the scenes, Microsoft Access uses the Admin account as the administrator account for the workgroup, as well as the owner (owner: When security is being used, the user account that has control over a database or database object. By default, the user account that created a database or database object is the owner.) of any databases and tables, queries, forms, reports, and macros 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 the database.

Because the Admin user account is exactly the same for every copy of Microsoft Access, the first steps in securing your database are to define administrator and owner user accounts (or use a single user account as both the administrator and owner account), and then to remove the Admin user account from the Admins group. Otherwise, anyone with a copy of Microsoft 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 itself — 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.

ShowConsiderations when organizing security accounts:

  • Only user accounts can log on to Microsoft 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're 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 created in the workgroup. A user who can log on 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. Microsoft Access prints a report of the accounts in the workgroup, showing 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 version 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 version 7.0 or later, all users in the workgroup can print user and group information.

 
 
Applies to:
Access 2003