locked
permission to access symmetric key RRS feed

  • 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

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

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

    RLF

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

    Thank you

    Monday, August 16, 2010 9:20 PM