Assigning permissions to tables does nothing to protect your sensitive data from anyone with a word processor. If you find Access security too cumbersome to use, Dima Mnushkin provides you with a simple solution for encrypting sensitive data. Along the way, you’ll be introduced to the costs and benefits of using Access encryption.
I don’t know what most people think about Access security, but I find it a royal pain in the neck. If all I want to do is restrict access to a form or module, I have to implement Access security and start popping aspirins. Ideally, I’d be able to secure a completed database once and not worry about it ever again. That never happens for me. Somehow there’s always one more form to tweak, bug to fix, report column to widen. Once I make that change, I have to adjust my permissions once again.
Largely because of my bad experiences with applying Access security, I was absolutely thrilled to find out that Access 97 could create MDE databases. What a godsend! No more messing around with permissions and database ownership–just choose Tools | Database Utilities | Make MDE, and presto! My database would be secure from tampering by adventurous users who think your form name should be more descriptive. But after some additional investigation, I was disappointed to find that all of my tables’ data was still completely open to anyone with a copy of Access. It looked like assigning permissions was still the only way of ensuring that unauthorized personnel couldn’t get at sensitive data. My next step was to find out what the Encrypt/Decrypt Database would do for me.
Access encryption
Some developers believe that implementing Access security will keep people out of sensitive information like salaries or passwords. This is simply not true. As a simple experiment, create an Access database with a one-field table. Enter the value “this is a test” into that field, set a database password, and close the database. Open the database with your favorite word processor and do a search for “this is a test.” You should find it easily. Now imagine that had been your boss’s name and the field right next to it contained his salary. Now imagine looking for a new job.
What this little experiment has demonstrated is that data within an Access database is easily accessible to anyone with a word processor, regardless of what permissions you’ve assigned to your objects or passwords you’ve set on the database. To make your database truly secure, it’s necessary to assign appropriate permissions and/or passwords and encrypt it. For this purpose, Access 97 provides the Encrypt/Decrypt Database function under the Tools | Security menu. I couldn’t find out precisely what type of encryption algorithm Access uses to protect each database, but it appears to be a form of symmetric encryption, likely DES.
The problem with database encryption is that it must be used in combination with regular Access security. An encrypted database without any database security can be decrypted by anyone with a copy of Access. Additionally, an encrypted database will run slower because of the extra overhead of encrypting and decrypting information all the time. Since Access isn’t the gazelle of development tools, slowing it down seems foolish.
Encrypting a whole database is often overkill. The average database doesn’t contain very much confidential information. Even a sensitive table like Employee has, at most, only one or two fields that must be protected. Thus the question becomes: Why encrypt (and therefore slow down) my entire database and enforce cumbersome Access security when all I want is to protect the values of two fields?
Encryption functions
A few months back, I was asked to protect employee passwords in an Access database. My client wanted the passwords hidden by more than just the “Password” input mask on the table field. The rest of the database hardly needed more protection than distributing it as an MDE. Under the circumstances, I was loath to implement both Access security and encryption (and I was all out of aspirin).
The only solution I could come up with was to implement my own encryption function that would reside in the MDE-protected module. I was faced with the difficult decision of selecting an encryption algorithm that was both easy to implement and tough to crack. Since I didn’t know much about encryption, I had to do some research on the Internet. I was overwhelmed by how much stuff was out there (see the sidebar “Encryption” for a brief synopsis of what I found).
Microsoft actually provides an API that performs encryption/decryption using the CryptEncrypt function. However, rather than re-using that perfectly good Microsoft code that had been well debugged and that included advanced encryption techniques, I decided to write my own. I was pretty sure I could write a reasonably secure function in less time than it would take to figure out how to use the API. As you can imagine, it took longer than I expected, but I learned a lot in the process.
The algorithm
One goal of my encryption function was to produce a different encrypted result for a given text value each time the function was run. In other words, when passed data, my function would produce an encrypted version of the data. If the process was repeated, a different version of the encrypted data would be produced. If you don’t do this, it’s possible for a hacker to take some known data, encrypt it, and compare the results to your encrypted data. Where your encrypted data and the hacker’s encrypted data match, the hacker knows what’s in your database. Another goal was to prevent users from getting a sense of the encrypted information by seeing clear word separation that could result from spaces being encoded to the same value each time.
I chose to implement a simple symmetric encryption algorithm that uses a single, developer-defined key to encrypt and decrypt information. In this algorithm, each byte in the data would have a byte added to it and so produce an encrypted byte. The byte to be added would be drawn from the developer-defined key. My function would draw the bytes one by one from the provided key. Once the last value in a key is used, the function will cycle back to the beginning of the key and continue encrypting until it runs out of data. This would ensure that even a common character, like a space, would rarely look like another.
A tougher challenge was to produce a different encrypted result each time the same value was passed through the function. To achieve this, it was necessary to begin at a randomly chosen position within the key each time. This would ensure that “baby” would encrypt to “_©”´È” the first time, “Ö™m£Ì” the second time, and so on. The more bytes in the key, the tougher it becomes to guess the key because there’s less repetition in the encrypted results.
While this isn’t an especially difficult algorithm to implement, it does pose a problem when decrypting the string. In order to decrypt the data, I have to know what starting position within the key was used to encrypt the data in the first place.
To solve this final problem, I had to store the starting position that was used to encrypt the data in the encrypted result (you might have noticed that the encrypted values for “baby” were one character longer than “baby”). Just to make things more interesting, I also encrypted the starting position (using the first key value) and inserted it into the middle of the result. With the function incorporated into my code and the appropriate data in the tables encrypted, my database was ready to be made into an MDE and distributed to my users.
The encryption function
You can find the encryption function in the sample database that I’ve included in the accompanying Download file. Here, I’ll walk you through some of the more interesting parts.
The first step in the encryption process is to establish the encryption key. It doesn’t matter what it is as long as it’s not longer than 255 letters and not empty. Here’s an example:
Const PASSWORD_KEY = "asd q98uiqw4t8ASEF9p513l"
When the function is encrypting data, the routine begins by generating a random number between 0 and the length of the encryption key. This number is the start position in the key:
Randomize intPassKeyPos = _ Int(Len(PASSWORD_KEY) * Rnd + 1)
Once I’ve selected the start position, I encrypt it by adding the first byte in the encryption key to it:
intTemp = intPassKeyPos + _ Asc(left(PASSWORD_KEY, 1))
The core of the routine is a loop that finds the next position in both the encryption key and the data. If the next position for the encryption key is past its end, the routine cycles back to position 1. The routine uses the Mid function to extract the characters at the current positions in the data and the key, converts them to numbers with the Asc function, and then adds them together to get the encrypted result.
If the result is a number greater than 255 (the largest value that can be converted into a character), I subtract 255 from it to get a number that I can convert to a character. The Chr function converts the encrypted result into a character, which I add to the end of the encrypted result. During this process, I check to see whether I’m halfway through the string and add the key’s start position:
For intInputPos = 1 To Len(strFull) intPassKeyPos = intPassKeyPos + 1 If intPassKeyPos > Len(PASSWORD_KEY) Then intPassKeyPos = 1 End If intTemp = Asc(Mid(strFull, intInputPos, 1) _ ) + Asc(Mid(PASSWORD_KEY, _ intPassKeyPos, 1)) If intTemp > 255 Then intTemp = intTemp — 255 End If If CInt(Len(strFull) / 2) + 1 = intInputPos _ Then strOutput =strOutput & strStartingPos End If strOutput = strOutput & Chr(intTemp) Next intInputPos
Not surprisingly, the decryption process is similar to the encryption process. The first step is to extract the start position for the encryption key:
intPassKeyPos = Asc(Mid(strFull, _ CInt((Len(strFull) - 1) / 2) + 1, 1)) - _ Asc(left(PASSWORD_KEY, 1))
The next step is to subtract the value of the encryption key from the value of the encrypted data. If this gives a result less than zero, it indicates that I had subtracted 255 during the encryption process. I compensate by adding 255 before converting the result to a character:
intTemp = Asc(Mid(strFull, intInputPos,1))- _ Asc(Mid(PASSWORD_KEY, intPassKeyPos, 1)) If intTemp <= 0 Then intTemp = intTemp + 255 End If
The only other trick is to jump over the middle character because it contains the start position for the encryption key.
The function accepts two values: the string to encrypt as its first parameter and a Boolean value as its second parameter. If the second parameter is True, the function encrypts the data; if the second parameter is False, the function decrypts the data passed to it. You can call this function from a variety of places. The first line in the following code accepts a text box on the form and stores the result in a field in a Recordset. The second line reverses the process:
rec("name") = strEncrypt(Me.txtName, True)
Me.txtName = strEncrypt(rec("name"), False)
Because of the integration of VBA and SQL, you can use the function in a SQL statement:
Select strEncrypt(rec("name"), False)
From SalaryTable
Known limitations
If you do use this function, you should be aware that it currently doesn’t handle numeric encryption. If numbers such as a salary must be encrypted, you must first convert them to strings and then store the encrypted result as a string. Also, it’s currently impossible to have a key longer than 255 bytes because the position is stored in a single byte whose largest possible value is 255.
Although Access encryption, in combination with Access security, allows for a truly secured database, it’s not always the best solution to protecting sensitive data. When deciding what method to use, consider how many fields need to be protected, the complexity introduced by adding Access security, and the performance hit taken by encrypting the database. Implementing a simple encryption function and distributing the database as an MDE will often be the most efficient way to ensure a reasonable level of security.
Sidebar: Encryption
Encryption goes back a long way. It could be argued that the need to break encrypted messages was the single biggest push for the development of modern-day computers. The English began computing at Bletchley Park during World War II when they fired up their Turing Bombes and cracked the “unbreakable” German Enigma cipher. These machines were good examples of parallel processing, as each Bombe handled only a small portion of the total possible Enigma settings.
We’ve come a long way since those mechanical relays and whirling spools. Public-key encryption has revolutionized electronic communications by providing a remarkably secure method for the average person to communicate with anyone they wish. The constant advances in computing power and code breaking power are now only defeated by regular increases in encryption key size.
The dictionary describes encryption as “the activity of converting from plain text into code.” Encrypting data is the process of systematically modifying data to make the result illegible. Only the recipient of your message has the code or algorithm to decode the gobbledy-gook and restore the original message.
Symmetric encryption
Until recently, there was really only one way to encrypt information. You took whatever message you wanted to encrypt, used a secret key to systematically modify the message contents, and then sent the result off to the recipient. The recipient would also possess your secret key and use it to decode your message. This method is called symmetric encryption (or secret-key encryption) because both parties must have the same secret key or cipher. That’s also the weakest link in the security chain because getting the cipher to the second party without interception is often the most difficult part.
Currently, the best known implementation of this type of encryption is the Data Encryption Standard (DES). DES was adopted by the U.S. Department of Defense in 1977 as the standard for encrypting confidential information. Several versions of DES have been implemented, but the most common uses a 56-bit key (72 quadrillion possible values) applied to 64-bit blocks of data. It’s only recently that computing power has advanced to the point of cracking DES in a reasonable amount of time. By the year 2000, a new encryption standard called Advanced Encryption Standard (AES) is expected to be announced, promising large enough key sizes to remain unbreakable for another 20 to 30 years.
One interesting application of DES-style encryption is the ability to verify the integrity of encrypted information. Due to the encryption algorithm used, modifying a single bit anywhere in the encrypted data will prevent proper decryption of that information. This lets you verify that no one has tampered with the contents of the message since it was sent to you. Unfortunately, it does nothing to assure you that the right person sent the message! If your decrypted communication says to “push the big red button,” what guarantee do you have that the cipher wasn’t intercepted by the enemy, who is now sending bogus commands?
Asymmetric (public-key) encryption
To get around the problem of managing secret keys that’s built into symmetric encryption, Whitfield Diffie and Martin Hellman introduced the concept of public-key cryptography in 1976. This approach is based on each individual having two related keys. One key is a secret one known only to you, the person receiving the message; the other is a public key that’s given to anyone who might want to send you a message. If someone wants to transfer confidential information to you, they use your public key to encrypt their message.
Unlike symmetric encryption, a message encoded with your public key can only be decoded with your private key. You can’t use the public key to decrypt a message that was encrypted with the public key. With this method, there’s never a need to transfer your secret key to the recipient of a message. The weakest aspect of symmetric encryption is avoided. It’s because public key encryption uses two different keys to encode and decode messages that it can be referred to as asymmetric encryption.
Digital signatures
Now when the command to “push the big red button” comes, there’s still a problem. Who was it that sent the message? Anyone has access to your public key, so anyone can send you an encrypted message. How do you verify that the message was sent by “M”? The solution to this problem is called a digital signature. When M wants to send you a message, M first encrypts the message (or a small portion of it) with his private key before encrypting it with your public key.
When you receive the message and decrypt it using your private key, you get another encrypted message. To decrypt this one, you must use the public key of the person who sent you the message. Since this message must have been encrypted with M’s private key, which no one else in the world has access to, you can be reasonably certain that M sent the message.
Certificates and certificate authorities
What happens if you don’t know M’s public key? Who publishes public keys for everyone to see? How can you be certain that the public key published for M is really M’s and not someone else’s? To solve these problems, certificate authorities (CAs) have come into existence. These are organizations that certify the identity of a public key holder, in effect guaranteeing that the public key listed really does belong to M. If you trust the CA, then you can trust any public keys it lists.
To publish your public key with a CA, you have to supply personal information that can be used to verify your identity. Information such as SSN, tax number, e-mail address, and so on might be required before a CA will certify you and publish your public key. There’s no need to worry about your information being intercepted en route to the CA because you can encrypt it with the CA’s public key before transmitting it.
