2026-08-19-storage-release-dual-gate-role-grant.sql 3.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111
  1. -- 入库放行审核:给指定角色授权(sys_role_menu)
  2. -- MySQL 5.7;可重复执行
  3. -- 研发主管 → 生产侧入库放行审核
  4. -- 质量主管 → 质检侧入库放行审核
  5. -- 祖先菜单(生产/生产管理、质检/质检管理)若已有则不重复插入
  6. SET @tenant_no = CONVERT('zkxw' USING utf8mb4) COLLATE utf8mb4_unicode_ci;
  7. -- 研发主管
  8. SET @role_rd = CONVERT('2668804724054AB6A45DF35D2108138B' USING utf8mb4) COLLATE utf8mb4_unicode_ci;
  9. -- 质量主管
  10. SET @role_qa = CONVERT('35DD110367764827ACCCFEEBA1E21699' USING utf8mb4) COLLATE utf8mb4_unicode_ci;
  11. -- 生产入库放行
  12. SET @menu_prod = CONVERT('95500000000000000000000000000001' USING utf8mb4) COLLATE utf8mb4_unicode_ci;
  13. -- 质检入库放行
  14. SET @menu_qc = CONVERT('70000000000000000000000000000014' USING utf8mb4) COLLATE utf8mb4_unicode_ci;
  15. SET @top_prod = CONVERT('TOP00000000000000000000000000002' USING utf8mb4) COLLATE utf8mb4_unicode_ci;
  16. SET @prm_id = CONVERT('PRM00000000000000000000000000001' USING utf8mb4) COLLATE utf8mb4_unicode_ci;
  17. SET @top_qc = CONVERT('TOP00000000000000000000000000003' USING utf8mb4) COLLATE utf8mb4_unicode_ci;
  18. SET @qcm_id = CONVERT('QCM00000000000000000000000000001' USING utf8mb4) COLLATE utf8mb4_unicode_ci;
  19. SET @web_root = CONVERT('2' USING utf8mb4) COLLATE utf8mb4_unicode_ci;
  20. -- 软删恢复
  21. UPDATE sys_role_menu
  22. SET del_flag = 0, update_time = NOW(), remark = 'grant storage release dual-gate'
  23. WHERE tenant_no COLLATE utf8mb4_unicode_ci = @tenant_no
  24. AND del_flag = 1
  25. AND (
  26. (role_id COLLATE utf8mb4_unicode_ci = @role_rd AND menu_id COLLATE utf8mb4_unicode_ci IN (@web_root, @top_prod, @prm_id, @menu_prod))
  27. OR
  28. (role_id COLLATE utf8mb4_unicode_ci = @role_qa AND menu_id COLLATE utf8mb4_unicode_ci IN (@web_root, @top_qc, @qcm_id, @menu_qc))
  29. );
  30. -- 研发主管:Web 根 + 生产 + 生产管理 + 生产入库放行
  31. INSERT INTO sys_role_menu (
  32. id, role_id, menu_id, tenant_no, order_num, remark,
  33. create_by, create_dept_id, create_time, update_by, update_time, del_flag
  34. )
  35. SELECT
  36. UPPER(MD5(CONCAT(x.role_id, '|', x.menu_id))),
  37. x.role_id,
  38. x.menu_id,
  39. @tenant_no,
  40. 0,
  41. 'grant storage release dual-gate',
  42. 'admin',
  43. '',
  44. NOW(),
  45. NULL,
  46. NOW(),
  47. 0
  48. FROM (
  49. SELECT @role_rd AS role_id, @web_root AS menu_id
  50. UNION ALL SELECT @role_rd, @top_prod
  51. UNION ALL SELECT @role_rd, @prm_id
  52. UNION ALL SELECT @role_rd, @menu_prod
  53. ) x
  54. WHERE EXISTS (
  55. SELECT 1 FROM sys_role r
  56. WHERE r.id COLLATE utf8mb4_unicode_ci = x.role_id AND r.del_flag = 0
  57. )
  58. AND EXISTS (
  59. SELECT 1 FROM sys_menu m
  60. WHERE m.id COLLATE utf8mb4_unicode_ci = x.menu_id AND m.del_flag = 0
  61. )
  62. AND NOT EXISTS (
  63. SELECT 1 FROM sys_role_menu rm
  64. WHERE rm.role_id COLLATE utf8mb4_unicode_ci = x.role_id
  65. AND rm.menu_id COLLATE utf8mb4_unicode_ci = x.menu_id
  66. AND rm.tenant_no COLLATE utf8mb4_unicode_ci = @tenant_no
  67. AND rm.del_flag = 0
  68. );
  69. -- 质量主管:Web 根 + 质检 + 质检管理 + 质检入库放行
  70. INSERT INTO sys_role_menu (
  71. id, role_id, menu_id, tenant_no, order_num, remark,
  72. create_by, create_dept_id, create_time, update_by, update_time, del_flag
  73. )
  74. SELECT
  75. UPPER(MD5(CONCAT(x.role_id, '|', x.menu_id))),
  76. x.role_id,
  77. x.menu_id,
  78. @tenant_no,
  79. 0,
  80. 'grant storage release dual-gate',
  81. 'admin',
  82. '',
  83. NOW(),
  84. NULL,
  85. NOW(),
  86. 0
  87. FROM (
  88. SELECT @role_qa AS role_id, @web_root AS menu_id
  89. UNION ALL SELECT @role_qa, @top_qc
  90. UNION ALL SELECT @role_qa, @qcm_id
  91. UNION ALL SELECT @role_qa, @menu_qc
  92. ) x
  93. WHERE EXISTS (
  94. SELECT 1 FROM sys_role r
  95. WHERE r.id COLLATE utf8mb4_unicode_ci = x.role_id AND r.del_flag = 0
  96. )
  97. AND EXISTS (
  98. SELECT 1 FROM sys_menu m
  99. WHERE m.id COLLATE utf8mb4_unicode_ci = x.menu_id AND m.del_flag = 0
  100. )
  101. AND NOT EXISTS (
  102. SELECT 1 FROM sys_role_menu rm
  103. WHERE rm.role_id COLLATE utf8mb4_unicode_ci = x.role_id
  104. AND rm.menu_id COLLATE utf8mb4_unicode_ci = x.menu_id
  105. AND rm.tenant_no COLLATE utf8mb4_unicode_ci = @tenant_no
  106. AND rm.del_flag = 0
  107. );