32

We have a few givens:

  1. Developers need a replica of the production database on their machines.
  2. Developers have the password to said database in the App.config files.
  3. We don't want the data in said database compromised.

A few suggested solutions and their drawbacks:

  1. Full-disk-encryption. This solves all problems, but degrades the laptop's performance, and we are a start-up, so don't have money for powerhorses.
  2. Creating a VM with encrypted hard disk, and store the database on it. It works well, but it doesn't help too much, since there's a password in Web.Config.
  3. Solution number 2 + requiring the developer to type the database password every time he runs anything. It solves all problems, but it is really cumbersome for developers that sometimes fire up the application multiple times a minute. Also, we have multiple applications that connect to the same database, and implementation of a password screen will have to differ in each.

So, my question is, if there's any common solution to such problem, or any suggestions on how to make any of the above solutions workable?

Improve this question
16
  • 26
    Have you actually measured the performance impact of full-disk encryption. I've been using it on fairly old laptops and didn't recognize any significant performance degradation. Modern operating systems are pretty good at caching and disks are slow anyway. The worst impact is probably on your battery life-time.
    – 5gon12eder
    Commented Nov 19, 2015 at 16:11
  • 69
    To be honest, this doesn't sound like the right approach. 1) Why do devs need a production database on their machines? Isn't there a way to create dummy data for a dev db? 2) Why is the password stored in plain text in a config file? You're trying to put a bandaid on a flawed process, it seems. Perhaps you can revise what actually lives on the dev machines as well as how the password is stored for the database.
    – Thomas Stringer
    Commented Nov 19, 2015 at 16:14
  • 2
    There are reasons for developers to need production database. For historical reasons their work is too coupled with with the live data. I know this is a bad idea, and if we find no good solution, we will move to dummy data. For now I'm trying to find a good solution without that.
    – Svarog
    Commented Nov 19, 2015 at 16:20
  • 6
    No user of a MacBook Pro could tell you from the speed of the machine whether full disk encryption is turned on or off on an SSD drive. There's no difference. None that you can notice. Maybe one you can measure, but nothing that is noticeable.
    – gnasher729
    Commented Nov 19, 2015 at 16:55
  • 5
    I second @gnasher729's comment. Having used full disk encryption for many years in regulated environments (financial and healthcare) it does not have to be a noticeable drain on performance. Lots of people bring up other valid points, but in a HIPAA environment it's hard to have a reasonable policy without full disk encryption, even if databases are not placed on notebooks. Emails and other fragments of data often wind up there anyway. Swap files....etc...Full Disk Encryption is not adequate, but it's usually necessary.
    – joshp
    Commented Nov 19, 2015 at 19:34

6 Answers 6

Reset to default
99

Not only do you not want a copy of the production database, it may actually be illegal. For example, in the US, you cannot move production data out of the production environment if it contains regulated information like personal health data, financial data, or even data that could be used in identity theft. If you do, you could be fined, lose your compliance standing and therefore be subject to more aggressive audits, or even be named in a lawsuit.

If you need production-scale data for testing, you have a couple options:

  1. Generate all dummy data. This is trickier than it sounds. It's surprisingly difficult and labor-intensive to generate sensible imaginary data.
  2. Anonymize your production data. This may be easier, but proceed with caution.

For option #2

  • In the production environment, an authorized database admin makes a copy of the production data.
  • Still in the production environment, the same authorized admin runs a routine that anonymizes all sensitive data. If in doubt, anonymize.
  • Only then should the data be moved to another environment.
Improve this answer
21
  • 31
    and the password to the database copy should not be the same as that for the production version.....
    – Lightness Races in Orbit
    Commented Nov 19, 2015 at 16:36
  • 3
    "For example, in the US, you cannot move production data out of the production environment if it contains regulated information" What? Do you have a source for this? Can you not, for example, use backups of a production database as data for the staging environment, or as testing dbs on developer machines?
    – nanny
    Commented Nov 19, 2015 at 16:38
  • 12
    @nanny Not if you're using regulated data. For example, I've worked under HIPAA regulation. HIPAA states "Covered entities also must implement reasonable minimum necessary policies and procedures that limit how much protected health information is used, disclosed, and requested for certain purposes." A minimum necessary policy is open to some interpretation. Our legal counsel suggested a strict interpretation that kept sensitive data contained where developers could not access it. (Is it truly necessary for them to do their jobs?) The same caution applies to financial compliance like PCI.
    – Corbin March
    Commented Nov 19, 2015 at 16:47
  • 4
    @nanny Take this as hearsay from a non-lawyer, but as I understand it, the rules vary greatly by state. The legal counsel I've work with always errs on the side of caution. Strictly speaking, devs don't need actual SSNs to perform their duties, so counsel suggests those SSNs live in a protected environment where devs can't access them. Don't listen to me, though. A lawyer looking out for your long-term interests will be the best resource.
    – Corbin March
    Commented Nov 19, 2015 at 17:20
  • 5
    Strictly speaking, it is not ILLEGAL to be careless with PPI, unless you're doing government work, then Title 32 comes into play...but it is a serious civil liability exposure. this is otherwise a great answer though. upvoted.
    – dwoz
    Commented Nov 19, 2015 at 17:45
9

Can you at least give the developers VMs in your datacenter that they can RD into for this work? While they really should be working off of non-production data, this would be safer until you can get there since the data wouldn't be stored on easily stolen laptops.

Improve this answer
7
  • this reads more like a comment, see How to Answer
    – gnat
    Commented Nov 20, 2015 at 5:57
  • 5
    @gnat, this answer may be short but it is a very good suggested alternative.
    – user82096
    Commented Nov 20, 2015 at 7:38
  • Don't be such a pedant, @gnat...this is a fine answer.
    – dwoz
    Commented Nov 20, 2015 at 23:39
  • @dan1111 That's the problem. It's not an answer. It's an alternative. That makes it a comment, not an answer.
    – corsiKa
    Commented Nov 21, 2015 at 7:42
  • 2
    @corsiKa, answers that challenge the premise of the question are allowed and are often very good answers. See XY problem: meta.stackexchange.com/questions/66377/what-is-the-xy-problem. And more detailed answers may be better, but this is still an answer.
    – user82096
    Commented Nov 21, 2015 at 21:37
7

Change your way of working if possible.

As others have pointed out:

  • Using production data for development is not good practice.
  • Having a password stored in plain text is not good practice.

Both of these expose you to significant risk and should be changed if possible. You should at least seriously assess what the cost of making these changes would be. If this is an external dependency that you don't have power to change, consider raising this as a concern to whoever does have that power.

In the real world, though, it may really not be possible to change this. Assuming that what you are doing is legal, you may have to live with this arrangement (at least temporarily).

If this is really necessary, you just need to do full-disk encryption.

Given the risks, you need to use the best-available security option, and this is it. If there is a performance hit, live with it. It is a cost of working with sensitive data.

If I were your customer, I would not be impressed that you decided not to use the best available security option with my data, because it made your laptops slightly slower.

Improve this answer
3
  • 1
    "Not an ideal solution" should be changed for "a completely stupid idea" IMO
    – Darkhogg
    Commented Nov 20, 2015 at 14:34
  • @Darkhogg, you're right it should be stronger. Edited. I would not go so far as "completely stupid" without knowing how sensitive the application is. Practically speaking the risk of compromise is very, very low if using full disk encryption, so it is possible to make too much of this as a security issue.
    – user82096
    Commented Nov 21, 2015 at 6:58
  • I agree with the first point, but not the second. If you are not storing your password in plaintext where are you storing it (1) ciphertext or (2) your brain. If (1) then where are you storing the password for the cipher (infinite loop detected). If (2), then I hope you like waking up at 2:00AM to type in the password to restart the service.
    – emory
    Commented Nov 21, 2015 at 15:25
1

Corbin March's answer is pretty good, I will just add an additional detail, that you generally have two classes of data in your production database: system/application metadata; and client user data/transactional data. This latter should NEVER be used in a development environment "as is."

It's very rare indeed that you need actual production client information to do development with.

However, if the problem that the OP is describing here involves trade secret data or otherwise highly proprietary system data that doesn't involve customer data, that is required by devs...the security approach has to involve a scheme that doesn't have the db password kept in cleartext in a resource file somewhere. There needs to be a mechanism to, for example, regenerate a daily password, that isn't stored on disk.

Improve this answer
6
  • 5
    client user data/transactional data... should NEVER be used in a development environment "as is." -- This sounds unworkable to me. Production-related programming issues having to do with a specific client's data would be unsolvable under this arrangement. Further, actual live data is extremely useful from a testing standpoint. The privatization or anonymization effort should be focused solely on the data that is specifically regulated.
    – Robert Harvey
    Commented Nov 19, 2015 at 18:58
  • @RobertHarvey, it's only unworkable if you can't reproduce the production issue in a dev environment. I think across my career (long) I can count on a couple fingers the number of times that appropriately sanitized test data wasn't adequate for reproducing a production bug. "Proprietary Business Information" goes far, far beyond SSNs and CC numbers!
    – dwoz
    Commented Nov 19, 2015 at 19:11
  • 4
    But if you're going to go down that route, you're going to have IT people that can't do their job because they don't have Administrative access to everything. I admit that causes potential Snowden problems, but I don't see a workable alternative, other than to hire people you can trust. Sarbanes Oxley and HIPAA are very specific about what kind of data needs to be sequestered, and it doesn't include "all production data," not by a long shot. That said, I don't believe production data of any kind should ever exist on roaming laptops.
    – Robert Harvey
    Commented Nov 19, 2015 at 19:14
  • 1
    -1 for NEVER. Your more nuanced comments are better than your answer; you should edit them into it.
    – user82096
    Commented Nov 20, 2015 at 7:37
  • 1
    @dan1111 we can agree to disagree then. Customer data "as is" should NEVER NEVER NEVER be used in dev systems. It should always be sanitized. You don't believe this because you've not yet been bitten by this rabid mongoose...and that's what it is, when it happens. A rabid insane rodent that intends to draw your blood. Take my advice, avoid the rabid mongoose.
    – dwoz
    Commented Nov 20, 2015 at 23:38
1

You don't state which database and which environment.

If you can use integrated security then the database is not accessible without logged in as that user. Yes if the data is on the hard disc it can be hacked but this is a first level defense.

App.config makes my think this may be a .NET. Put config in a thumb drive and read it from the thumb drive. If the drive is not present then make the user type in the password.

Is there a way to store the password in memory the first time it is entered and read by all. Again you don't state the environment. Memory-Mapped Files

With some TDE you can store the key on a separate device so they just supply the key when the database sever is started.

Improve this answer
0

One possible option is to make a copy of the database and scrub that copy with a script so you end up with different data than what is actually in production. You won't end up with the same data as production But you will have the same scale.

Improve this answer
1
  • this seems to merely repeat point made and explained in top answer about a week ago
    – gnat
    Commented Nov 24, 2015 at 21:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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