Recipe 10.1 Properly Secure Your Database
10.1.1 Problem
The database you've
developed contains sensitive data to which you wish to limit access.
You'd like to be able to create different classes of
users so that some users have no access to this data, others can read
the data but can't change it, and still others can
modify the data. At the same time, you don't want to
secure every object in the database this way; you'd
like to apply security only to selected objects. Is this possible
with Access?
10.1.2 Solution
Access supports two forms of security:
workgroup-based security and database-password security. If you use
the simpler database-password security system, you can assign only a
single password to the entire database, which is inadequate for your
purposes. Fortunately, your needs can be met by using the more
sophisticated workgroup-based security system. However, securing a
database this way can be tricky. This solution guides you through the
process, starting with a completely unsecured database and finishing
with a well-secured database that should meet your needs.Before you can properly secure your database, you must have a
security plan. Consider who will be using the database and what
security permissions those users should have for each database
object. With a plan in place, you can go about securing your
database.
10.1.2.1 Make a security plan
The
first step in creating a security plan is to make a list of the
people who will be using the database. Write out the names of the
users and put them into distinct groups. A user can be a member of
more than one group, but you need to assign each user a unique name.
Users will have to type in their usernames each time they log into
Access, so you may wish to keep the names as short as possible (but
still unique). In a small workgroup, you may be able to use an
individual's first name; in larger settings, you may
need to use the first name plus the first letter of the last name or
some similar scheme to ensure uniqueness. For example, if you were
charged with designing a secured database for the solution company,
you might come up with the users and groups in Table 10-1.
Group | Members |
---|---|
Employees | Tom, Pat, Bill |
Programmers | Paul, Peter |
Managers | Joan, Thomas, Paul |
Admins | Paul |
programmer. Second, two individuals in this company are named Tom
but, to ensure uniqueness, we've assigned one of the
Toms the username Thomas. Third, we recommend using the following
convention: make usernames singular and group names plural. Finally,
you need to identify members of a special built-in group called
Admins. This group of users will have full access to all objects and
will also be able to administer the security system.Once you have come up with a
plan of users and groups of users, you need to inventory your
database objects and determine which groups of users can do what with
which objects. While you can assign each user a
separate set of permissions, it's better to assign
permissions to groups of users; this makes adding or subtracting
users later much easier. An object inventory for the solution company
database (10-01UNS.MDB ) is shown in Table 10-2.
Object | Group | Access level |
---|---|---|
tblCustomer | Employees | Read, write access to data only |
Programmers | Read, write access to data and design | |
Managers | Read, write access to data only | |
Admins | Full access | |
tblEmployee | Employees | No access |
Programmers | Read, write access to data and design | |
Managers | Read, write access to data only | |
Admins | Full access | |
frmCustomer | Employees | Run access |
Programmers | Run, read, write access to design | |
Managers | Run, read, write access to design | |
Admins | Full access | |
frmEmployee | Employees | No access |
Programmers | Run, read, write access to design | |
Managers | Run, read, write access to design | |
Admins | Full access |
10.1.2.2 Secure your database
Note
that Access ships with a Security Wizard that will help you secure
your database. With a plan in hand, you can now begin to secure your
database, following these steps:
- Choose Tools Security
User-Level Security Wizard from the menu. The first
dialog prompts you to create a new workgroup information file, and
the second dialog (shown in Figure 10-1) prompts for
the workgroup file information. The strings you enter here for the
Workgroup ID (WID), name, and company will be encrypted to form a
unique identifier. The default workgroup file,
system.mdw , is the same across all installations
of Access, and is thus not secure. At the bottom of the dialog you
can choose either to make this workgroup file the default or to
create a shortcut to open the secured database. If you choose the
first option, the workgroup file will be used with all databases. For
most environments, the second option is a better choice. Click Next
to continue the wizard.
Figure 10-1. Creating a new workgroup file
- You will see a tabbed dialog for selecting the database objects you
want to secure. You'll probably want to secure all
the objects in your database. Click the All Objects tab, then click
the Select All button (see Figure 10-2). Click Next
to continue.
Figure 10-2. Selecting the objects to secure
- The next dialog (see Figure 10-3) can create default
groups for you. If you click on a group, you can see the permissions
that will be granted to it. However, for this example,
you'll create your own custom groups after the
wizard has completed. Don't select any of these
items, and click Next to continue.
Figure 10-3. Creating default groups
- The next dialog allows you to
grant the Users group selected permissions on some objects. However,
you probably don't want to do that, since
permissions granted to the Users group are granted to
everyoneall authorized users must be members of the Users
group. The best policy here is to grant permissions only to your own
custom groups, so don't select that option. Click
Next to continue. - The next dialog allows you to create
additional administrators and set a password on the administrator
account that will be automatically created (see Figure 10-4). This name of this account comes from your
Windows login. So if you are logged in as Paul, as shown in Figure 10-4), then this account will be named Paul. Any
additional administrators you create here will be added to the Admins
built-in group, giving them irrevocable administrative powers in your
secured database. You will therefore want to limit the number of
administrators, as they have unlimited power. Only administrators can
manage passwords and create and delete users and groups. Set a
password for the administrator account and click the Next button.
Figure 10-4. Setting a password for the Administrator account
- The next dialog allows you to add users to
any groups you have created. If you have not created any additional
groups or administrative users, only the administrator account (in
Figure 10-4, this account is named Paul) will be
displayed, as shown in Figure 10-5. If you attempt
to remove the administrator account from the Admins group, you will
receive an error message unless you've created an
additional administrator. Access requires there to be at least one
member in the Admins group and will not let you delete the last user.
Click Next to continue.
Figure 10-5. Adding users to groups
- The final dialog prompts you for the name
and location of the backup file (see Figure 10-6).
The default value is the name of the MDB with a
.bak extension. To revert to the unsecured
version of your database, just delete the secured MDB after the
wizard has completed and rename the extension of the backup file to
MDB. Click the Finish button to complete the wizard.
Figure 10-6. Creating a backup file of the secured database
The one-step Security Wizard report is
then displayed. This report lists all the security options
you've chosen, along with the settings for users,
groups, and Personal IDs (PIDs). You should save this and lock it
away in a safe place. Should you ever need to recreate your workgroup
file, you can use the same settings. Be careful: if your workgroup
file is irretrievably lost and you can't restore it
from a backup or recreate it, you could be locked out of your
database forever. The wizard will save the report in snapshot format.The
wizard then notifies you that the database has been encoded (prior
versions of Access refer to this as encrypted) and that you must log
onto the secured database using the new workgroup file (see Figure 10-7). This means that the database
can't be read by a text editor and
can't be compressed by file-compression utilities.
If being able to compress the database means more to you than the
remote chance that someone will use a text editor to read strings out
of the .MDB file, you can decode (decrypt) the
database using Tools Security Encode/Decode
Database....
Figure 10-7. The wizard notifies you that the database has been encoded
10.1.2.3 Work with the secured database
Once the Security Wizard has finished, you
need to shut down Access and restart. The Security Wizard creates a
shortcut that automatically connects you to the newly secured
database. Figure 10-8 shows the property settings of
the shortcut. Note that the Target includes the
/wrkgrp switch that points to the new workgroup
file.
Figure 10-8. The property settings of the database shortcut
Follow these steps to manually create your users and groups according
to the security model you've planned:
- Double-click the shortcut on your desktop
to open the newly secured database. Login as the administrator
account you created when running the wizard (the one with the same
name as your Windows login). Select Tools Security
User and Group Accounts from the menu. This will load the
User and Group dialog. Create the groups shown in Table 10-1 by clicking the Groups tab and then the New
button. When you create a new group account, you will be asked to
enter a Name and a Personal ID (PID). For each group account, enter
the name of the group account under Name and a case-sensitive
alphanumeric string between 4 and 20 characters long under Personal
ID (see Figure 10-9).
Figure 10-9. Creating custom groups and users
- Create the users. Click the Users tab and
the New button to create each new user. The PID that you enter is not
the passwordyou'll need to log on as each
user to set an initial password for that user. Add each user to his
or her groups, as listed in Table 10-1. By default,
new users will be members of the built-in Users group; do not remove
users from this group. - Assign
permissions to the database objects. You will now take the object
inventory in Table 10-2 and add permissions to the
groups of your security plan. Select Tools Security
User and Group Permissions. Select the Permissions tab and
assign permissions to the groups according to your security plan.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.
10.1.3 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.