huimv-baqing/doc/密码改造/数据库改造DDL.md

数据库改造 DDL（草案）

密码改造相关表结构扩展脚本草案。
正式执行前须在测试环境验证，并与《数据分类与字段加密映射表.md》对齐。

脚本	阶段	说明
sql/crypto_phase1.sql	一期	已提供：sys_user 扩展、sys_login_policy
sql/crypto_migration.sql	二～三期	全量 DDL（待编写）

---

1. 用户表扩展（身份鉴别 + 个人信息）

一期仅执行 cert_sn、cert_subject、login_policy 三列；其余加密列属二期。

-- 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. 权限表行级完整性

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. 操作日志完整性

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. 密码改造配置表

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. 密钥版本表

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. 安全告警表

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. 密码机调用审计表

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. 登录策略配置（可选）

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. 业务表示例（畜牧医疗资源）

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 → 删明文列