LOOKING AT THE SECURITY USER INTERFACE

You've already learned how to add a password to a database through the user interface. The following sections demonstrate how to create new users and groups, assign permissions for the database and its objects, change the owner of an object, encrypt a database, and—finally— create a new workgroup information file.

Working with PIDs, SIDs, WIDs, and Passwords

Before you learn how to create a user or a group, you need to understand one additional fundamental security concept. When you create a user or a group (or a workgroup information file, in some sense), what you're actually doing is creating a security identifier (SID). A SID is an encrypted string used to identify a user or group.

When creating a user or group, you must enter a name and a personal identifier (PID). These two strings are combined and encrypted to form the SID, as in the following formula:

Name can be a user or a group.

The SIDs for the Users group and Admin user are the same across all versions of Access. This way, security can run invisibly without getting in the way of the millions of users who don't want to deal with security.

User Name Criteria

Names can be one to 20 alphanumeric characters long. Alphanumeric characters include accented characters, numbers, spaces, and symbols. Special exceptions apply as follows: names can't begin with spaces, control characters can't be used, and the reserved characters—" / [ ] : | < > + = ; , ? *—can't be used.

Personal Identifier Criteria

PIDs can be four to 20 alphanumeric characters long. The PID part of a SID is case sensitive. PIDs follow the same character limitations that user names do.

Workgroup Identifier

You use workgroup identifiers (WIDs) when you create a new workgroup information file. When creating a workgroup information file, you must fill out three fields instead of the traditional two fields. Name, Company, and Workgroup ID are all required to create a workgroup information file.

In creating a workgroup information file, you're actually creating a file that will function as your system database. The default system database is named System.mdw. As this file is created, it also creates the SID for the Admins group. It's your Admins group that keeps your system secure.

So unlike the other two default accounts (Admin and Users), which are the same across all versions of Access, the Admins group is unique to your workgroup information file.

Re-creating Accounts

Suppose that you create a user with the name MyNewUser and a PID of ThisIsMyPID999. If you have to re-create that user again by using MyNewUser and ThisIsmyPID999, you didn't re-create the proper SID. This is one letter off (the m in the PID isn't the same), and the encryption algorithm will create different SIDs.

SIDs are so important because they're used to compare permissions with objects. If a SID matches the object's permissions, Access allows the user to perform an action on an object. Without the proper SID, you might find yourself without any access to any objects, including the database object.

Passwords

User passwords are used as an extra step of security within Access. Passwords aren't necessarily needed for the Access user-level security model to work properly. They're simply used for additional protection.

Passwords verify a user. If a password is created, it and the name are compared to make sure that the logon is valid. From there, the SID is used to verify permissions.

Passwords can be up to 14 alphanumeric characters long, with the same limitations on special characters that user and group names have.

Creating a New User

In the Access user-level security model, users must be created. You can log on only as a user, such as Ryan; you can't log on as a group, such as Managers.

To create a new user through the Access user interface, follow these steps:

1.	From the Tools menu, choose Security and then User and Group Accounts.
2.	Select the Users tab.
3.	Click New.
4.	In the New User/Group dialog, enter a Name and Personal ID.
5.	Click OK to save the user.

Figure 21.5 shows the creation of the user Scott with a PID of B1a2R3k4E5r.

Creating a New Group

Creating new groups is almost identical to creating new user accounts. In fact, you'll see that they share a common interface in Figure 21.6, which shows the creation of a group named Managers with the PID S1e2C3r4E5t.

To create a new group account, follow these steps:

1.	From the Tools menu, choose Security and then User and Group Accounts.
2.	Select the Groups tab.

3.	Click New.
4.	In the New User/Group dialog, enter a Name and a Personal ID.
5.	Click OK to save the group.

Removing Users and Groups

Removing users and groups is just as easy as creating them. To remove a user or group, follow these steps:

1.	From the Tools menu, choose Security and then User and Group Accounts.
2.	Select the Users or Groups tab, depending on whether you want to remove a user or a group.
3.	Select the appropriate user or group from the Name combo box.
4.	Click Delete.
5.	At the prompt to verify the deletion, click OK for the user or group to be deleted, or click Cancel if you don't want to delete the user or group.

Adding a User to a Group

After you create your users and groups, the next logical step is to add the users to their appropriate groups. Figure 21.7 shows user Ryan being added to the Managers group.

You can add users to groups through the same dialog used for creating new users. Follow these steps to add a user to a group:

1.	From the Tools menu, choose Security and then User and Group Accounts.
2.	Select the Users tab.
3.	Select the appropriate user from the Name combo box.
4.	In the Group Membership section, select the appropriate group from the Available Groups list.
5.	Click Add. The group you selected will be transferred from the Available Groups list to the Member Of list.

Adding a Password to a User Account

For added protection, you can add a password to a user account. Through the user interface, you must log on as that user to add a password to the account. Follow these steps to add a password to a user account:

1.	Start Access, logging on as the user you want to give a password to.
2.	From the Tools menu, choose Security and then User and Group Accounts.
3.	Select the Change Logon Password tab (refer to Figure 21.2).
4.	Leave the Old Password text box blank. (The first time you add a password, you don't have a previous password. If you're changing a password, provide the old password.)
5.	Enter the new password in the New Password and Verify text boxes. (Remember, passwords are case sensitive.)
6.	Click OK ,to accept this new password and exit this dialog. Click Apply to accept this new password and remain in this dialog. Click Cancel if you don't want to save this password.

Removing a Password from a User Account

You can remove a ,password from a user account in two ways. You can have the user do it, or you can have a member of the Admins group clear the password.

To remove a password from a user account if you're logged on as that user, simply re-enter the User and Group Account dialog's Change Logon Password page, enter the old password in the Old Password text box, and leave the New Password and Verify text boxes blank. Click OK or Apply, and the password will be cleared.

If a user forgets his password, any member of the Admins group can clear the password by selecting the Users tab from the User and Group Accounts dialog, selecting the user's name from the Name combo ,box, and clicking the Clear Password button in the User section.

Setting Permissions on an Object

After your users and groups are created, you're ready to assign permissions to the objects you've created. Figure 21.8 shows the Managers group getting Read Design, Read Data, Update Data, Insert Data, and Delete Data permissions for the Categories, CustomerRelations, Customers, ErrorLog, Invoices, and MovieTitles tables.

To assign permissions to objects within the database, follow these steps:

1.	Open the database you want to change object permissions on.
2.	From the Tools menu, choose Security and then User and Group Permissions.
3.	Select the Permissions tab.
4.	Choose whether you want to see a list of Users or Groups from the List options.
5.	Choose which database object type you want to modify from the Object Type combo box.
6.	Select the user or group from User/Group Name list that you want to assign permissions for. (This is a single-select list box; you can choose only one user or group.)
7.	Select the object(s) you want to assign permissions to in the Object Name list box. (This is a multi-select list box; select as many objects as you want by Shift+clicking or Ctrl+clicking.)

8.	Select the permissions you want to assign to the selected object(s) by selecting and deselecting the check boxes in the Permissions section. (Grayed-out fields don't apply to the object type you've selected.)
9.	Click OK to accept the permissions and exit this dialog, click Apply to accept the permissions and remain in this dialog, or click Cancel to exit this dialog without making changes to any permissions.

Changes to permissions take effect immediately. The one exception is that objects that are already opened by users won't have their permissions marked until the next time they're opened.

Only the owner of the object, members of the Admins group, and users with the Administer permission for that object can assign permissions to other users and groups.

Securing Modules in the VBE

Access 2000 and VBA 6 now have you secure your modules by using the Visual Basic Editor. To do this, in Chap21.mdb, follow these steps:

1.	Open a module.
2.	Choose Chapter 21s Project Properties from the Tools menu.
3.	Click the Protection tab (see Figure 21.9). Figure 21.9. Modules can now been secured through the VBE.
4.	Click the Lock Project for Viewing check box.
5.	Supply the password, confirm it, and then click OK.

After closing and reopening Access, you are asked to supply a password when you next try to edit a module.

Setting Database Permissions

Setting database permissions is exactly the same as setting object permissions. The following steps show you how to set permissions for a database:

1.	Open the database you want to change permissions on.
2.	From the Tools menu, choose Security and then User and Group Permissions.
3.	On the Permissions page, choose whether you want to see a list of Users or Groups from the List options.
4.	Choose the Database object from the Object Type combo box.
5.	Select from the User/Group Name list the user or group that you want to assign permissions for.
6.	Select the permission you want to assign to the database by selecting and deselecting the check boxes in the Permissions section.
7.	Click OK to accept the permissions and exit this dialog. Click Apply to accept the permissions and remain in this dialog. Click Cancel to exit this dialog without changing any permissions.

Setting database permissions separately from the other objects is stressed for two reasons:

• These permissions are often overlooked and not used.

• You should take advantage of these very powerful permissions.

Recall that the three database properties you can set are Open/Run, Open Exclusive, and Administer. Each one yields significant security strengths:

• Open/Run is perhaps the most powerful property. By clearing it for the Users group and Admin user, you can effectively require users to utilize your workgroup information file to log on. Your database can be copied around the world, but if they don't have your workgroup information file or the names and PIDs of your accounts so that they can be re-created, nobody can get into the database.

• Open Exclusive is useful for database developers/administrators operating in multiuser environments. By clearing this property for all users and groups (except for your developer/administrator accounts), you can prevent users from inadvertently locking other users out of the database. Remember, however, that to compact and repair a database, you need to be able to open the data base exclusively.

• Administer grants users the ability to change the startup properties, change the database (share-level) password, and create replicas of the database.

Changing the Owner of an Object

Sometimes it's necessary to change the owner of an object. Access provides two ways of doing this through the user interface.

• The easiest way doesn't even require accessing the Security submenu off the Tools menu. If you have Modify Design permissions, you can simply cut (or copy) the object from the database window and paste it back in. After pasting, you might need to delete the old object and rename your new pasted object to the original object's name. Similarly, you can import or export an object to become its owner.

• Use the security user interface to transfer ownership. Figure 21.10 shows three table objects (RelationCodes, RentalHistory, and Stars) with ownership created by user Scott and transferred to the Managers group.

  Figure 21.10. The ownership of three tables is changed from user Scott to the Managers group.

  

To change the owner of an object, follow these steps:

1.	Open the database in which you want to change object owners.
2.	From the Tools menu, choose Security and User and Group Permissions.
3.	Select the Change Owner tab.
4.	Choose which database object type you want to modify from the Object Type combo box.

5.	Select the object(s) you want to change the owner of from the Object list box.
6.	Choose whether you want to see a list of Groups or Users from the List options.
7.	Select the appropriate user or group from the New Owner combo box.
8.	Click the Change Owner button to transfer ownership.
9.	Click OK to accept the change and exit, or Cancel just to exit this dialog.

Encrypting a Database

Earlier in this chapter, you learned about encryption and how it prevents unwanted eyes from viewing your data through a text editor by encoding how it stores information. To encrypt a database through the user interface, follow these steps:

1.	If it's not already started, start Access, but don't open a database. If Access is running, close any databases you might have open.
2.	From the Tools menu, choose Security and then Encrypt/Decrypt Database.
3.	From the Encrypt/Decrypt Database dialog (a standard file dialog), select the database you want to encrypt and click OK.
4.	At the prompt to encrypt the database as another name, choose a new filename and click Save. Access closes the dialog and proceeds with the encryption.

To decrypt a database, follow these same steps. When you select a database in step 3, Access determines whether it's encrypted and, if so, prompts you to decrypt it as a different name.

Creating a Workgroup Information File

The workgroup information file is one of the most important aspects of Access security. Creating a new workgroup information file is what makes your database secure—it's the basis for your Admins group SID and the storage of your users, groups, and passwords.

To create a new workgroup information file, you have to run a separate executable (Wrkgadm.exe) outside the Access program.

Choose Run from the Start menu and Browse to find the “MS Access Workgroup Administrator” shortcut file in your Microsoft Access folder (the default path is C:Program FilesMicrosoft OfficeOffice).

The following steps explain how to create a new workgroup information file:

1.	Start the Workgroup Administrator.
2.	Choose Create from the Workgroup Administrator dialog.
3.	Enter a Name, Organization, and Workgroup ID in the Workgroup Owner Information dialog, and then click OK.
4.	Choose a Database file name and location in the Workgroup Information File dialog and click OK.
5.	Confirm the options you've selected in the Confirm Workgroup Information dialog (see Figure 21.11) by clicking OK. Or click Change to return to step 3. Figure 21.11. Keep track of the information about this dialog in a safe place; you might need it later.
6.	The Workgroup Administrator creates your new workgroup information file and updates the Windows Registration database to point to the newly created file. Then you're prompted with a success message. Click OK.
7.	Choose Exit from the Workgroup Administrator dialog.

Creating a workgroup can't be done programmatically. Everything else done up to this point—users, groups, passwords, permissions, ownership, and encryption—can all be performed programmatically. (For examples of how to perform these functions programmatically, see the section “Managing Security Through Code” later in this chapter.)

Manually Securing a Database

Now that you've gone through many of the details on how to create new users and groups, set permissions, change ownership, and the like, step back and review the entire process of securing your database in Access. The following steps, if followed in order, will ensure that your database is secured to the maximum degree that Access allows:

1.	Create a new workgroup information file. By creating a new workgroup information file, you in turn create a secured Admins SID. To create a new workgroup information file, run the Wrkgadm.exe file in the Access folder. Click Create and provide a new Name, Company, and Workgroup ID. Save the file as something other than System.mdw. One suggestion is to keep it the same name as your applications, except with an .mdw extension—for example, MyApp.mdw.
2.	Start Access. Don't open a database.
3.	Add a password to the Admin user account. From the Tools menu, choose Security and then User and Group Accounts. Select the Change Logon Password tab. Leave the Old Password text box blank; add and verify the new password in the text boxes provided.
4.	Create a new Admin account. This new user will be the owner of the database and all its objects. To create a new Admin account, from the Tools menu choose Security and then User and Group Accounts. On the Users page, click the New button. Provide a new Name and Personal ID. (Remember, the longer the name and PID, the better the encryption.) For this example, use MyAdmin for the Name.
5.	Add the new Admin to the Admins group. To do this from the same dialog and the Users page, select your new user's name from the Name combo box. (In this example, select MyAdmin.) In the Group Membership section, highlight Admins and choose Add to move the Admins account over to the Member Of section.
6.	Log on as the new Admin. Exit and restart Access. In the Logon dialog, enter the new user's name in the Name text box. For this example, type myadmin (case isn't sensitive for logging on). Do not enter a password; currently, this user has no password.
7.	Add a password for your new Admin (optional). For added protection, add a password to your new Administrator's account. To do this, follow the directions in step 3.
8.	Remove Admin from the Admins group. This step ensures that if somebody does find out the password to the Admin user, it won't have any permissions. To do this, follow the same directions as in step 5, except make sure that you select Admin for the user, highlight Admins from the Member Of section, and choose Remove.
9.	Restart Access. This step ensures that the Admin user is removed from the Admins group, and the only user left in your Admins group is your new Admin. When prompted to log on, enter your new Admin's Name and any Password you might have created in step 7.
10.	Create a new database. This will be the secured database. For this example, name the database MyApp.mdb. To create a new database, either choose Blank Database from the Access startup screen, or choose New Database from the File menu and then choose Blank Database, followed by OK.
11.	Encrypt the database. Encryption protects you from others snooping through your database with a word processor or some other file-utility viewer. To encrypt your database, close the database, and from the Tools menu choose Security and then Encrypt/Decrypt Database.
12.	Create any application-specific users and groups (optional). Remember, security shouldn't be an afterthought; if it is, it's a lot more work for the developer. If you don't know all your users and groups at this point, you can add them later, but if you add them now, it will save time and could possibly prevent some security holes. At this point, the groups are the most important accounts. Because traditionally you'll be assigning users to groups and that's where they will be getting their permissions, groups should be created here.
13.	Set up default permissions. By establishing default permissions up front, you'll save yourself a ton of work later. If you know you don't want any groups except the Admins group (or only your new Admin account) to have access to your forms, remove all permissions from all the groups (especially the Users group) from the New Forms object. To do this, open your newly secured, encrypted database; then from the Tools menu, choose Security and then User and Group Permissions. On the Permissions page, choose Form from the Object Type combo box. With New Forms selected in the Object Name list, clear all the check boxes under the Permissions section.
14.	Create (or import) all your objects. This will make your new Admin the owner of all the objects in the database.

15.	Set database permissions. Remove the Open Exclusive permission from all your users and groups, except the users who'll need to compact or repair the database. Remove the Administer permissions from all the users and groups except your database Administrator user. Remove Open/Run permissions from any users and groups you don't want accessing your database.
16.	Run the ap_CanNotCreateDatabase() and ap_CanNotCreateTables() functions (optional, but highly recommended). ap_CanNotCreateDatabase(), discussed later in the section “Denying Users the Ability to Create Databases,” prevents users of your workgroup information file from creating new databases. ap_CanNotCreateTables(), discussed later in the section “Denying the Creation of Table and Query Objects,” prevents users from creating new table and query objects in your database.
17.	Open the VBE. Choose databasename Properties from the Tools menu. Click the Protection tab, and then click the Lock project for viewing checkbox. Lastly, add and confirm a password.

Provided that you follow all these steps in order, your database will be as secure as the Access environment can make it. Both customer and developer can feel good knowing that neither the data nor the code will be seen by unwanted eyes.

Which Permissions Should I Set?

Nobody can tell you exactly which permissions to set on your database to make it secure. Each database is different and fulfills different needs for different users. With the creation of multiple users and groups, the management of security becomes even more of a task.

The following are a few good basic guidelines:

• Never assign permissions to individual user accounts.

• Use as few groups as possible, but do use them.

• Assign permissions only to groups.

• Remove all permissions from the Users group and Admin user accounts.

• Always remove the Admin user from the Admins group.

• Create all objects (including the database) with one user who is a member of the Admins group.

• Keep as few users as possible in the Admins group. If you can stick with just one, that's great!

• Be sure to set the database properties.

For a database that has only one type of user (all users requiring the same permissions), create a group called MyUsers and then use the permissions listed in Table 21.3 as a base.

Table 21.3. Suggested Default Permissions on a Simple Application with Only One Group

Object	Permissions
Tables	No permissions for any users or groups.
Queries	No permissions for Admin or Users; Read Definition, Read Data (and Update/Insert/Delete Data where applicable), and RWOP (Read With Owner's Permission) query properties set on all queries for the MyUsers group.
Forms	No permissions for Admin or Users; Open/Run permissions for MyUsers group.
Reports	Same as Forms. Make sure that all reports have their printer property set to the default printer.
Macros	Same as Forms.
Modules	Protected as outlined earlier in the section “Securing Modules in the VBE,” where a password is specified for the VBA code in the project and the password is given only to developers and admins.
Database	Open/Run permissions only for MyUsers group and owner account. Open Exclusive only for the owner's account. Don't use Administer for any groups.

Steps to Unsecure a Database

Because databases can be secured, you might also need to know how to unsecure a database. One reason it's good to know how to unsecure a database is if you need to upgrade your version of Access.

To unsecure a database, follow these steps:

1.	Start Access, logging on as a member of the Admins group.
2.	Change any queries that have their Run Permissions property set to Owner's to User's.
3.	Give the Users group full permission on all objects in the database.
4.	Exit Access.
5.	Restart Access, logging on as Admin.
6.	Create a new database.
7.	Import all the objects from your originally secured database.

This new database is completely unsecure. Anybody with a copy of Access has full permissions to this database and all its objects.