After asking this I still have a question.
A company need to store sensitive data. Please let's not talk about certificate right now.
A DBA does the following :
CREATE SYMMETRIC KEY SecureSymmetricKey1 WITH ALGORITHM = DESX ENCRYPTION BY PASSWORD = N'blabla';
A programmer wants to encrypt data and does the following :
-- open key OPEN SYMMETRIC KEY SecureSymmetricKey1 DECRYPTION BY PASSWORD = N'blabla' -- actual encrpyt DECLARE @encrypted_str VARBINARY(MAX) SET @encrypted_str = EncryptByKey(Key_GUID('SecureSymmetricKey1'),'my data');
Another programmer wants to READ the data so he does :
DECLARE @decrypted_str VARBINARY(MAX) SET @decrypted_str = DecryptByKey(...encrypted_str...)
When a programmer opens a symmetric key he must the know the password. I don't think that a programmer should know the password. How can this be solved ?
If a GOD hacker got the entire
.BAKfile and he restore the backup onto his own machine - he can view the stored procedure source which one of the programmers wrote, and see the password. and then the HACKER can do :
OPEN SYMMETRIC KEY SecureSymmetricKey1 DECRYPTION BY PASSWORD = N'blabla'
What am I missing ?
Thanks for helping.