Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have user associated to a schema with limited privileges.During run time we are generating symmetric key using same user log-in.since the user has limited privilege, we are not able to create key now.

How to grant permission for creating,reading and executing symmetric key in sql server 2008 for this user ?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

If you create keys without a certificate like:

CREATE SYMMETRIC KEY smTestKey
 WITH ALGORITHM=AES_256
    , IDENTITY_VALUE = 'Key to protect bla'
    , Key_SOURCE = N'Secret pass phrase'
  ENCRYPTION BY PASSWORD = 'secret password';

then the following is enough:

GRANT ALTER ANY SYMMETRIC KEY TO dbuser

If you create symmetric keys that's encrypted by a certificate (that is created by another db user) for example:

CREATE SYMMETRIC KEY smTestKeyCert 
 WITH ALGORITHM = AES_256
  ENCRYPTION BY CERTIFICATE testCert;

then you also need VIEW DEFINITION permission on the certificate:

GRANT VIEW DEFINITION ON CERTIFICATE::testcert TO dbuser

However, if you want to open symmetric key by decrypting with the certificate the dbuser opening the key would need CONTROL permission on the certificate:

GRANT CONTROL ON CERTIFICATE::testcert TO dbuser

UPDATE To summerize:

  • The user that is creating asymmetric keys needs ALTER ANY ASYMMETRIC KEY permission
  • The user that is creating symmetric keys needs ALTER ANY SYMMETRIC KEY permission
  • The user that is creating keyA and encrypting it with keyB needs VIEW DEFINITION permissions on keyB
  • The user that is OPENING keyA and decryptiong it with keyB needs CONTROL permission on KeyB

your scenario:

--UserA needs to create Asymmetric keys so needs ALTER ANY ASYMMETRIC KEY PERMISSION
GRANT ALTER ANY ASYMMETRIC KEY TO userA

--UserB needs to create Symmetric keys so needs ALTER ANY SYMMETRIC KEY PERMISSION
GRANT ALTER ANY SYMMETRIC KEY TO userB


--UserA creates Asymmetric keys that are used by userB to create Symmetric keys and later open them
--So userA must create the Asymmetric key and Also give CONTROL permission on the Asymmetric key to UserB

--Create Asymmetric key
CREATE ASYMMETRIC KEY asym_CommonKey 
WITH ALGORITHM = RSA_2048 
ENCRYPTION BY PASSWORD = 'admin@123'; 

--Give control permission to UserB
GRANT CONTROL ON ASYMMETRIC KEY::asym_CommonKey to UserB


--UserB creates a symmetric key using the Asymmetric key from userA
--Note, at this stage VIEW DEFINITION permission on the Asymmetric key would have been sufficient
CREATE SYMMETRIC KEY sym_CommonKey 
WITH ALGORITHM = AES_256 
ENCRYPTION BY ASYMMETRIC KEY asym_CommonKey

--UserB opens the Symmetric key, decrypting it with the Asymmetric Key
--Note, at this stage User B needs the CONTROL permission on the Asymmetric key.
OPEN SYMMETRIC KEY sym_CommonKey DECRYPTION BY ASYMMETRIC KEY asym_CommonKey with password ='admin@123'

Source:

MSDN

Requires ALTER ANY SYMMETRIC KEY permission on the database. If AUTHORIZATION is specified, requires IMPERSONATE permission on the database user or ALTER permission on the application role. If encryption is by certificate or asymmetric key, requires VIEW DEFINITION permission on the certificate or asymmetric key. Only Windows logins, SQL Server logins, and application roles can own symmetric keys. Groups and roles cannot own symmetric keys.

MSDN 2

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.
share|improve this answer
    
Thanks for your reply. –  vijay Dec 20 '13 at 10:15
    
But still i am having same issue ..I got following exception "Cannot find the asymmetric key 'asym_CommonKey', because it does not exist or you do not have permission. The key 'sym_CommonKey' is not open. Please open the key before using it." My sample code for creating symmetric key "CREATE ASYMMETRIC KEY asym_CommonKey WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = 'admin@123'; GO CREATE SYMMETRIC KEY sym_CommonKey WITH ALGORITHM = AES_256 ENCRYPTION BY ASYMMETRIC KEY asym_CommonKey GO" –  vijay Dec 20 '13 at 10:26
    
That is because the first key that you create is a Asymmetric key and not a Symmetric key. So you have to give permission to the dbuser to altrer asymmetric keys. ALTER ANY ASYMMETRIC KEY. –  Edward Dortland Dec 20 '13 at 10:44
    
Hi Edward, i have given that permission to DB user(both symmetric and asymmetric alter permission).But when i was trying to open the symmetric key by asymmetric key, i am getting following error "Cannot find the asymmetric key 'asym_CommonKey', because it does not exist or you do not have permission." .My DB Query :"OPEN SYMMETRIC KEY sym_CommonKey DECRYPTION BY ASYMMETRIC KEY asym_CommonKey WITH PASSWORD = 'admin@123'" –  vijay Dec 20 '13 at 10:52
    
Did you create the asymmetric key with another user? Was that by accident or intentional? –  Edward Dortland Dec 20 '13 at 11:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.