Re: Search encrypted column in SQL 2005http://msdn.microsoft.com/w

"Brian" <Brian@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:16C7E7ED-37A2-403E-9982-6D9465FA83AF@xxxxxxxxxxxxxxxx
Thanks for all your advise. It helps to understand the encryption and
searching are in opposite goal.

As HashBytes() defeats the purpose of the Random IV generated during
encryption process, I'll look at Raul Garcia's proposed MAC indexing
method.
It could help to improve the searching. Raul's method is a bit complex
and I
will need to study in order to understand it.

To clarify the search function on CC#, we need to provide a partial or
exact
CC# search for our security department to find the suspicious transaction.
We print the last 4 digits of the CC# on the transaction along with the
transaction amount. To identify the CC#, they will need to perform the
CC#
search along with the date/time and transaction amount from our system.
We
even have FBI came in to investigate fraudulent credit card activities.
This
will be the only system that we can provide them a way to recall the
entire
card number. For now, it will be slow to decrypt all this column since we
are required to encrypt them. I will check with Raul's method to see if
we
can improve the search for them later.


From what you say, it sounds reasonable to assume that your partial CC#
searches will be performed only on the last 4 digits of the CC#. If so,
storing a hash of the last 4 digits in another column can speed up your
searches, and should not compromise the security of your encrypted CC#
column. If, however, you need to perform searches on the middle 8 digits of
a CC# or something else "outside the norm" like that, you're in for some
serious performance headaches.


.

Quantcast