If you create keys without a certificate like:
CREATE SYMMETRIC KEY smTestKey
, 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
- 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
--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'
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.
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.