Database encryption

Question

I have a database that contains user details including sensitive data. They're not as sensitive as financial, but they are sensitive nonetheless. The passwords to the accounts are hashed and salted but the rest can only be encrypted not hashed to allow editing.

How far would you go encrypting the fields? Would you go as far as encrypting everything including generic fields like username, first name, last name, or only fields like address and phone. The first name is used frequently after the user logs in.

Can someone suggest an algorithm (with sample code if available) to encrypt the fields? I use PHP and MySQL primarily.

Answer 1

I wouldn't encrypt the fields at all since it's going to be a royal pain in the rear end :-)

I would instead move sensitive data to a separate table and use the security features of the DBMS itself to protect the data while still allowing access to the non-sensitive data.

In other words, have two tables (user and user_sensitive) tied together with a userID column. Let anyone peruse the user table to their hearts content but access (of any sort) to user_sensitive is restricted to admin-type bods).

And, if my DBMS didn't provide such facilities (I do not know whether MySQL does), I would move to a DBMS that did.

If you want a user to have access to their own sensitive data but not that of other users, we once implemented such a scheme in DB2 by providing a stored procedure. It retrieved all the desired rows but also checked to see which user was executing it. For rows that didn't match that user, the sensitive information was blanked out. The underlying table was fully protected from everyone except the stored procedure itself.

In order for that to work, you would have to be able to run the stored procedure under a different user from the one invoking it. Whether that's possible under MySQL, I have no idea.

Answer 2

I'd google for "transculent databases" - there are both printed books on the subject and some on-line resources.

There are variations of this method but basic idea is to:

1. encrypt only sensitive fields
2. encrypt with key from data only the user knows of (like login/password pair)

Password ofc must not be clearly saved in any table. Keys should be held only for session. This way the attacker doesn't have the means to decrypt information whether the database and/or the application is compromised (forgetting for a moment possibility of modifying app code and silently gathering keys).