Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

A client has asked me to update his web app. At the last minute, he has expressed some concern about allowing me access to his database (and thus to sensitive client information), because this is the first time we have worked together and he doesn't know me.

As far as I know, there is no way for me to work on this project without having full access to his database. I need to make updates to the database for one thing (adding columns, creating new tables etc). And I need to test the live app when I've updated it and I would be concerned not to have full database access.

Is there a way I can work on his project without having access to his sensitive info?

Many thanks

share|improve this question
1  
I imagine in an optimal situation, you create a script that can be run (even by the client) that does the updates, and you would have already tested it on dummy data. –  Tesserex Jul 28 '11 at 15:51
    
@Tesserex: Testing on dummy data only works if the dummy data is a good representation of the real data, and even then you can get surprises! –  FrustratedWithFormsDesigner Jul 28 '11 at 15:53
    
@FrustratedWithFormsDesigner Could the OP ask the client for "good" dummy data? Maybe a sample of real data with redactions? –  Tesserex Jul 28 '11 at 15:56
1  
@FrustratedWithFormsDesigner: And a good sample including all the inconsistencies left behind by former programmers like stray records and missing mandatory fields. –  thorsten müller Jul 28 '11 at 16:39
1  
@thorsten müller: Of course! Capturing all those cases that "should never happen!", "used to happen but we fixed the app but couldn't change the key constraints", "It was a ONE-TIME data-fix, and shouldn't break too many records" is very important! ;) –  FrustratedWithFormsDesigner Jul 28 '11 at 17:46

6 Answers 6

up vote 10 down vote accepted

Options would be:

  • To mask the "sensitive" data before he lets you in and then unmask it after.
  • He could also move the sensitive data to a separate schema and deny you access, except maybe through a view that either masks or omits sensitive data.
  • He could also delete the sensitive data and then simply reload it.
  • He could give you a copy of the database with the sensitive data masked.
  • He could give you the DDL to recreate the database sans data (except non-sensitive reference data) for your dev/test system.

No matter how he does it, eventually you will need to eventually test with data that is very similar to what is in production, or you could get nasty surprises when you implement. Masking/sanitizing results in testing on data that is not quite what the system will use in the Real World and often the differences are minor/trivial, sometimes those differences matter a lot.

Without knowing the nature of the work you're doing some of these may not be very practical or possible. And of course, if he doesn't have his own DBA, he will have to trust you do do this properly and if you're the one that sets it up, you could still see everything during the set up.

share|improve this answer
    
Thanks so much for your post. He doesn't have any credit card details or anything, he just seems to want to protect the names and details of his clients presumably just in case I sold the details to someone. –  Joe Jul 28 '11 at 16:19
    
In the end, because he doesn't seem to have a DBA and testing and updating the live server will be therefore be difficult, I'm going to try and persuade him to give me full access. –  Joe Jul 28 '11 at 16:21
1  
I think these are good suggestions, but I have the nagging suspicion that the customer doesn't have the technical skills necessary for most of these options. –  jprete Jul 28 '11 at 17:55

If the client doesn't trust you to look at his data, how can he trust you to modify his database?

Sign a mutual NDA and get to work, or seek more trusting clients.

share|improve this answer
    
Happily, we've taken this option :) –  Joe Jul 29 '11 at 18:44

What you need to have is a testing environment.

Duplicate the schema of his database and populate it with fake data. Test against that, with your changes. Then when you're finished, you encapsulate your database changes in a single script that contains an update procedure to bring the database from the old version to the new version.

share|improve this answer
1  
Won't work. You don't know what madness lies in the production data unless you look at it. –  Christopher Mahan Jul 28 '11 at 16:01
    
@Christopher Mahan: I guess you're right. I was thinking of magical unicorn land where there's a spec for what data will be stored and what it has to conform to :P –  Daenyth Jul 28 '11 at 17:38
    
@Daenyth: Don't give up on your idea so quickly. You could dump the live data along with the schema, import it to a new db, and run a sanitizing script to replace sensitive info with fake data without losing the shape of the original data. Certainly the madness referred to by Christopher Mahan isn't the actual email address of Client number 452. –  grossvogel Jul 28 '11 at 17:50
    
@grossvogel - good suggestion, except that I'll wager he doesn't have anyone who can write such a sanitizing script, except for the OP, who the data needs to be sanitized for! :-) –  Carson63000 Jul 29 '11 at 4:21

This depends on many factors.

Is there anybody with access to the database who could run some scripts who has enough knowledge to do this in a reliable way? For example the company which hosts his servers or his IT department?

In this case you could provide the scripts and they could run them (after doing a backup first). They could also provide you with some sample data, so you have the structure of the database but without real data in it.

This could work as long as the data is consistent and there are no errors. It becomes far more difficult if part of your work would be to clean up errors.

share|improve this answer
    
I don't think he does have a reliable DBA, so I would need access just to run the script. Also as you say, and other people here too, I really need to have a look at the current data to make sure my code handles all the possible formats and data. Thanks very much for your post. –  Joe Jul 28 '11 at 16:25

My advice: tell the customer no.

share|improve this answer

There's probably no need to have actual data. In fact, for development purposes, you should be able to use a sanitized subset of the data. Everything should have the same characteristics as the actual data in terms of formatting, but it doesn't have to actually be the real data set.

If the data is truly sensitive in nature, integration would probably be performed in-house by technical staff. If any errors arise, they could sanitize log files and provide them to you should they need guidance.

In the event of you needing to perform the integration, it becomes much trickier. But if you have been hired to do the job, then the person paying should trust you. They should also have legal backing (NDAs and the like), but if they don't trust you to do what they hired you to do while maintaining the ethical and legal high ground, why did they hire you?

share|improve this answer
1  
You definitely have to have a chunck of data that is the same size as the production data. updating a 500,000,000 rows table isn't the same as updating the same table with 10,000 rows. –  Christopher Mahan Jul 28 '11 at 16:03
2  
@Christopher Not true. For performance tuning and evaluation, yes. But for developing code to work with a given schema, absolutely not. You just need enough data to account for variations in terms of content of each field. Once the software has been developed and tested against that, performance tuning can be done against a more realistic data set. –  Thomas Owens Jul 28 '11 at 16:57
    
My example was poor. How do you know you need to shard unless you see the data? How do you know what changes to make to the data structure unless you know how it affects the whole? –  Christopher Mahan Jul 28 '11 at 18:12
    
@Christopher That's a problem at the system administration and DBA level, not for the application programmer. Any such changes should be invisible to the application. The only time you might have to worry is if you are defining your own abstraction layer. And why are you reinventing the wheel, instead of focusing on building software to solve your client's problems? –  Thomas Owens Jul 28 '11 at 18:14
    
Ah, but the OP said he needs to "adding columns, creating new tables", so he's no longer in front-end-land but in DBA-land. –  Christopher Mahan Jul 28 '11 at 18:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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