Where to use the column-level encryption of SQL?

Question

I was able to do the column encryption in SQL with the following code:

USE EncryptionDemonstration

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ThisIsMySampleStrongPassword'

CREATE CERTIFICATE MyServerCertificate WITH SUBJECT = 'This is my Demonstration Certificate'


CREATE SYMMETRIC KEY SSN_Keys  
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE MyServerCertificate;  
GO  
CREATE SYMMETRIC KEY CreditCard_Keys  
    WITH ALGORITHM = AES_128  
    ENCRYPTION BY CERTIFICATE MyServerCertificate;  

ALTER TABLE Customer
    ADD SSN_Encrypted varbinary(128),
        CCN_Encrypted varbinary(128)

OPEN SYMMETRIC KEY CreditCard_Keys 
   DECRYPTION BY CERTIFICATE MyServerCertificate 
OPEN SYMMETRIC KEY SSN_Keys 
   DECRYPTION BY CERTIFICATE MyServerCertificate

UPDATE  Customer 
SET SSN_Encrypted = EncryptByKey(Key_GUID('SSN_Keys')  
    , SocialSecurityNumber)

UPDATE  Customer 
SET CCN_Encrypted = EncryptByKey(Key_GUID('CreditCard_Keys  ')  
    , CreditCardNumber)

GO  

OPEN SYMMETRIC KEY SSN_Keys
   DECRYPTION BY CERTIFICATE MyServerCertificate 

OPEN SYMMETRIC KEY CreditCard_Keys
   DECRYPTION BY CERTIFICATE MyServerCertificate 



SELECT SocialSecurityNumber, SSN_Encrypted   
    AS 'Encrypted SSN',  
    CONVERT(varchar, DecryptByKey(SSN_Encrypted))   
    AS 'Decrypted SSN'  
    FROM Customer  

SELECT CreditCardNumber, CCN_Encrypted 
    AS 'Encrypted Credit Card Number',
     CONVERT(varchar, DecryptByKey(CCN_Encrypted))   
     AS 'Decrypted Credit Card Number'
     FROM Customer

Now my question is, how does this really help in security if anyone who has access this on this database could just run the DecryptByKey function to view the decrypted value? I am not fully familiar with the use of database master key and certificates so please bear with me.

Answer 1

The answers is, that everyone can run DecryptByKey, but not everyone has access to the symmetric key used for protecting the data. There is a strict encryption hierarchy in SQL Server and you have chosen the following path:

1. Database Master Key is protected password
2. Certificate protected by DMK
3. Symmetric key protected by certificate
4. data encrypted by symmetric key

So, everyone who has access to the symmetric key can used it, but to do that, one needs to have access to the certificate protecting the given symmetric key. That's why, you are using:

OPEN SYMMETRIC KEY ... DECRYPTION BY CERTIFICATE ...

Try creating a user that has no access to the CERTIFICATE and execute the above statement:

DROP USER IF EXISTS [StackOverflow];

CREATE USER [StackOverflow] WITHOUT LOGIN;

EXECUTE AS USER = 'StackOverflow';

OPEN SYMMETRIC KEY [SK_SecurityUsers_V001] DECRYPTION BY CERTIFICATE [CERT_V001];

REVERT;

You will get the following error:

Msg 15151, Level 16, State 1, Line 7
Cannot find the symmetric key 'SK_SecurityUsers_V001', because it does not exist or you do not have permission.

So, it depends on you who can use the symmetric key controlling the access to the certificate protecting it. And the question is, who has access to certificate at all?

The permission diagram is showing the bigger picture, but basically:

or on short:

• sys.admin
• db_owner
• the creator of certificate
• users who have been given access to the certificate

Also, something about the backups. When you create a backup and restore it on different instance for example, you need to recreate the security hierarchy. Basically on such database, even the sys.admin has no access to the key.

So, if someone stole your backup, the data is again protected. You need to either have a backup of your certificate and restore it again in the new database, or use CERTENCODED to create the same certificate.

This is just touching the surface, plan very well any aspect before encrypted the data (for example, index on encrypted columns are useless as random salt is added to each value when encrypted - encrypt one value several times and you will get different binaries each time).