字段级加密是在应用侧对数据库中的敏感列单独做加密、密钥与数据分离存放的做法,目的是让「拖库」和「DBA 越权」不再等于明文泄露。本文给出一套可落地的 MySQL 字段级加密方案:AES-256-GCM 密文列、HMAC 盲索引支持等值查询、密钥版本化支持轮换,并附存量数据迁移与验证脚本。
适用场景
- 库中存有手机号、身份证号、银行卡号、邮箱、家庭住址、病历摘要等个人敏感信息;
- 合规要求明确「存储环节加密」,但服务器磁盘加密或云盘加密已做过,仍被判定不足;
- 数据库由外部团队或云厂商托管,DBA 能看到全部明文,内部越权风险无法用权限模型消解;
- 发生过备份文件外流、只读从库被拖库一类事件,需要降低单点失守的损失面;
- 业务需要按手机号或身份证号做精确查询,不能简单地把整列加密后放弃检索能力。
前置条件
- MySQL 5.7 及以上或 PostgreSQL 12 及以上,字符集统一为 utf8mb4;
- 应用侧具备密码学库:Python 使用
cryptography,PHP 使用sodium或openssl,Java 使用 JDK 内置 AES/GCM; - 有一个与应用服务器分离的密钥存放位置,例如 KMS、Vault,或至少是权限 600、不随代码库分发的配置文件;
- 已确认哪些列需要加密、哪些查询必须保留,这决定盲索引如何设计;
- 迁移前完成全量备份并验证可恢复:
mysqldump --single-transaction加一次实际恢复演练。
原理说明
先要理解为什么「磁盘加密」和「TDE 透明加密」解决不了这个问题。这两类方案的密钥由数据库或云平台自己管理,数据在落盘时加密、在查询时自动解密,因此任何持有数据库连接凭据的人——包括 DBA、包括拿到只读从库的攻击者——看到的都是明文。它们的防护目标是「物理介质被窃取」,不是「逻辑越权」。
字段级加密把加解密动作移到应用进程里:数据库只存密文,密钥在应用侧,DBA 即便 dump 全库也只是一堆二进制。代价是数据库失去对这些列的运算能力,等值查询、模糊查询、排序、聚合都会失效,必须额外设计检索结构。
算法选择上,必须用带认证的加密模式,即 AES-256-GCM 或 ChaCha20-Poly1305。原因有两点:一是 GCM 附带 16 字节认证标签(tag),密文被篡改一个比特解密就会失败,能防住「改密文让系统解密出攻击者期望值」这类攻击;二是 GCM 只需要一个 96 位随机 nonce,实现简单。绝对不要用 ECB 模式——相同明文产生相同密文,攻击者通过比对密文就能推断字段值,例如把整列密文按出现频次排序,出现最多的那一条往往就是「男」或某个默认值。
检索问题用盲索引(blind index)解决:额外存一列 HMAC-SHA256(索引密钥, 规范化明文)。查询时应用先算 HMAC,再用这个值去等值匹配。因为索引密钥与数据分开存放,攻击者拿到数据库也无法反推明文;同一明文始终产生同一索引值,所以等值查询、唯一约束、去重都能正常工作。注意盲索引只支持等值,不支持范围与模糊——这是设计上必须接受的取舍。
密钥管理采用两层结构:主密钥(KEK)存放在 KMS 或环境变量里,数据密钥(DEK)由 KEK 加密后存配置文件,每个字段或每张表一个 DEK。这样轮换时只需重加密 DEK,不必立即重写全部数据;同时用 key_version 列标记密文用的是哪一版 DEK,实现新旧共存与灰度切换。
操作步骤
第一步:改造表结构,密文与索引分离存储
密文列用 VARBINARY,不要用 TEXT——TEXT 会走额外排序规则与编码转换,可能破坏二进制内容。nonce 与 key_version 单独成列,便于轮换与排查:
-- 以客户表为例,加密 phone 与 id_card 两列
ALTER TABLE customer
ADD COLUMN phone_bi CHAR(64) NULL COMMENT '手机号盲索引',
ADD COLUMN phone_ct VARBINARY(512) NULL COMMENT '手机号密文(GCM含tag)',
ADD COLUMN phone_nonce VARBINARY(12) NULL COMMENT '手机号nonce',
ADD COLUMN phone_key_ver SMALLINT UNSIGNED NOT NULL DEFAULT 1,
ADD COLUMN idc_bi CHAR(64) NULL,
ADD COLUMN idc_ct VARBINARY(512) NULL,
ADD COLUMN idc_nonce VARBINARY(12) NULL,
ADD COLUMN idc_key_ver SMALLINT UNSIGNED NOT NULL DEFAULT 1;
-- 盲索引唯一约束:既能去重,也能让等值查询走索引
ALTER TABLE customer
ADD UNIQUE KEY uk_customer_phone_bi (phone_bi),
ADD KEY idx_customer_idc_bi (idc_bi);
-- 迁移完成并校验无误后,再删除明文列(务必先备份)
-- ALTER TABLE customer DROP COLUMN phone, DROP COLUMN id_card;
保留明文列直到校验通过,是迁移阶段的关键安全垫。任何一步出问题都能回退,代价只是多占一点空间。
第二步:实现加密、解密与盲索引
下面这套函数是整套方案的核心,建议直接封装成独立模块,业务代码只调用 encrypt_field / decrypt_field / blind_index,不允许在业务层手写 crypto 调用:
# crypto_fields.py
import os, hmac, hashlib
from cryptography.hazmat.primitives.ciphers.aead import AESGCM
NONCE_LEN = 12
def load_dek(kms_client, key_name: str, version: int) -> bytes:
"""从 KMS 取回被 KEK 包裹的 DEK 并解包,返回 32 字节原始密钥"""
wrapped = kms_client.get_secret(f"{key_name}:v{version}")
return kms_client.unwrap(wrapped) # 具体实现按所用 KMS 而定
def encrypt_field(plaintext: str, dek: bytes) -> tuple[bytes, bytes]:
"""返回 (密文, nonce);密文末尾 16 字节是 GCM 认证标签"""
if plaintext is None:
raise ValueError("plaintext required")
aes = AESGCM(dek)
nonce = os.urandom(NONCE_LEN) # 每次加密都必须重新随机
ct = aes.encrypt(nonce, plaintext.encode("utf-8"), None)
return ct, nonce
def decrypt_field(ct: bytes, nonce: bytes, dek: bytes) -> str:
"""认证失败会抛 InvalidTag,说明密文被篡改或密钥不对"""
aes = AESGCM(dek)
return aes.decrypt(nonce, ct, None).decode("utf-8")
def blind_index(value: str, index_key: bytes) -> str:
"""规范化后做 HMAC-SHA256,输出 64 位十六进制"""
norm = "".join(value.split()).lower() # 去空白、统一小写
return hmac.new(index_key, norm.encode("utf-8"), hashlib.sha256).hexdigest()
def mask(value: str, keep_head: int = 3, keep_tail: int = 4) -> str:
"""展示层脱敏,避免解密后直接把明文吐给前端"""
if len(value) <= keep_head + keep_tail:
return "*" * len(value)
return value[:keep_head] + "*" * (len(value) - keep_head - keep_tail) + value[-keep_tail:]
三点实现细节必须守住:nonce 每次加密都重新随机,绝不复用,复用会让 GCM 的认证性彻底失效;索引密钥与数据密钥分开,索引密钥只用于 HMAC,且绝不能和 DEK 存在同一份配置里;解密失败必须抛异常而不是返回空串,否则密文被篡改会被静默吞掉,等发现时已经无从追溯。
第三步:分批迁移存量数据并逐条校验
不要写一条 UPDATE 全表加密,锁表加长事务会直接打挂线上。按主键分页、每批提交、每批校验,可随时中断续跑:
import pymysql
from crypto_fields import encrypt_field, blind_index, decrypt_field
BATCH = 500
def migrate(conn, dek_phone: bytes, dek_idc: bytes, idx_key: bytes, key_ver: int = 1):
cur = conn.cursor()
last_id = 0
total = 0
while True:
cur.execute(
"SELECT id, phone, id_card FROM customer "
"WHERE id > %s AND phone IS NOT NULL ORDER BY id LIMIT %s",
(last_id, BATCH),
)
rows = cur.fetchall()
if not rows:
break
for cid, phone, idc in rows:
p_ct, p_nonce = encrypt_field(phone, dek_phone)
p_bi = blind_index(phone, idx_key)
# 回读校验:解出来的必须和原文一致,否则中止
assert decrypt_field(p_ct, p_nonce, dek_phone) == phone, f"roundtrip fail id={cid}"
if idc:
i_ct, i_nonce = encrypt_field(idc, dek_idc)
i_bi = blind_index(idc, idx_key)
else:
i_ct, i_nonce, i_bi = None, None, None
cur.execute(
"UPDATE customer SET phone_ct=%s, phone_nonce=%s, phone_bi=%s, phone_key_ver=%s, "
"idc_ct=%s, idc_nonce=%s, idc_bi=%s, idc_key_ver=%s WHERE id=%s",
(p_ct, p_nonce, p_bi, key_ver, i_ct, i_nonce, i_bi, key_ver, cid),
)
last_id = cid
total += 1
conn.commit()
print(f"committed {total} rows, last_id={last_id}")
return total
if __name__ == "__main__":
conn = pymysql.connect(host="127.0.0.1", user="app", password="***",
database="shop", charset="utf8mb4", autocommit=False)
print("migrated:", migrate(conn, DEK_PHONE, DEK_IDC, INDEX_KEY))
迁移完成后必须做一次反向比对:抽 1000 条密文解密,与备份库中的明文逐条对比,全部一致才允许删除明文列。
第四步:改造查询逻辑,等值走盲索引
原本的 WHERE phone = ? 要改成 WHERE phone_bi = ?,参数由应用计算。这一步最容易漏改,漏改的查询会静默返回空结果,而不是报错:
-- 改造前:按明文等值查询(加密后将全部返回空)
SELECT id, name FROM customer WHERE phone = '13800000000';
-- 改造后:应用先算 blind_index,再按索引列查询
SELECT id, name, phone_ct, phone_nonce, phone_key_ver
FROM customer WHERE phone_bi = 'a3f1c9...'; -- 由应用侧 HMAC 得出
-- 范围/前缀场景:额外保留一个不敏感的号段桶列,用组合条件缩小范围
ALTER TABLE customer ADD COLUMN phone_prefix CHAR(3) NULL COMMENT '号段桶,仅前3位';
SELECT id FROM customer WHERE phone_prefix = '138' AND phone_bi IN (...);
若业务确实需要「按手机号模糊搜索」,只能退回低敏感前缀列或禁止该功能,不要试图用 LIKE '%x%' 匹配密文——密文无前缀性,这样做既无结果又浪费索引。同时把应用数据库账号的权限收到最小:只授予需要的列,撤销 FILE 与 PROCESS,禁止该账号执行 mysqldump 全库导出。
第五步:密钥轮换与版本共存
有了 key_version 列,轮换可以渐进进行:新数据写 v2,读取时按行上记录的版本选择对应 DEK,后台任务再慢慢把 v1 重加密为 v2。任何时候都可以停在中途而不影响业务:
def read_phone(row, deks: dict) -> str:
"""按行记录的版本号选择 DEK,天然支持新旧共存"""
ver = row["phone_key_ver"]
if ver not in deks:
raise RuntimeError(f"missing dek version {ver}")
return decrypt_field(row["phone_ct"], row["phone_nonce"], deks[ver])
def rotate(conn, deks: dict, new_ver: int):
"""把仍为旧版本的行重加密到新版本,分批执行"""
cur = conn.cursor(pymysql.cursors.DictCursor)
while True:
cur.execute(
"SELECT id, phone_ct, phone_nonce, phone_key_ver FROM customer "
"WHERE phone_key_ver < %s LIMIT 500", (new_ver,))
rows = cur.fetchall()
if not rows:
break
for r in rows:
plain = read_phone(r, deks)
ct, nonce = encrypt_field(plain, deks[new_ver])
cur.execute(
"UPDATE customer SET phone_ct=%s, phone_nonce=%s, phone_key_ver=%s WHERE id=%s",
(ct, nonce, new_ver, r["id"]))
conn.commit()
配置验证
- 密文不可读:
SELECT phone_ct FROM customer LIMIT 3;应显示二进制乱码,用HEX()查看也不含明文特征; - 同值密文不同:对同一手机号加密两次,两段密文与 nonce 必须不同,证明 nonce 未复用;
- 往返一致:随机抽 1000 条解密并与备份明文比对,差异数必须为 0;
- 盲索引可查:用已知手机号计算 HMAC 后查询,能命中且
EXPLAIN显示走了uk_customer_phone_bi; - 篡改被拒:手动改一个字节的
phone_ct后再解密,必须抛出InvalidTag; - 密钥不落库:全库搜索密钥字符串应零命中,
/etc/app/config权限为 600 且属主非 web 用户。
常见问题
问:为什么不用 MySQL 自带的 AES_ENCRYPT()?
三个致命问题。密钥要写在 SQL 语句里,会原样进入 general log、慢查询日志和 binlog,任何能读日志的人都能拿到密钥;AES_ENCRYPT() 默认使用 ECB 模式,相同明文产生相同密文,可被频率分析;加解密在数据库侧完成,DBA 依然能构造查询看到明文。字段级加密的前提就是「数据库不可信」,把密码学运算放回数据库等于放弃这个前提。
问:盲索引会不会被字典穷举反推?
会,如果索引密钥泄露。手机号只有约 10 亿个有效组合,攻击者拿到 idx_key 后几分钟就能建出全量对照表。所以索引密钥必须与数据库物理分离(KMS 或独立密钥服务),并且用 HMAC 而非裸 SHA256——裸哈希可以被公开彩虹表预计算,HMAC 在没有密钥时无法预计算。若风险容忍度更低,可对手机号额外加固定长度的盐再 HMAC,代价是盐泄露后同样可穷举。
问:加密后 ORDER BY 和 GROUP BY 还能用吗?
不能,密文没有保序性。需要排序或分组的场景只有三条路:把该列移出加密范围(通常是时间、金额等非敏感列);额外存一列可排序的桶值(如金额区间、日期截断);或在应用层取回后排序。前两条可接受,第三条在数据量大时不可行,选型阶段就要想清楚。
问:字段级加密与数据库审计、脱敏是什么关系?
三者控制点不同,不能互相替代。字段级加密防「数据被拿走后的可读性」;数据库审计防「越权访问行为不被记录」;脱敏防「正常业务展示环节过度暴露」。完整方案通常是:加密存储、审计访问、展示时再做掩码,本文的 mask() 函数就是第三层的兜底。
总结
字段级加密不是「加个 AES 就完事」,而是一套包含算法选择、检索结构、密钥分层、迁移路径的工程方案。落地的判断标准有四条:数据库里看不到明文、相同明文不产生相同密文、等值查询仍然走索引、密钥不在数据库可触及的范围内。四条同时满足,拖库的成本才会从「下载一个 dump」变成「拿到密文却无解」。
实施顺序建议:先小范围试点单列(如手机号),跑通加密、迁移、查询改造、轮换四条链路,再推广到身份证与银行卡。一次性全表改造的风险远高于分列推进,而字段级加密的收益本身是逐列累加的。