Answered by:
permission to access symmetric key

Question
-
Hi,
I am trying to compare the password entered by user from GUI with the encrypted password in DB. The encrypted password has been created follows SQL encryption procedure: create master key, a certificate and a symmetric key for date encryption, encrypted by that certificate. In the C# code, i call the Select sproc. there i got the exception that there is permission required for access symmetric key. How should i grant the permission for users ( they are all in a role)
Thank you
Monday, August 16, 2010 6:00 PM
Answers
-
Please see Raul Garcia's response at:
http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/6437f857-aa5e-4cb7-83bb-55285be37f91/In part it says: " In order to encrypt by a certificate you only need access to the public key (therefore VIEW DEFINITION is sufficient), but in order to decrypt, the private key is required (hence the CONTROL permission requirement)."
So, depending on your needs you should be able to use the grant syntax defined here:
http://technet.microsoft.com/en-us/library/ms179887.aspxGRANT VIEW DEFINITION ON SYMMETRIC KEY::KeyName TO RoleName; -- Encrypt
GRANT CONTROL ON SYMMETRIC KEY::KeyName TO RoleName; -- DecryptRLF
- Marked as answer by Alex Feng (SQL) Tuesday, September 7, 2010 5:17 AM
Monday, August 16, 2010 7:28 PM
All replies
-
Please see Raul Garcia's response at:
http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/6437f857-aa5e-4cb7-83bb-55285be37f91/In part it says: " In order to encrypt by a certificate you only need access to the public key (therefore VIEW DEFINITION is sufficient), but in order to decrypt, the private key is required (hence the CONTROL permission requirement)."
So, depending on your needs you should be able to use the grant syntax defined here:
http://technet.microsoft.com/en-us/library/ms179887.aspxGRANT VIEW DEFINITION ON SYMMETRIC KEY::KeyName TO RoleName; -- Encrypt
GRANT CONTROL ON SYMMETRIC KEY::KeyName TO RoleName; -- DecryptRLF
- Marked as answer by Alex Feng (SQL) Tuesday, September 7, 2010 5:17 AM
Monday, August 16, 2010 7:28 PM -
Thank you for your information
However i get another error message in Select sproc say " Cant not grant, deny or revoke permission to sa, dbo, owner ... or yoursefl" where i made the GRANT CONTROL to a role.What did i do wrong?
Monday, August 16, 2010 8:31 PM -
Could you show the command you used? Seeing your GRANT may be useful.
RLF
- Edited by SQLWork Tuesday, August 17, 2010 2:59 PM Removed code example different topic. My mistake.
Monday, August 16, 2010 9:10 PM -
Hi Russell,
Here is my Select sp:
USE [Member]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[p_user_select]
@username nvarchar(25) = NULL,
@password nvarchar(max) = NULL,
AS
OPEN SYMMETRIC KEY pw
DECRYPTION BY CERTIFICATE UserPassword;
GRANT CONTROL ON SYMMETRIC KEY::pw TO member; -- Member is a role for any member login from GUI, specified in connection string in web.config
SELECT
u.userid,
u.firstname,
u.lastname,
u.username,
u.password
FROM [User] u
WHERE
((u.username = @username AND CONVERT(nvarchar(max), DecryptByKey(u.EncryptedPassword)) = @password)OR (@username IS NULL AND @password IS NULL))
ORDER BY u.FirstName, u.LastNameThank you
Monday, August 16, 2010 9:20 PM