"Jonathan Bond-Caron" <jbondc@xxxxxxxxxx> wrote: > > On Thu Apr 16 05:06 PM, Bill Moran wrote: > > > > The problem comes when the company head wants to search through the > > database to find out which employee has a specific SSN. He should be > > able to do so, since he has access to everything, but the logistics of > > doing so in a reasonable amount of time are rather complex and very > > time consuming. On a million rows with the SSN unencrypted, such a > > query would take less than a second with an appropriate index, but > > pulling those million rows into the application in order to decrypt > > each one and see if it matches can easily take a half hour or longer. > > > > That's where we're having difficulty. Our requirements are that the > > data must be strongly protected, but the appropriate people must be > > able to do (often complex) searches on it that complete in record time. > > Would storing a one-way hash of the SSN work for you? i.e. combine sha1 > and/or md5, use a salt... > > SELECT ssn_encrypted FROM employees WHERE ssn_hash = > yourhashmethod(SSN_PLAINTEXT) > > So you have both an encrypted version of the SSN and a one-way hash of it. > > That's how we store credit card numbers. We're considering that for some fields. It does limit a lot ... we can't do partial matching for example. Other fields don't work so well. If I try to use that trick on a field that has too few choices (true/false is the worst, but anything with less than a few thousand possibilities, i.e. "county of residence" is a problem) then I've left it open to an easy dictionary attack. Thanks for the input. -- Bill Moran http://www.potentialtech.com -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
