仅允许Service-Dev-Acc执行SQL Server列级解密代码的权限配置问题

问题描述 #

我们已对存储含用户名/密码连接字符串的CON_String列实现列级加密，解密查询代码如下：

OPEN SYMMETRIC KEY AdventureSymmetricKey 
DECRYPTION BY CERTIFICATE AdventureCertificate

SELECT CONVERT(VARCHAR(2000), DecryptByKey(CON_String)) as 'Decrypted_Con_String'
FROM   dbo.Connection_Details

CLOSE SYMMETRIC KEY AdventureSymmetricKey 

当前所有开发者均可执行该代码查看解密结果，需求是仅允许Service-Dev-Acc账号通过定时任务执行此查询，其他用户无法获取解密后的值。

我们尝试了以下权限配置：

给Service-Dev-Acc授权 #

GRANT CONTROL ON SYMMETRIC KEY::AdventureSymmetricKey TO Service-Dev-Acc;
GRANT CONTROL ON CERTIFICATE::AdventureCertificate TO Service-Dev-Acc;
GRANT VIEW DEFINITION ON SYMMETRIC KEY::AdventureSymmetricKey TO Service-Dev-Acc;
GRANT VIEW DEFINITION ON CERTIFICATE::AdventureCertificate TO Service-Dev-Acc;

拒绝PUBLIC权限 #

DENY CONTROL ON SYMMETRIC KEY::AdventureSymmetricKey TO PUBLIC;
DENY CONTROL ON CERTIFICATE::AdventureCertificate TO PUBLIC;
DENY VIEW DEFINITION ON SYMMETRIC KEY::AdventureSymmetricKey TO PUBLIC;
DENY VIEW DEFINITION ON CERTIFICATE::AdventureCertificate TO PUBLIC;

但操作后开发者仍能执行解密代码查看结果。我们不考虑行级安全、视图、带EXECUTE AS USER的表值函数方案，仅希望通过SQL Server的GRANT/DENY权限控制解决问题。

解决方案 #

问题出在PUBLIC权限的冲突以及过度授权，需要更精准的权限控制：

1. 清理之前的PUBLIC权限配置
   直接给PUBLIC加DENY可能影响系统默认账号，先撤销相关配置：

   REVOKE CONTROL ON SYMMETRIC KEY::AdventureSymmetricKey FROM PUBLIC;
   REVOKE CONTROL ON CERTIFICATE::AdventureCertificate FROM PUBLIC;
   REVOKE VIEW DEFINITION ON SYMMETRIC KEY::AdventureSymmetricKey FROM PUBLIC;
   REVOKE VIEW DEFINITION ON CERTIFICATE::AdventureCertificate FROM PUBLIC;
   

2. 给Service-Dev-Acc授予最小必要权限
   解密操作不需要CONTROL权限，仅需以下权限即可完成：

   -- 密钥和证书的必要权限
   GRANT VIEW DEFINITION ON SYMMETRIC KEY::AdventureSymmetricKey TO Service-Dev-Acc;
   GRANT REFERENCES ON SYMMETRIC KEY::AdventureSymmetricKey TO Service-Dev-Acc;
   GRANT VIEW DEFINITION ON CERTIFICATE::AdventureCertificate TO Service-Dev-Acc;
   GRANT REFERENCES ON CERTIFICATE::AdventureCertificate TO Service-Dev-Acc;
   
   -- 授予表的查询权限（若未配置）
   GRANT SELECT ON dbo.Connection_Details TO Service-Dev-Acc;
   

3. 精准拒绝开发者的解密权限
   针对开发者所属的角色或单个用户，直接拒绝相关权限：

   -- 替换为实际开发者角色
   DENY VIEW DEFINITION ON SYMMETRIC KEY::AdventureSymmetricKey TO Devs_Role;
   DENY REFERENCES ON SYMMETRIC KEY::AdventureSymmetricKey TO Devs_Role;
   DENY VIEW DEFINITION ON CERTIFICATE::AdventureCertificate TO Devs_Role;
   DENY REFERENCES ON CERTIFICATE::AdventureCertificate TO Devs_Role;
   
   -- 若有单独开发者账号，逐个拒绝
   DENY VIEW DEFINITION ON SYMMETRIC KEY::AdventureSymmetricKey TO [Dev_User1];
   DENY REFERENCES ON SYMMETRIC KEY::AdventureSymmetricKey TO [Dev_User1];
   

4. 验证权限效果
   用开发者账号执行解密代码会因无权限打开对称密钥报错，而Service-Dev-Acc可正常解密。

关键说明 #

• 遵循最小权限原则：CONTROL权限过于宽泛，仅VIEW DEFINITION和REFERENCES即可满足解密需求。
• DENY权限优先级高于GRANT，精准拒绝目标用户/角色比给PUBLIC加DENY更安全，避免影响系统账号。
• 确保Service-Dev-Acc未被包含在被拒绝权限的角色中。

内容的提问来源于stack exchange，提问作者Vishwanath Dalvi