Encrypting data within the database(*) severely limits its usability - you can't use it in queries, etc. In some cases it's not a problem since you'll never want to use it in a query anyway, or you can use a proxy(**). But as a general rule I think if you're encrypting much of your data then a traditional database isn't the right solution to your problem.
(*) the underlying filesystem and should still be encrypted. The backups should also be encrypted - a lot of people forget to do that.
(**) for instance you might consider the person's email address to be sensitive information that should be encrypted but you still want to be able index the field so you can perform a rapid lookup. In that case you can add a salted hash of the email and index that. Your app knows how to perform the same hash so it can quickly find the record but it's totally opaque to an intruder.
It's important to use a salted hash since an unsalted hash is no longer secure since a knowledgeable intruder probably already has a list of emails from other attacks and can easily compute the values to check. At the minimum a salted hash is something like sha1(email + "my secret") (NOT sha1("my secret" + email)) but you should really use one of the standard algorithms to convert a passphrase and salt into an encryption key. (PBE2K?) For performance reasons you might not want to perform all 1000+ iterations required for an encryption key but it's important to use a standard algorithm since it's really easy to create hashes that aren't nearly as strong as you think. E.g., there's a huge difference between hash(value + salt) and hash(salt + value).
In this case the salt has to systemwide, or at least easily computed given the email address but not derived from it (e.g., you can use the last few digits of the hash of the email address as the index into a lookup table but don't use the hash itself.) In most cases it's best to add a 'salt' column to the record, perhaps in a shadow table that's not obvious to an intruder, but you can't do that with anything used in a lookup since you have no idea what value to use.
On Mon, Aug 6, 2018 at 7:19 AM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
On Monday, August 6, 2018, <bejita0409@xxxxxxxxxxx> wrote:I have a request for revoking the access to user's data from DBA-user.I think the request is right because users should be the only ones can access their data.User then needs to encrypt data prior to storing it. Superuser can still access the data but would be challenged to make sense of it,Usually DBAs are tasked with backups which requires read access to all relevant data.David J.
