all-project/docs/设计文档/database/fix_朱茏_学籍异动新增权限.sql

-- ============================================================
-- 临时权限修复：学籍异动「新增」权限（退学/休学/复学/毕业提交接口）
-- 背景：
--   前端退学按钮权限：studentMsg:status:tuixue
--   后端提交接口校验：studentMsg:studentStatusChange:add
--   系统菜单中原先缺少 add 权限码，导致有按钮但提交报 403
-- 适用：
--   朱茏老师（user_id=223，角色含 3003-学生信息管理）及同类角色
-- 执行后：
--   相关用户需重新登录，使权限缓存生效
-- ============================================================

-- ------------------------------------------------------------
-- 0) 修改前备份相关表（带时间戳后缀，避免覆盖）
-- ------------------------------------------------------------
SET @bak_suffix = DATE_FORMAT(NOW(), '%Y%m%d_%H%i%s');

SET @sql_bak_menu = CONCAT(
  'CREATE TABLE IF NOT EXISTS sys_menu_bak_', @bak_suffix,
  ' AS SELECT * FROM sys_menu'
);
PREPARE stmt_bak_menu FROM @sql_bak_menu;
EXECUTE stmt_bak_menu;
DEALLOCATE PREPARE stmt_bak_menu;

SET @sql_bak_role_menu = CONCAT(
  'CREATE TABLE IF NOT EXISTS sys_role_menu_bak_', @bak_suffix,
  ' AS SELECT * FROM sys_role_menu'
);
PREPARE stmt_bak_role_menu FROM @sql_bak_role_menu;
EXECUTE stmt_bak_role_menu;
DEALLOCATE PREPARE stmt_bak_role_menu;

-- 查看本次备份表名
SELECT
  CONCAT('sys_menu_bak_', @bak_suffix) AS menu_backup_table,
  CONCAT('sys_role_menu_bak_', @bak_suffix) AS role_menu_backup_table;

-- ------------------------------------------------------------
-- 1) 新增「学籍异动-新增」按钮权限（挂在「学籍异动信息」菜单下）
-- ------------------------------------------------------------
INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query_param,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_dept, create_by, create_time, update_by, update_time, remark
)
SELECT
  2005461500000000001,
  '新增',
  2004197349643042818,  -- 学籍异动信息
  0,
  '',
  NULL,
  NULL,
  1,
  0,
  'F',
  '0',
  '0',
  'studentMsg:studentStatusChange:add',
  '',
  100,
  1,
  NOW(),
  1,
  NOW(),
  '补齐学籍异动提交接口权限，解决退学/休学等提交403'
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM sys_menu WHERE perms = 'studentMsg:studentStatusChange:add'
);

-- 若库中已用其他 menu_id 创建了同名 perms，下面授权统一按 perms 查找 menu_id

-- ------------------------------------------------------------
-- 2) 给「学生信息管理（保留）」角色(3003)授权（朱茏已拥有该角色）
-- ------------------------------------------------------------
INSERT INTO sys_role_menu (role_id, menu_id)
SELECT 3003, m.menu_id
FROM sys_menu m
WHERE m.perms = 'studentMsg:studentStatusChange:add'
  AND NOT EXISTS (
    SELECT 1 FROM sys_role_menu rm
    WHERE rm.role_id = 3003 AND rm.menu_id = m.menu_id
  );

-- ------------------------------------------------------------
-- 3) 兼容：所有已拥有「退学」按钮权限的角色，一并补上 add
-- ------------------------------------------------------------
INSERT INTO sys_role_menu (role_id, menu_id)
SELECT DISTINCT rm.role_id, add_menu.menu_id
FROM sys_role_menu rm
JOIN sys_menu tuixue ON tuixue.menu_id = rm.menu_id AND tuixue.perms = 'studentMsg:status:tuixue'
JOIN sys_menu add_menu ON add_menu.perms = 'studentMsg:studentStatusChange:add'
WHERE NOT EXISTS (
  SELECT 1 FROM sys_role_menu x
  WHERE x.role_id = rm.role_id AND x.menu_id = add_menu.menu_id
);

-- ------------------------------------------------------------
-- 4) 校验：朱茏是否已具备 add 权限
-- ------------------------------------------------------------
SELECT
  u.user_id,
  u.user_name,
  u.nick_name,
  r.role_id,
  r.role_name,
  m.perms,
  m.menu_name
FROM sys_user u
JOIN sys_user_role ur ON ur.user_id = u.user_id
JOIN sys_role r ON r.role_id = ur.role_id
JOIN sys_role_menu rm ON rm.role_id = r.role_id
JOIN sys_menu m ON m.menu_id = rm.menu_id
WHERE u.user_id = 223
  AND m.perms IN (
    'studentMsg:status:tuixue',
    'studentMsg:studentStatusChange:add',
    'studentMsg:studentStatusChange:list'
  )
ORDER BY m.perms, r.role_id;

-- ------------------------------------------------------------
-- 回滚示例（按需执行，把后缀换成上面查出的备份表名）
-- ------------------------------------------------------------
-- TRUNCATE TABLE sys_role_menu;
-- INSERT INTO sys_role_menu SELECT * FROM sys_role_menu_bak_YYYYMMDD_HHMMSS;
-- DELETE FROM sys_menu WHERE perms = 'studentMsg:studentStatusChange:add';
-- -- 或整表回滚菜单：
-- -- TRUNCATE TABLE sys_menu;
-- -- INSERT INTO sys_menu SELECT * FROM sys_menu_bak_YYYYMMDD_HHMMSS;