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.