June 7, 2018 at 11:47 am
Hi there,
There are many articles available to implement column level encryption on the sql tables, but I see that everybody is creating a new column against the original and keeping the encrypted results there. But the issue on the approach is one can still see the actual unencrypted columns which is like a rick to the overall business.
For e.g,
UPDATE UserDetails
SET [EncryptedPassword] = EncryptByKey(Key_GUID('SQLSymmetricKey'), UserPassword);
Here they are creating a new column say "EncryptedPassword" and keeping the actual column (UserPassword) values.
Instead of this, can I not update the values on the same column itself? But not sure what level of changes does it require for application to read the encrypted values and getting back the actual results
UPDATE UserDetails
SET [UserPassword] = EncryptByKey(Key_GUID('SQLSymmetricKey'), UserPassword);
Thanks.
June 7, 2018 at 12:19 pm
I suppose it depends on the datatype of column. EncryptByKey will return VARBINARY so if the original column is already that type you might be able to get away with it. If the original column is something else, say VARCHAR, then you could always drop the VARCHAR column after you do the UPDATE to populate the VARBINARY column.
June 7, 2018 at 12:24 pm
Sample code that people post in articles and the like is almost never going to be "production ready." It's simply there so that someone can see the *how* of doing something, then from there build on that to get what they need.
So, to that extent you're correct, you wouldn't keep the un-encrypted and encrypted data, in "real" production code you would only have the encrypted data stored. How that gets accomplished is entirely dependent on business requirements, etc.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply