Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
1  
I'm more of a client-side dev, but I suspect encrypting everything would actually make the data less, not more secure if you're using the same key. –  Erik Reppen Nov 30 '12 at 4:12
4  
can you put the entire database on an encrypted volume and call it a day. Sure reads/writes will be slower, but you keep all advantage of RDMS (or whatever you are using), while the data on disk is in encrypted –  DXM Nov 30 '12 at 4:59
1  
That also means you wont be able to see data in mysql workbench? All the best for debugging. –  Manoj R Nov 30 '12 at 7:11
2  
Medical is a highly regulated industry. The professionals working there are use to having someone tell them what the rules are. This mindset tents to spill over onto IT projects. It's not really a security issue. It's a cultural issue. The cost of doing business in the medical field. –  Mathew Foscarini Nov 30 '12 at 8:57
1  
Let me tell you about medical data, short form, for the layman: Encrypt the damn data. Believe you me: Your client's lawyers are waaay more likely to be correct about this than you unless you're a lawyer yourself (IN which case, why ask us?) –  medivh May 26 at 13:44

6 Answers 6

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.

share|improve this answer
1  
I am not sure if this is the case. The question is not about encrypting the database, but about encrypting the data in the database. That means the data in sql queries will be encrypted. –  Manoj R Nov 30 '12 at 6:45
    
The performance hit should be small? Searching on data will be SLOW. The entire concept of indexing does not work when data is encrypted. It will require a full-table scan. –  mike30 Nov 30 '12 at 22:10
    
@mike The approach above would encrypt the volume and would not impact indexing etc. –  jdigital Nov 30 '12 at 22:18
    
IMO you need more expertise than you can get here. IANAL, but I think your client has pretty high exposure if this data is compromised. –  kevin cline Nov 30 '12 at 22:38

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.

share|improve this answer
    
totally agree, yet LAWS doesn't :( –  Zalaboza Feb 12 at 11:34
1  
Well you could AES_DESCRYPT('') LIKE 'Smith%' albeit incredibly slow. Or you could get intense and make an inverted index with salted hashes –  foochow Mar 10 at 20:29

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.

share|improve this answer
    
IANAL, but IMO laymen should not "check the laws" when the possible liability is large. They should consult an attorney. –  kevin cline Nov 30 '12 at 22:39
    
i go with this approach as true answer.. medical records that is not linked to patients nor doctor is meaningless and unusable even for statistical analysis as it has no reference nor prove that its not made up. –  Zalaboza Feb 12 at 11:40

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.

share|improve this answer
    
+1: without a threat model, you are most likely locking the front door but leaving the back door wide open. –  kevin cline Nov 30 '12 at 22:40

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.