About user-level security (MDB)

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) and users (named the Users group), 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 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.

The User-Level Security Wizard will help you to assign permissions and create user and group accounts. 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.

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 and group accounts 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 of an administrator account (a member of 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). 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.

There are 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 those permissions that are 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 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:

• 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 permission 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, 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.

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

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 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 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.

• 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.