授予适当的权限以在存储过程中使用对称密钥

我使用以下代码在SQL2012中创建了一个对称密钥(以Windows Admin用户身份登录):

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '34trg45trgf546t';
CREATE CERTIFICATE SSCert01 WITH SUBJECT = 'SS Certificate 01';
CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE SSCert01;

完成后,我将加密应用于某些数据库列.仍以管理员身份登录,我可以使用密钥成功解密列:

OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE SSCert01;
SELECT
    name,
    surname,
    CONVERT(nvarchar(50),DECRYPTBYKEY(PasswordEnc)) as DecryptedPassword
FROM
    [tbl_Users];
CLOSE SYMMETRIC KEY SSN_Key_01;

然后我将上面的代码放入存储过程中.问题是我的应用程序使用两个角色访问SQL,访问相应的proc.当这两个角色中的任何一个尝试执行包含上述代码的proc时,我看到此错误:

找不到证书'SSCert01',因为它不存在或您没有权限.密钥"SSN_Key_01"未打开.请在使用前打开钥匙.

当我以任一角色登录时,他们无法看到密钥或证书.因此,任何人都可以建议授予角色的WHICH权限,以便他们可以在存储过程(仅)中使用密钥/证书来加密/解密数据.除了加密/解密之外,不应允许角色使用密钥/证书执行任何功能.我看过MSDN /谷歌并没有更聪明.

更新 以下代码允许角色使用proc,但我担心CONTROL访问太多.有人可以提供一些清晰度吗？

GRANT CONTROL ON CERTIFICATE :: SSCert01 TO Role001;
GRANT CONTROL ON SYMMETRIC KEY :: SSN_Key_01 TO Role001;

steoleary.. 9

我通常解决这个问题的方法是将过程设置为作为所有者执行,然后确保过程的所有者具有执行解密的正确权限,很多时候proc的所有者都是DBO,所以没有额外的除了改变过程之外,还需要完成配置:

ALTER PROCEDURE proc_name
WITH EXECUTE AS OWNER
AS
OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE SSCert01;
SELECT
    name,
    surname,
    CONVERT(nvarchar(50),DECRYPTBYKEY(PasswordEnc)) as DecryptedPassword
FROM
    [tbl_Users];
CLOSE SYMMETRIC KEY SSN_Key_01;

这意味着您不必向应用程序角色或用户授予任何其他权限.