如何配置SQL Server用户权限：可读写表但无法解密加密列？

核心思路 #

要实现「用户拥有表读写权限但无法解密加密列」，需从两个维度控制：阻止客户端获取解密所需的密钥元数据，以及限制用户对加密列的查询权限。

具体步骤 #

1. 撤销用户对密钥的元数据访问权限

Always Encrypted的解密依赖客户端获取列主密钥（CMK）和列加密密钥（CEK）的元数据，撤销VIEW DEFINITION权限后，客户端无法定位解密密钥，自然无法获取明文：

-- 替换为你的列主密钥名称和目标用户名
REVOKE VIEW DEFINITION ON COLUMN MASTER KEY::[你的CMK名称] FROM [目标SQL账户名];

-- 替换为你的列加密密钥名称和目标用户名
REVOKE VIEW DEFINITION ON COLUMN ENCRYPTION KEY::[你的CEK名称] FROM [目标SQL账户名];

2. 配置表的列级权限

授予用户对非加密列的完整读写权限，同时明确拒绝其对加密列的查询权限（DENY优先级高于GRANT，即使用户有表级权限也会被拦截）：

-- 授予非加密列的读写权限（替换实际列名和表名）
GRANT SELECT (列1, 列2) ON [你的表名] TO [目标SQL账户名];
GRANT INSERT (列1, 列2, 加密列名) ON [你的表名] TO [目标SQL账户名]; -- 若需允许写入加密列则保留
GRANT UPDATE (列1, 列2) ON [你的表名] TO [目标SQL账户名];

-- 拒绝加密列的查询权限，彻底阻止读取明文
DENY SELECT (加密列名) ON [你的表名] TO [目标SQL账户名];

3. 验证效果

用目标账户连接SSMS并启用Enable Always Encrypted参数后：

• 查询加密列会直接提示权限不足；
• 插入/更新包含加密列的数据时，客户端驱动会自动加密（无需用户操作明文），但用户无法查看插入后的明文内容；
• 若尝试绕过权限查询加密列，只会得到加密后的二进制值。

内容的提问来源于stack exchange，提问作者LA.27