0

As per https://technet.microsoft.com/en-us/library/ms190499.aspx, to open a symmetric key that is encrypted by a certificate, a user needs the CONTROL permission on the certificate.

I tested this and indeed, nor VIEW DEFINITION, nor ALTER, nor REFERENCES suffice.

I wonder why. Moreover, I wonder how secure it is if everyone needs the CONTROL permission (just to be able to use it), and as a consequence, can easily grant this permission to everyone else (confirmed by own test).

(I also tried granting CONTROL and denying all the others, but I guess that this doesn't make much sense because CONTROLoverrides the others. Or so I suppose. Please correct me if I'm wrong.)

1

With security in SQL Server, one needs to be very watchful that permissions are not indirectly granted when not intended. Designing granular permissions for the SQL Server system must be a very meticulous task for Microsoft. Once created, the certificate can be altered, but only to drop the private key. View definition lets the user see the metadata, however when it comes to the private key this is not enough because it is encrypted by the database master key, which, in turn is encrypted by the service master key. Besides, the documentation states that "view definition does not imply access to the securable itself". References permission as well is not enough for a certificate, however, it is enough to use a symmetric key. The user of encryption needs control permissions.

There are several very good reasons why the other answer recommends designing stored procedures to read and write encrypted data using code signing. Anyone with the control permission on a certificate can back up the certificate and private key to the file system. By code signing, you don't need to grant control directly to any user as the additional permissions are made available by associating a certificate user with permissions with the procedure through code signing. Also, anyone with control permissions can grant permissions on the securable to others, which could lead to some surreptitious activity. The db_owner role, db_securityadmin and the db_ddladmin role have CONTROL permissions on certificates automatically and the db_backupoperator role can back up the database.

Although it seems that you are asking about column encryption. Certificates with SQL Server are created in master and msdb for other purposes, such as Transparent Data Encryption and backup encryption. Stolen certificates can be used to defeat these. So don't allow anyone in the roles mentioned above in master or msdb.

0

Guess the reason is that you need access to certificates private key in order to open symmetric key (private key of certificate is used to decrypt symmetric key password- and password you use behind the scene for further data encryption/decryption). So very high permissions required.

I would suggest using signed procedures or proxy user to use certificate.

And about signed procedures and proxy users you can read here. Quite complex at beginning.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.