29 lines
1.7 KiB
SQL
29 lines
1.7 KiB
SQL
-- 本机旧权限目录修复：按权限编码补齐内置平台管理员的确认发车授权。
|
|
-- 只连接本机 baiyue_iam 执行；保留权限真实 ID，不覆盖其他角色或现有授权。
|
|
USE baiyue_iam;
|
|
START TRANSACTION;
|
|
CREATE TEMPORARY TABLE tmp_local_admin_depart_repair AS
|
|
SELECT r.id AS role_id, p.id AS permission_id
|
|
FROM iam_role r
|
|
JOIN iam_permission p ON p.tenant_id=0 AND p.app_code='PLATFORM'
|
|
AND p.permission_code='platform:overseas-warehouse:dispatch:depart'
|
|
AND p.status='ACTIVE' AND p.deleted=0
|
|
WHERE r.tenant_id=0 AND r.app_code='PLATFORM' AND r.role_code='PLATFORM_ADMIN'
|
|
AND r.status='ACTIVE' AND r.deleted=0
|
|
AND NOT EXISTS (SELECT 1 FROM iam_role_permission rp
|
|
WHERE rp.tenant_id=0 AND rp.role_id=r.id AND rp.permission_id=p.id);
|
|
INSERT IGNORE INTO iam_role_permission (tenant_id,role_id,permission_id,created_at)
|
|
SELECT 0,role_id,permission_id,CURRENT_TIMESTAMP(3) FROM tmp_local_admin_depart_repair;
|
|
-- 与授权同事务推进所有受影响有效平台成员的安全版本，旧会话不能继续使用旧权限。
|
|
INSERT INTO iam_security_subject_version (auth_user_id,permission_version)
|
|
SELECT DISTINCT u.auth_user_id,1
|
|
FROM iam_user u
|
|
JOIN iam_membership m ON m.user_id=u.id AND m.tenant_id=0 AND m.status='ACTIVE' AND m.deleted=0
|
|
JOIN iam_membership_role mr ON mr.membership_id=m.id AND mr.tenant_id=0
|
|
JOIN tmp_local_admin_depart_repair change_row ON change_row.role_id=mr.role_id
|
|
WHERE u.status='ACTIVE' AND u.deleted=0 AND u.auth_user_id IS NOT NULL
|
|
ON DUPLICATE KEY UPDATE permission_version=permission_version+1;
|
|
SELECT COUNT(*) AS added_admin_depart_grants FROM tmp_local_admin_depart_repair;
|
|
DROP TEMPORARY TABLE tmp_local_admin_depart_repair;
|
|
COMMIT;
|