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

Answer 1

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

GRANT VIEW DEFINITION ON SYMMETRIC KEY::KeyName TO RoleName;  -- Encrypt
GRANT CONTROL ON SYMMETRIC KEY::KeyName TO RoleName; -- Decrypt

RLF

Answer 2

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?

 

Answer 3

Could you show the command you used?   Seeing your GRANT may be useful.

RLF

Answer 4

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

Thank you