Multiuser System With Encrypted Database

Question

I am currently developing a hosted solution in ASP.NET using MVC3 and Entity Framework. This product will then be made available to a number of clients as a hosted solution.

As the data stored by each client will be critical to their business, we anticipate that many of them will insist that the data is stored in an encrypted form in the database. We are therefore designing the system with this in mind.

Are there any recommended procedures for doing this? How would one go about encrypting the clients data in such a way that even us with root access to the database cannot possibly access their data?

I believe there is no built in support in EF for encrypted data, is this true? And if so, how would one encrypt/decrypt the data?

Answer 1

I'd recommend taking something like the jTDS driver and adding encryption to it. You would have to have some method of mapping client names to encryption keys, but that shouldn't be hard. And this way, you don't have to do anything in your application itself -- all the encryption and decryption is handled by the driver. Doing it this way also means you can use third-party database management tools to examine the database -- just make sure they connect to the database using your driver. It's a non-trivial task, but I think it offers a nice clean solution. You can do your development using a standard driver, so you can still look at the data once its in the database, and then switch to using the encrypting driver in production.

Answer 2

.Net has encryption libraries

Give each Client their own encryption key, that is stored on there servers. The Client encrypts the data then sends it to you for insertion. The DB returns encrypted Data that the Client has to decrypt.

Problem is... You can't help the client if they lose the Key, NOBODY CAN.

But that seems to be the business case (from what I am reading) so...

(You can also encrypt the data again after the client sends you the data so there is essentially a "2 Key" lock on the data.)

Answer 3

As far as preventing access, database schemas could be useful. Schemas could be created and access could be granted to specific database users, which exclude god level accounts such as SA and so forth. When the database is created, the creation scripts could be dynamic to allow for it.

Example:  Database1.ClientX.Table1
          Database2.ClientY.Table1

If a database user wasn't given access to the ClientX schema, then they couldn't see any of the database no matter what thier privilege level to the DBMS system was. Of course a user was high privilege could give themselves access to that schema, but I assume there is some trust given to the few DBAs who actually have those permissions.

As far as encryption, probably some sort of secure middleware layer would be the best. Each middle ware layer could have a different private key, so they couldn't decrypt each others data. That way you wouldn't have to store keys on each client, but on the middle layer, maybe in a config file or something.

The keys to encrypt the data would probably been encrypted as well so if any one got access to the server they could just grab them and connect up to the middle layer and see everything. Who has access to those keys, someone who is trusted.

Client X Data- MiddleWare Layer X Encrypt/Decrypt- Database X
Client Y Data- MiddleWare Layer Y Encrypt/Decrypt - Database Y

A separate DB instance would be the most secure.

Answer 4

In a purely technical sense, encrypting the database such that the admins can't read the data is pretty tricky -- somewhere something has to decrypt something to show it to users so the key tends to live somewhere in the system so someone with root access could always decrypt the data.

Insofar as encrypting the data, if you can swing an Sql Enterprise license Sql server could do it for you. But that really doesn't get you much unless your database server is on an untrusted network in the first place. I would focus my efforts on designing the app so each customer had a separate database as well as building a strong audit trail to discourage nefarious behavior.