Microsoft Access for Beginners
- Additional Information
Information Security in Microsoft Access
This information applies to Access 2003 and previous versions. Access 2007
introduces significant changes not covered by this article. For information
on security in Access 2007, read the MSDN article "Security Considerations and Guidance for Access 2007".
One of the important considerations when designing a new database is whether the
information that you're storing needs to be secured. Beyond keeping nosy people
out of your data, you may need to assign different levels of access for different
people depending on their needs.
Access offers two methods for securing a database:
-
The database password, available from the Tools - Security
menu is one method for keeping the most casual browsers out of the database.
Depending on the nature of the data and the environment in which it is kept, this
might be all that you need, but probably not. The database password doesn't
offer any real security. Free utilities are available on the Internet that
will crack this password in seconds. It won't even keep the most amateur hacker
out and it is never suitable for sensitive data.
- Access also offers user-level security, also called workgroup security, which enables you to require a login and
password to open the database. This method is based around a workgroup file
which stores the user names, passwords and security group membership information
for all users. Using workgroup security, you can assign rights to specific
parts of the database to individuals or groups of people. While this form
of security is better than the database password, it is not a total solution.
Inexpensive utilities are also available that will defeat the encryption placed
on the workgroup file.
Access offers two types of security, the best being workgroup security.
It's important to remember that Microsoft Access is a desktop database system and
cannot be completely secure. For an application that requires high security,
it's best to upgrade to a network database system such as SQL Server. Again however,
depending on the application and the environment in which you're working, Access
may provide for your needs.
First, remember that even the use of workgroup security is not an end-all solution
to securing your data. User-level security that is poorly setup or administered
is as worthless as the database password. Worse, it can endanger your data
by locking you out of the database.
If you are going to secure your data using Microsoft Access, here are a few things
to keep in mind that will help you to achieve the maximum amount of security possible.
Use the search terms provided to find more information in the Access help files.
- Microsoft offers an Access Security FAQ which can be found at
http://support.microsoft.com/support/access/content/secfaq.asp or
by going to
http://support.microsoft.com and searching on 'Access Security FAQ'.
Download it and read it carefully! It contains all the steps to implementing
workgroup security and a lot of other important information. I will not secure
a database without this document at my side. Although it was specifically
written for Access 97 and then updated for 2000, the information still applies through
Access 2003.
- As mentioned in Part I,
sensitive information should be split off into separate tables so that it is not
mixed with information available to the general user.
- Most databases of any sophistication should be split into two parts; the back-end
which contains all of the data tables and the front-end which contains the forms,
queries and all other objects. This is automatically true for any database
which supports multiple users. Each user or group of users should be using a different
copy of the front-end, preferably on the local machine. Aside from separating
the interface (forms, reports, etc.) from the data, this raises performance as users
are accessing different copies of the program instead of the same one. The
workgroup files that you will create when you implement security should also be
placed on local machines. (Search: Database Splitter and Linked Table Manager)
- For the most sensitive applications, the back-end should go into a hidden directory.
The idea is that the users should not be able to browse to the directory and the
name should not be made public. Speak with your network administrator about
setting up a separate directory for the database as you don't want someone guessing
the directory name. DO NOT place the workgroup file in this directory.
- Use the startup options in Access to deactivate the special keys, including F11,
and hide the database window. You can also disable the shift bypass whereby
a user will bypass the startup settings by holding down the shift
key while starting Access. The idea is to ensure that users see only the menus
and toolbars that you have provided for them. (Search: Startup options and AllowBypassKey)
- After you have thoroughly studied the security FAQ mentioned earlier, learn how
to create RWOP (Run With Owner's Permission) queries.
In general, all data access should be done through these and the users should not
have direct access to the tables themselves. This is because you have finer
control over what a user can do with a query than a table. (Search: RunPermissions)
- You can also design separate front-end interfaces for groups requiring different
levels of security. This is why sensitive information is placed in different
tables. Groups requiring minimal access can receive specially-designed front-ends
that don't contain links to the sensitive tables in the back-end. This also
applies to the workgroup files. The workgroup file designed for one group
should not contain the user profiles from another group.
- Distribute the front-end as an MDE. This will remove any editable code from
the application and prevent changes to the objects. In later versions of Access,
the ability to assign rights under workgroup security to code modules has been removed
and this is the recommended method for securing code. (Search: MDE Files)
- Remember that your database security is only as strong as its weakest link.
This weak link may be the front-end that enables full access and is stored on the
machine of a manager who likes to leave the database open all the time, even overnight
or while going off to lunch and leaving his or her door wide open to an unsupervised
area. While management compliance cannot be guaranteed, it is possible to
compensate somewhat by programming the database to shut down after a specified period
of inactivity.
For more information on any of the above, search the Access help files using the
search terms provided or go to
http://support.microsoft.com .
These are resources that you should be firmly in the habit of using. Another
invaluable resource is the archive of internet newsgroup postings at http://groups.google.com.
The newsgroup comp.databases.ms-access
has been of particular help over the years as I've learned about Access.
Sounds like a lot of work doesn't it? It certainly is. This is the security
of your data, after all. In the end, it's worth it, especially if you're responsible
for securing the personal information of others or financial information.
In addition, becoming familiar with the concepts I've mentioned above will help
you to design better applications.
Above all, remember that even with the measures outlined above, Access security
is not completely secure! Always take into account the needs and risks of
the situation before using Access to secure your data or recommending it to others.
Return to Series Index