Access Cookbook - Ken Getz [106]
Every Access workgroup file includes two built-in groups: the Users group, which contains every user; and the Admins group, the members of which automatically get permission to administer security. There is also one built-in user, Admin. The Admin user starts out in the Admins group, but don't let the name Admin confuse you. You can remove Admin from the Admins group and take away all its administrative privileges, as long as some other user is left in Admins to act as the administrator. The Admin user has the same identity in every Access workgroup file, so any privileges that you give Admin will be available to anyone.
Securing a database involves adding a new member to the Admins group, removing the Admin user from that group, removing permissions from the Admin user and from the Users group, and assigning permissions to the custom groups that you define. The steps that follow show you how to implement user-level security in your Access database:
Create a new, unique workgroup file. In Access 2002 and later, this capability is built into the product, but in older versions you must run a separate utility called the Workgroup Administrator (Wrkgadm.exe). Write down the Name, Organization, and Workgroup ID strings that will be requested when you create your new workgroup file, and store them in a safe place. These strings will be encrypted to form the unique identity of your new workgroup file—if the original ever becomes lost or corrupted, it can be reconstructed as long as you input the identical strings. Each database "knows" the workgroup file it was secured with by this unique token (the Workgroup ID, or WID) and will not recognize a workgroup file that has a different WID. This means that you'll be permanently locked out of your database if you lose these strings. Also, upgrading a secured Access database to a newer version of Access is almost impossible if you don't have this information, because the recommended upgrade path is to recreate the workgroup file in the new version of Access and then upgrade the secured database. Figure 4-19 shows the Workgroup Administrator dialog with the new workgroup information. You can try this solution with any of the MDB files used in this chapter, such as 04-09.MDB.
Figure 4-19. The Workgroup Administrator dialog
The Workgroup Administrator automatically switches you to the new workgroup file, so you can simply close when you're finished. The Workgroup Administrator will create the necessary entries in the registry, making the new workgroup file the default. Start Access and load your database.
You will be logged on as a user named Admin. Use the Security menu options to set a password for the Admin user. This causes Access to prompt for a logon name and password the next time you try to open a database using this workgroup file.
Create a new user, which is the account you will use to secure the database. Add this new user to the Admins group, to make it the administrator. None of the user accounts has any built-in capabilities. You also need to write down the strings used for the Name and Personal Identifier (PID). Part of recreating a workgroup file is recreating the key accounts stored in it. The PID is not a password—it is encrypted along with the name string to create a System Identifier, or SID. The SID is the token used when assigning permissions and when distinguishing users from each other. The name alone isn't secure, although Access won't let you have duplicate names in the same workgroup file.
Quit Access entirely and restart, logging on as the new user account that you created in Step 4. Don't type anything in the Password dialog—you haven't set one for this account yet.
Remove the Admin user from the Admins group so that Admin is a member of