Access Cookbook - Ken Getz [205]
In addition to the permissions listed in the object inventory, you will need to assign the Database Open permission to each of the groups you created. This is necessary because the Security Wizard automatically revokes this permission for all users except those who are members of the Admins group. To add this permission, select Database as the Object Type and check the Open/Run permission.
Log out of Access and now log in as each new user. Select Tools → Security → User and Group Accounts. Recall that all new user accounts start out with no password. Choose the Change Logon Password tab and enter and confirm a new, non-blank password for each new account.
Discussion
Access's workgroup-based security model consists of two parts:
A system database, which defines a workgroup and contains user and group accounts
One or more databases associated with a workgroup, each containing objects (with their permissions) pointing to the user and group accounts in the workgroup
In Step 1 of this solution, the wizard created a new securable workgroup file. Do not use the default workgroup file that Access installed on your system. That file, called system.mdw, contains a null WID and is the same across all Access installations. Therefore, someone trying to break into your database can easily recreate it.
The wizard created the Administrator account, a new member of the Admins group, and then removed the default Admin user account from the Admins group. Although the Admin user and the Admins group have similar names, they are very different in Access security.
The Admin user account is the default user account for all new workgroups. Its presence in every workgroup allows you to ignore security until you need it, because Access attempts to log you on as Admin with a blank password whenever you start Access. By changing the password for this account, you are unhiding security. Once you assign a password to Admin, however, you must create a new administrator-level user account (in the example, we used the account Paul), since the Admin account is the same across all Access workgroups and is thus unsecurable.
Unlike the unsecurable Admin user account, the Admins group account is securable. In fact, this account is the key account in any secured Access database and derives its PID from the workgroup's WID. Each Admins group account is unique across Access workgroups. Therefore, you can't use the Admins account in one workgroup to try to break into another Access workgroup. Members of this account are able to modify and administer every object in every database associated with that workgroup.
The Security Wizard secures your database by removing all permissions to objects from all users other than the members of the Admins group and the person who ran the wizard. While it's certainly possible to secure your database without using the Security Wizard, it's easy to make a mistake and create a database with one or more security holes. Thus, using the wizard is a very good idea!
It's best not to assign object permissions explicitly to individual users; you'll find it easier to manage the security for a workgroup by considering the security of only groups. Occasionally, however, you may want to give a single user some special set of permissions. The actual level of permissions users get for a particular object is the sum of the permissions they have been assigned plus the permissions of each group in which they have membership.
Again, remember not to assign any permissions to either the Admin user account or the Users group account, as these accounts are the same in all workgroups and are thus unsecured.
10.2. Maintain Multiple Synchronized Copies of the Same Database
Problem
You have a database that you'd like to distribute to mobile salespeople. Multiple users update the central copy of the database on a daily basis,