On the hash issue...
If you are only searching for exact matches then hash buys
you nothing.
Search would be something like "where #MyEncryptFunc(SSN)# =
EncryptedDB_Column".
Just have an index on EncryptedDB_Column and performance is
fine.
In fact, a hash can be risk because all hashes are now
crackable and having both an encrypted and a hashed column
decreases the time needed to crack either scheme.
If you need to do a range search for some reason, then you
would build up an index of "partial-string hashes". You probably
don't need this for SSN though.
Overall, the key things are:
-
- Never store keys in the payload database.
- Never have the keys in any code: SQL, CFM, etc.
- You need at least 3 systems, ideally on 3 separate machines:
(a) The payload database, (b) The web app code, (c) The secure key
repository/source.
- The Web app accepts/sends all data to/from browsers via SSL.
- Changing the password will require a full pass through the SSN
column to reencrypt but you must do this frequently anyway.
- You need a way to associate the correct password with a given
DB backup but do not store these together.
- Put audit mechanisms in place.
- Have at least two enemies overseeing each other for admin
duties. I like the old air force tactic of giving each a pistol
with orders to shoot at the first sign of a security breach.