I have some experience in building PHP based websites with MySQL access, storing encrypted user details, but all other fields being plain text. My most recent project will require sensitive data to be stored in the database.
I want to set up a system where a user can access his own entries and see the plain text results, but even if he was able to access someone else's they would be encrypted, unintelligible strings.
I have an idea of how to accomplish this, but perhaps it's not optimal or there exist tools to do this already in a more efficient way.
1.) Store username as plain text, and password hashed with sha1() or better hashing algorithm plus random salt etc.
2.) Take the user's password (not the hashed one, but the one he typed in) and use it do define a key specific to that username and password, which will then be stored as a session variable. Since the key is never stored anywhere except in the user's head (in the form of him knowing his plain-text password, which will then be converted to a key somehow by mixing it with his username and some salt, hashing etc, as he inputs it to log-in) this should be a good solution right?
3.) Encrypt, or decrypt all of that user's data with that key.
In my opinion even if someone gained access to the database and saw a list of plain text usernames and encrypted passwords they couldn't figure out the specific key since it's not stored anywhere. Therefore even if access was gained, they couldn't decipher the content of the sensitive database fields. Of course I'm building in ways to stop them accessing the database anyway, but as a catch-all effort this seems like a good set of steps.
Could the experts please comment on this, and offer some advice? Thanks a lot.
I posted this in the programmers.SE forum and they advised me this was a better location for the question. More specifically why I shouldn't do this myself. If so then what alternatives are there?