November 16, 2011 at 10:41 am
Hi Folks,
I wanted to get some ideas on how to grant correct permissions for different db objects I have created for column level data encryption?
I am using Symmetric Key with Certificate to encrypt credit card information column. Symmetric key is created using Identity_value and key_source.
I have created various SP and user defined functions to view, insert encrypted, decrypt, etc.
Do I just need to make sure that users have appropriate privileges on Symmetric Key and Certificate or how should I do it? I need to make sure that all users already in DB should be able to see data (not decrypted credit card info) and only privileged users should be able to view credit card info...
Please let me know if I should worry about giving execute permissions on SP and Function to users
OR
Should I should give appropriate privileges to CERTIFICATE and SYMMETRIC KEY to users and let everyone execute Functions and SPs...
Thanks,
Laura
November 16, 2011 at 11:54 am
Only grant access to the symetric keys to those users/roles that absolutely need to decrypt any column. In my opinion that would be the database administrator(s) only.
Grant execute only on the stored procedures that are required to retrieve specific subsets of data (i.e. for a given user).
The probability of survival is inversely proportional to the angle of arrival.
November 16, 2011 at 12:07 pm
Thanks a lot Sturner.
Yes and I think you missed onething I should grant symmetric keys to user/role that will do insert on that table as well rite...
November 16, 2011 at 1:28 pm
If you are granted execute persmission on the Stored Procedures , the keys can be used in them by the TSQL code in the SP but not directly.
Except for sysadmins, permission to use the key to encrypt or decrypt should be via the execute permission on the procedure that uses them.
The probability of survival is inversely proportional to the angle of arrival.
November 16, 2011 at 1:38 pm
In that case a user who has EXECUTE permission to Stored Procedure but do not have permission in the Symmetric Keys and Certificates will be able to execute the Stored Procedure; however, data will not be encrypted/decrypted. The SP will execute without failing though correct? If the Proc does a select, it should retrieve the records without decrypting the column rite? Thanks a lot.
I will do a test with this scenario...
November 16, 2011 at 1:48 pm
yes, if you specifically deny access to a key by a user, otherwise it can be used in the context of the stored procedure.
The probability of survival is inversely proportional to the angle of arrival.
November 16, 2011 at 1:56 pm
Thanks a lot Sturner.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply