Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLNOVICE999
Yak Posting Veteran
62 Posts |
 Posted - 2011-11-29 : 16:05:38
|
| Hi Guys,I am using Symmetric encryption with Certificate to encrypt a data column. I have three types of users:1. One who can only view data, but should not see decrypted data (say User1)2. One who can view encrypted data, but should not be able to update or insert (Say User2)3. One who can view encrypted data and can insert/update on that table as well (Say User3)I have created SP to view all encrypted data, function to decrypt data, SP to insert new records, etc..For testing purposes, I need to create three users and test for the privileges. My question is what types of permissions do I have to give to each of the above three Users? What permissions on Symmetric Key and what on Certificates? Also for SP and for Function?Thanks a lot for taking time in helping me with this.Regards,Laura |
|
|
SQLNOVICE999
Yak Posting Veteran
62 Posts |
Posted - 2011-11-30 : 10:37:11
|
| Anyone! |
 |
|
|
SQLNOVICE999
Yak Posting Veteran
62 Posts |
Posted - 2011-11-30 : 13:06:17
|
Seems like I have to Grant Control on CERTIFICATE to user to give the user the ability to encrypt and decrypt. If I just grant View Definition on the Symmetric Key to user, the user cannot decrypt the data; however, there are errors like user cannot find certificate and user cannot open the symmetric key.How can I make a user who can have Execute permission on the Procs and Functions, but the user cannot view the decrypted data without seeing those errors? Thanks for taking time in responding...quote:
Originally posted by SQLNOVICE999
Anyone!
|
|
|
|
SQLNOVICE999
Yak Posting Veteran
62 Posts |
Posted - 2011-11-30 : 14:01:32
|
I did not have to Grant Control on Certificate... View Definition on Symmetric Key did it, but I had to create a SP to Open Symmetric Key and use that SP in other SPs to open they key... Now I need to find a way to let other users to execute SP that Selects data without Errors and showing nulls and not decrypted data.quote:
Originally posted by SQLNOVICE999
Seems like I have to Grant Control on CERTIFICATE to user to give the user the ability to encrypt and decrypt. If I just grant View Definition on the Symmetric Key to user, the user cannot decrypt the data; however, there are errors like user cannot find certificate and user cannot open the symmetric key.How can I make a user who can have Execute permission on the Procs and Functions, but the user cannot view the decrypted data without seeing those errors? Thanks for taking time in responding...quote:
Originally posted by SQLNOVICE999
Anyone!
|
|
|
|
SQLNOVICE999
Yak Posting Veteran
62 Posts |
Posted - 2011-12-01 : 10:31:27
|
| I was able to grant user permission to select, update, insert with encryption/decryption to user by giving just VIEW permission on the symmetric key and not granting Control on Certificate. I had to create a Proc to open the symmetric keys and use that in SPs to open keys.Now I still have to find out how can I deal with users who will not have privilege to decrypt the data, but should be able to use the SPs without failing... Any suggestion guys. |
|
|
|
|
|
|
|
|
Encryption Permissions