What is a good rebuttal to users who wish all data WITHIN Db be encrypted?

Question

I am building an application in VB.NET (WinForms) utilizing SQL Server 2008 R2 as a Backend.

The application is to track employee's money they defer each paycheck, or month, to deposit into separate 'fund' accounts. (Think of it like putting $200 of your weekly check into a few different stocks).

The business side says "ALL DATA MUST BE ENCRYPTED!" which makes no sense to me, and makes basic queries (Especially for dates) unusable. That makes the utilization of a RDMS futile.

The only fields I would deem necessary to encrypt would be:

• Employee Information (Name, DOB, Hire Date, Address, etc.)
• Employee Beneficiary Information (Name, SSN Address)
• Account Number for in-house that could be tied back to an employee via payroll

Nothing else encrypted (In my opinion) does not deem these people's information at risk.

What can I tell business users to actually make them hear me out and believe me? These are executives / officers within the company, so a respectful tone would be appreciated!

*Edit:*The information is to be encrypted so DBA's cannot see it, even if they have access to it. I've already tried to bring up TDE. It is not an option.

Answer 1

I am building an application in VB.NET (WinForms) utilizing SQL Server 2008 R2 as a Backend.

*Edit:*The information is to be encrypted so DBA's cannot see it, even if they have access to it.

Well, that narrows things down - assuming the DBA's have full access to the communications coming in and out of the database server, this indicates that to fulfil the requirement, the encryption must be done prior to arrival of data to the database, and decryption must be done after departure from the database.

Therefore, we can consider the following:

• Some application or middleware layer must handle the encryption and decryption.
  • Talk to the DBA's! See what they think about it.
    • If they agree with you that it's a bad idea, then work with them to communicate your shared concerns to management.
  • Note that encryption does not prevent indexes from being useful for seeks on an equality - a database index is just as useful to look up "Bob" or "Jan 1, 2010" as it is to look up "51hsnGL58Uz" or "n8DfUp9vvJJECT". It's inequality operations that turn into:
    • Can't do it effectively/efficiently
    • Generate a list of all the options within the range/inequality, encrypt each of them, and then do an equality operation using indexes.
    • Download the entire dataset to the app (caching it if there's enough RAM available), decrypt, cache it all if you have the RAM, then do operation locally.
    • Have aggregated data tables/dimensions that the application keeps up to date "often enough" for that kind of (date) range based queries/reports.
• If at all possible, help the DBA's set up a proof of concept - and I do mean genuinely help them.
  • Also work together to load it up with the "expected" amount of data you'll see in 2-3 years, and then work together to do performance stress testing.
    
    • Either you're right, and it won't handle the load (provably so), or you're wrong, and have nothing to worry about.
• Be clear about the limitations fully encrypted data you aren't allowed to see place on you to the business.
• Be clear about the limitations fully encrypted data you aren't allowed to see place on the DBA's to the business (i.e. no more help from them figuring issues out, finding bad data, or fixing bad data).
• Be clear about added costs, added time for the project, and added risk, included but not limited to disaster recovery, restoring backups, having to change encryption keys when they get leaked/the DBA's find them/requirements change/someone who had them is no longer employed, etc.
  • Properly done encryption means if the keys are lost/corrupted, and not backed up, your data is gone. This means key management is a very big deal.

You can and should ask the business about:

• The business goals that this requirement is intended to meet (i.e. the threat model @ColinCassidy mentioned).
• How those goals interact with other business goals that it may affect, such as performance - i.e. priority and resources.
• Legal requirements, regulatory compliance, auditing requirements, or industry standards that may be driving this.
  • If any are, ask for references to the original requirements - read them carefully, because they are important.
• You as the developer will either be able to see the decrypted data, as you'll have the keys, or you will not have the keys and will not be able to effectively investigate possible bad data in production.
  • i.e. "Why does Bill's fund have $520.15 in it? That's wrong!" gets answered with "I can't see the data, nor can the DBA's. I'm sorry; please replicate it in a test environment where I or the DBA's can see the data."

On an office politics note, either you're dealing with legal or regulatory compliance issues (in which case it's normal enough), or you and the DBA's should update your resumes, just in case they really simply don't trust you folks.

Answer 2

See what you have there is a solution looking for a problem.

You should start by asking the business what problem is it they are actually trying to solve. you most likely want to frame the response in terms of CIA (Confidentiality, Integrity, Availability).

If you are feeling really brave try to get the business to perform a threat model on the system, you may find that data confidentiality is not necessarily your biggest risk.

Once you have that information you can come up with real mitigations to the real problem that do not necessarily include encrypting the whole database. e.g. for confidentiality you can use good authorisation/access control lists. For data integrity you can consider using data hashes or MACs (you would also want to consider strong auditing to see who is using/changing the data). For Availability you are looking at typical IT solutions for this, data backup and restoration, server redundancies etc.

Answer 3

Ask them to rank a list of requirements in order of importance. Include security requirements, such as:

#1. DBAs cannot see employee identity (name, DOB).

#2. DBAs cannot see transaction details (date, amount).

and performance requirements:

#3. Answer search query of form a in b seconds.

#4. Answer search query of form c in d seconds.

If they rank the requirements in the above order, draw a line under #2, and tell them that is all that is possible.

If they put any performance requirements ahead of #2, draw the line above #2.

It is then their responsibility to work out whether to compromise. If they still decide #2 is more important than any reasonable performance, build it, and keep all evidence that that is what they asked for.