Post reply

Permission and Privileges for Encryption

  • November 29, 2011 at 10:47 am

    #263761

    Hi Guys,

    I am using Symmetric encryption using 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

  • November 29, 2011 at 11:13 pm

    #1414446

    Only grant access to the symetric keys to those users/roles that absolutely need to decrypt any column.

    Grant execute only on the stored procedures that are required to retrieve specific subsets of data.

    If you grant execute persmission on the Stored Procedures, the keys can be used in them by the code in the SP but not directly.

  • November 30, 2011 at 8:56 am

    #1414741

    Ghanta (11/29/2011)

    Only grant access to the symetric keys to those users/roles that absolutely need to decrypt any column.

    Grant execute only on the stored procedures that are required to retrieve specific subsets of data.

    If you grant execute persmission on the Stored Procedures, the keys can be used in them by the code in the SP but not directly.

    Thanks Ghanta for the response. Since the data will be retreived from the front-end, I do want User1 to get ERRORs for not having permission to execute SP or UDFs. And I would like User1 to see nulls for the encrypted field.

    So should I

    GRANT VIEW DEFINITION ON SYMMETRIC KEY::key_Encryption TO User1

    and Grant Execute Permission on SPs and Function

    AND SHOULD not GRANT Control on the Certificate ....

    Will these ensure that users can view other data, but nulls for the column encrypted.

    Thanks!!!!

  • November 30, 2011 at 10:48 am

    #1414849

    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...

    Laura

  • November 30, 2011 at 11:38 am

    #1414874

    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.

    Laura.

  • November 30, 2011 at 2:50 pm

    #1414964

    I created another use and gave execute permission on the SPs that retrieves data. I gave permission to proc that opens the Symmetric keys. I deny view on Symmetric Keys and Deny control on Certificate. This user was still able to decrypt the data. I wanted this use to do select using the same SP that decrypts the data, but I wanted this user to get Nulls for that particular column....

    Anyone with experience... please suggest what I am doing wrong here?

    Thanks,

    Laura

  • December 1, 2011 at 2:06 pm

    #1415578

    Laura how about creating a View?

    If the users who do not have access to Select Procs that decrypts the data do a select using Query Analyzer, they should not be able to see decrypted column.

  • December 1, 2011 at 2:09 pm

    #1415582

    Ghanta thanks for the response.... yeah those users cannot see the decrypted data in the back-end; however, data is being accessed from front-end and I cannot restrict those users from executing the Proc that decrypts the data. I need to do in a way that they can use the same Procs/Objects and should not be able to view decrypted data...

    Ghanta (12/1/2011)

    Laura how about creating a View?

    If the users who do not have access to Select Procs that decrypts the data do a select using Query Analyzer, they should not be able to see decrypted column.

  • December 1, 2011 at 10:01 pm

    #1415684

    bumping it up.. sorry

  • December 2, 2011 at 3:00 pm

    #1416198

    You want the user that should be able to select the data using (the SP) from front-end but not be able to view decrypted data and view nulls instead rite?

    Laura did you try by not giving permission on the symmetric key and certificate and giving execute permission on Proc that does the Select?

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply