4

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.

  • 1
    Could you fulfill the policy by running the database on an encrypted filesystem? – Philipp Feb 26 '14 at 13:22
  • I think this would be more fitting for workplace.stackexchange.com as a general "How to explain to the management that a nonsensical technical requirement makes the project impossible" question. – Philipp Feb 26 '14 at 13:25
  • I'm not familiar with that, so let me explain what I can to perhaps guide your question towards an answer. The Database is created and mapped by a DBA / Data Architect. It is then put out on a shared server BY them, for me to utilize in my development. – Mark Feb 26 '14 at 13:25
  • My hopes in posting here were that others have had a similar experience and were able to prove that it is indeed the wrong route to go, and in return could provide me with their solutions or recommendations! – Mark Feb 26 '14 at 13:27
2

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.

  • Thanks for the reply! All very good information. I've decided to introduce a 'Threat Model' approach to distinguish between PPI and Non-PPI. However, you say that looking up a date encrypted is not an issue - but you are incorrect there. Sending values to be encrypted requires that 'value' be converted to type string. That being said, a WHERE clause is useless, because it's impossible to query a 'range' of strings when they are encrypted, rambled jibberish within the DBMS. – Mark Mar 1 '14 at 4:08
  • I tried to be clear - looking up A date is an equality operation, and the index works on the encrypted data. Looking up a date range is based on inequality operations, which are prohibited. This leaves you with either multiple equality operations, i.e. WHERE date IN (...), or pulling the whole column and key into the app, decrypting it, and searching within the app. Note also that if you're storing strings, you're wasting a lot of space - after encryption, you have binary data, so use a BINARY() or VARBINARY() column! – Anti-weakpasswords Mar 1 '14 at 4:17
  • It's been a long day, haha. Thanks for clearing it up. I guess I would store query results in a data table, loop through the data table and decrypt all fields, then run a query on the decrypted datatable? Is that possible lol – Mark Mar 1 '14 at 4:20
  • 1
    To pseudocode it for general languages, from the app's perspective: YourLanguageData = SELECT Key, EncryptedDates FROM table; FOR EACH Date IN YourLanguageData Date = Decrypt(YourLanguageData.values(EncryptedDates)); FIND(YourLanguageData.Date BETWEEN X AND Y); This is quite effective on tiny tables, quite effective on read-only tables where the app can do this once at the beginning and cache the results in RAM for the rest of the session or sessions (plural, preferably), and not at all effective if the table's data is "too large" compared to the RAM the app has available except disk cache – Anti-weakpasswords Mar 1 '14 at 5:55
  • Very interesting approach. I will have to try this! Thanks for the comments – Mark Mar 1 '14 at 15:34
4

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.

  • I like the "threat model" approach. I think that's a great idea. Not sure how they will respond ;) – Mark Feb 26 '14 at 15:59
0

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.

  • The unfortunate reality is that it's coming down from upper management to "encrypt everything." So, we are really in a bind because explaining this to the business users really isn't going anywhere. Performance is not a concern, for whatever reason. They don't seem to want to compromise, or they don't want to do any work on determining what should/shouldn't be encrypted. – Mark Feb 26 '14 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.