Perms required to encrypt and decrypt with s key protected by cert

I want to allow a user to encrypt with a symmetric key that is secured by a
certificate.

What permissions must be granted to the user?

As shown in the script below, I _believe_ I must grant the following in
addition to nornal SELECT:

GRANT view definition on symmetric key::TestKey to testuser1

GRANT CONTROL ON CERTIFICATE::TestCert to testuser1

Can anyone who is knowledgable in encryption and certificates confirm my
theory?

Thanks
Dave


USE master
GO
SET NOCOUNT ON
GO
CREATE DATABASE test
GO
USE test
GO

--build table
IF OBJECT_ID('testTable') IS NOT NULL
DROP TABLE testTable
GO
CREATE TABLE testTable
(IDCol int IDENTITY
,decrypt_value varchar(30)
,encrypt_value varbinary(3000)
)
GO

--dummy up values
DECLARE @i int
SET @i=0
WHILE @i < 10
BEGIN
INSERT testTable (decrypt_value)
VALUES (right(cast(rand(checksum(newid())) as decimal(16, 16)), 16))
SET @i=@i+1
END
--check
SELECT * FROM testtable


--create database master key (already exists in Admin)
CREATE master key
ENCRYPTION BY password = '1239()&56GFtrdG'


--create the certificates that protect the data encryption keys
CREATE certificate TestCert
with subject = 'this is used to test perms on certs and keys'


-- Create symmetric keys
CREATE symmetric key TestKey
with algorithm = AES_256
ENCRYPTION BY certificate TestCert

--now test the key
open symmetric key TestKey
decryption by certificate TestCert

select * from sys.openkeys

--now encrypt the values in the table
UPDATE testtable
SET encrypt_value = encryptByKey(Key_GUID('TestKey'), decrypt_value)

SELECT decrypt_value
,encrypt_value
,encryptByKey(Key_GUID('TestKey'), decrypt_value)
FROM testtable

--close and check the keys
select * from sys.openkeys
close all symmetric keys
select * from sys.openkeys


--------------------
--create logins and users
CREATE LOGIN testUser1 with password = '1239()&56GFtrdG'
CREATE USER testUser1
--CREATE LOGIN testUser2 with password = '1239()&56GFtrdG'
--CREATE USER testUser2

GRANT SELECT, UPDATE, INSERT, DELETE ON testtable to testUser1
--GRANT SELECT, UPDATE, INSERT, DELETE ON testtable to testUser2

EXEC sp_helprotect testtable

--we want to allow test user to encrypt and decrypt

--Attempt 1
execute as login = 'testuser1'
select suser_name()
select * from sys.openkeys

open symmetric key TestKey
decryption by certificate TestCert
--Cannot find the symmetric key 'TestKey', because it does not exist or you
do not have permission.

--Attempt 1 fails, cannot find key
Revert
select suser_name()


--Attempt 2
--Grant View Def on key
GRANT view definition on symmetric key::TestKey to testuser1

execute as login = 'testuser1'
select suser_name()
select * from sys.openkeys

open symmetric key TestKey
decryption by certificate TestCert
--Cannot find the certificate 'TestCert', because it does not exist or you
do not have permission.

--Attempt 2 fails; cannot find cert
Revert
select suser_name()


--Attempt 3
--Grant View Def on cert
GRANT view definition ON CERTIFICATE::TestCert to testuser1

execute as login = 'testuser1'
select suser_name()
select * from sys.openkeys

open symmetric key TestKey
decryption by certificate TestCert
--Cannot find the certificate 'TestCert', because it does not exist or you
do not have permission.

--Attempt 3 fails; still cannot find cert
Revert
select suser_name()


--Attempt 4
--Grant Control on cert
GRANT CONTROL ON CERTIFICATE::TestCert to testuser1

execute as login = 'testuser1'
select suser_name()
select * from sys.openkeys

open symmetric key TestKey
decryption by certificate TestCert
--Command(s) completed successfully.

--Attempt 4 is successful
select * from sys.openkeys

--user can encrypt and decrypt data
SELECT convert (varchar, decryptbykey(encrypt_value)) AS 'encrypt_value'
,encryptByKey(Key_GUID('TestKey'), decrypt_value) AS 'decrypt_value'
FROM testtable
--encrypt_value decrypt_value
--2527358046701709 0x00B778EF2A958D4B8E374604A0F9CD3C0100000044CF142E571D9614CC984CA2092F90C4313B1971A94899A38842B9106EB6027B94AC95C768F717124990F7B07A55E7A1
--1561534982826975 0x00B778EF2A958D4B8E374604A0F9CD3C01000000F2B0C28B3DB25784E076724310E34A684770EC5AB64749967AFD580CACDDE7AA126D93C66DB34FBEFAECC9774715008E

SELECT * FROM testtable

revert
select suser_name()

--clean up
revert
DROP TABLE testTable
DROP USER testUser1
DROP LOGIN testUser1
DROP SYMMETRIC KEY TestKey
DROP CERTIFICATE TestCert
USE master
DROP DATABASE test
GO







.

Relevant Pages

  • Re: Is symmetric key distribution equivalent to symmetric key generation?
    ... http://www.garlic.com/~lynn/2005o.html#31 Is symmetric key distribution equivalent to symmetric key generation? ... dealings with the sender, has no local repository about the sender ... the digital certificate is a stale, ... and the receiver has ...
    (sci.crypt)
  • Re: SSL certificates
    ... I question the point of developing session ... > keys after the initial contact using the certificate keys. ... magnitude slower than using a symmetric key. ... With the EDH cipher suites ...
    (sci.crypt)
  • SQL Server Encryption Questions
    ... SELECT @encryptedstuff = EncryptByCert, ... declare @temp tinyint ... database to create a certificate so i assumed the certificate was pretty ... much a symmetric key that used the master key in your database. ...
    (microsoft.public.sqlserver.security)
  • Re: SSL certificates
    ... > certificate key isn't secure enough for the entire conversation, ... > negotiated session keys would also be compromised, ... computational overhead than a symmetric key ... ...
    (sci.crypt)
  • Re: Where to store private key
    ... There are, however, some known techniques which are ... However being quite new to security I still don't ... > Rijndael class to encrypt the data. ... >> uses underlying symmetric key encryption based on user principal ...
    (microsoft.public.dotnet.security)