Re: how do I protect/encrypt sensitive data in a database?

> So the problem becomes, how can I encrypt all the important fields of the > database so that anyone, even root can't go in and start perusing/linking up > who has what? Working with strings will sort of defeat the purpose of having > fields -- no indexes, no specific function calls (like DATE stuff). The short answer is that you can't without doing encryption on the client side. Root could always install some logging trojan on the webserver otherwise. You could, however, write a Java applet that would handle decryption and encryption on the client side. This would still be vulnerable to someone with write access to the webserver replacing your applet class with a trojaned version. Of course, if you have to worry about this in your workplace, I'd recommend find another job now. Assuming we don't need to go quite that far down the cypherpunk road, the idea you had about using the user's password to encrypt their data works, but only if they're the only one inputting it, which probably isn't true ("Yeah, I have 9,999,999 options. What of it?"). What you need is public key encryption (buy a copy of Applied Cryptography if you aren't completely familiar with this): 1) User creates their account. A keypair is generated; the public key is stored unencrypted and the private key is stored encrypted with their password. 2) Someone creates an option record for a user. The data is encrypted with the user's public key. 3) User looks at their data; private key is decrypted in memory with the user's password and used to decode the option data. However, this still isn't secure against root. Since there has to be *someone* trustworthy in the entire company (did I mention you should quit now if there isn't?) why not set up an OpenBSD box with nothing other than an SSL webserver running on it and restrict the root password to a single trustworthy person? The software cost is zero and OpenBSD is designed to be secure out of the box, so you don't need to spend an excessive amount of time locking the system down. PHP and MySQL are easily installed from OpenBSD's ports system. The only problem is that public key encryption system. You could call an external program like GPG or you could do what I've been thinking about and write a wrapper for the libcrypto functions provided by OpenSSL. Alternately, an even more secure approach would incorporate both methods above and have all of the key generation and encryption/decryption for storage done in a Java applet running on the user's browser. While this would be more work, it's also the most secure. > There has to be a secure way of doing this type of thing in a database > right? How do people like E-Trade and Hospitals and stuff protect records of > their customers/clients? A perusal of RISKS Digest (anyone doing security should subscribe to BUGTRAQ and RISKS Digest) suggests that entirely too many of these places operate on the "Hope nothing bad happens" security model.