Microsoft Access for Beginners - Additional Information
Information Security in Microsoft Access
You can read the entire Microsoft Access for Beginners series, completely reviewed and updated for Access 2010, in the new Kindle edition now available from Amazon.com!
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.
It's important to remember that the native Microsoft Access database format is a desktop database cannot be completely secure. If someone can get to the database file and wants the information badly enough, they can get it. When storing sensitive information that needs to be protected, it's best to upgrade to a network database system such as Microsoft SQL Server. Depending on the application and the environment in which you're working, however, Access might provide for your needs.
Microsoft Access offers two methods for securing a database, depending on the version you’re using.
User-Level Security
User-level security, also called workgroup security, was available in Access 2003 and earlier versions. This system enabled the database administrator to require a login and password to open the database and to assign rights to objects within the database to specific users and groups.
For all databases, secured or not, Access uses a workgroup file (SYSTEM.MDW) that stores user names, passwords and security group membership information for all users. The database file itself stores information on the permissions required for each object. Whenever you open an Access database, you are actually logging in as the Admin user using the default workgroup file, even though you don’t see a login screen, and you have complete access to everything in the database.
Using workgroup security, a custom workgroup file is created that will be referenced whenever starting the secured database. A new version of the database itself created and ownership of all objects is transferred to a new administrative user that you create in the new workgroup file. After this, the database can’t even be opened without referencing the custom workgroup file and supplying a correct username and password. Custom user names and passwords can be added to the workgroup file and these new users can be assigned rights to different areas of the database or added to user groups from which they will inherit rights.
When properly implemented, user-level security was the best native security Access had to offer although it wasn’t a total solution. Utilities are available that will defeat the encryption placed on the workgroup file and reveal all the user names and passwords.
Unfortunately, user-level security is no longer available for databases created in Access 2007 and later. Security settings applied to MDB files created in previous versions are still supported but the security cannot be applied to ACCDB files. Users wishing to secure their data from intrusion are now encouraged to move it to network solutions such as Sharepoint or SQL Server.
Access offers two types of security, the best being workgroup security.
Password Encryption
The database password, available from the Database Tools section of the ribbon in Access 2007 and the Tools - Security menu in earlier versions is another 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. For Access 2003 and earlier, 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.
In Access 2007 and 2010, Microsoft has enhanced the strength of password security and continues to offer it as a solution. The nature of encryption and the Internet being what they are, however, I anticipate that it won’t be long before new utilities are available to crack the enhanced password.
Additional Security Measures
First I would state once again that, as a desktop database, Access cannot be fully secured. I do not recommend it for highly sensitive data stored in a network or multiple user environment. Even properly implemented user-level security was not an end-all solution to securing your data. If poorly setup or administered it was as worthless as the database password. Worse, it could 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. Except where specified, many of these recommendations are still valid for Access 2007 and 2010. Use the search terms provided to find more information in the Access help files or online.
- In any version of Access, sensitive information should be split off into separate tables so that it is not mixed with data available to the general user. Most databases of any sophistication should also 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 also 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. It also makes it easier to implement design updates. The workgroup files that you will create when you implement security should also be placed on local machines. (Search on Database Splitter and Linked Table Manager)
- 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 used with Access 2003 and earlier. The workgroup file designed for one group should not contain the user names and passwords from another group.
- Distribute the front-end as an MDE or ACCDE file. This will remove any editable code from the application and prevent changes to the objects. This is the recommended method for securing code. (Search: ACCDE Files)
- 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 on startup options and AllowBypassKey)
- For the most sensitive applications, the back-end should go into a hidden directory on the network. 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 workgroup files in this directory as the location of the workgroup file is included in the shortcut you create to open a secured database.
- 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 this, see my online article at http://www.drewslair.com/desk/access/general/shut_it_down.shtml.
- If you are still using Access 2003 or earlier and plan to implement
user-level security, then check out the Access Security FAQ which can be found
at
http://support.microsoft.com/kb/207793 or by going to
http://support.microsoft.com and searching on 'Access Security FAQ'.
Download this document and read it carefully! It contains all the steps to implementing workgroup security and a lot of other important information. The information applies through Access 2003. Also, learn how to create RWOP (Run With Owner's Permission) queries. These queries allow you to grant the users access to specific data from tables without giving them actual permissions on the tables. In general, all data access should be done through these queries because you have finer control over what a user can do with a query than a table. (Search on RunPermissions)
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.
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.
Read the entire Microsoft Access for Beginners series offline ...
Microsoft Access for Beginners
Expanded, updated for Office Access 2010 and available from Amazon.com.
