databasedev.co.uk - database solutions and downloads for microsoft access

Web 2.0-like User-Level Menus in Access
UI Builder for Microsoft Access offers user-level menus out of the box. Limit users to specific forms and reports in your database without implementing Access workgroup security. UI Builder is packed with features to enhance any multi-user database. Download a free trial today.

Microsoft Access Database Security

Security Overview:

To control what an individual user or group of users can do with database objects, you can secure a Microsoft Access database. As you create database objects, you might consider who will use them and what types of activities the users should be allowed to perform with the objects. These activities might include viewing, modifying or deleting database objects or information. Database security is primarily an issue in a multi-user environment, although you can also secure databases that are stored on a single-user computer.

In Microsoft Access, a complete security system contains several parts. These parts include workgroups, user and group accounts, ownership and permission assignments. A workgroup is a group of users listed in a system database file, who usually share data in a multi-user environment. Ownership is a security feature that establishes which account owns a database and its objects. Permission assignments establish levels of authority for each user or group to use database objects.

Information about workgroups and accounts is stored in the workgroup information file, System.mdw. This file is used when you start Microsoft Access. It is good practice to make a backup copy of the System.mdw file that you installed with Access before you add security features that change this file. The System.mdw file contains the following information: users and groups who can open the application; user passwords and a binary code called a SystemID (SID), which identifies the current user; the operating preferences set in the Options dialog box; and the custom toolbars, windows, and security settings.

Permissions are what you can do with an object, such as entering and editing data or just reading data. Users inherit rights and permissions from the groups to which they belong.

The System.mdw file contains three pre-defined accounts:

  • Admin - the default user account. This account is the same for every installation of Microsoft Access.
  • Admins - the administrators' group account. A group account is a collection of user accounts contained in a workgroup. Permissions assigned to a group are passed to all users of the group.
  • Users - the group account containing all user accounts.

When you install Microsoft Access, you are automatically made a member of a group named Admins with the user-account name Admin. By default, you also have an empty password and Personal Identifier or Personal ID (PID). Until you activate the logon procedure, the Admin user account is hidden, so you will not be aware of it working in the background. The administrator and owner user-accounts have permissions that can't be removed (even if it looks as if these permissions have been removed). An administrator account always has full permissions for all objects created in a workgroup. An owner account has full permissions for all objects created in a workgroup. An owner account has control (irrevocable permissions) over a database and its objects created in a particular workgroup or in an application. You can create administrator and owner accounts in the workgroup in which you create the application. Then you can have users join the workgroup when they use the application; this means their copy of Microsoft Access will use that particular workgroup information file and its accounts.

Personal IDs and account names are case-sensitive. It's good practice to write down each exact account name and its PID and to store them in a secure place in case you need to rebuild the system database file. These items can not be recovered if you forget them.

Before considering implementing any of the Microsoft Access database security features mentioned above, you should download a copy of the Security FAQ's from the address below. This document answers just about any Microsoft Access security question you may have:

Microsoft Access Security FAQ Available in Download Center

Check out the 12 part tutorial regarding Access Database Security by looking at part 1 - Steps to Securing an Access Database by Using User-level Security