158 lines
10 KiB
SQL
158 lines
10 KiB
SQL
-- ============================================================
|
||
-- 完整按钮权限修复 SQL（PROD 执行）
|
||
-- 功能：
|
||
-- 1. 补充所有按钮权限菜单（INSERT IGNORE，幂等安全）
|
||
-- 2. 将所有 gateway 系统菜单（含按钮）绑定到 super_admin
|
||
-- 3. 执行后 super_admin 重新登录即可看到所有按钮
|
||
--
|
||
-- 执行方式：mysql -u root -p < sql/fix_all_permissions.sql
|
||
-- ============================================================
|
||
USE mokeegateway;
|
||
|
||
-- ========================================
|
||
-- 一、补充按钮权限菜单（INSERT IGNORE）
|
||
-- ========================================
|
||
|
||
-- 1. 系统列表 按钮权限
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, m.id, '新增系统', 3, 1, 1, 'system:system:add'
|
||
FROM sys_system s, sys_menu m WHERE s.system_code='gateway' AND m.system_id=s.id AND m.menu_name='系统列表';
|
||
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, m.id, '编辑系统', 3, 2, 1, 'system:system:edit'
|
||
FROM sys_system s, sys_menu m WHERE s.system_code='gateway' AND m.system_id=s.id AND m.menu_name='系统列表';
|
||
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, m.id, '删除系统', 3, 3, 1, 'system:system:delete'
|
||
FROM sys_system s, sys_menu m WHERE s.system_code='gateway' AND m.system_id=s.id AND m.menu_name='系统列表';
|
||
|
||
-- 2. 用户管理 按钮权限
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, m.id, '新增用户', 3, 1, 1, 'system:user:add'
|
||
FROM sys_system s, sys_menu m WHERE s.system_code='gateway' AND m.system_id=s.id AND m.menu_name='用户管理';
|
||
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, m.id, '编辑用户', 3, 2, 1, 'system:user:edit'
|
||
FROM sys_system s, sys_menu m WHERE s.system_code='gateway' AND m.system_id=s.id AND m.menu_name='用户管理';
|
||
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, m.id, '删除用户', 3, 3, 1, 'system:user:delete'
|
||
FROM sys_system s, sys_menu m WHERE s.system_code='gateway' AND m.system_id=s.id AND m.menu_name='用户管理';
|
||
|
||
-- 3. 角色管理 按钮权限
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, m.id, '新增角色', 3, 1, 1, 'system:role:add'
|
||
FROM sys_system s, sys_menu m WHERE s.system_code='gateway' AND m.system_id=s.id AND m.menu_name='角色管理';
|
||
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, m.id, '编辑角色', 3, 2, 1, 'system:role:edit'
|
||
FROM sys_system s, sys_menu m WHERE s.system_code='gateway' AND m.system_id=s.id AND m.menu_name='角色管理';
|
||
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, m.id, '删除角色', 3, 3, 1, 'system:role:delete'
|
||
FROM sys_system s, sys_menu m WHERE s.system_code='gateway' AND m.system_id=s.id AND m.menu_name='角色管理';
|
||
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, m.id, '分配菜单', 3, 4, 1, 'system:role:assignMenu'
|
||
FROM sys_system s, sys_menu m WHERE s.system_code='gateway' AND m.system_id=s.id AND m.menu_name='角色管理';
|
||
|
||
-- 4. 菜单管理 按钮权限（重点：新增/编辑/删除）
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, m.id, '新增菜单', 3, 1, 1, 'system:menu:add'
|
||
FROM sys_system s, sys_menu m WHERE s.system_code='gateway' AND m.system_id=s.id AND m.menu_name='菜单管理';
|
||
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, m.id, '编辑菜单', 3, 2, 1, 'system:menu:edit'
|
||
FROM sys_system s, sys_menu m WHERE s.system_code='gateway' AND m.system_id=s.id AND m.menu_name='菜单管理';
|
||
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, m.id, '删除菜单', 3, 3, 1, 'system:menu:delete'
|
||
FROM sys_system s, sys_menu m WHERE s.system_code='gateway' AND m.system_id=s.id AND m.menu_name='菜单管理';
|
||
|
||
-- 5. 接口管理 按钮权限（重点：新增/编辑/删除）
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, m.id, '新增接口', 3, 1, 1, 'system:api:add'
|
||
FROM sys_system s, sys_menu m WHERE s.system_code='gateway' AND m.system_id=s.id AND m.menu_name='接口管理';
|
||
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, m.id, '编辑接口', 3, 2, 1, 'system:api:edit'
|
||
FROM sys_system s, sys_menu m WHERE s.system_code='gateway' AND m.system_id=s.id AND m.menu_name='接口管理';
|
||
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, m.id, '删除接口', 3, 3, 1, 'system:api:delete'
|
||
FROM sys_system s, sys_menu m WHERE s.system_code='gateway' AND m.system_id=s.id AND m.menu_name='接口管理';
|
||
|
||
-- 6. 数据字典 按钮权限
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, m.id, '新增字典', 3, 1, 1, 'system:dict:add'
|
||
FROM sys_system s, sys_menu m WHERE s.system_code='gateway' AND m.system_id=s.id AND m.menu_name='数据字典';
|
||
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, m.id, '编辑字典', 3, 2, 1, 'system:dict:edit'
|
||
FROM sys_system s, sys_menu m WHERE s.system_code='gateway' AND m.system_id=s.id AND m.menu_name='数据字典';
|
||
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, m.id, '删除字典', 3, 3, 1, 'system:dict:delete'
|
||
FROM sys_system s, sys_menu m WHERE s.system_code='gateway' AND m.system_id=s.id AND m.menu_name='数据字典';
|
||
|
||
-- 7. 文件对象储存 按钮权限
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, m.id, '上传文件', 3, 1, 1, 'system:file:upload'
|
||
FROM sys_system s, sys_menu m WHERE s.system_code='gateway' AND m.system_id=s.id AND m.menu_name='文件对象储存';
|
||
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, m.id, '删除文件', 3, 2, 1, 'system:file:delete'
|
||
FROM sys_system s, sys_menu m WHERE s.system_code='gateway' AND m.system_id=s.id AND m.menu_name='文件对象储存';
|
||
|
||
-- 8. 邮件管理 按钮权限
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, m.id, '发送邮件', 3, 1, 1, 'system:email:send'
|
||
FROM sys_system s, sys_menu m WHERE s.system_code='gateway' AND m.system_id=s.id AND m.menu_name='邮件管理';
|
||
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, m.id, '删除邮件', 3, 2, 1, 'system:email:delete'
|
||
FROM sys_system s, sys_menu m WHERE s.system_code='gateway' AND m.system_id=s.id AND m.menu_name='邮件管理';
|
||
|
||
-- 9. 个人中心 按钮权限（parent_id='0'，独立权限，不挂在页面菜单下）
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, '0', '保存个人信息', 3, 1, 1, 'system:profile:edit'
|
||
FROM sys_system s WHERE s.system_code='gateway';
|
||
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, '0', '修改密码', 3, 2, 1, 'system:profile:password'
|
||
FROM sys_system s WHERE s.system_code='gateway';
|
||
|
||
INSERT IGNORE INTO sys_menu (id, system_id, parent_id, menu_name, menu_type, sort_order, status, permission)
|
||
SELECT REPLACE(UUID(),'-',''), s.id, '0', '更换头像', 3, 3, 1, 'system:profile:avatar'
|
||
FROM sys_system s WHERE s.system_code='gateway';
|
||
|
||
|
||
-- ========================================
|
||
-- 二、绑定所有 gateway 菜单到 super_admin
|
||
-- ========================================
|
||
INSERT IGNORE INTO sys_role_menu (id, role_id, menu_id)
|
||
SELECT REPLACE(UUID(),'-',''), r.id, m.id
|
||
FROM sys_role r, sys_menu m, sys_system s
|
||
WHERE r.role_code='super_admin' AND r.system_id=s.id AND m.system_id=s.id AND s.system_code='gateway';
|
||
|
||
-- ========================================
|
||
-- 三、验证查询（检查结果）
|
||
-- ========================================
|
||
SELECT '=== 按钮权限检查 ===' AS info;
|
||
SELECT m.menu_name AS '按钮名称', m.permission AS '权限标识',
|
||
CASE WHEN rm.id IS NOT NULL THEN '已绑定' ELSE '未绑定' END AS 'super_admin绑定状态'
|
||
FROM sys_menu m
|
||
LEFT JOIN sys_system s ON m.system_id = s.id AND s.system_code = 'gateway'
|
||
LEFT JOIN sys_role r ON r.system_id = s.id AND r.role_code = 'super_admin'
|
||
LEFT JOIN sys_role_menu rm ON rm.menu_id = m.id AND rm.role_id = r.id
|
||
WHERE m.menu_type = 3 AND s.system_code = 'gateway'
|
||
ORDER BY m.permission;
|
||
|
||
SELECT '=== super_admin 权限总数 ===' AS info;
|
||
SELECT COUNT(DISTINCT m.permission) AS '权限数量'
|
||
FROM sys_role r, sys_role_menu rm, sys_menu m, sys_system s
|
||
WHERE r.role_code = 'super_admin' AND r.system_id = s.id AND s.system_code = 'gateway'
|
||
AND r.id = rm.role_id AND rm.menu_id = m.id AND m.permission IS NOT NULL AND m.permission != '';
|
||
|
||
SELECT '=== 修复完成！请 super_admin 重新登录 ===' AS result;
|