Recipe 4.10 Secure Your Access Database
4.10.1 Problem
You've
created an Access database that you'd like to
secure. The database contains some 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. How can you accomplish this?
4.10.2 Solution
The
Microsoft Jet database engine, which Access uses to store and
retrieve its objects and data, employs a workgroup-based security
model that allows you to secure your Access databases, assigning
permissions to users and groups. Access supports two mechanisms for
securing your database: the database password feature and user-level
security. The database password feature is an all-or-nothing
proposition—users who know the password aren't
restricted in any way once they're in the database.
If you want to assign varying permissions to different users,
you'll need user-level security. User-level security
is fairly complex—it doesn't work if you leave
out a step. It consists of creating a new workgroup file (which holds
user, group, and password information) and then using this new
workgroup file to secure the database. There is a Security Wizard
built into Access that will help you secure your database, but you
can also manually perform the process, which will help you understand
what's happening.
User-level security relies on a special
database, called a workgroup file, to store
users, the groups to which they belong, and their passwords. When you
install Access, you are automatically hooked up to a default
workgroup file called System.mdw. To secure your
database, you will need to create your own unique workgroup file.
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.
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 only the Users group. Every
user is automatically added to the Users group, which is similar to
Everyone in Windows. You can't delete any of the
built-in users or groups (Admin, Admins, and Users), but you can move
users in and out of various groups. Access requires that there always
be one member of the Admins group (that would be you). Later
you'll create additional groups, assigning
permissions to the groups for various database objects. Users then
inherit permissions from their group membership.
You'll probably want to remove all permissions from
the Users group, since permissions granted to Users are granted to
all. At this point you'll
want to secure the database. You can either run the Security Wizard
or manually secure it. If you manually secure it,
you'll create a new database (this is how you
transfer ownership of the database) and then import all of the
objects. Next, remove all permissions for the Users group and the
Admin user. The Admins group has full permissions by
default—only the Admins group can work with users and groups
and has irrevocable administrative permissions on the database. If
you use the Security Wizard, it will also remove all permissions from
the Admin user and the Users group and encrypt the new database (you
can do this manually if you choose). You need to create your own custom
groups and assign the desired level of permissions to these groups.
Every user is required to be a member of the Users group (otherwise,
a user would not be able to start Access), so grant to Users only
those permissions that you want everyone to have. Members of the
Admins group have irrevocable power to administer database objects,
so make sure to limit membership in the Admins group to only those
users who are administrators. Create your own users and assign them to
the groups that reflect the level of permissions you want them to
have. Do not assign permissions directly to users, because that is
extremely difficult to administer; users inherit permissions from the
groups of which they are members, and keeping track of the
permissions assigned to a group is much easier than keeping track of
the separate permissions of individuals. If a user is a member of
multiple groups, that user will have all the permissions granted to
any of those groups plus any permissions assigned specifically to the
user (this is known as the
"least-restrictive" rule). There is
no way to deny permissions to a user if that user is a member of a
group that has been granted those permissions. If you need to create
specific permissions for only a single user, create a group for that
user and assign the permissions to the group; then add the user to
the group. The reason for this becomes clear when you consider that
the user may leave unexpectedly, and you may have to set up
permissions for the replacement on short notice. Test security by logging on as users with varying levels of
permissions. Try to do things that a user at that level
shouldn't be able to do. The only way
you'll be able to see if your database security is
working is to bang on it and try to break it.
4.10.3 Discussion
The
Microsoft Jet database engine, which Access uses to store and
retrieve its objects and data, employs a workgroup-based security
model. Every time the Jet database engine runs, it looks for a
workgroup file, which holds information about the users and groups of
users who can open databases during that session. The default
workgroup file, System.mdw, is identical across
all installations of Access. That's why
it's important not to skip the first step of
creating a new workgroup file.
The workgroup file contains the names
and security IDs of all the groups and users in that workgroup,
including passwords. Each workgroup file contains built-in groups
(Admins and Users) and a generic user account (Admin). You
can't delete any of the built-in accounts, but you
can add your own group and user accounts.
The built-in accounts each have their own characteristics and
properties:
The built-in Admins group is always present, and its users have
administration rights that cannot be revoked. You can remove rights
from the Admins group through the menus or through code, but any
member of Admins can assign them right back. Access ensures that
there is always at least one member in the Admins group to administer
the database. The Admins group is the only built-in account that has
any special properties. The default user account, Admin, is a member of the Admins group in
an unsecured database and is the only user account present in the
default System.mdw workgroup file. It has no
special properties of its own; all of its power is inherited through
membership in the Admins group. The Users group is a generic group to which all users belong. You can
create users in code and not add them to the Users group, but they
won't be able to start Access—internal tables
and system objects are mapped to the permissions of the Users group.
Other than the fact that all users must belong to the Users group, it
has no special properties.
Permissions to various Access
objects can be assigned directly to users (explicit permissions) or
to groups. Users inherit permissions from the groups to which they
belong (implicit permissions). It's always a good
idea from an administrative point of view to assign permissions only
to groups, and not to users, which could become endlessly
complicated.
Access employs the least-restrictive rule:
users have the sum total of their explicit and implicit permissions.
In other words, if a user belongs to a group that has full
permissions and you make that user a member of a group that has
restricted permissions, the user will still have full permissions
because he is a member of the unrestricted group.
User and group information, including passwords, is saved in the
workgroup file, or System.mda/mdw, which
validates user logons at startup. Permissions to individual objects
are saved in the database itself. You can give the groups and users
within a workgroup various levels of permission to view, modify,
create, and delete the objects and data in a database. For example,
the users of a particular group might be permitted to read only
certain tables in a database and not others, or you could permit a
group to use certain forms but not to modify the design of those
forms.
Most Access database applications consist of a frontend with linked
tables against a backend database. You need to secure both the
frontend and the backend using the same workgroup file.
Access user-level security works best when securing data—if you
want to secure your code, the best solution is to compile your
application as an MDE. This prevents anyone from viewing or altering
the design of forms, reports, or module code. It also prevents users
from creating new Access objects, but it has no effect on data
objects (tables and queries). You'll need to save a
backup copy of the original .mdb file if you
want to make alterations later—there's no way
to decompile an MDE to recover the source code and source objects.
Also
bear in mind that security in an Access database is mainly good for
deterrence only. In any situation in which the physical files are
exposed, it is impossible to guard against determined hackers. An
additional weakness is that the network share where the Access
.mdb and .mdw files are
located also needs to have read, write, and delete permissions, which
means you can't prevent users from deleting or
copying the .mdb and .mdw
files. The only alternative is to create an n-tier application where
the middle-tier objects alone have access to the physical files.
However, this means that you need to write the application
"unbound," since the users will no
longer be directly connected to the database. When you get to that
point, you'll probably be considering SQL Server or
another database platform that is capable of scaling to support more
users and larger volumes of data.
|
