Best way to set permissions for Symmetric Keys

Question

Best way to set permissions for Symmetric Keys

Paul A

SSC Eights!

Points: 985
More actions
November 7, 2007 at 8:49 am

#183802

Hi,
I have set up symmetrical encryption where the symmetrical key is encrypted by the asymetrical key.
I have a view which uses the decryptbyasykey function to decrypt data.
I have Granted a non-admin user public role in my database, and select permissions on the view, which I notice, does NOT allow them to decrypt the data from the view as they do not have permissions on the keys.
I have granted control on the assymetrical and symmetrical keys, and now the view will display the data decrpyted.
At this point the user can access Management Studio, and I notice it is possible for them to delete the symmetrical and assymetrical keys, which should not be allowed?
To get around this I tried denying view definition on the keys, but the decryption stops working in the view, so i have to revoke it... and am back to where I started...
How do I stop a user from deleting/altering keys, which still allows the user to decrypt data?
Hope this isn't too confusing, thanks for any help.
Paul.

Viewing 5 posts - 1 through 4 (of 4 total)

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

I believe granting just VIEW DEFINITION on the symmetric key is sufficient for it to be used. You should then by able to revoke CONTROL.

K. Brian Kelley
@kbriankelley

Paul A SSC Eights! Points: 985 More actions · Answer 2

Hi Brian,

I tried the suggestions, but I still have the problem.

I basically granted view definition on symmetrical and asymmetrical key, and then revoked control on both.

But the decryption via the view failed until I granted control again.

The deleting of the key was prevented by revoking control again, having the desired result.

So I can basically have decrypted data, but run the risk of the key being deleted, or have no decrypted data and prevent the user deleting the key.

Any other ideas on this, is there a different permission I can revoke or deny, as I cannot find anything about permissions on keys that is of any value.

I just want to bolt this down as tight as possible with no room for error.

Thanks so far... 🙂

Paul

Paul A SSC Eights! Points: 985 More actions · Answer 3

Hello,

My colleague has found this link which appears very useful regarding the permissions problem I have.

http://blogs.technet.com/keithcombs/archive/2005/11/24/415079.aspx

Basically I need to run the decryption code in a UDF with EXECUTE AS 'dbo' and then add the UDF into the view, which only has Select permission to the low-privledge user.

The only downside I have had with this, is that the performance is slower with the UDF inside the View. But then it depends how the view will be used.

Thanks

Paul

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

I see. I only did the encryption by symmetric key. I didn't encrypt the symmetric key with an asymmetric key. Having view definition on the asymmetric key apparently isn't enough to be able to use it to decrypt the symmetric key. I think that's something that needs to be submitted to Microsoft as a suggestion for improvement... the ability to give a user rights to use a certificate/asymmetric key/symmetric key without giving them CONTROL rights over it.

K. Brian Kelley
@kbriankelley