all-project/docs/设计文档/database/rs-role-permissions-mysql-fixed.sql

-- =================================================
-- 人事管理模块权限配置脚本 (MySQL版本 - 修复版)
-- 创建日期: 2025-07-05
-- 修改日期: 2025-07-10
-- 作者: cursor
-- 说明: 配置人事管理模块的角色权限
-- =================================================

-- 设置变量 (MySQL语法)
-- 获取角色ID
SET @adminRoleId = (SELECT role_id FROM sys_role WHERE role_key = 'admin');
SET @commonRoleId = (SELECT role_id FROM sys_role WHERE role_key = 'common');

-- =============================
-- 1. 人事管理模块菜单权限
-- =============================

-- 设置菜单ID (与rs-menu-mysql-fixed.sql中的ID一致)
SET @rsParentMenu = 2050;  -- 人事管理顶级菜单

-- 职位管理菜单及按钮
SET @jobMenu = 2051;       -- 职位管理菜单
SET @jobQuery = 2052;      -- 职位管理查询
SET @jobAdd = 2053;        -- 职位管理新增
SET @jobEdit = 2054;       -- 职位管理修改
SET @jobRemove = 2055;     -- 职位管理删除
SET @jobExport = 2056;     -- 职位管理导出

-- 招聘中心菜单
SET @publicJobMenu = 2057; -- 招聘中心菜单

-- 申请管理菜单及按钮
SET @appMenu = 2058;       -- 申请管理菜单
SET @appQuery = 2059;      -- 申请管理查询
SET @appAdd = 2060;        -- 申请管理新增
SET @appEdit = 2061;       -- 申请管理修改
SET @appRemove = 2062;     -- 申请管理删除
SET @appExport = 2063;     -- 申请管理导出

-- 应聘者管理菜单及按钮
SET @applicantMenu = 2064; -- 应聘者管理菜单
SET @applicantQuery = 2065; -- 应聘者管理查询
SET @applicantAdd = 2066;  -- 应聘者管理新增
SET @applicantEdit = 2067; -- 应聘者管理修改
SET @applicantRemove = 2068; -- 应聘者管理删除

-- 教育经历管理菜单及按钮
SET @eduMenu = 2069;       -- 教育经历管理菜单
SET @eduQuery = 2070;      -- 教育经历管理查询
SET @eduAdd = 2071;        -- 教育经历管理新增
SET @eduEdit = 2072;       -- 教育经历管理修改
SET @eduRemove = 2073;     -- 教育经历管理删除

-- 工作经历管理菜单及按钮
SET @expMenu = 2074;       -- 工作经历管理菜单
SET @expQuery = 2075;      -- 工作经历管理查询
SET @expAdd = 2076;        -- 工作经历管理新增
SET @expEdit = 2077;       -- 工作经历管理修改
SET @expRemove = 2078;     -- 工作经历管理删除

-- =============================
-- 2. 为管理员角色添加所有人事管理权限
-- =============================

-- 检查并添加菜单权限 (先删除可能存在的权限，再添加)
-- 顶级菜单权限
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id = @rsParentMenu;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @rsParentMenu);

-- 职位管理权限
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id = @jobMenu;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @jobMenu);
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id IN (@jobQuery, @jobAdd, @jobEdit, @jobRemove, @jobExport);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @jobQuery);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @jobAdd);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @jobEdit);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @jobRemove);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @jobExport);

-- 招聘中心权限
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id = @publicJobMenu;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @publicJobMenu);

-- 申请管理权限
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id = @appMenu;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @appMenu);
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id IN (@appQuery, @appAdd, @appEdit, @appRemove, @appExport);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @appQuery);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @appAdd);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @appEdit);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @appRemove);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @appExport);

-- 应聘者管理权限
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id = @applicantMenu;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @applicantMenu);
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id IN (@applicantQuery, @applicantAdd, @applicantEdit, @applicantRemove);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @applicantQuery);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @applicantAdd);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @applicantEdit);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @applicantRemove);

-- 教育经历管理权限
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id = @eduMenu;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @eduMenu);
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id IN (@eduQuery, @eduAdd, @eduEdit, @eduRemove);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @eduQuery);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @eduAdd);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @eduEdit);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @eduRemove);

-- 工作经历管理权限
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id = @expMenu;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @expMenu);
DELETE FROM sys_role_menu WHERE role_id = @adminRoleId AND menu_id IN (@expQuery, @expAdd, @expEdit, @expRemove);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @expQuery);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @expAdd);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @expEdit);
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@adminRoleId, @expRemove);

-- =============================
-- 3. 为普通用户添加有限的人事管理权限
-- =============================

-- 招聘中心权限（查看公开职位）
DELETE FROM sys_role_menu WHERE role_id = @commonRoleId AND menu_id = @rsParentMenu;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@commonRoleId, @rsParentMenu);
DELETE FROM sys_role_menu WHERE role_id = @commonRoleId AND menu_id = @publicJobMenu;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@commonRoleId, @publicJobMenu);

-- 职位申请权限（仅查询）
DELETE FROM sys_role_menu WHERE role_id = @commonRoleId AND menu_id = @jobMenu;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@commonRoleId, @jobMenu);
DELETE FROM sys_role_menu WHERE role_id = @commonRoleId AND menu_id = @jobQuery;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@commonRoleId, @jobQuery);

-- 申请管理权限（仅新增）
DELETE FROM sys_role_menu WHERE role_id = @commonRoleId AND menu_id = @appMenu;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@commonRoleId, @appMenu);
DELETE FROM sys_role_menu WHERE role_id = @commonRoleId AND menu_id = @appAdd;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@commonRoleId, @appAdd);
DELETE FROM sys_role_menu WHERE role_id = @commonRoleId AND menu_id = @appQuery;
INSERT INTO sys_role_menu (role_id, menu_id) VALUES (@commonRoleId, @appQuery);

-- =============================
-- 4. 确认权限分配
-- =============================

-- 输出验证信息
SELECT '人事管理模块权限分配完成' AS Status;
SELECT COUNT(*) AS AdminPermissionCount 
FROM sys_role_menu 
WHERE role_id = @adminRoleId 
AND menu_id IN (
  @rsParentMenu, @jobMenu, @publicJobMenu, @appMenu, @applicantMenu, @eduMenu, @expMenu,
  @jobQuery, @jobAdd, @jobEdit, @jobRemove, @jobExport,
  @appQuery, @appAdd, @appEdit, @appRemove, @appExport,
  @applicantQuery, @applicantAdd, @applicantEdit, @applicantRemove,
  @eduQuery, @eduAdd, @eduEdit, @eduRemove,
  @expQuery, @expAdd, @expEdit, @expRemove
);

SELECT COUNT(*) AS CommonUserPermissionCount 
FROM sys_role_menu 
WHERE role_id = @commonRoleId 
AND menu_id IN (@rsParentMenu, @publicJobMenu, @jobMenu, @jobQuery, @appMenu, @appAdd, @appQuery);