Why should I protect self-assigned certificate with asymmetric/symmetric keys?-CodePudding

I'm absolutely new to topics like data encryption/decryption with MS SQL Server.

I have no problem with data encryption/decryption, but I cannot understand why the following example is given as a 'best practice'? Why should I protect the symmetric key with certificate if I can encrypt/decrypt data directly with symmetric key?

USE SOMEDB
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '***';  
GO  

CREATE CERTIFICATE [some_cert] WITH SUBJECT = 'Key Protection';  
GO  

CREATE SYMMETRIC KEY [some_sem_key] WITH  
    KEY_SOURCE = 'My key generation bits. This is a shared secret!',  
    ALGORITHM = AES_256,   
    ENCRYPTION BY CERTIFICATE [some_cert];  
GO

CodePudding user response：

The short answer - so that you don't have to share the symmetric key's password in order for those who would use it to encrypt/decrypt to be able to do so.

Exposition - what your example code doesn't show is how the key is used. In the case of a symmetric key that is encrypted by a certificate, it's

OPEN SYMMETRIC KEY [some_sem_key] 
   DECRYPTION BY CERTIFICATE [some_cert];
SELECT EncryptByKey(Key_GUID('some_sem_key'), 'Top Secret');

That's on the assumption that the certificate's private key itself is protected via a database master key (which your example shows, but just putting that out there as the certificate's can also be protected via a password which defeats this argument). Access can be controlled via GRANT'ing or revoking permissions on the certificate. Further, if the certificate is somehow compromised, rotating the protection is as easy as:

create certificate [some_cert2] WITH SUBJECT = 'Key Protection';
go
OPEN SYMMETRIC KEY [some_sem_key]
    decryption by certificate [some_cert];
ALTER SYMMETRIC KEY [some_sem_key]
    add encryption by certificate [some_cert2];
ALTER SYMMETRIC KEY [some_sem_key]
    drop encryption by certificate [some_cert];

Contrast that with the method necessary if the symmetric key is protected via a password:

OPEN SYMMETRIC KEY [some_sem_key] 
   DECRYPTION BY PASSWORD = 'Sup3rSecurePassword!!!';
SELECT EncryptByKey(Key_GUID('some_sem_key'), 'Top Secret');

In that usage, you'd have to put disseminate Sup3rSecurePassword!!! everywhere it needs to be used (i.e. applications, individuals who have need to use, etc).

Credential rotation is mostly the same:

OPEN SYMMETRIC KEY [some_sem_key]
    decryption by password = 'Sup3rSecurePassword!!!'
ALTER SYMMETRIC KEY [some_sem_key]
    add encryption by password = 'M0r3SecurePassword!!!'
ALTER SYMMETRIC KEY [some_sem_key]
    drop encryption by password = 'Sup3rSecurePassword!!!'

But the access control story is not. Possession of the password is the only thing controlling access to secrets protected with that symmetric key.

CodePudding user response：

Your symmetric key is used because it's a lot faster and more resource-efficient than an asymmetric key for encrypting your data. That means you can get away with the (relatively negligible) overhead of encrypting data with it "in real time". Encrypting with a symmetric key uses less CPU and produces smaller encrypted data so it uses less storage. However, it's still a single key. To mitigate the inherent security risks of leaking a single master key, it's secured using a slower but far more secure certificate.

In short: the certificate protects the key(s) that is (are) used to encrypt the data.