Using MS Access as a Back-End Database - Making an MS Access Database Secure

by Charles E. Brown

MS Access vs. SQL
SQL is a highly used and reliably secure database server that is used by many small and large businesses alike. The reasons for it's popularity and high regard are numerous, but the fact that it is a database server designed to be accessed securely over the Internet is the leading reason why it is the database of choice. Online merchants and Web developers who create sites heavily reliant on database information exchange use it religiously.

However, this is not to say that Microsoft Access cannot be a great database application. Because MS Access is not designed to be a database server, it does not do as proficient of a job as SQL in providing clients with access to database information in a secure and reliable fashion.

Nevertheless, many companies elect to use Microsoft Access instead of a program like SQL Server as the back-end database for their Active Server Page Applications (ASP). The number one reason for this is that MS products are very familiar to corporate entities and their staff. Secondly, most companies who have decided to establish an online presence would rather use software they already possess as opposed to incurring further expense and re-training all their personnel on new software. Third, it is a burden to try to transfer all database data over to a new database. This takes time and money away from a company. Fourth, creating and setting up a database in Access is much easier than in SQL Server. Finally, most companies can afford Microsoft Access but the requirements for SQL Server beyond just the software itself can be a problem for a financially strapped company.

One major caveat of Access is that because it is not a database server, you don't have a server dedicated to passing out query results. This means it is not optimized for very heavy use. In addition to this is the fact that you must address the issue of security if you are going to use an Access database in an Internet setting. This means that unless you change many of the default settings in Access, your entire database can be open to anyone in the world.

Security in Access works through your "data database" and a "user database". The "data database" is the database with your tables, queries, forms, reports, macros and modules. The "user database" stores user name, password, identifiers, groups and group membership information. All the "user database" does is validate a user and a password and provides the specific "permission" within itself.

Even though it is not casually apparent, security is always on in Access. The reason that you don't see any "log-in" is because by default you are "logging-in" as the default administrator without a password.

Eight Steps for Creating and Securing an Access Database
The remainder of this article will present an eight-step process for making an Access database secure with a focus on removing the privileges of the default account. This is necessary because the default account is available to any Access installation.  The topics covered will be:

• Creating a New User Database
• Getting the Log-in Box to Appear
• Creating a New Admin
• Removing the "Admin" from the "Admins" Group
• Taking Ownership of the Unsecured Database
• Removing Privileges to the Secure Database
• Encrypting the Database
• Additional Users

Step 1: Creating a New User Database:
In this step we will walk through the procedure of creating a new "user database" and then use that database to secure our "data database". As previously stated, the "user database" is where the log-ins for the database are stored. In order to create and join a new "user database", you will use a tool that comes with MS Access called the "Microsoft Access Work-group Administrator".

It is unlikely that this tool will make an appearance on your shortcut menu, therefore you will have to browse to find it. Most of the time there will be a shortcut for the tool in the "Microsoft Office" directory. However the simplest way to execute this program is to search for the name of the file, which is WRKGADM.EXE.

Once you have located this tool, start it and you should see a dialog box. You should then choose "CREATE". You will then be prompted to supply a name for your "user database". Here you will enter the organization for the database as well as an ID for it. After you have supplied the information you should select "OK".

You will now be prompted to supply the location where you want to create your "user database" and you will need to give the file a name. After you complete the file name and location, press "OK". You must then confirm that the information you have just supplied is correct. If the information is correct, press the "OK" button. If you need to make changes, press the "CHANGE" button. After you have successfully completed this task, the tool will tell you that you have successfully created and joined the "new user database".

Step 2: Getting the Log-in Box to Appear:
As stated previously, the security in Access is on even though you don't log-in. A log-in box does not appear because the default account that you are logging in through does not have a password yet. In this step we will address the appropriate method of changing this.

Open MS Access and DON'T OPEN A DATABASE! From the menu, select "TOOLS-SECURITY-USER-GROUP ACCOUNTS". Once selected, switch over to the "CHANGE LOGON PASSWORD" tab.

You'll notice that the old password for the "ADMIN" account by default is left blank. This is the reason you are able to log into Access without entering a password. You should now enter a new password for the "ADMIN" account and press "OK". Go ahead and shut down Access, once it is shut down, re-start it. If all goes correctly you will be prompted for a "NAME" and "PASSWORD". Use the "ADMIN" name and password you supplied in the previous step.

Step 3: Creating a New Admin:
Normally when you log-in to Access under the default "Admin" you enter under an account that is available with every installation of Access. Therefore even if you do change the password in your own copy of the user database as described in the previous step, you are not secure. Someone can take your database and place it on a different computer and use the default "ADMIN" account without a password to access your database. Therefore we must go further and create a "NEW ADMIN".

Once again, start Access and DON'T OPEN A DATABASE. On the menu, select "TOOLS-SECURITY-USER and GROUP ACCOUNTS". Go over to the "USER" tab and select it.

You will now add a "NEW ADMIN", so you should press the "NEW" button. You must now supply the new administrator with a name and an identifier. It is necessary that the new administrator belong to the "ADMINS" group. Therefore your next move will be to highlight the "ADMINS" group in the "AVAILABLE GROUPS" list. After you have successfully completed this task, press the "ADD" button.

Now simply press the "OK" button and the "NEW ADMIN" account is created. However since new accounts are created without a password we will need to create one. Once again, you need to close Access and restart it. Now you will log-in as the new administrator that you just created and leave the password field blank. As before, DON'T OPEN A DATABASE. Proceed to "TOOLS-SECURITY-USER and GROUP ACCOUNTS". Now go over to the "CHANGE LOGON PASSWORD" tab, there you should notice that the current account is the "NEW ADMIN" account.

Leave the old password field blank since this is a new user. Supply a new password for the "NEW ADMIN".

Step 4: Removing the "Admin" from the "Admins" Group:
Although it would be easier if it were possible, you cannot just delete the original "Admin" account. However you can remove the "Admin" from the "Admins" group. Doing this will keep that account from being able to manage new databases.

In order to perform this task you need to log-in to the database as the "NEW ADMIN" account. As before, DON'T OPEN A DATABASE, go to the "TOOLS-SECURITY-USER and GROUP ACCOUNTS" menu. Move over to the "USERS" tab and under "NAME" choose "ADMIN". Look in the "MEMBER OF" list, find and highlight the "ADMINS" group and then select "REMOVE".

Step 5: Taking Ownership of the Unsecured Database:
Because your original databases were created in an unsecured format using the default, there is no real way of removing ownership of those databases from the "ADMIN" account. Therefore you will have to create a new database and import all the objects from the unsecured databases into the new secure databases. After you have accomplished this you may want to go back and remove or delete the unsecured database(s).

Log-in to Access as the "NEW ADMIN" and create a new database. Go to the "File" menu, there you will select "GET EXTERNAL DATA-IMPORT". You will now "browse" to one of the databases that contains the unsecured data and objects you wish to import into the new secure database. If your actions are correct up to this point you will be presented with the "IMPORT OBJECT" dialog box.

Now you will be able to select all of the "TABLES, QUERIES, FORMS, REPORTS, MACROS and MODULES" that you want to be part of your new secured database. Note: On the "TABLES" tab, make sure that you are importing the "DEFINITION" and "DATA".

You will need to repeat this step for each unsecured database that you wish to recreate as a secure database.

Step 6: Removing Privileges to the Secure Database:
We have accomplished many things thus far. We have created a new administrator, assigned it to the "ADMINS" group and removed the default "ADMIN" from the "ADMINS" group. You should have successfully created a secured database/s and imported objects into that/those database/s. Nevertheless, the "ADMIN" user is in the "USERS GROUP", which still has "privileges" for this database. Because of this, we need to remove those "privileges".

Start Access and Log-in as the "NEW ADMIN" and open one of the "secured databases". Next, from the menu choose "TOOLS-SECURITY-USER and GROUP PERMISSIONS". Look under "LIST" and find and select "GROUPS". Next, look in the "USER/GROUP NAME" list and select "USERS". You will now set the "OBJECT TYPE" to "DATABASE". Next you will uncheck all of the "PERMISSIONS" that are checked. Finally, press the "APPLY" button.

If all the actions in step 6 were performed correctly, the original "ADMIN" should no longer be able to access this database. You should now test your security, exit Access and reenter as the original "ADMIN". Go ahead and try to open a newly secured database. You should be presented with an error message explaining that you do not have permissions on this database.

You will need to perform step 6 for every new database that you created using step 5.

Step 7: Encrypting the Database:
Although the database is now secure from unauthorized entry there is still the problem of the data type. MS Access internally stores most of the data in the database as raw text. It is possible for someone to see the contents of your databases by looking at them through a text editor. Therefore you will need to perform the last step in the securing of your databases by encrypting them.

Go ahead and log-in to Access as the "NEW ADMIN" and open a secured database. Go to the menu and select "TOOLS-SECURITY-ENCRYPT/DECRYPT DATABASE". Next you will need to "browse" and find the database that you want to encrypt. Then, supply the new encrypted database with a name.

You will need to repeat step 7 for all of your un-encrypted databases.

Step 8: Additional Users:
You have successfully completed the securing of your databases but only one person can access them (the NEW ADMIN). If you will be the only one accessing any of the information in the database/s then you could stop here. However chances are that most of the people who have created databases to be accessed by a Web page have done it so that others may access database records. Therefore you may wish to create an additional Web user that has the specific privileges that will be needed by your code in the log-in page of your site. Furthermore you may have certain privileges that you may want to issue to some users and not issue to others. For example, you may have some tables that you want to keep as read-only so users can view database data but not be able to append database records. Or it's possible that you may have some tables that you only want the Web site user to add records to.

You would do this by creating a "NEW USER" such as you did with the "NEW ADMIN". Then give them "permissions" to access the database and further issue them the "specific permissions" to the database that you wish them to have. This is similar to what we did when we removed the permissions from the "ADMIN" user previously.

* * *

Even after all of these changes MS Access is still not a database server and will not perform as well as SQL. However if you choose to use it as the back-end database you will now be able to do so with out paying a heavy price for lack of security.

About the author...
Charles Brown is a freelance writer specializing in Internet and consumer related articles.  He is also a content writer for EWM / PSI (Everyone Working Mutually providing Products Services & Ideas).  Mr. Brown is also the author of a new series of eBooks that will begin publication in mid February, 2001.  "The Art of Complaining © 1998 Charles E. Brown" will be a how-to on writing effective letters of complaint. "The Art of eShopping © 2000 Charles E. Brown" which will be a how-to on safe and efficient internet shopping.  Scheduled for publication in July/August, 2001are "The Art of Web Design © 2000 Charles E. Brown" and "The Art of Desktop Publishing © 2000 Charles E. Brown".

http://www.productsservicesideas.com