I am new to SQL Server Encryption.
What I'm struggling with is I am thinking it could be easy to decrypt an encrypted column.
I was looking at this code which I'm taking from a Microsoft Web Page:
-- Verify the encryption. -- First, open the symmetric key with which to decrypt the data. OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037; -- Now list the original ID, the encrypted ID, and the -- decrypted ciphertext. If the decryption worked, the original -- and the decrypted ID will match. SELECT NationalIDNumber, EncryptedNationalIDNumber AS 'Encrypted ID Number', CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) AS 'Decrypted ID Number' FROM HumanResources.Employee; GO
So, a person who has access to the Database, but not authorized access to the information in this column, if this code exists in a stored procedure, all the person has to do is search sys.sql_modules for the column name. And then they will find the code used to decrypt the column and they'll have no problem looking at the data. Is that not the case?
Even if the code doesn't exist in a Stored Procedure, someone could look through sys.certificates and sys.asymmetric_keys and probably after a view tries, they can figure out the certificate and key. Couldn't they?