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 |
There are several things to note in Table 10-1. First, Paul
is both a manager and a 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 consider
permissions for only 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. Click the All Objects tab, then click the Select
All button (see Figure 10-2). You'll probably want to secure all the objects
in your database. 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 everyone梐ll
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). Any 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 will be displayed, as shown in Figure
10-5. If you attempt to remove the Administrator user 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. 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
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 decrypt the database.
Figure 10-7. The wizard notifies you that the
database has been encrypted
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 and choose 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 password梱ou'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.
Log onto each new user account and select Tools
Security
User and Group Accounts. Choose the Change Logon Password tab and enter and
confirm a new, non-blank password for each new account.
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.
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.
No comments:
Post a Comment