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...)
All fine.
Questions :
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
.BAK
file 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.