MendoClick™
Article

Overview

If confidential personal or financial information resides on a database, liability issues may arise if the wrong person gains access. Even if such is not the case, damage to the data could cost substantial time and money for reconstruction. Any delay in the ability to gather new data and to produce output, such as accurate and timely reports, statements, letters, etc., may result in lost revenue.

Security, therefore, has two main components: protection from data theft, and protection from data loss. Both of these involve stopping unauthorized users and malware, such as spyware, viruses, etc., from accessing the database. Protection from data loss also includes backing up the data, and the backup copies need to be secured from damage and theft also.

Top

General Protection from Unauthorized Users

Top

Access Security Overview

Access security is mainly good for keeping people who should be allowed some level of data access out of parts of the database where they should not be. Even if all users should be able to update all the data, most will have no need or reason to view or change the programming code or the underlying design of tables, queries, forms, reports or macros. When Access security is properly set up, it will keep all but the most computer-savvy and determined out of areas where they do not belong. Even if well-intentioned, a non-technical user in the wrong place can accidentally change code or properties that will cause problems until repaired.

Top

Protect your Logon Identity

How to Change your Password in Access

To change your password in Access, open the database as usual, providing your logon user name and the old password when prompted. Then do the following (unless your developer has provided you with a custom menu or toolbar, in which case seek instruction from that source):

1. From the Access menu, choose: Tools-> Security-> User and Group Accounts
2. Click the “Change Logon Password” tab.
3. Type your old password in the first box, and your new password both in the “New Password” and “Verify” boxes.
4. Click OK.

Top

User Logon, Security and Data Integrity

The weakest link in security is often the users themselves. Obviously, having your password clearly visible on a sticky note at the side of your monitor makes your database less secure (though a remote hacker won’t be able to read it). Also not recommended is a password that can be easily guessed by others. Don’t pick an obvious password simply because you are afraid of forgetting it otherwise. If necessary, your security administrator can override the old one so that you will be able to get back into the database.

The best passwords are not in the dictionary, as password-cracking software can test every dictionary word in a reasonable amount of time, given the speed of today’s computers. If you choose a word or word combination, scatter at least a couple of stray numbers or extra letters at random between letters of the word.

When a user needs to share access to a database, s/he may opt to share a logon name and password. This is not the best solution. Ideally, the database security administrator should give each person his or her own logon and password. Nevertheless, if sharing a user security account is done for expediency, a new logon account should be obtained for the new user as soon as possible, and the old user should change his or her password immediately thereafter.

User logon names are logged in many systems, both for tracking sources of security breaches and also for troubleshooting system bugs and data problems. This is one aspect in safeguarding the overall integrity of the data. If there are data errors, knowing who last changed it may be indispensable in solving the problem. Many systems record logon names as part of the data record itself, for this very reason. My point here is that you want your logon name attached only to the records that you yourself actually created or changed. Change your password occasionally to help ensure this.

Most systems (except for those that require the utmost in security) do not go so far as to keep a history of everyone who changed – and which fields were changed within – each record. Simply tracking who last updated each record is usually enough for troubleshooting later. This means that nobody can point at you with absolute certainty as having been responsible for any given data problem, so if you are prone to paranoia, relax. But if you make the same mistake over and over, someone will probably be talking to you about it, because a significant percentage of the problematic data will have your user name on it. However, it may well be the case that the system itself needs fixing or enhancing, or at the very least an entry screen has confusing text that needs correcting. Whether a training issue, design issue, or coding bug is the cause, knowing which user logons are associated with a given data problem may save a maintenance programmer hours of work.

Management is well-advised to notify employees when their logon names are being recorded. Although the intent may be similar to that in having typists put their initials at the bottom of a letter, the computer system may be surreptitious about it, and finding out about it after the fact may offend some people as a matter of principle.

Top

The Access Workgroup Security File

All Access databases use a workgroup security file. The workgroup security file stores the logon user names and passwords. When the database opens without prompting for a user name or password, the default user, "Admin," is used, and no password has been assigned. The trigger for Access to request user name and password is the presence of a password for the "Admin" user, so the first step in securing a database is giving "Admin" a password.

By default, all new Access databases are joined to the workgroup file named "System.mdw." This file can be stored anywhere on the computer, though the Access installation program does suggest a default location, such as:

C:\Documents and Settings\User Name\Application Data\Microsoft\Access

If Windows is hiding "hidden files and folders," you will not be able to navigate to it via My Computer, as the "Application Data" folder is hidden. However, a Windows search will find it for you (Start button, Search). If Access is currently joined to "System.mdw," you can also find out where it resides via these Access menu options: Tools-> Security-> Workgroup Administrator. (You can change the Windows setting to show hidden folders in My Computer via Tools-> Folder Option, View tab, and selecting "Show hidden files and folders.")

It is a good idea to make a copy of the "System.mdw" file in case anything ever happens to the original. The "System.mdw" file should not be modified. Instead, add user logons and passwords to a brand new workgroup security file.

As this article is aimed at Access users, it is assumed that the developer of your database will create the workgroup security file for you. Nevertheless, we will discuss how to join a database to a different workgroup security file, as some people use multiple Access databases and in some cases these databases do not all use the same workgroup file.

Top

Object Permissions and User Logon

The database itself knows nothing about which workgroup security file should be used with it. This means that there is no secret place within the database where a hacker can discover which security file to use. Instead, the database contains information about object permissions by user and user group names. For example, all users in the "Marketing" user group may have read-only permission for certain tables, while the "DataEntry" group has permission to add, edit and delete data in the same tables, and only the "Developer" group can change table designs.

To use the database, you must have the workgroup file joined, not to the database itself, but to your Access session. Access will prompt you for user name and password, and if they match what is in the workgroup security file, Access will pass the user name and user group name to the database, which will then decide if you have permission even to open it. Once you get in, the database will grant you access to whatever is allowed according to its object permissions settings.

Top

Access Sessions and the Workgroup File

Since the database cannot say which workgroup security file to use, Access remembers it for you. However, Access does not keep track of which workgroup file goes with which database. Instead, it simply defaults to the same workgroup file you used the last time, as soon as you open any database. If that does not happen to be the correct workgroup file, the user name and password you enter may be rejected by the workgroup file even though it is the correct user name for the database, or, if you give a user name and password that satisfies the workgroup security file, once the database starts opening it may immediately close again, if that user name is not registered in the database itself as having permission to open it.

If all databases use the same workgroup file - or some databases aren't secured – you won't have to worry about changing to a different workgroup file. Otherwise, you have four choices:

1. Open Access, join to the relevant workgroup file, and then open the database.
2. Use command-line instructions to open Access and join it to the correct security file.
3. Use a Windows shortcut, which contain the same instructions you could have entered on the command line.
4. Have your developer create a custom script to open your database with the correct workgroup file joined.

Worth noting is that the last three options join the workgroup file for only that single Access session.

The security hole for the first option is that, if a person can run Access on your computer, s/he may be able to see which databases you had open most recently via the File menu and also which workgroup security file you used last. It is therefore a good idea to stop using the "recently used file list" by changing the Access default setting as follows:

1. On the Access menu, choose Tools-> Options, General tab.
2. Uncheck "Recently used file list" and click OK.

The problems with using the command line to open Access are that Windows by default "remembers" the last few things you typed into the command line, so someone could see what you had typed there, and besides, entering the information into the command line in the first place is a lot of typing (or you have to store it somewhere else for cut-and-paste). Nevertheless, it is worth knowing how to do this so that you can test it before copying it into a shortcut. The shortcut option is easy to use once it is set up, but again, all someone has to do is open the shortcut properties to see the exact location of both the database and its workgroup file.

Using a script to open the database allows the location of both the database and its security file to be much harder to discover, while still allowing the legitimate user easier access. Ask your programmer about writing such a script, or refer to Garry Robinson's book, Real World Microsoft Access Database Protection and Security. The first three options are covered below.

One point before we move on to how to switch to another workgroup file: A workgroup security file may be joined to your Access session, but just because you logged on does not necessarily mean your database has been secured properly. In fact, your database may not be secured at all, because if no object permissions have been set, it doesn't matter what user you are logged on as; the database will let you do anything you want.

One good way to test your database is to join Access back to the default workgroup file, "System.mdw," and then open the database. Unless someone has altered "System.mdw," Access should not ask you for any logon user name or password, and the database will see your user name as "Admin." (If you find that "System.mdw" has been altered, get another copy from a reputable source, because you should always have the original as Microsoft provided it.)

In a properly secured database, the "Admin" user should have either no permissions at all or a limited set of permissions. The latter is the case when the security approach is to let a group of users into the database without requiring a logon, while still restricting their access. Test the database to see what it will allow "Admin" to do.

Garry Robinson, in the aforementioned book, makes a compelling case for allowing the "Admin" user just enough permissions to do all the work the end users need to do, as long as the database does not need differing levels of restriction for different users. The main advantage of this is that the workgroup security file that has the full administrative permissions can then be left off of the end users' system entirely. Without that file, Password-cracking software will not find any passwords to crack, because the default "System.mdw" workgroup file being used in production data entry has no passwords.

The key here is that all databases recognize the "Admin" user, but object permissions can be changed on the database itself to limit what the "Admin" user can do. The developer's workgroup security file has a user group that has been granted full design permissions for all database objects, but that user group is not found in the "System.mdw" file. In order to modify designs for tables, forms, etc., one must be logged on as a user belonging to the group with such privileges. Since that user is only found in the developer's workgroup file, that security file must be joined to the Access session before such modifications can happen. Most end users won't have access to that security file, so the system is well protected against such design changes.

A variation on this would be to give the custom workgroup file only to those few users who need extra permissions, but keep that file off of the network and the desktops of everyone else.

By limiting who has access to the custom security file, you are limiting who might succeed with password-hacking software. Such software depends upon hacking into the workgroup file, but if the would-be hacker cannot get a copy of this, s/he will have no success. More information for this approach is in Garry Robinson's book.

Top

Joining to another Workgroup Security File from within Access

1. From the Access menu: Tools-> Security-> Workgroup Administrator
2. In the "Workgroup Administrator" dialog box, click the Join button.
3. In the next dialog box, click the Browse button and select the workgroup file you want to use.
4. Click OK.

Top

Opening a Database from the Command Line

You reach the command line via the Windows Start button-> Run. The syntax for running Access from there is simply the complete path and file name for the Access program itself, all within double quotes. The default path and file name in Office 2003 is as follows, but your path may be different:

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"

To open a particular database at the same time, you start with the Access program file as above, add a space, and then add the full path and file name of the access database, also in quotes, all on a single line (but your path and file name will likely be different):

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "C:\Documents and Settings\Margaret\My Documents\MendoClick\Clients\MyDatabase.mdb"

To open the database with a particular workgroup security file attached, add the /wrkgrp switch followed by the full path and file name to the workgroup file:

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "C:\Documents and Settings\Margaret\My Documents\MendoClick\Clients \MyDatabase.mdb" /wrkgrp "C:\Documents and Settings\Margaret \My Documents\MendoClick\Clients\MyWorkgroup.mdw"

Top

Opening a Database from a Shortcut

Opening a database from a shortcut is the same as doing it from a command line, except that the shortcut stores the command string so that you do not have to retype it each time.

To create the shortcut:

1. Click File-> New-> Shortcut from within the subfolder in My Computer where you want the shortcut to reside (note this may be the second "New" in the file menu, depending upon whether or not a file was selected before clicking the File menu, and it is the "New" with an arrow to the right of it), or right-click a blank area of the Windows desktop and pick New-> Shortcut to create one on the desktop.
2. The "Create Shortcut" dialog box should appear. Enter the full command string just as in the instructions in the previous section, and click Next.
3. Type a name for your shortcut and click Finish.

You can see – and change – the command line string in the shortcut after it has been created by right-clicking the shortcut, clicking Properties, and then the Shortcut tab. It will be in the "Target" box.

The shortcut approach is preferred by many developers when there are multiple workgroup files, because it simplifies opening the database with the correct security file. The drawback is that the workgroup file name is saved next to the database file name, which helps the would-be hacker find the right workgroup file. As already mentioned above, a more secure solution is to use script to open the database and join the security file, as covered in Garry Robinson's book.

Top

Backup

As we are undoubtedly concerned about possible damage to the database file, an important line of defense is backup. Microsoft Access 2003 includes a backup option from within the program, reached via File-> Back Up Database. Otherwise, back up the database by simply copying the database file when the database is closed. Access does not have built-in support for transaction logging (a form of real-time backup in which each change is written to a log file, which can later be used to restore the database as it was just moments before it went down).

Most small organizations simply copy the database file at the end of each day. When this approach is used, those who do data entry should keep the source of the information they are entering for at least one day and preferably two, in case the database becomes corrupt and the last backup was also damaged for some reason. In addition, sending a copy of the database off-site will help protect data against the possibility of a fire or other disaster destroying the backups along with the active database.

The question is: can the organization afford to lose a day or two of data entry from the database and then re-enter it all over again? If the answer is "no," the database should be backed up even more frequently. The drawback is the time it takes to close the database, copy it, and then re-open it to continue working.

Some third-party solutions automatically back up the database every set number of minutes even while it is open. (There are none that I am aware of that actually log transactions, however. Organizations that need transaction logging usually upgrade to a database that supports it, such as Microsoft's SQL Server 2000 or 2003.) Probably one of the best of these is Total Visual Agent by FMS, which as of the date of this article costs $299.00. It has other features that may make the price worth paying, including automation of compact and repair (see below), but for a single database file the product may be overkill. Access Autopilot by CRiSP Software, at $129.00, is a less-expensive alternative. (I have yet to test either of these.)

Another option is general backup software, such as AISBackup by Aquarius Consultancy, Ltd. (a program I use and like), that will automatically back up all files you designate and can be scheduled to run automatically at set times during the day, but the Access database must be closed for the backup to work properly. There is also free source code available that promises database backups while the database is open, but it has fewer features and would require more testing by your programmer before implementation. A more customized solution is also a possibility, but at the cost of a developer's time.

Top

Compact and Repair

Besides backing up the database, it should be compacted regularly. The Access "Compact and Repair" function reorganizes bits of information that have become fragmented, due to deleting and editing data, to make the database smaller and run faster, while correcting some potential problems that could lead to file corruption.

"Compact and Repair" can be run from inside Access via the menu options: Tools-> Database Utilities-> Compact and Repair Database. It can also be run from a shortcut or the Windows command line (see "Opening a Database from a Shortcut," above), using the /compact switch. This switch causes the database to be compacted and repaired immediately upon opening, and then to close again immediately after those processes have completed.

To compact and repair when the database is closed, type the following either in the "Target" field of the shortcut properties, or in the command line for the Windows Run box:

1. The full path and file name for the Microsoft Access program file, enclosed in double quotes.
2. The path and file name for the database, also in quotes.
3. The "/compact" switch, without the quotes.
4. If the database requires logging in, you will also need to add the "/wrkgrp" switch (no quotes), followed by the full path and file name of the workgroup security file.
5. Click OK.

Following is an example without any workgroup security file. This example uses the Office 2003 and Windows XP default locations for Access and the "My Documents" folder. Your computer may have different paths to the program and database files. Everything is entered on one line:

"C:\Program Files\Microsoft Office\Office11\msaccess.exe" "C:\Documents and Settings\user name\My Documents\database.mdb" /compact

Example which includes the workgroup security file:

"C:\Program Files\Microsoft Office\Office11\msaccess.exe" "C:\Documents and Settings\user name\My Documents\database.mdb" /compact /wrkgrp "C:\Documents and Settings\user name\My Documents\workgroup.mdw"

Top

Conclusion

Any damage or theft of database information will cost an organization. Security and backup procedures and technology help to minimize such risk. Securing an Access database involves more than Access security alone, and should include Windows security, user education, and specialized security software including firewall and anti-virus software. Other aspects of data protection are data backup and maintenance of the database, specifically Access "compact and repair," though there are other maintenance issues we did not discuss, such as data archiving.

Access security consists of a properly set up workgroup security file and properly implemented user and/or group permissions for database objects. Although these are normally put into operation by the developer, the client gains from a good understanding of how these work to secure the database. The end user in a multi-database environment may need to join to different workgroup files to open different databases. Anyone concerned about whether or not a database has been properly secured should test it by opening it while joined to the default "System.mdw" workgroup file.

Hopefully, this discussion leaves the reader a little wiser about the importance of and strategies for protecting valuable data.

For a more in-depth discussion of Access security, see the Microsoft white paper, which is still valid for Access 2003:

Frequently Asked Questions About Microsoft Access Security for Microsoft Access versions 2.0 through 2000
http://support.microsoft.com/default.aspx?scid=%2Fsupport%2Faccess%2Fcontent%2Fsecfaq.asp

Better yet, read the book, Real World Microsoft Access Database Protection and Security, by Garry Robinson (published November 2003 by Apress L. P., 1590591267: Product Link on Barnes & Noble.com.)

Top

Microsoft, Windows, Outlook, PowerPoint, FrontPage, and Microsoft Office are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. Other product and company names mentioned herein may be the trademarks of their respective owners.