locked
granting permissions on symmetric key RRS feed

  • Question

  • We are thinking about using symmetric key encryption for several columns in a table (personnel data).  There doesn't seem to be much info on granting permissions to use the key.  Books Online shows I can grant   ALTER, CONTROL, REFERENCES, TAKE OWNERSHIP, and VIEW DEFINITION but BOL doesn't tell me what these permission mean.  None of these permissions sound like what is needed but I can't be sure.  I need to be able to grant the right to USE a symmetric key to encrypt and decrypt data to specific principals.  Does anyone know what I need to do to accomplish this task?
    Monday, October 10, 2011 7:02 PM

Answers

  • Hi,

     

    http://msdn.microsoft.com/en-us/library/ms190499.aspx
    Permissions
    --------------------------------------------------------------------------------
    The caller must have some permission on the key and must not have been denied VIEW DEFINITION permission on the key. Additional requirements vary, depending on the decryption mechanism:
    DECRYPTION BY CERTIFICATE: CONTROL permission on the certificate and knowledge of the password that encrypts its private key.
    DECRYPTION BY ASYMMETRIC KEY: CONTROL permission on the asymmetric key and knowledge of the password that encrypts its private key.
    DECRYPTION BY PASSWORD: knowledge of one of the passwords that is used to encrypt the symmetric key.

    An example:

     

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'verysecretpassword.;34.53##/../909345';
    
    CREATE CERTIFICATE [MyCert] WITH SUBJECT = 'MyCert';
    
    CREATE SYMMETRIC KEY [MyKey] 
    WITH KEY_SOURCE = 'My Secret'
    ,IDENTITY_VALUE = 'Key Identity'
    ,ALGORITHM = TRIPLE_DES 
    ENCRYPTION BY CERTIFICATE [MyCert];
    
    
    OPEN SYMMETRIC KEY [MyKey] DECRYPTION BY CERTIFICATE [MyCert];	
    			DECLARE @Secret VARBINARY(MAX)
    			SELECT @Secret = ENCRYPTBYKEY(KEY_GUID('[MyKey]'),'MySecretMessage')
    			SELECT @Secret,CONVERT(VARCHAR(50), DECRYPTBYKEY(@Secret))
    CLOSE SYMMETRIC KEY [MyKey];
    
    USE [YourDatabase]
    GO
    GRANT CONTROL ON CERTIFICATE::[MyCert] TO [YourPrincipal]
    GO
    GRANT VIEW DEFINITION ON SYMMETRIC KEY::[MyKey] TO [YourPrincipal]
    GO
    
    OPEN SYMMETRIC KEY [MyKey] DECRYPTION BY CERTIFICATE [MyCert];	
    			DECLARE @Secret VARBINARY(MAX)
    			SELECT @Secret = ENCRYPTBYKEY(KEY_GUID('[MyKey]'),'MySecretMessage')
    			SELECT @Secret,CONVERT(VARCHAR(50), DECRYPTBYKEY(@Secret))
    CLOSE SYMMETRIC KEY [MyKey];
    

     

     

     


    Jon
    • Marked as answer by Ira Davis Tuesday, October 11, 2011 1:08 PM
    Tuesday, October 11, 2011 7:47 AM

All replies

  • Hi Davis,

    http://msdn.microsoft.com/en-us/library/ms179887.aspx

    check this link, I hope it helps to you.

    Thanks


    Thanks, Satish Kumar.
    Monday, October 10, 2011 7:22 PM
  • This is a link to the Books Online documentation which explains nothing.  What specific RIGHTS do I need to assign to a principal so that security entity can only USE the symmetric key?  I don't want the principal to alter or control or do anything except be able to OPEN the key for encrypting and decrypting data.
    Monday, October 10, 2011 8:16 PM
  • for security reasons Microsoft will not allow to u OPEN the key for encrypting and decrypting data. i belive :)
    Thanks, Satish Kumar.
    Monday, October 10, 2011 8:27 PM
  • So encryption is worthless, then.
    Monday, October 10, 2011 8:28 PM
  • i m talking about keys not data... The encryption of data is based on some specific algorithms....

    Hence i dont think so, we have an option to modify keys i belive.

    for encryption and de-cryption of data, plz check the below link:

    http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/

    let me know, we both are in sync.. or our thinking is different :)


    Thanks, Satish Kumar.
    Monday, October 10, 2011 8:51 PM
  • Basically a rehash of whats in books online and other sources.  But doesn't contain a word about what permissions to grant a database user or role.  

    Monday, October 10, 2011 9:31 PM
  • let me know what kind of roles u r looking for on database user ??
    Thanks, Satish Kumar.
    Monday, October 10, 2011 10:27 PM
  • Hi,

     

    http://msdn.microsoft.com/en-us/library/ms190499.aspx
    Permissions
    --------------------------------------------------------------------------------
    The caller must have some permission on the key and must not have been denied VIEW DEFINITION permission on the key. Additional requirements vary, depending on the decryption mechanism:
    DECRYPTION BY CERTIFICATE: CONTROL permission on the certificate and knowledge of the password that encrypts its private key.
    DECRYPTION BY ASYMMETRIC KEY: CONTROL permission on the asymmetric key and knowledge of the password that encrypts its private key.
    DECRYPTION BY PASSWORD: knowledge of one of the passwords that is used to encrypt the symmetric key.

    An example:

     

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'verysecretpassword.;34.53##/../909345';
    
    CREATE CERTIFICATE [MyCert] WITH SUBJECT = 'MyCert';
    
    CREATE SYMMETRIC KEY [MyKey] 
    WITH KEY_SOURCE = 'My Secret'
    ,IDENTITY_VALUE = 'Key Identity'
    ,ALGORITHM = TRIPLE_DES 
    ENCRYPTION BY CERTIFICATE [MyCert];
    
    
    OPEN SYMMETRIC KEY [MyKey] DECRYPTION BY CERTIFICATE [MyCert];	
    			DECLARE @Secret VARBINARY(MAX)
    			SELECT @Secret = ENCRYPTBYKEY(KEY_GUID('[MyKey]'),'MySecretMessage')
    			SELECT @Secret,CONVERT(VARCHAR(50), DECRYPTBYKEY(@Secret))
    CLOSE SYMMETRIC KEY [MyKey];
    
    USE [YourDatabase]
    GO
    GRANT CONTROL ON CERTIFICATE::[MyCert] TO [YourPrincipal]
    GO
    GRANT VIEW DEFINITION ON SYMMETRIC KEY::[MyKey] TO [YourPrincipal]
    GO
    
    OPEN SYMMETRIC KEY [MyKey] DECRYPTION BY CERTIFICATE [MyCert];	
    			DECLARE @Secret VARBINARY(MAX)
    			SELECT @Secret = ENCRYPTBYKEY(KEY_GUID('[MyKey]'),'MySecretMessage')
    			SELECT @Secret,CONVERT(VARCHAR(50), DECRYPTBYKEY(@Secret))
    CLOSE SYMMETRIC KEY [MyKey];
    

     

     

     


    Jon
    • Marked as answer by Ira Davis Tuesday, October 11, 2011 1:08 PM
    Tuesday, October 11, 2011 7:47 AM