Should I encrypt data in database?

Question

I have a client, for which I'm going to do an Web application about patient care, managing patients, consults, history, calendars, everything about that basically.

The problem is that this is sensitive data, patient history and such.

The client insists on encrypting the data at the database level, but I think this is going to deteriorate the performance of the web app. ( But maybe I shouldn't be worried about this )

I've read the laws about data protection on health issues ( Portugal ), but isn't very specific about this ( I just questioned them about this, I'm waiting for their response ).

I've read the following link, but my question is different, should I encrypt the data in the database, or not.

One problem that I foresee in encrypting data, is that I'm going to need a key, this could be the user password, but we all know how user passwords are ( 12345 etc etc ), and generating a key I would have to store it somewhere, this means that the programmer, dba, whatever could have access to it, any thoughts on this?

Even adding an random salt to the user password isn't going to solve the problem since I can always access it, and therefore decrypt the data.

Answer 1

Yes, you should encrypt the database.

Basic encryption for stored data ("data at rest") is a Generally Accepted Security Principle, and is probably mandated by law if your country has laws that protect personal or health information.

We're using SQL Server 2008, so we use Microsoft's TDE; there may be some third-party solution for MySQL or perhaps just a general volume encryption approach (such as TrueCrypt) would work (although I'd want to have something that has been certified for use with a database).

If done properly, the performance hit should be small.

By the way, the link you mentioned (regarding the separation of sensitive information) is something that you should consider on top of the basic database encryption.

EDIT: The encryption mentioned above would encrypt the volume. If someone were to steal the hard drive, they'd find the data encrypted. However, if someone were to run queries on the database, they'd see the unencrypted data (that's why I mentioned the separation of information, even though the OP didn't want to discuss this).

Note that this recommendation is meant as the minimum that you should do. If you want legal advice, then of course you'll need to look elsewhere. If you want a more thorough discussion of writing secure code, I would starting with the book Writing Secure Code.

Answer 2

Before deciding on such security matters, you should assess the threat model. Without an idea what you're defending against, any measures you take are likely to be of little value.

Now, there are a few things you could worry about in this context:

• Attackers gaining physical access to your data (e.g., breaking into the datacenter, stealing backup tapes, etc.)
• Attackers gaining read access to your raw database
• Attackers compromising your application, e.g. through SQL injection, buffer overruns, etc.

For the first scenario, storing the database and all backups on encrypted volumes should work, provided the server is headless - stealing the server or the tapes would then require breaking the disk-level encryption.

For the second scenario, encrypting database data does help, but only if you don't store the required keys or passphrases anywhere.

In the third scenario, everything depends on the context in which the attack happens: if it is, for example, an XSS or CSRF attack, then an attacker can do anything the legitimate user can, and encrypting your data doesn't help at all.

You threat model, thus, is an attacker who gains read access to the raw database, either by finding the login credentials and managing to log into the database server from outside, or by gaining root access to the database server. A typical path is to first gain shell access on the web server; from there, an attacker can then read the access credentials from the configuration file and connect to the database.

An additional consideration is where you keep the keys and passphrases, especially if you're using a platform with a stateless execution model such as PHP. Ideally, you have the customer type their passphrase when required, and keep it only in memory, or even better, do the decryption client-side (but this is not often feasible). On a stateless platform, state is usually carried along using sessions, memcache, databases, or flat files; but all these are far more vulnerable than keeping state in a stateful web application's own memory. Avoiding this is a chicken-and-egg problem, because if you encrypt the state before persisting it, you just created another secret that you have to remember safely. Remembering the passphrase on the client and sending it along with each request that needs it may be the least horrible solution then; the client is still vulnerable if you have any XSS leaks, and you need to take the usual precautions (serve only https, set all cookies to httponly and secure, etc.), but those are issues you have anyway.

Answer 3

I would personally check the laws on this. If the data needs to be encrypted, then it needs to be encrypted.

If you don't receive any guidance though, I would aim to protect the link between the patient, and their data. I.e. you most likely have a PatientID that's used in tables throughout the database. PatientID does not identify a patient, only the patient's medical history etc... However, to identify the PatientID as Joe Bloggs living at Rua de São Bernardo Lisbon, I'd keep this in a separate DB if I can. Use TDE for the patient's personal details and consider encrypting it on-top of that using keys in your web application.

Whilst theft of that medical data without the means to identify the patients will be extremely embarrassing, it is unlikely to be anything beyond that. There are literally online competitions that use this anonymised medical data.

With the separation of the medical data from the patient's personal details. Use a robust set of roles to limit staff to only what they need. With the exception of medical staff that require to deal with the patient directly (front line nurses & doctors), no one should have access to both. Receptionists only need Patient's personal details, lab staff only need the medical record and PatientID, surgical nurses only currently medical condition and first name.

When you've identified each set of roles, aim to not only implement them in your web application, but also in the database as well as an extra layer of security.

Answer 4

Ignoring for a moment what the client is asking for, and whatever the laws are...

No, you probably should not encrypt the data. If you do, you will not be able to easily search it. For example, how would you search for a last name like 'Smith%' if every name entry is encrypted? How would you graph a patient's blood pressure over time if you cant select .... from.... where patient_id = N?

You should, obviously, make sure the server is properly secured, the network connection is secured, and that the user interface is properly secured (including session timeouts so users cant walk away leaving access to anyone who uses their computer). You also may want to encrypt database backups. And physically secure the room the server is located in. But I would not encrypt the live data.

Clarification: This is assuming what the OP was asking about is actually encrypting the data in the database. Not the file system the database resides on.

Answer 5

If you develop the web application carefully using an effective MVC framework like CakePHP. Zend or Rails, then you should be able to enable/disable encryption at the data Modal level.

CakePHP for example has a few examples of Behaviors for Modal's that encrypt data. Making the process transparent to the Controllers and Views.

Ignoring issues about indexing and other technical database issues when doing this. It should be possible to have this as a configurable option.

Additionally, I would turn encryption on at either a later time or only on the production server. Encrypted data is difficult to debug and work with while developing, and can only be done on certain columns.

Answer 6

Yes, you should encrypt the database.

Since this is personal and sensitive information, I definitely believe it should.

From the password, you can derive an encryption key which you only store for the session. That way, it isn't stored anywhere and nobody (including DBAs) can know it since nobody can know the password either. Anyone attempting to view the DB directly will be looking at gibberish. The only way to corrupt this is via session hijacking but I'm assuming here that sessions are secure as well.

Answer 7

I ask myself why does the client ask you to encrypt the DB? If he would ask you to protect the data I would agree, but he already has a implicit implementation in mind. So as long he doesn't know exactly what he is talking about he is just throwing buzzwords from my point of view.

I also find it very useless to encrypt the DB, because I am convinced that literally every major DBMS takes security into account appropriatly and probably better than you could. In order to acces the DB through the DBMS you would need credentials. In case of a encrypted DB you would need those credentials aswell, and to decrypt the data you would need those credentials you already seem to have.

Following this mindset I propose to let the DBMS handle security, and put efforts in protecting credentials all the way from user input to db access, may also enforce strong passwords and periodical change.