Sql-server – Why CONTROL permission on certificate to be able to use it

The proper key hierarchy is the Service Master Key protects the Database Master Key, the Database Master Key protects either an asymmetric key or certificate, the asymmetric key or certificate can be used for encryption or can be used to protect a symmetric key which is used for encryption. The keys are created in that order. The system creates and protects the SMK, and the DMK and subsequent keys are created by the user.

The design of the key hierarchy in SQL Server protects both the data and keys from compromise. Remember that with a symmetric key, the same key is used to encrypt and decrypt data, or in this case other keys. The main purpose of introducing an asymmetric key or certificate into the hierarchy protected by the Database Master Key is to prevent an attack on the DMK and SMK from inside of the database. If someone malicious wanted to obtain the DMK unencrypted, then that person would need to do one of two things. Either decrypt the service master key and use it to decrypt the DMK, which is not possible because only the database engine can use the Data Protection API to do this, or attempt to decrypt from the top of the hierarchy down, which would be possible if there were not an asymmetric key or certificate in the way.

All of the signed or encrypted bits of the symmetric keys in SQL Server are in a system table named sys.crypt_properties in each database, including the encryption of the Service Master Key in the master database. There is no system table that contains the private key for either of the asymmetric key types. If all keys in a hierarchy were symmetric keys, then the SMK would encrypt the DMK and the DMK would encrypt the symmetric key that would encrypt the data. Because of the way that symmetric keys work, that would also mean that, if someone opens the symmetric key for the data, then it can theoretically decrypt the DMK and the decrypted DMK can be saved by a malicious user or used to decrypt the SMK because the same key is used for encryption and decryption. This is why an asymmetric key or certificate is required to be an integrated part of the encryption hierarchy.

While it's true that anyone with read permissions can browse the database using SQL Server Management Studio, additional permissions need to be granted to use the keys for decrypting the data. The first point to keep in mind is that a certificate is a public object, and it's the related private key that needs protection to ensure that it can't be used by unauthorized users to decrypt the symmetric key and subsequently the data. The private key is encrypted by the Database Master Key and is not visible to any user, even to those in the sysadmin role. Just because a user can see the objects doesn't mean they can use them. The permissions needed to decyrypt data using the encryption hierarchy you described, which is a symmetric key protected by certificate protected by the database master key, are CONTROL on the certificate and REFERENCES on the symmetric key. Without both of those permissions, decryption will not be possible. However, if you grant the CONTROL permission on the certificate directly to the user or the user is in the db_owner, db_ddladmin or db_securityadmin role, then the user can back up the certificate and private key, which is a threat to the security of your encryption. In addition to removing the users from the aforementioned roles, there are several ways to address this issue. One is to use an asymmetric key in your hierarchy instead of a certificate, since there is no backup command for asymmetric and symmetric keys. The other is to use code signing and only grant the permissions to a certificate user. Someone also mentioned that it may be possible to use impersonation , or "execute as" in a procedure, but this may not be the safest choice. If you want to replace the certificate with an asymmetric key in your hierarchy, then you should back up the Database Master Key for the database and backup the database, then restore both in a non production environment so you can create the script and test it completely before attempting this in a production environment.

If you really want to hide the names of all of the objects, then you can deny view definition to the users in the database. Again, test in non-production first.