November 7, 2015 at 12:45 pm
I've read a ton and am good with how to create the keys, encrypt data and decrypt data as an admin. But I have yet to find anyone that has a good posting about how to grant users the right to use the DECRYPTBYKEY function to decrypt the data. I have seen some people talk about using a stored procedure or view to surface the decrypted data, but how would you implement that when trying to pull back a single dataset? It would be best to use an inline function to allow the row to be returned decrypted, but opening the keys isn't allowed in the function construct.
So, I know I have to be missing something, but how do you let basic users (db_reader types) decrypt the data they need based on a custom database role? What do I need to give the user permission to?
My setup is simple:
- I have my SMK
- I have a DMK encrypted by password
- I have my self signed certificate
- I have my symmetric Key encrypted by the certificate
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
November 9, 2015 at 12:10 pm
Found it. You have to give both the view definition and control permissions to the key and the certificate.
GRANT VIEW DEFINITION ON SYMMETRIC KEY::SymmetricKey1 TO test;
GO
GRANT VIEW DEFINITION ON Certificate::Certificate1 TO test;
GO
GRANT CONTROL ON SYMMETRIC KEY::SymmetricKey1 TO test;
GO
GRANT CONTROL ON Certificate::Certificate1 TO test;
GO
Then the user can use the inline DECRYPTBYKEY function.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply