The Importance of Encryption by John Maenpaa

The Importance of Encryption

John Maenpaa

A few days ago, I received an urgent question about how to protect sensitive data stored in a table on their database server. Her boss wanted to make it easy for team members to get the information so they could do testing. In this case, the data was actually userids and passwords to be used by application routines that connect to remote FTP servers. Even worse, the remote servers are government servers with sensitive medical data. My initial response was that she should avoid doing this, but if it is truly necessary then the passwords need to be encrypted. She agreed, but her boss considered the need for encryption to be a low priority since only the team would have access to the server.

Encrypting your data can be a lot of work. Do we really need to do that extra work? Why shouldn’t we trust the built-in security of our database management systems? There’s that concept of trust again. Can we trust the security that we’ve put in place? To some extent we can. We can trust that normal use of the database system to enforce access rules. Unfortunately, we can also trust that people will forget the details of why that security was put in place. Inevitably, someone new will be given access to the server or the databases will be consolidated. People forget details or move on to new jobs. If no one remembers why a server was locked in the closet they’ll stop locking the door. After all, it’s harder to get to the other items in the closet when the door is locked. Why is this important?

It is virtually impossible to fully secure the contents of a computer from someone who has physical access to the machine. With physical access, anyone can install a new operating system or even disassemble the computer and take the disk. Encrypting the data on your disk can help prevent data breaches that occur when someone gained inappropriate physical access. Windows and Mac OS X both have the ability to encrypt their disks. Is this enough? If we encrypted the disk are we safe?

Physical security is just one part of the solution. We’ve seen some high profile data security breaches. Credit card companies have had to send new cards to thousands, possibly millions, of customers. Researchers have discovered serious bugs in commonly used software components that have allowed people to gain more access than they should have. With access to the files stored on the disk, it is possible to copy the data and send it elsewhere. If operating systems stored passwords in plain text, we would have all been panicking when millions of userids and passwords were exposed and published for any to use. Fortunately, most of those passwords were encrypted. In some cases the encryption was identified as inadequate and steps had to be taken, but the use of encryption mitigated the losses due to the exposures. If nothing else, it bought us time to react and change our passwords.

For password storage, most operating systems and software products use one-way encryption. The user enters their password and the software drives it through the one-way algorithm and compares the result with the stored password. These algorithms can be difficult to understand without intense study and there is always the possibility that someone will figure out how to reverse the algorithm. To try and hold that off, researchers upgrade their algorithms and we have to upgrade from time to time. But one-way password encryption isn’t going to work for my friend. She needs to decrypt her passwords in order to use them.

Two-way encryption algorithms come in two basic flavors. The first, shared-key symmetric encryption, requires that the person decrypting the data use the same pass phrase as the person that encrypted the data the first time. The second, asymmetric encryption commonly used by public key infrastructure (PKI), has private and public keys that allow one user to encrypt the data using the public key and the other to decrypt it using the private key. It can also be used to certify the originator of a message by encrypting a message using the private key so that everyone can verify the identity of the sender by decrypting it using that person’s public key. PKI is quite useful for messaging between parties that have not had the opportunity to exchange a pass phrase.

For my friend, shared-key encryption will be sufficient to protect the passwords her boss wants to store in their database. The next problem is how to implement the encryption. If she were using DB2 for z/OS, the ENCRYPT_TDES function could be used during the insert and the DECRYPT_CHAR function could be used on select statements. Both the insert and the select would need to specify the same password-string. On DB2 for Linux, UNIX and Windows use the ENCRYPT and DECRYPT_CHAR functions. This protects the data at rest on the disks and keeps it safe on backups as well.

DB2 for z/OS lets you specify the encryption password in a special register, like so:

SET ENCRYPTION PASSWORD =’My memorable stapled monkey’;

INSERT INTO FTP_SITES (SITE, FTP_USERID, FTP_PASSWORD)

     VALUES (‘SITE1’,’CO1256’,ENCRYPT_TDES(’289-46-8832’);

The corresponding decryption would look like this:

SET ENCRYPTION PASSWORD = 'My memorable stapled monkey';

SELECT FTP_USERID,

       DECRYPT_CHAR(FTP_PASSWORD) AS FTP_PASSWORD

  FROM FTP_SITES

 WHERE SITE = ‘SITE1’;

Of course, now there is the storage of the pass phrase to consider. The pass phrase  could be a simple sentence or jumble of words that is easy for the team to remember. The more complex the pass phrase, the more secure the encrypted data. For using complex pass phrases (and passwords) I strongly recommend the use of a password management application. These applications can usually be configured to use a stored file with some level of sharing available. Of course, that’d be even better for storing the original userid and password in the first place. You still need to ensure the pass phrase doesn’t get lost and totally forgotten. And don’t forget, when the team changes you have to change the pass phrase:

UPDATE FTP_SITES

   SET FTP_PASSWORD =

       ENCRYPT_TDES( DECRYPT_CHAR( FTP_PASSWORD

                                 , 'My memorable stapled monkey'

                                 )

                   , 'Your giraffe forgot his keys'

                   )

 WHERE OWNER = 'John';

Be careful doing this update. What happens if there was data in the column the used a different key?

We’ve managed to address my friend’s problem without adding too much complexity or time to her work. I believe her boss would be satisfied and we’ll all feel more comfortable.

Now let’s look briefly at another area where encryption is extremely important: data in motion. We need to protect our data as it moves across the network. Transport Layer Security (TLS) and Secure Sockets Layer (SSL) protocols were developed to secure data sent over the Internet. TLS is newer and replaces SSL which has been found to have exposures. These protocols use asymmetric encryption in the form of certificates to confirm the identity of at least one side of the connection. The client and server negotiate using asymmetric encryption to derive a shared key value that can then be used for the rest of the session as a symmetric key by both sides. DB2 supports the use of TLS encryption between clients and database servers, use it. You never know who may be listening to your network traffic, even behind a firewall.

We’ve covered encryption of several types here that keep our data secure both at rest and in motion. Disk-level encryption protects our data in case the physical machine is lost or accessed by an inappropriate individual. Column-level encryption protects our data from lapses in security that come from the fundamental flaws in people and organizations. Network-level encryption protects our data as we send it between systems and applications. Without encryption, we cannot be assured of any privacy in this digital age.

Recent Stories
Selecting Appropriate Data Architecture Technologies

SQL Tuning for Developers – Part 2 Explain Output

Some of my favorite tunings tips and tricks for DB2 by Joe Geller