SQL Server中解密加密列并关联表创建视图的技术问询

我来帮你梳理下这个加密键关联+创建视图的解决方案，结合你给出的部分代码，咱们一步步把逻辑补全、优化：

1. 先把对称密钥的使用逻辑理清楚 #

你开头打开密钥的语句是对的，但有几个细节要注意：

OPEN symmetric KEY decryption_key 
DECRYPTION BY password = 'password';

• 生产环境绝对不要硬编码密码，建议用证书/非对称密钥来保护对称密钥，避免密码泄露风险；
• 密钥只在当前会话中有效，视图本身不会保留密钥打开状态，后续查询视图前得重新打开密钥（或者用存储过程封装这个操作）。

2. 完善解密+多表关联的视图创建语句 #

假设你要关联tableA和tableB，它们的加密关联键都是accountno，那可以把解密逻辑和关联逻辑整合起来：

OPEN symmetric KEY decryption_key 
DECRYPTION BY password = 'password';

CREATE VIEW new_view AS
SELECT 
    a.id, 
    a.yearid, 
    a.quarterid,
    -- 解密用户信息字段
    CONVERT(VARCHAR(200), DecryptByKey(a.accountno)) AS AccountNo,
    CONVERT(NVARCHAR(200), DecryptByKey(a.firstname)) AS FirstName,
    -- 关联其他表并解密对应字段
    b.order_amount,
    CONVERT(NVARCHAR(200), DecryptByKey(b.lastname)) AS LastName
FROM tableA a
-- 用解密后的键做关联，注意两边数据类型要一致
JOIN tableB b 
    ON CONVERT(VARCHAR(200), DecryptByKey(a.accountno)) = CONVERT(VARCHAR(200), DecryptByKey(b.accountno));

-- 显式关闭密钥更规范，会话结束也会自动关闭
CLOSE symmetric KEY decryption_key;

3. 避坑要点 #

• 数据类型匹配：解密后的字段一定要转成相同类型再关联，比如两边都转成VARCHAR(200)，不然会因为类型不匹配关联失败；
• 性能优化：如果表数据量大，行级解密+关联会很慢，因为没法用索引。可以考虑创建持久化计算列提前解密并建索引：

  ALTER TABLE tableA 
  ADD DecryptedAccountNo AS CONVERT(VARCHAR(200), DecryptByKey(accountno)) PERSISTED;
  CREATE INDEX IX_tableA_DecryptedAccountNo ON tableA(DecryptedAccountNo);
  

  之后关联直接用这个计算列就行，能大幅提升速度；
• 权限控制：确保创建视图的用户有SELECT、VIEW DEFINITION权限，以及打开对称密钥的权限；
• 查询视图的便捷性：因为每次查视图都要先打开密钥，你可以封装成存储过程：

  CREATE PROCEDURE GetEncryptedViewData
  AS
  BEGIN
      OPEN symmetric KEY decryption_key 
      DECRYPTION BY password = 'password'; -- 生产环境换成证书解密
      SELECT * FROM new_view;
      CLOSE symmetric KEY decryption_key;
  END;
  

  调用存储过程就能直接拿到解密后的关联数据了。

内容的提问来源于stack exchange，提问作者Pat Doyle