I've received this question a couple of times before: is it possible to restrict a user to encrypt data, but not be able to decrypt it? The answer to this is yes, but before I present the solution, let's discuss a little how symmetric keys are used. What restricts a user from being able to encrypt or decrypt with a symmetric key is the ability to open it. If a key can be opened by a user, then he can both encrypt and decrypt with it. To restrict the use of the key, we need to remove the user's ability to open the key. We can do this by encapsulating the encryption function and the opening of the key within a procedure that has permissions to do these operations (through signing) and by granting EXECUTE permission on this procedure to the user. The user will be able to access the key through the procedure, but not directly. Note that there is a window within the procedure where the key would be available to use if the user can execute code in parallel with the procedure. I don't think this can be exploited now, but this might be a possible issue with this scheme.
Here's the code that demonstrates this solution:
---- Demo for how to restrict a user's capability to decrypt-- while allowing him to encrypt--create database test
-- create a master keycreate master key encryption by password = 'Avcptnwgu@)!)'
-- create a certificate to protect the symmetric key that we will createcreate certificate cert_protect_skey_data with subject = 'Certificate for encrypting symmetric key'
-- create the symmetric key that will be used to protect datacreate symmetric key skey_data with algorithm = triple_des encryption by certificate cert_protect_skey_data
-- now create a procedure for encrypting data using the symmetric keycreate procedure sp_encrypt_with_skey_data @plaintext varbinary(8000), @ciphertext varbinary(8000) outputasbegin open symmetric key skey_data decryption by certificate cert_protect_skey_data set @ciphertext = encryptbykey(key_guid('skey_data'), @plaintext) close symmetric key skey_dataend
-- verify the encryption procedure; execute as batch the next linesdeclare @plaintext varbinary(200)set @plaintext = convert(varbinary(200), 'Plaintext')declare @ciphertext varbinary(200)exec sp_encrypt_with_skey_data @plaintext, @ciphertext outputprint 'Ciphertext: 'print @ciphertextprint 'Plaintext: 'open symmetric key skey_data decryption by certificate cert_protect_skey_dataprint convert(varchar(200), decryptbykey(@ciphertext))close symmetric key skey_datago
-- now create a principal with no access to the keycreate login alice with password = 'TiA''ssptncgt#)))'create user alice
-- we'll allow Alice to execute the stored procedure;-- we want her to be able to encrypt with the key-- but to not be able to decryptgrant execute on sp_encrypt_with_skey_data to alice
-- verify what Alice can doexecute as login = 'Alice'select suser_name()
-- she can execute the procedure, but she can't access the key-- no ownership chaining for keys!declare @plaintext varbinary(200)set @plaintext = convert(varbinary(200), 'Plaintext')declare @ciphertext varbinary(200)exec sp_encrypt_with_skey_data @plaintext, @ciphertext outputprint 'Ciphertext: 'print @ciphertext
-- she obviously can't access the key directly eitheropen symmetric key skey_data decryption by certificate cert_protect_skey_data
-- revert contextrevert
-- now let's sign the procedure, so it can access the key-- create a certificate for signingcreate certificate cert_sign2use_skey_data with subject = 'Certificate for signing code that will use the symmetric key'
-- create a user mapped to the certificate, so that we can grant permissions to the certificatecreate user u_cert_sign2use_skey_data for certificate cert_sign2use_skey_data
-- grant permissionsgrant view definition on symmetric key::skey_data to u_cert_sign2use_skey_datagrant control on certificate::cert_protect_skey_data to u_cert_sign2use_skey_data
-- sign the procedureadd signature to sp_encrypt_with_skey_data by certificate cert_sign2use_skey_data
-- now Alice should be able to really use the procedureexecute as login = 'alice'select suser_name()
-- note we still don't have access to the key directlyopen symmetric key skey_data decryption by certificate cert_protect_skey_data
-- let's try an encryption; execute as batch the next lines-- note that the encryption succeeds but the decryption will faildeclare @plaintext varbinary(200)set @plaintext = convert(varbinary(200), 'Plaintext')declare @ciphertext varbinary(200)exec sp_encrypt_with_skey_data @plaintext, @ciphertext outputprint 'Ciphertext: 'print @ciphertextprint 'Plaintext: 'print convert(varchar(200), decryptbykey(@ciphertext))go
-- cleanupuse masterdrop database testdrop login alice-- EOD