Viewing an Encrypted Column

SQL Server Administration
1

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?

2

Does the person need permission to see the stored procedures? You can control that by sp.

Also if the person does not have permission for the certificate/key they will not be able to execute the command.

3

Thankyou. In some cases yes the User, if it is a developer, should have access to the stored procedure but not to the information (i.e. encrypted column). I didn't realize you could put permission on the certificate/key, maybe that is what we need to do.

I'm wondering now, maybe we should use column-level permissions instead - why not? With encryption, it sounds like we are putting permission on a key, which unlocks a column - why not just put the permission directly on the column?