Re: Search encrypted column in SQL 2005http://msdn.microsoft.com/wn3/a
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Sat, 27 Jan 2007 10:38:50 +0000 (UTC)
Brian (Brian@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
Can someone provide me a sample of how to search encrypted column? Using
below method seems pretty slow.
select FirstName, LastName,
convert(varchar,decryptbykey(SSN)) as SSN
from PatientTable
WHERE convert(varchar,decryptbykey(SSN)) = '123-45-6789'
Yes, if you encrypt key columns it will be very very slow. There is no
way an index can be used, and the decryption is expensive too.
Take a look at the HashBytes() function. This gives a deterministic
hash of the key value, which is good for quick key lookups. It's of
course less good for security, since an intruder who is looking for a
person with a known SSN easily will find what he is looking for. But
security and performance don't go hand in hand.
The alternative is to use application-specific keys like customer numbers.
Then you can encrypt the SSN fully. But this also means that if the
customer calls in, and don't know his customer number, but only the SSN,
the helpdesk will not be able to find his data.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Follow-Ups:
- Prev by Date: Re: Grant insert, but only allow entering vals in some fields
- Next by Date: Re: SQL Accounts on Mirror
- Previous by thread: Grant insert, but only allow entering vals in some fields
- Next by thread: Re: Search encrypted column in SQL 2005http://msdn.microsoft.com/w
- Index(es):
Relevant Pages
|
|
