95 lines
5.7 KiB
SQL
95 lines
5.7 KiB
SQL
-- =============================================================================
|
||
-- IT-1 / DV-1 权限点种子与 M0 验收账号（可重放，二次执行不产生重复数据）
|
||
-- 依据：公寓端/04 §1 权限点命名、表 13-11 DV-1、表 7-3 M0 四项验收
|
||
-- 说明：
|
||
-- 1) 只写 sys_menu / sys_role / sys_user / sys_user_role / sys_role_menu 框架表，
|
||
-- 不触碰 59 张业务表；DDL 与回填分离口径不变。
|
||
-- 2) apt_guest 是「有令牌但缺权限点」的本地验收账号，口令为若依公开模板
|
||
-- BCrypt 密文（对应 admin123）；生产部署前必须删除，不进生产镜像。
|
||
-- 3) 后续域权限点只登记、不关联任何角色（置灰），符合表 13-29 预埋不上线。
|
||
-- =============================================================================
|
||
|
||
-- 1. 公寓端父目录（M）
|
||
INSERT INTO sys_menu
|
||
(menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
|
||
is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
|
||
SELECT 3000, '公寓端', 0, 9, 'apt', NULL, '', '', 1, 0, 'M', '0', '0', '',
|
||
'build', 'IT-1', NOW(), 'IT-1 / M0 权限点父目录'
|
||
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3000);
|
||
|
||
-- 2. 权限点（F）：仅 3001 在本轮启用，其余为后续迭代预置且不关联角色
|
||
INSERT INTO sys_menu
|
||
(menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
|
||
is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
|
||
VALUES
|
||
(3001, '交路列表', 3000, 1, '', NULL, NULL, '', 1, 0, 'F', '0', '0',
|
||
'apt:route:list', '#', 'IT-1', NOW(), 'IT-1 启用：M0 只读列表'),
|
||
(3002, '连通性自检', 3000, 2, '', NULL, NULL, '', 1, 0, 'F', '0', '0',
|
||
'apt:ops:connectivity', '#', 'IT-1', NOW(), 'IT-0 自检接口（@Anonymous，权限点仅登记）'),
|
||
(3003, '房间列表', 3000, 3, '', NULL, NULL, '', 1, 0, 'F', '1', '1',
|
||
'apt:room:list', '#', 'IT-1', NOW(), 'IT-2 预置，置灰不启用'),
|
||
(3004, '叫乘列表', 3000, 4, '', NULL, NULL, '', 1, 0, 'F', '1', '1',
|
||
'apt:jcxx:list', '#', 'IT-1', NOW(), 'IT-5 预置，置灰不启用'),
|
||
(3005, '入住待审列表', 3000, 5, '', NULL, NULL, '', 1, 0, 'F', '1', '1',
|
||
'duty:checkin:list', '#', 'IT-1', NOW(), 'IT-4 预置，置灰不启用'),
|
||
(3006, '本地账务', 3000, 6, '', NULL, NULL, '', 1, 0, 'F', '1', '1',
|
||
'apt:ledger:local', '#', 'IT-1', NOW(), 'IT-9~11 延后域预置，置灰不启用')
|
||
ON DUPLICATE KEY UPDATE menu_name = VALUES(menu_name), perms = VALUES(perms);
|
||
|
||
-- 3. 超管角色显式关联 apt:route:list（超管本身已有 *:*:*，此处为审计留痕）
|
||
INSERT INTO sys_role_menu (role_id, menu_id)
|
||
SELECT 1, 3001
|
||
WHERE NOT EXISTS (SELECT 1 FROM sys_role_menu WHERE role_id = 1 AND menu_id = 3001);
|
||
|
||
-- 4. M0 验收角色：无任何公寓端权限点
|
||
INSERT INTO sys_role
|
||
(role_id, role_name, role_key, role_sort, data_scope, menu_check_strictly,
|
||
dept_check_strictly, status, del_flag, create_by, create_time, remark)
|
||
SELECT 300, '公寓只读测试', 'apt_guest', 9, '1', 1, 1, '0', '0',
|
||
'IT-1', NOW(), 'IT-1 M0 验收：有令牌但缺 apt:route:list；生产部署前删除'
|
||
WHERE NOT EXISTS (SELECT 1 FROM sys_role WHERE role_id = 300);
|
||
|
||
-- 5. M0 验收账号（BCrypt 密文取自若依公开模板，对应 admin123）
|
||
INSERT INTO sys_user
|
||
(user_id, dept_id, user_name, nick_name, user_type, email, phonenumber, sex,
|
||
avatar, password, status, del_flag, login_ip, login_date, create_by, create_time, remark)
|
||
SELECT 300, 103, 'apt_guest', '公寓只读测试', '00', '', '', '0', '',
|
||
'$2a$10$7JB720yubVSZvUI0rEqK/.VqGOZTH.ulu33dHOiBE8ByOhJIrdAu2',
|
||
'0', '0', '', NULL, 'IT-1', NOW(), 'IT-1 M0 验收账号；生产部署前删除'
|
||
WHERE NOT EXISTS (SELECT 1 FROM sys_user WHERE user_id = 300);
|
||
|
||
INSERT INTO sys_user_role (user_id, role_id)
|
||
SELECT 300, 300
|
||
WHERE NOT EXISTS (SELECT 1 FROM sys_user_role WHERE user_id = 300 AND role_id = 300);
|
||
|
||
-- 只给一个与公寓端无关的目录权限，保证 permissions 集合不含 apt:route:list / *:*:*
|
||
INSERT INTO sys_role_menu (role_id, menu_id)
|
||
SELECT 300, 1
|
||
WHERE NOT EXISTS (SELECT 1 FROM sys_role_menu WHERE role_id = 300 AND menu_id = 1);
|
||
|
||
-- 6. 校验
|
||
SELECT '=== IT-1 权限点 ===' AS section;
|
||
SELECT menu_id, perms, visible, status, remark FROM sys_menu WHERE menu_id BETWEEN 3000 AND 3006 ORDER BY menu_id;
|
||
SELECT '=== M0 验收账号 ===' AS section;
|
||
SELECT u.user_id, u.user_name, r.role_key, r.role_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 WHERE u.user_id = 300;
|
||
SELECT '=== 验收角色是否被误授公寓端权限点（应为 0）===' AS section;
|
||
SELECT COUNT(*) AS apt_permission_count
|
||
FROM sys_role_menu rm JOIN sys_menu m ON m.menu_id = rm.menu_id
|
||
WHERE rm.role_id = 300 AND (m.perms LIKE 'apt:%' OR m.perms LIKE 'duty:%' OR m.perms = '*:*:*');
|
||
|
||
-- 7. 种子幂等与「预埋不上线」校验
|
||
SELECT '=== 权限点总数（应为 7，重复执行不增长）===' AS section;
|
||
SELECT COUNT(*) AS menu_count FROM sys_menu WHERE menu_id BETWEEN 3000 AND 3006;
|
||
SELECT '=== 置灰权限点是否被误关联角色（应为 0）===' AS section;
|
||
SELECT COUNT(*) AS grey_permission_bound
|
||
FROM sys_role_menu rm JOIN sys_menu m ON m.menu_id = rm.menu_id
|
||
WHERE m.menu_id BETWEEN 3003 AND 3006;
|
||
SELECT '=== 超管角色只读列表权限点留痕（应为 1）===' AS section;
|
||
SELECT COUNT(*) AS admin_route_perm
|
||
FROM sys_role_menu rm JOIN sys_menu m ON m.menu_id = rm.menu_id
|
||
WHERE rm.role_id = 1 AND m.perms = 'apt:route:list';
|
||
SELECT '=== M0 验收账号状态（应 status=0 / del_flag=0）===' AS section;
|
||
SELECT user_id, user_name, status, del_flag FROM sys_user WHERE user_id = 300;
|