SQL Server 2008 – How to Grant Permission for Creating, Reading, and Executing Symmetric Key

encryptionsql-server-2008

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 ?

Best Answer

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.
Related Question