We got instruction from HQ to implement encryption on our database. The reason be, should a client gets hacked, the hackers cannot access personal info such as names, phone numbers, addresses, etc.
I have done a fair bit of research and it seems that Cell Level Encryption could be the way to got but I have some questions regarding the implementation (I am a developer, not really DBA material...).
Am I correct in saying the following:
- All data types, for the columns we want to encrypt, must be changed to varbinary
- We have to change all our SELECT statements to include DecryptByKey for encrypted columns
- We have to change all INSERT & UPDATE statements to include EncryptByKey for encrypted columns
Since 99% of all selects, inserts and updates in our system happen in stored procedures, how would I implement the opening and closing of the SYMMETRIC KEY? Surely, any hacker worth something, can look at the SP code to see which key was used, or just execute the SP the get the data?
Also, is there a way to determine if a column contains encrypted data? We have generic insert/update functions and these would need to know when to use EncryptByKey.
Any guidance will be greatly appreciated!