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!
Best Answer
Based on your requirements, I would actually recommend Transparent Data Encryption (TDE) instead of CLE.
TDE encrypts at the database level and therefore you don't have to worry about being compromised by storing the decryption process in a stored procedure. Rather, TDE works on the traditional private and asymmetric key pattern to prevent access to the unencrypted data without the proper key (which you can handle authenticating outside of the database to decouple the security concerns).
It's also better in my opinion because it doesn't force you to change your data types to
VARBINARYwhich almost certainly could affect the performance of your queries. Rather it keeps everything as you designed under the hood (hence Transparent) when you access your database.