March 22, 2010 at 9:17 am
I am following through example in the eBook Protecting SQL Server Data by John Magnabosco and have run into an issue. In chapter 5, I am going through some examples on Cell Level Encryption. Basically data is being encrypted by a symmetric key, which is encrypted by a certificate, encrypted by the database master key, etc. The only permissions it states in the book that are needed (unless I missed something) is view definition on the symmetric key, but I am unable to get the correct data without giving the user control permission on the certificate being used.
My question is when using DecryptByKeyAutoCert to decrypt the data is it possible to do this without the user having control permission. Also, what are the implications of giving control permission on the certificate to a user who only needs to be able to view the data?
Thanks for any information.
Sam
March 23, 2010 at 7:26 am
I have not read the book, but according to the BOL entry for DECRYPTBYKEYAUTOCERT:
Permissions
Requires VIEW DEFINITION permission on the symmetric key and CONTROL permission on the certificate.
From the BOL entry for Permissions:
CONTROL
Confers ownership-like capabilities on the grantee. The grantee effectively has all defined permissions on the securable. A principal that has been granted CONTROL can also grant permissions on the securable. Because the SQL Server security model is hierarchical, CONTROL at a particular scope implicitly includes CONTROL on all the securables under that scope. For example, CONTROL on a database implies all permissions on the database, all permissions on all assemblies in the database, all permissions on all schemas in the database, and all permissions on objects within all schemas within the database.
So the implication is that, granting CONTROL on the certificate means that the user granted this permission could change the certificate or grant rights to others on the certificate. Of course they need to know how to do it, and have to have access to write T-SQL against the database.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply