Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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
propositionusers 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 complexit 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:

1. 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 fileif 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.

Figure 4-19. The Workgroup Administrator dialog

2. 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.

3. 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.

4. 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 passwordit 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.

5. 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 dialogyou haven't set one for
   this account yet.

6. 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.

7. 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
   defaultonly 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).

8. 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.

9. 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.

10. 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 Accessinternal 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 dataif 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 laterthere'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.