4.10 Secure Your Access Database
4.10.1 Problem
Youve 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梪sers 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梚t 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, 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梚f 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.
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梚t 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梱ou 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梠nly 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梚nternal 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梚f
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梩here'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.
No comments:
Post a Comment