We're building a cloud web app backed by a multi-tenant db. To maximize security we plan to encrypt each customer's data with a separate key. The problem is querying. Say they're in the report writer and want to sort their 1,000,000 customer table by LastName. That would perform horribly because all 1M rows would have to be unencrypted and then sorted on every query!
Here are the only options I could come up with. What would you do? Are there other options?
Only encrypt NPI columns in the db and then never allow them to sort or filter by NPI values.
PRO: Protects sensitive data while still providing great query performance on non-NPI columns
CON: Not a great UX for our users
Same as #1 but keeps an unencrypted cache of NPI columns in RAM
PRO: Better UX
CON: Now DataInUse is unencrypted (is that any better than DataAtRest unencrypted??). Also, filters with NPI and non-NPI columns would be very ugly to code. For example, if the filter was SELECT ALL CUSTOMERS WHERE LASTNAME LIKE "S%" AND BIZTYPE="MEDICAL", the lastname record ID's would have to first come from the cache and then sent to the database to further limit records by biztype.
Give up and keep all data unencrypted in the database and apply db encryption (SQLServer TDE) or disk encryption.
PRO: Great UX and performance
CON: Performance would suffer. Also, if the key were compromised all of our customers' data would be exposed and we'd be on the cover of the Wall Street Journal.