Database encryption or application level encryption?

Question

When you need to store sensitive data such as CCs or SSNs, do you:

1) Build your own encryption routine within the application, define a secret key somewhere in a config file, and then manually encrypt/decrypt data going to the database.

2) Push all the problem to the database, using the built in DB capabilities (I think most vendors call it Transparent Database Encryption).

What trade-offs have you find for your solution? Does writing your own routine perform poorly when compared to TDE? Is code maintainability, or conversely DB vendor lock-in an issue?

Answer 1

I've used a variety of encryption techniques and I believe it is both easier and more secure to encrypt on the application side using a proven encryption routine (i.e. .NET libraries).

If you encrypt on the database, that means the data is sent to and from the database in unencrypted form. This potentially allows for snooping/tampering between the application and the encryption routines on the database. Even if you store the key on the application side, it is still required on the database side to perform encryption. If the database is compromised, your data is at serious risk (just imagine someone running profiler while your application runs).

If you encrypt/decrypt in the application, sensitive data (including the key) is never revealed outside of the application server. Someone would have to compromise both the Web server and database server to access all of your data.

Also, I would highly recommend you not roll your own encryption routine. Chances are you will make a mistake that will reduce the overall security of your solution.

EDIT:

Also wanted to add another factor that will influence your decision. Do you need to query off of that encrypted data? If you encrypt at the application level, you will need to bring the data to the application, decrypt, and work from there. This becomes prohibitive as the data set grows larger - whereas with database encryption you can filter the data before it is sent back to the application.

Answer 2

When you encrypt sensitive data, you are essentially restricting access to those who have access to a key. The problem then becomes one of key management: ensuring only authorized people/systems have access to the key needed to decrypt the data.

You should of course use a standard encryption algorithm, easy enough these days, but what you do need to think about is what threats you are protecting against, how you are going to control access to the key(s), and how you control physical access to the servers.

Using TDE ensures that the contents of a database and its backups are encrypted, with minimal impact for authorized users of the database. So anyone who can access the database server with valid credentials will be able to see the unencrypted data. Also any DBA will usually have access to the key and be able to see the unencrypted data. But a third party who, say, gets hold of an offsite backup won't be able to access the data - which can be important for compliance with regulatory requirements.

On the other hand, if you encrypt in the application tier, you can use a key that is only accessible by administrators of the application server. This potentially gives you more security, if, for example, database server and application server administrators are kept apart (e.g. members of different organizations). DBAs who don't have access to the application server key won't be able to see the data.

In your original post, you talk about hiding a secret key in a configuration file on the application server. On the face of this, it sounds like the security equivalent of hiding the front door key under the doormat. If you do this, you need to think about how you will ensure that unauthorized people can't get access to the key.