# 数据库改造 DDL（草案） > 密码改造相关表结构扩展脚本草案。 > 正式执行前须在测试环境验证，并与《数据分类与字段加密映射表.md》对齐。 | 脚本 | 阶段 | 说明 | | --- | --- | --- | | `sql/crypto_phase1.sql` | 一期 | 已提供：`sys_user` 扩展、`sys_login_policy` | | `sql/crypto_migration.sql` | 二～三期 | 全量 DDL（待编写） | --- ## 1. 用户表扩展（身份鉴别 + 个人信息） > 一期仅执行 `cert_sn`、`cert_subject`、`login_policy` 三列；其余加密列属二期。 ```sql -- sys_user 扩展：证书登录 + 加密元数据（字段级 hmac 可按列添加，此处示例常用列） ALTER TABLE sys_user ADD COLUMN cert_sn VARCHAR(128) DEFAULT NULL COMMENT '国密证书序列号' AFTER remark, ADD COLUMN cert_subject VARCHAR(512) DEFAULT NULL COMMENT '证书主题 DN' AFTER cert_sn, ADD COLUMN login_policy VARCHAR(32) DEFAULT 'PASSWORD' COMMENT '登录策略：PASSWORD/CERT/SMS_2FA' AFTER cert_subject, ADD COLUMN phonenumber_cipher VARCHAR(512) DEFAULT NULL COMMENT '手机号 SM4 密文' AFTER phonenumber, ADD COLUMN phonenumber_hmac VARCHAR(128) DEFAULT NULL COMMENT '手机号 SM3 HMAC' AFTER phonenumber_cipher, ADD COLUMN email_cipher VARCHAR(512) DEFAULT NULL COMMENT '邮箱 SM4 密文' AFTER email, ADD COLUMN email_hmac VARCHAR(128) DEFAULT NULL COMMENT '邮箱 SM3 HMAC' AFTER email_cipher, ADD COLUMN nick_name_hmac VARCHAR(128) DEFAULT NULL COMMENT '昵称完整性 HMAC' AFTER nick_name, ADD COLUMN password_hmac VARCHAR(128) DEFAULT NULL COMMENT '口令完整性 HMAC' AFTER password, ADD COLUMN integrity_hmac VARCHAR(128) DEFAULT NULL COMMENT '用户权限相关字段行级 HMAC' AFTER password_hmac, ADD COLUMN crypto_key_ver INT DEFAULT 1 COMMENT '加密密钥版本' AFTER integrity_hmac; CREATE INDEX idx_sys_user_cert_sn ON sys_user (cert_sn); ``` **说明** - 迁移期可保留原 `phonenumber`、`email` 明文列，双写验证后删除或置空 - `password` 若改 SM4 存储，列长需扩至 `VARCHAR(512)` --- ## 2. 权限表行级完整性 ```sql ALTER TABLE sys_role ADD COLUMN integrity_hmac VARCHAR(128) DEFAULT NULL COMMENT '角色行 SM3 HMAC', ADD COLUMN integrity_key_ver INT DEFAULT 1 COMMENT 'HMAC 密钥版本'; ALTER TABLE sys_user_role ADD COLUMN integrity_hmac VARCHAR(128) DEFAULT NULL COMMENT '用户角色关联 HMAC', ADD COLUMN integrity_key_ver INT DEFAULT 1 COMMENT 'HMAC 密钥版本'; ALTER TABLE sys_role_menu ADD COLUMN integrity_hmac VARCHAR(128) DEFAULT NULL COMMENT '角色菜单关联 HMAC', ADD COLUMN integrity_key_ver INT DEFAULT 1 COMMENT 'HMAC 密钥版本'; ``` --- ## 3. 操作日志完整性 ```sql ALTER TABLE sys_oper_log ADD COLUMN integrity_hmac VARCHAR(128) DEFAULT NULL COMMENT '操作日志行 SM3 HMAC', ADD COLUMN integrity_key_ver INT DEFAULT 1 COMMENT 'HMAC 密钥版本'; ALTER TABLE sys_logininfor ADD COLUMN integrity_hmac VARCHAR(128) DEFAULT NULL COMMENT '登录日志行 SM3 HMAC', ADD COLUMN integrity_key_ver INT DEFAULT 1 COMMENT 'HMAC 密钥版本'; ``` --- ## 4. 密码改造配置表 ```sql DROP TABLE IF EXISTS sys_crypto_field_config; CREATE TABLE sys_crypto_field_config ( config_id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键', table_name VARCHAR(64) NOT NULL COMMENT '表名', column_name VARCHAR(64) NOT NULL COMMENT '列名', protect_type VARCHAR(16) NOT NULL COMMENT 'HMAC/SM4/BOTH', data_type VARCHAR(32) NOT NULL COMMENT 'CREDENTIAL/PERSONAL/PERMISSION/LOG/BUSINESS', key_index_hmac INT DEFAULT NULL COMMENT 'HMAC 密钥索引', key_index_sm4 INT DEFAULT NULL COMMENT 'SM4 密钥索引', canonical_order INT DEFAULT 0 COMMENT 'HMAC 拼接顺序', enabled CHAR(1) DEFAULT '1' COMMENT '0停用 1启用', remark VARCHAR(500) DEFAULT NULL, create_by VARCHAR(64) DEFAULT '', create_time DATETIME DEFAULT NULL, update_by VARCHAR(64) DEFAULT '', update_time DATETIME DEFAULT NULL, PRIMARY KEY (config_id), UNIQUE KEY uk_table_column (table_name, column_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='字段加解密配置表'; ``` --- ## 5. 密钥版本表 ```sql DROP TABLE IF EXISTS sys_crypto_key_version; CREATE TABLE sys_crypto_key_version ( id BIGINT(20) NOT NULL AUTO_INCREMENT, data_type VARCHAR(32) NOT NULL COMMENT '数据类型', key_ver INT NOT NULL COMMENT '版本号', key_index_hmac INT DEFAULT NULL, key_index_sm4 INT DEFAULT NULL, status CHAR(1) DEFAULT '1' COMMENT '1当前 0历史', effective_time DATETIME DEFAULT NULL, remark VARCHAR(500) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uk_type_ver (data_type, key_ver) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='密码机密钥版本'; ``` --- ## 6. 安全告警表 ```sql DROP TABLE IF EXISTS sys_security_alert; CREATE TABLE sys_security_alert ( alert_id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '告警ID', alert_level CHAR(1) NOT NULL COMMENT 'H高 M中 L低', alert_type VARCHAR(32) NOT NULL COMMENT 'INTEGRITY_FAIL/TAMPER/CRYPTO_ERROR', table_name VARCHAR(64) DEFAULT NULL, record_id VARCHAR(64) DEFAULT NULL COMMENT '业务主键', data_type VARCHAR(32) DEFAULT NULL, alert_msg VARCHAR(500) NOT NULL, oper_user VARCHAR(64) DEFAULT NULL, oper_ip VARCHAR(128) DEFAULT NULL, handled CHAR(1) DEFAULT '0' COMMENT '0未处理 1已处理', handle_by VARCHAR(64) DEFAULT NULL, handle_time DATETIME DEFAULT NULL, create_time DATETIME DEFAULT NULL, PRIMARY KEY (alert_id), KEY idx_alert_time (create_time), KEY idx_alert_handled (handled) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='安全告警（完整性/篡改）'; ``` --- ## 7. 密码机调用审计表 ```sql DROP TABLE IF EXISTS sys_crypto_audit_log; CREATE TABLE sys_crypto_audit_log ( log_id BIGINT(20) NOT NULL AUTO_INCREMENT, oper_type VARCHAR(32) NOT NULL COMMENT 'HMAC/ENCRYPT/DECRYPT/VERIFY', data_type VARCHAR(32) DEFAULT NULL, key_index INT DEFAULT NULL, success CHAR(1) NOT NULL COMMENT '0失败 1成功', error_msg VARCHAR(500) DEFAULT NULL, oper_user VARCHAR(64) DEFAULT NULL, oper_ip VARCHAR(128) DEFAULT NULL, cost_ms INT DEFAULT NULL, create_time DATETIME DEFAULT NULL, PRIMARY KEY (log_id), KEY idx_crypto_audit_time (create_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='密码服务调用审计（不含明文）'; ``` --- ## 8. 登录策略配置（可选） ```sql DROP TABLE IF EXISTS sys_login_policy; CREATE TABLE sys_login_policy ( policy_id BIGINT(20) NOT NULL AUTO_INCREMENT, role_key VARCHAR(100) DEFAULT NULL COMMENT '角色权限字符，空表示默认', network_zone VARCHAR(32) NOT NULL COMMENT 'INTERNAL/INTERNET', policy_type VARCHAR(32) NOT NULL COMMENT 'CERT/SMS_2FA/PASSWORD', enabled CHAR(1) DEFAULT '1', remark VARCHAR(500) DEFAULT NULL, PRIMARY KEY (policy_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='登录策略配置'; -- 示例：互联网环境管理员强制短信双因子 INSERT INTO sys_login_policy (role_key, network_zone, policy_type, remark) VALUES ('admin', 'INTERNET', 'SMS_2FA', '互联网超级管理员双因子'); ``` --- ## 9. 业务表示例（畜牧医疗资源） ```sql ALTER TABLE biz_medical_resource ADD COLUMN contact_phone_cipher VARCHAR(512) DEFAULT NULL COMMENT '联系电话 SM4', ADD COLUMN contact_phone_hmac VARCHAR(128) DEFAULT NULL COMMENT '联系电话 HMAC', ADD COLUMN detail_address_cipher VARCHAR(512) DEFAULT NULL COMMENT '地址 SM4', ADD COLUMN detail_address_hmac VARCHAR(128) DEFAULT NULL COMMENT '地址 HMAC', ADD COLUMN integrity_hmac VARCHAR(128) DEFAULT NULL COMMENT '业务行 HMAC', ADD COLUMN crypto_key_ver INT DEFAULT 1 COMMENT '密钥版本'; ``` --- ## 10. 存量迁移注意事项 1. **停写窗口**：大批量重加密建议只读维护窗口或双写期 2. **回滚**：保留原明文列直至验收通过 3. **索引**：密文列一般不加业务索引；查询用手机号哈希列（可选 `phonenumber_hash` SM3 摘要索引） 4. **脚本顺序**：DDL → 配置数据 → 迁移 Job → 校验 Job → 删明文列