162 lines
6.0 KiB
PL/PgSQL
162 lines
6.0 KiB
PL/PgSQL
-- ============================================================
|
||
-- Ether 权限体系升级回滚脚本 V3
|
||
-- 创建日期: 2026-02-27
|
||
-- 说明: 回滚权限系统升级，删除新增角色、恢复更新角色、删除新增权限
|
||
-- ============================================================
|
||
|
||
BEGIN;
|
||
|
||
-- ============================================================
|
||
-- 第一部分：删除角色权限关联
|
||
-- ============================================================
|
||
|
||
-- 1.1 删除超级管理员的新增权限关联
|
||
DELETE FROM auth_role_permission
|
||
WHERE permission_id IN (
|
||
SELECT id FROM auth_permission
|
||
WHERE permission_code LIKE 'ops:work_order:%'
|
||
OR permission_code LIKE 'ops:inspection:%'
|
||
OR permission_code LIKE 'mdm:equipment:%'
|
||
OR permission_code LIKE 'ops:visitor:%'
|
||
OR permission_code LIKE 'finance:bill:%'
|
||
);
|
||
|
||
-- ============================================================
|
||
-- 第二部分：删除新增权限
|
||
-- ============================================================
|
||
|
||
-- 2.1 删除工单操作权限
|
||
DELETE FROM auth_permission
|
||
WHERE permission_code LIKE 'ops:work_order:%'
|
||
AND permission_type = 'BUTTON';
|
||
|
||
-- 2.2 删除巡检操作权限
|
||
DELETE FROM auth_permission
|
||
WHERE permission_code LIKE 'ops:inspection:%'
|
||
AND permission_type = 'BUTTON';
|
||
|
||
-- 2.3 删除设备操作权限
|
||
DELETE FROM auth_permission
|
||
WHERE permission_code LIKE 'mdm:equipment:%'
|
||
AND permission_type = 'BUTTON';
|
||
|
||
-- 2.4 删除访客操作权限
|
||
DELETE FROM auth_permission
|
||
WHERE permission_code LIKE 'ops:visitor:%'
|
||
AND permission_type = 'BUTTON';
|
||
|
||
-- 2.5 删除财务操作权限
|
||
DELETE FROM auth_permission
|
||
WHERE permission_code LIKE 'finance:bill:%'
|
||
AND permission_type = 'BUTTON';
|
||
|
||
-- ============================================================
|
||
-- 第三部分：恢复更新的角色
|
||
-- ============================================================
|
||
|
||
-- 3.1 恢复 CUSTOMER_SERVICE 角色
|
||
UPDATE auth_role
|
||
SET role_code = 'CUSTOMER_SERVICE',
|
||
role_name = '客服人员',
|
||
data_scope = 'DEPARTMENT',
|
||
description = '客服人员',
|
||
updated_at = NOW()
|
||
WHERE role_code = 'CS_STAFF' AND project_id IS NULL;
|
||
|
||
-- ============================================================
|
||
-- 第四部分：删除新增角色
|
||
-- ============================================================
|
||
|
||
-- 4.1 删除新增的系统角色
|
||
-- 注意：如果角色已分配给用户，需要先删除用户角色关联
|
||
DELETE FROM auth_user_role
|
||
WHERE role_id IN (
|
||
SELECT id FROM auth_role
|
||
WHERE role_code IN ('SYS_ADMIN', 'ENGINEERING_LEAD', 'SECURITY_LEAD', 'CLEANING_LEAD', 'FINANCE_LEAD', 'CLEANING_STAFF', 'OWNER')
|
||
AND project_id IS NULL
|
||
);
|
||
|
||
DELETE FROM auth_role
|
||
WHERE role_code IN ('SYS_ADMIN', 'ENGINEERING_LEAD', 'SECURITY_LEAD', 'CLEANING_LEAD', 'FINANCE_LEAD', 'CLEANING_STAFF', 'OWNER')
|
||
AND project_id IS NULL;
|
||
|
||
-- ============================================================
|
||
-- 第五部分：恢复约束（可选，根据需要执行）
|
||
-- ============================================================
|
||
|
||
-- 5.1 恢复 data_scope 约束（移除 PROJECT 值）
|
||
-- 注意：如果有角色使用了 PROJECT 值，需要先更新这些角色
|
||
-- ALTER TABLE auth_role DROP CONSTRAINT IF EXISTS auth_role_data_scope_check;
|
||
-- ALTER TABLE auth_role ADD CONSTRAINT auth_role_data_scope_check
|
||
-- CHECK (data_scope::text = ANY (ARRAY['ALL'::character varying, 'DEPARTMENT'::character varying, 'SELF'::character varying]::text[]));
|
||
|
||
-- 5.2 恢复 action 约束（移除新增的操作类型）
|
||
-- 注意：如果有权限使用了新的 action 值，需要先删除这些权限
|
||
-- ALTER TABLE auth_permission DROP CONSTRAINT IF EXISTS auth_permission_action_check;
|
||
-- ALTER TABLE auth_permission ADD CONSTRAINT auth_permission_action_check
|
||
-- CHECK (action::text = ANY (ARRAY['VIEW'::character varying, 'CREATE'::character varying, 'EDIT'::character varying, 'DELETE'::character varying, 'EXPORT'::character varying, 'IMPORT_DATA'::character varying, 'APPROVE'::character varying, 'ASSIGN'::character varying]::text[]));
|
||
|
||
-- ============================================================
|
||
-- 第六部分：验证回滚结果
|
||
-- ============================================================
|
||
|
||
-- 验证角色已删除
|
||
DO $$
|
||
DECLARE
|
||
role_count INTEGER;
|
||
BEGIN
|
||
SELECT COUNT(*) INTO role_count FROM auth_role
|
||
WHERE role_code IN ('SYS_ADMIN', 'ENGINEERING_LEAD', 'SECURITY_LEAD', 'CLEANING_LEAD', 'FINANCE_LEAD', 'CLEANING_STAFF', 'OWNER')
|
||
AND project_id IS NULL;
|
||
|
||
IF role_count > 0 THEN
|
||
RAISE NOTICE '警告: 仍有%个新增角色未删除', role_count;
|
||
ELSE
|
||
RAISE NOTICE '成功: 所有新增角色已删除';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- 验证 CUSTOMER_SERVICE 已恢复
|
||
DO $$
|
||
DECLARE
|
||
cs_exists INTEGER;
|
||
BEGIN
|
||
SELECT COUNT(*) INTO cs_exists FROM auth_role
|
||
WHERE role_code = 'CUSTOMER_SERVICE' AND project_id IS NULL;
|
||
|
||
IF cs_exists = 0 THEN
|
||
RAISE NOTICE '注意: CUSTOMER_SERVICE角色不存在（可能原本就不存在）';
|
||
ELSE
|
||
RAISE NOTICE '成功: CUSTOMER_SERVICE角色已恢复';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- 验证权限已删除
|
||
DO $$
|
||
DECLARE
|
||
permission_count INTEGER;
|
||
BEGIN
|
||
SELECT COUNT(*) INTO permission_count FROM auth_permission
|
||
WHERE (permission_code LIKE 'ops:work_order:%'
|
||
OR permission_code LIKE 'ops:inspection:%'
|
||
OR permission_code LIKE 'mdm:equipment:%'
|
||
OR permission_code LIKE 'ops:visitor:%'
|
||
OR permission_code LIKE 'finance:bill:%')
|
||
AND permission_type = 'BUTTON';
|
||
|
||
IF permission_count > 0 THEN
|
||
RAISE NOTICE '警告: 仍有%个新增权限未删除', permission_count;
|
||
ELSE
|
||
RAISE NOTICE '成功: 所有新增权限已删除';
|
||
END IF;
|
||
END $$;
|
||
|
||
COMMIT;
|
||
|
||
-- ============================================================
|
||
-- 执行完成提示
|
||
-- ============================================================
|
||
-- 回滚完成后请运行以下命令验证:
|
||
-- SELECT role_code, role_name, data_scope FROM auth_role WHERE project_id IS NULL ORDER BY sort_order;
|
||
-- SELECT COUNT(*) FROM auth_permission WHERE permission_type = 'BUTTON';
|