SQL Server 2019多用户列级加密访问方案实现咨询

一、密钥体系设计（匹配你的需求） #

你的设想核心是用统一密钥加密所有列，再给每个用户分配专属解密密钥，结合列权限控制实现需求。我们基于SQL Server的密钥层次结构调整优化，确保管理员无用户密钥时无法访问加密数据：

• 用**数据库主密钥（DMK）**保护核心加密密钥，且仅用密码加密（不绑定SQL Server服务主密钥），避免管理员通过服务权限绕过密钥验证。
• 用**列加密密钥（CEK）**统一加密10列数据（对称加密效率高，适合批量列加密）。
• 为每个用户创建专属对称密钥，用该密钥加密CEK的副本，用户仅需保管自己的密钥密码，即可解密CEK进而访问授权列。

二、具体实现步骤 #

1. 创建数据库主密钥（DMK） #

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongDMKPassword@2024';
-- 重要：不要执行 ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
-- 此操作会让DMK被SQL Server服务主密钥加密，管理员可通过服务权限获取DMK，违反你的需求

2. 创建列加密密钥（CEK） #

这是用来加密所有10列的核心对称密钥，采用AES-256算法（SQL Server推荐的高强度加密标准）：

CREATE SYMMETRIC KEY ColumnEncryptionKey
WITH ALGORITHM = AES_256
ENCRYPTION BY MASTER KEY;

3. 为每个用户创建专属解密密钥 #

以用户UserA为例，创建其专属密钥并加密备份CEK（每个用户重复此步骤）：

-- 创建UserA的专属对称密钥，密码由UserA自行保管
CREATE SYMMETRIC KEY UserA_DecryptionKey
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = 'UserA_SecretPass@123';

-- 打开CEK和UserA的密钥，将CEK用UserA的密钥加密后备份到本地文件
OPEN SYMMETRIC KEY ColumnEncryptionKey DECRYPTION BY MASTER KEY = 'StrongDMKPassword@2024';
OPEN SYMMETRIC KEY UserA_DecryptionKey DECRYPTION BY PASSWORD = 'UserA_SecretPass@123';

BACKUP SYMMETRIC KEY ColumnEncryptionKey
TO FILE = 'D:\SQL_Secure_Keys\CEK_UserA.key'
ENCRYPTION BY SYMMETRIC KEY UserA_DecryptionKey;

-- 关闭密钥，避免长期占用
CLOSE SYMMETRIC KEY ColumnEncryptionKey;
CLOSE SYMMETRIC KEY UserA_DecryptionKey;

4. 加密表中列数据 #

假设目标表为UserLoginAccounts，10列对应Col1到Col10（原数据类型为NVARCHAR(MAX)）：

-- 打开CEK，开始加密列
OPEN SYMMETRIC KEY ColumnEncryptionKey DECRYPTION BY MASTER KEY = 'StrongDMKPassword@2024';

-- 逐个修改列类型为VARBINARY并加密数据
ALTER TABLE UserLoginAccounts ALTER COLUMN Col1 VARBINARY(MAX);
UPDATE UserLoginAccounts SET Col1 = EncryptByKey(Key_GUID('ColumnEncryptionKey'), Col1);

ALTER TABLE UserLoginAccounts ALTER COLUMN Col2 VARBINARY(MAX);
UPDATE UserLoginAccounts SET Col2 = EncryptByKey(Key_GUID('ColumnEncryptionKey'), Col2);

-- 重复上述ALTER和UPDATE操作，完成Col3到Col10的加密

CLOSE SYMMETRIC KEY ColumnEncryptionKey;

5. 配置用户列访问权限 #

给用户分配仅能查看自选3列的权限，以UserA自选Col1、Col3、Col5为例：

-- 先创建数据库用户（如果未创建）
CREATE USER UserA FOR LOGIN UserA_Login;

-- 授予指定列的SELECT权限
GRANT SELECT ON UserLoginAccounts(Col1, Col3, Col5) TO UserA;

6. 用户解密数据的操作流程 #

用户登录后，需先打开自己的专属密钥，解密CEK，再查询解密后的数据：

-- UserA执行的解密查询
OPEN SYMMETRIC KEY UserA_DecryptionKey DECRYPTION BY PASSWORD = 'UserA_SecretPass@123';
OPEN SYMMETRIC KEY ColumnEncryptionKey DECRYPTION BY SYMMETRIC KEY UserA_DecryptionKey
    FROM FILE = 'D:\SQL_Secure_Keys\CEK_UserA.key';

-- 查询授权列并解密
SELECT 
    ID,
    CONVERT(NVARCHAR(MAX), DecryptByKey(Col1)) AS Col1,
    CONVERT(NVARCHAR(MAX), DecryptByKey(Col3)) AS Col3,
    CONVERT(NVARCHAR(MAX), DecryptByKey(Col5)) AS Col5
FROM UserLoginAccounts;

-- 操作完成后关闭密钥
CLOSE SYMMETRIC KEY ColumnEncryptionKey;
CLOSE SYMMETRIC KEY UserA_DecryptionKey;

三、关键注意事项 #

• 密钥安全：用户专属密钥的密码必须由用户自行保管，绝对不能告知管理员；DMK的密码由运维人员离线存储，仅用于密钥备份/恢复场景。
• 性能优化：列级加密会增加CPU负载，建议对查询频繁的加密列创建加密索引（SQL Server支持基于加密列的索引，需确保索引与加密算法兼容）。
• 密钥备份：定期将DMK、原始CEK备份到离线安全存储，避免密钥丢失导致数据永久无法恢复。
• 重叠列处理：多个用户访问同一列时，只要各自的专属密钥能解密CEK，即可正常访问，无需额外配置，完全匹配你的需求。
• 管理员权限限制：禁止给管理员分配加密列的SELECT权限，即使管理员获取到DMK密码，无用户专属密钥也无法解密数据。

内容的提问来源于stack exchange，提问作者Sheikh Nazimuddin