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.
