Database Encryption

You may find this a little odd, but the most important thing to know about Access database encryption is to ascertain whether or not your database is encrypted. An encrypted database costs you in terms of performance and compression of backups and file transfers. Therefore, it is something that you may want to avoid. If you are unsure about the state of encryption of your database, add your database to a compressed format file such as a .ZIP file. If it remains virtually the same size , then you have an encrypted database. If you think that it couldn't happen to you, be aware that anyone may have used the encryption command in Access without your knowledge. Also, bear in mind that you will never know if you have an encrypted database unless you test for it.

Now that the author's unusual and important encryption issue is out in the open , why did Microsoft introduce encryption to Access databases? The reason given is that they want to make the file hard to read in another tool such as a text editor. After looking through a number of normal databases in a text editor, I have concluded that encryption will make it impossible to read the small text strings and numbers that are easy to find by scrolling through the normal database in an editor.

In general, the average user would be hard-pressed to assemble anything substantial from viewing an unencrypted database. Of course, the layout of normal databases will not confuse people who are dedicated to extracting the information, but I don't consider that one of the bigger Access security issues. The main problem with the Access file format is that there are companies that can decipher appropriate parts of the file format and extract your information. For these organizations, the weak encryption method employed by Access is not an issue.

Caution  

Do not open your important databases in an editor, especially Microsoft Word, as it may render the database useless. If you want to look at it, make a copy first.

If you have not added any workgroup security to your database, you may want to use database encryption to stop the database from being compressed (by a factor of five times or more) into a .ZIP format and transferred by email or onto a disk. If you intend to use encryption to make a database harder to read, you must invest some effort into workgroup security. Chapters 8 and 10 will provide you with enough material to protect your database this way.

How do You Encrypt/Decrypt a Database?

Start encrypting and decrypting a database by choosing Tools ˜ Security ˜ Encrypt/Decrypt Database, as shown in Figure 9-1.

Figure 9-1: Encrypting/decrypting a database from the Tools menu.

In all versions of Access, if you don't already have a database open, you will first be prompted to select a database. The File dialog will tell you whether you are encrypting or decrypting the database. Be careful to ensure that you are actually making the change that you want and not reversing it back. In Access 2000 or later, if you have a database open, the File dialog will select the current database and then inform you whether you are encrypting or decrypting the database. You can deduce from this information whether the database is encrypted or decrypted.

Before you encrypt or decrypt a database, you must be the owner of the database. This detail is important because, unless you have joined an alternative workgroup such as a developer workgroup file (discussed in Chapter 8), you will probably have created the database by using the anonymous Admin account. If that is the case, then any other person can use that Admin account to reverse the encryption. To verify that ownership will protect your database from being encrypted or decrypted, choose Tools ˜ Security ˜ User and Group Permissions and verify that the Admin account is not the owner of the database (shown in Figure 9-2).

Figure 9-2: Verifying the ownership of the database.

If a workgroup user is not the owner of the database, that user will see a message, such as that shown in Figure 9-3, when they try to change the encryption.

Figure 9-3: Message that appears when you cannot encrypt and decrypt a database.

To change ownership, follow the guidelines in Chapter 8. It is also interesting to note that the Access help instructions discuss exclusive ownership permissions as being important. This statement is incorrect, because only the database owner can decrypt a database. So remember, if the developer workgroup file is not distributed, then your users can never decrypt the database. In addition, users cannot encrypt it either, which will keep the performance at optimum levels.

To encrypt a database regularly, you can use the following VBA DAO command:

 DBEngine.CompactDatabase "C:\north.mdb", "C:\northEncr.mdb", , dbEncrypt 

You do not need to add a reference to the DBEngine object in Access 2000 or later because Access maintains a secret reference to that object. Otherwise, you can use the CompactDatabase method of the Microsoft Jet Replication Object in Access 2000 or later.

How Does Encryption Work?

In simple terms, Access opens the database and looks for an encryption flag. If it finds it, then just before Access writes a page of data to the hard disk, each character is encrypted to another character. When Access reads an encrypted page, it will first decrypt the page and then process the page as usual. As far as the database user is concerned , it doesn't matter whether the database is encrypted. Notably, the size of the database does not change after encryption; only the performance suffers.

Microsoft has this to say about the encryption technology: Microsoft Access uses an RC4 encryption algorithm with a 32-bit key from RSA Data Security Incorporated. If you are creating an international application, this algorithm is acceptable for export outside of the United States (according to United States export laws) because the key is less than 40 bits. When you encrypt a database, all objects (tables, forms, queries, indexes, and so on) are affected because encryption is implemented at the page-level and not at the data-level . Microsoft Access encrypts a database in 2K (kilobyte) pages, regardless of the data stored in a page. Each encrypted page is assigned a unique 32-bit key. ^[1]

That said, a number of companies have cracked Access encryption. Fortunately, these companies, as of mid-2003, do not seem to market this software to the public and instead use the software to help clients rebuild damaged databases.

What are the Disadvantages of Encryption to the User?

The biggest disadvantage is that the database is slower (by 15 percent, according to Access help) as the encrypted pages take time to decrypt. The other disadvantage is that you can't use compression utilities, such as .ZIP, on the database file. This limitation has repercussions for developers because any database larger than a few megabytes might be difficult to transfer over the Internet. DBAs may also be concerned because the encrypted databases may clog your backups if you are relying on compression.

Under What Circumstances is Encryption Recommended?

If you have a large database and you have some control over the maximum size that your staff can email, encryption would stop staff from transferring the database to their home or to a third party. Of course, CD-ROM burners and other devices would negate the effectiveness of this approach.

The only other reason that I would use encryption would be to make single words of great importance hard to read. Examples of these would be terms such as credit (card), password, salary, commission, and the SQL text of queries. You can test this practice yourself by editing a copy of your database and looking to see whether you can make sense of the information that is stored with those terms.

Note  

The Access User-Level Security wizard (discussed in Chapter 10) automatically encrypts your database as part of the security process.

If you want to stop people from emailing a small database, you could add a dummy table and fill it with 50,000 records to swell the size of the database.

In summary, if you have implemented workgroup security and protected the user interface, encrypting the database will improve your security. Unfortunately, it is going to come at a cost in performance and compression of backups.

^[1] "ACC: How Microsoft Access Uses Encryption." Microsoft Knowledge Base Article No. 140406, May 2003.