Re: Looking for advice on database encryption

"Jonathan Bond-Caron" <jbondc(at)openmv(dot)com> 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