能否根据明文类型确定ENCRYPTBYKEY最大返回值？字段加密方案咨询

一、能否根据明文数据类型确定ENCRYPTBYKEY的最大返回值？ #

答案是可以参考明文类型估算，但不能完全只依赖它，因为加密后的最终长度还受加密算法、是否使用认证器（authenticator）等因素影响。

具体来说，ENCRYPTBYKEY的返回值是varbinary类型，它的长度由以下几个部分组成：

• 固定头部：4字节的版本/标识信息 + 16字节的密钥GUID（用来关联加密时使用的对称密钥）
• 加密后的密文：这部分长度取决于明文的字节数和加密算法的块大小。比如你用的AES-256，块大小是16字节，明文会被填充到最近的16字节倍数长度（PKCS#7填充）。举个例子：
  • 如果明文是varchar(10)（10字节），填充后变成16字节；如果是nvarchar(10)（20字节），填充后变成32字节
• 可选认证值：如果加密时指定了@authenticator参数，会额外添加32字节的HMAC值用于验证数据完整性

所以你可以根据明文类型的字节数（比如varchar(n)是n字节，nvarchar(n)是2n字节），加上固定的20字节（4+16），再加上认证器的32字节（如果用了的话），再向上取整到算法块大小的倍数，来估算最大返回值。

举个实际的例子：用AES-256加密nvarchar(100)的明文（200字节），不使用认证器，那么加密后的长度是20（固定头） + 208（200向上取整到16的倍数是208）= 228字节左右。

二、关于你给出的加密实现代码的建议 #

你的代码步骤是正确的，但在实际生产环境中还有几个需要注意的点：

• 不要随意删除密钥和证书：你代码最后写了DROP SYMMETRIC KEY和DROP CERTIFICATE，这会导致加密后的数据永久无法解密！只有当你确定不再需要解密这些数据时才能执行删除操作。
• 加密现有表字段的正确流程：如果要加密现有表的字段，应该先修改字段类型为足够长度的varbinary（比如根据上面估算的最大长度设置，比如varbinary(2000)，预留足够余量），然后批量更新数据：

  -- 先修改字段类型
  ALTER TABLE YourTable ALTER COLUMN YourField varbinary(2000);
  
  -- 打开密钥后批量加密
  OPEN SYMMETRIC KEY SK_01 DECRYPTION BY CERTIFICATE CERT_01;
  UPDATE YourTable SET YourField = ENCRYPTBYKEY(KEY_GUID('SK_01'), OriginalField);
  CLOSE SYMMETRIC KEY SK_01;
  

• 权限控制：确保执行加密/解密操作的用户拥有对应的权限：比如CONTROL权限在证书上，VIEW DEFINITION权限在对称密钥上，避免权限不足导致操作失败。
• 主密钥的备份：创建主密钥后，一定要备份到安全的地方，比如：

  BACKUP MASTER KEY TO FILE = 'C:\SecureLocation\master_key_backup'
  ENCRYPTION BY PASSWORD = 'another_strong_password@';
  

  一旦主密钥损坏或丢失，备份是恢复加密数据的关键。
• 考虑使用认证器：如果需要验证加密数据的完整性（防止被篡改），可以在加密时添加认证器，比如：

  SELECT ENCRYPTBYKEY(KEY_GUID('SK_01'), 'test', 1, CONVERT(varbinary, 'YourAuthenticatorValue'));
  

  解密时需要对应提供相同的认证器值才能解密成功。

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