Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Ghanta
Yak Posting Veteran
96 Posts |
 Posted - 2011-07-27 : 16:20:39
|
| Hi I have to implement a column level encryption. I was able to encrypt and decrypt data using symmetric key using certificate; however, I need to create something that can be run wthout manual intervention. So people viewing the data with privileges should be able to view data in a normal way.. Insert should happen in a normal way...Also that field can be updated.. so seems like I would need to create: 1. functions or SP to encrypt data2. functions or SP to decrypt data3. trigger to encrypt data while inserted4. trigger to encrypt data while being updatedAnyone with experience please suggest or help me out with the generic code if you have it. Thanks! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-27 : 16:27:40
|
| As far as viewing the decrypted data, better to make a view that includes the decrypted column, and grant permissions only to the people who should see it. You should also encrypt the view definition so people cannot see which method or certificate is used for decryption.For encrypting the data, better to use a stored procedure to handle all INSERT and UPDATE operations. You could possibly make this work with an INSTEAD OF trigger, but exposing the table details weakens the security provided by data encryption. |
 |
|
|
Ghanta
Yak Posting Veteran
96 Posts |
Posted - 2011-07-27 : 16:33:19
|
Rob great suggestions. Thanks!quote:
Originally posted by robvolk
As far as viewing the decrypted data, better to make a view that includes the decrypted column, and grant permissions only to the people who should see it. You should also encrypt the view definition so people cannot see which method or certificate is used for decryption.For encrypting the data, better to use a stored procedure to handle all INSERT and UPDATE operations. You could possibly make this work with an INSTEAD OF trigger, but exposing the table details weakens the security provided by data encryption.
|
|
|
|
|
|
|
Symmetric Encryption Using Certificate