wren
290 lines
15 KiB
PL/PgSQL
290 lines
15 KiB
PL/PgSQL
-- 用户权限初始化 SQL（leaudit-platform）
|
||
-- 目标：初始化最小可用的 RBAC + 单地区数据隔离模型
|
||
-- 核心约定：
|
||
-- 1. 用户地区只认 sso_users.area
|
||
-- 2. 数据范围只认 ALL / DEPT / SELF
|
||
-- 3. 当前业务角色只保留 provincial_admin / admin / common
|
||
-- 4. super_admin 仅作为可选系统维护角色
|
||
|
||
BEGIN;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- 1) 角色初始化
|
||
-- ------------------------------------------------------------
|
||
INSERT INTO roles (role_name, description, data_scope, status, created_at, updated_at)
|
||
VALUES
|
||
('super_admin', '系统超级管理员（可选，仅系统维护使用）', 'ALL', 0, NOW(), NOW()),
|
||
('provincial_admin', '省级管理员', 'ALL', 0, NOW(), NOW()),
|
||
('admin', '地区管理员', 'DEPT', 0, NOW(), NOW()),
|
||
('common', '普通用户', 'SELF', 0, NOW(), NOW())
|
||
ON CONFLICT (role_name) DO UPDATE SET
|
||
description = EXCLUDED.description,
|
||
data_scope = EXCLUDED.data_scope,
|
||
status = EXCLUDED.status,
|
||
updated_at = NOW();
|
||
|
||
-- ------------------------------------------------------------
|
||
-- 2) 菜单 / 路由初始化
|
||
-- 说明：这里只放当前 leaudit-platform 已明确的核心菜单。
|
||
-- ------------------------------------------------------------
|
||
WITH upsert_routes AS (
|
||
INSERT INTO sys_routes (path, name, component, parent_id, sort_order, visible, enabled, meta, created_at, updated_at)
|
||
VALUES
|
||
('/documents', '文档管理', 'Layout', NULL, 10, TRUE, TRUE, '{"icon":"files"}'::jsonb, NOW(), NOW()),
|
||
('/documents/list', '文档列表', 'documents/list', NULL, 11, TRUE, TRUE, '{"icon":"table"}'::jsonb, NOW(), NOW()),
|
||
('/rules', '规则管理', 'Layout', NULL, 20, TRUE, TRUE, '{"icon":"rule"}'::jsonb, NOW(), NOW()),
|
||
('/audit', '评查任务', 'Layout', NULL, 30, TRUE, TRUE, '{"icon":"audit"}'::jsonb, NOW(), NOW()),
|
||
('/audit/runs', '评查运行', 'audit/runs', NULL, 31, TRUE, TRUE, '{"icon":"history"}'::jsonb, NOW(), NOW()),
|
||
('/system', '系统管理', 'Layout', NULL, 90, TRUE, TRUE, '{"icon":"setting"}'::jsonb, NOW(), NOW()),
|
||
('/system/users', '用户管理', 'system/users', NULL, 91, TRUE, TRUE, '{"icon":"user"}'::jsonb, NOW(), NOW()),
|
||
('/system/roles', '角色权限', 'system/roles', NULL, 92, TRUE, TRUE, '{"icon":"shield"}'::jsonb, NOW(), NOW())
|
||
ON CONFLICT (path) DO UPDATE SET
|
||
name = EXCLUDED.name,
|
||
component = EXCLUDED.component,
|
||
sort_order = EXCLUDED.sort_order,
|
||
visible = EXCLUDED.visible,
|
||
enabled = EXCLUDED.enabled,
|
||
meta = EXCLUDED.meta,
|
||
updated_at = NOW()
|
||
RETURNING id, path
|
||
)
|
||
SELECT COUNT(*) FROM upsert_routes;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- 3) 权限点初始化
|
||
-- permission_code 建议全局唯一。
|
||
-- ------------------------------------------------------------
|
||
INSERT INTO permissions (permission_code, permission_name, resource_type, method, path, description, created_at, updated_at)
|
||
VALUES
|
||
('auth.me', '查看当前登录用户', 'api', 'GET', '/api/auth/me', '获取当前用户登录信息', NOW(), NOW()),
|
||
|
||
('documents.upload', '上传文档', 'api', 'POST', '/api/upload', '上传文档并可选自动触发评查', NOW(), NOW()),
|
||
('documents.list', '查看文档列表', 'api', 'GET', '/api/documents/list', '查看文档列表（按数据范围过滤）', NOW(), NOW()),
|
||
('documents.detail', '查看文档详情', 'api', 'GET', '/api/documents/{documentId}', '查看单个文档详情', NOW(), NOW()),
|
||
('documents.history', '查看文档历史版本', 'api', 'GET', '/api/documents/{documentId}/versions', '查看同名文档历史版本', NOW(), NOW()),
|
||
('documents.delete', '删除文档', 'api', 'DELETE', '/api/documents/{documentId}', '删除文档或逻辑删除', NOW(), NOW()),
|
||
|
||
('audit.run', '发起评查任务', 'api', 'POST', '/api/audit/run', '手动触发文档评查', NOW(), NOW()),
|
||
('audit.run.status', '查看评查运行状态', 'api', 'GET', '/api/audit/run/{runId}', '查看 run 当前状态', NOW(), NOW()),
|
||
('audit.result', '查看评查结果', 'api', 'GET', '/api/audit/result/{runId}', '查看评查结果明细', NOW(), NOW()),
|
||
|
||
('rules.list', '查看规则集列表', 'api', 'GET', '/api/rule-sets', '查看规则集列表', NOW(), NOW()),
|
||
('rules.version.list', '查看规则版本列表', 'api', 'GET', '/api/rule-sets/{ruleType}/versions', '查看某规则集的版本列表', NOW(), NOW()),
|
||
('rules.content', '查看规则正文', 'api', 'GET', '/api/rule-sets/versions/{versionId}/content', '查看某个版本的 YAML 内容', NOW(), NOW()),
|
||
('rules.validate', '校验规则 YAML', 'api', 'POST', '/api/rule-sets/{ruleType}/validate', '校验规则 YAML 是否合法', NOW(), NOW()),
|
||
('rules.version.create', '创建规则版本', 'api', 'POST', '/api/rule-sets/{ruleType}/versions', '创建新规则版本', NOW(), NOW()),
|
||
('rules.publish', '发布规则版本', 'api', 'POST', '/api/rule-sets/{ruleType}/publish', '发布规则版本', NOW(), NOW()),
|
||
('rules.rollback', '回滚规则版本', 'api', 'POST', '/api/rule-sets/{ruleType}/rollback', '回滚规则版本', NOW(), NOW()),
|
||
('rules.binding.list', '查看规则绑定', 'api', 'GET', '/api/rule-sets/bindings', '查看规则绑定列表', NOW(), NOW()),
|
||
('rules.binding.create', '创建规则绑定', 'api', 'POST', '/api/rule-sets/{ruleType}/bindings', '创建规则绑定', NOW(), NOW()),
|
||
('rules.binding.update', '更新规则绑定', 'api', 'PUT', '/api/rule-sets/bindings/{bindingId}', '更新规则绑定', NOW(), NOW()),
|
||
('rules.binding.delete', '删除规则绑定', 'api', 'DELETE', '/api/rule-sets/bindings/{bindingId}', '删除规则绑定', NOW(), NOW()),
|
||
|
||
('users.list', '查看用户列表', 'api', 'GET', '/api/users/list', '查看用户列表（按地区过滤）', NOW(), NOW()),
|
||
('users.create', '创建用户', 'api', 'POST', '/api/users', '创建本地用户或同步用户', NOW(), NOW()),
|
||
('users.update', '更新用户', 'api', 'PUT', '/api/users/{userId}', '更新用户基础信息', NOW(), NOW()),
|
||
('users.disable', '禁用用户', 'api', 'PUT', '/api/users/{userId}/disable', '禁用或启用用户', NOW(), NOW()),
|
||
('users.roles.assign', '分配用户角色', 'api', 'POST', '/api/users/{userId}/roles', '为用户分配角色', NOW(), NOW()),
|
||
|
||
('rbac.roles.list', '查看角色列表', 'api', 'GET', '/api/rbac/roles', '查看角色列表', NOW(), NOW()),
|
||
('rbac.roles.update', '维护角色信息', 'api', 'PUT', '/api/rbac/roles/{roleId}', '维护角色定义', NOW(), NOW()),
|
||
('rbac.permissions.list', '查看权限点列表', 'api', 'GET', '/api/rbac/permissions', '查看权限点列表', NOW(), NOW()),
|
||
('rbac.role_permissions.assign', '分配角色权限', 'api', 'POST', '/api/rbac/roles/{roleId}/permissions', '分配角色权限点', NOW(), NOW()),
|
||
('rbac.role_routes.assign', '分配角色菜单', 'api', 'PUT', '/api/rbac/roles/{roleId}/routes', '分配角色菜单', NOW(), NOW())
|
||
ON CONFLICT (permission_code) DO UPDATE SET
|
||
permission_name = EXCLUDED.permission_name,
|
||
resource_type = EXCLUDED.resource_type,
|
||
method = EXCLUDED.method,
|
||
path = EXCLUDED.path,
|
||
description = EXCLUDED.description,
|
||
updated_at = NOW();
|
||
|
||
-- ------------------------------------------------------------
|
||
-- 4) 角色菜单授权
|
||
-- ------------------------------------------------------------
|
||
WITH role_map AS (
|
||
SELECT id, role_name FROM roles WHERE role_name IN ('super_admin', 'provincial_admin', 'admin', 'common')
|
||
),
|
||
route_map AS (
|
||
SELECT id, path FROM sys_routes WHERE path IN (
|
||
'/documents', '/documents/list',
|
||
'/rules',
|
||
'/audit', '/audit/runs',
|
||
'/system', '/system/users', '/system/roles'
|
||
)
|
||
),
|
||
seed(role_name, path) AS (
|
||
VALUES
|
||
('super_admin', '/documents'),
|
||
('super_admin', '/documents/list'),
|
||
('super_admin', '/rules'),
|
||
('super_admin', '/audit'),
|
||
('super_admin', '/audit/runs'),
|
||
('super_admin', '/system'),
|
||
('super_admin', '/system/users'),
|
||
('super_admin', '/system/roles'),
|
||
|
||
('provincial_admin', '/documents'),
|
||
('provincial_admin', '/documents/list'),
|
||
('provincial_admin', '/rules'),
|
||
('provincial_admin', '/audit'),
|
||
('provincial_admin', '/audit/runs'),
|
||
('provincial_admin', '/system'),
|
||
('provincial_admin', '/system/users'),
|
||
('provincial_admin', '/system/roles'),
|
||
|
||
('admin', '/documents'),
|
||
('admin', '/documents/list'),
|
||
('admin', '/rules'),
|
||
('admin', '/audit'),
|
||
('admin', '/audit/runs'),
|
||
('admin', '/system'),
|
||
('admin', '/system/users'),
|
||
|
||
('common', '/documents'),
|
||
('common', '/documents/list'),
|
||
('common', '/audit'),
|
||
('common', '/audit/runs')
|
||
)
|
||
INSERT INTO role_route (role_id, route_id, created_at)
|
||
SELECT DISTINCT rm.id, tm.id, NOW()
|
||
FROM seed s
|
||
JOIN role_map rm ON rm.role_name = s.role_name
|
||
JOIN route_map tm ON tm.path = s.path
|
||
ON CONFLICT (role_id, route_id) DO NOTHING;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- 5) 角色权限点授权
|
||
-- 说明：role_permissions.data_scope 优先级高于 roles.data_scope。
|
||
-- ------------------------------------------------------------
|
||
WITH role_map AS (
|
||
SELECT id, role_name FROM roles WHERE role_name IN ('super_admin', 'provincial_admin', 'admin', 'common')
|
||
),
|
||
perm_map AS (
|
||
SELECT id, permission_code FROM permissions
|
||
),
|
||
seed(role_name, permission_code, data_scope) AS (
|
||
VALUES
|
||
('super_admin', 'auth.me', 'ALL'),
|
||
('super_admin', 'documents.upload', 'ALL'),
|
||
('super_admin', 'documents.list', 'ALL'),
|
||
('super_admin', 'documents.detail', 'ALL'),
|
||
('super_admin', 'documents.history', 'ALL'),
|
||
('super_admin', 'documents.delete', 'ALL'),
|
||
('super_admin', 'audit.run', 'ALL'),
|
||
('super_admin', 'audit.run.status', 'ALL'),
|
||
('super_admin', 'audit.result', 'ALL'),
|
||
('super_admin', 'rules.list', 'ALL'),
|
||
('super_admin', 'rules.version.list', 'ALL'),
|
||
('super_admin', 'rules.content', 'ALL'),
|
||
('super_admin', 'rules.validate', 'ALL'),
|
||
('super_admin', 'rules.version.create', 'ALL'),
|
||
('super_admin', 'rules.publish', 'ALL'),
|
||
('super_admin', 'rules.rollback', 'ALL'),
|
||
('super_admin', 'rules.binding.list', 'ALL'),
|
||
('super_admin', 'rules.binding.create', 'ALL'),
|
||
('super_admin', 'rules.binding.update', 'ALL'),
|
||
('super_admin', 'rules.binding.delete', 'ALL'),
|
||
('super_admin', 'users.list', 'ALL'),
|
||
('super_admin', 'users.create', 'ALL'),
|
||
('super_admin', 'users.update', 'ALL'),
|
||
('super_admin', 'users.disable', 'ALL'),
|
||
('super_admin', 'users.roles.assign', 'ALL'),
|
||
('super_admin', 'rbac.roles.list', 'ALL'),
|
||
('super_admin', 'rbac.roles.update', 'ALL'),
|
||
('super_admin', 'rbac.permissions.list', 'ALL'),
|
||
('super_admin', 'rbac.role_permissions.assign', 'ALL'),
|
||
('super_admin', 'rbac.role_routes.assign', 'ALL'),
|
||
|
||
('provincial_admin', 'auth.me', 'ALL'),
|
||
('provincial_admin', 'documents.upload', 'ALL'),
|
||
('provincial_admin', 'documents.list', 'ALL'),
|
||
('provincial_admin', 'documents.detail', 'ALL'),
|
||
('provincial_admin', 'documents.history', 'ALL'),
|
||
('provincial_admin', 'documents.delete', 'ALL'),
|
||
('provincial_admin', 'audit.run', 'ALL'),
|
||
('provincial_admin', 'audit.run.status', 'ALL'),
|
||
('provincial_admin', 'audit.result', 'ALL'),
|
||
('provincial_admin', 'rules.list', 'ALL'),
|
||
('provincial_admin', 'rules.version.list', 'ALL'),
|
||
('provincial_admin', 'rules.content', 'ALL'),
|
||
('provincial_admin', 'rules.validate', 'ALL'),
|
||
('provincial_admin', 'rules.version.create', 'ALL'),
|
||
('provincial_admin', 'rules.publish', 'ALL'),
|
||
('provincial_admin', 'rules.rollback', 'ALL'),
|
||
('provincial_admin', 'rules.binding.list', 'ALL'),
|
||
('provincial_admin', 'rules.binding.create', 'ALL'),
|
||
('provincial_admin', 'rules.binding.update', 'ALL'),
|
||
('provincial_admin', 'rules.binding.delete', 'ALL'),
|
||
('provincial_admin', 'users.list', 'ALL'),
|
||
('provincial_admin', 'users.create', 'ALL'),
|
||
('provincial_admin', 'users.update', 'ALL'),
|
||
('provincial_admin', 'users.disable', 'ALL'),
|
||
('provincial_admin', 'users.roles.assign', 'ALL'),
|
||
('provincial_admin', 'rbac.roles.list', 'ALL'),
|
||
('provincial_admin', 'rbac.roles.update', 'ALL'),
|
||
('provincial_admin', 'rbac.permissions.list', 'ALL'),
|
||
('provincial_admin', 'rbac.role_permissions.assign', 'ALL'),
|
||
('provincial_admin', 'rbac.role_routes.assign', 'ALL'),
|
||
|
||
('admin', 'auth.me', 'DEPT'),
|
||
('admin', 'documents.upload', 'DEPT'),
|
||
('admin', 'documents.list', 'DEPT'),
|
||
('admin', 'documents.detail', 'DEPT'),
|
||
('admin', 'documents.history', 'DEPT'),
|
||
('admin', 'documents.delete', 'DEPT'),
|
||
('admin', 'audit.run', 'DEPT'),
|
||
('admin', 'audit.run.status', 'DEPT'),
|
||
('admin', 'audit.result', 'DEPT'),
|
||
('admin', 'rules.list', 'DEPT'),
|
||
('admin', 'rules.version.list', 'DEPT'),
|
||
('admin', 'rules.content', 'DEPT'),
|
||
('admin', 'rules.validate', 'DEPT'),
|
||
('admin', 'rules.binding.list', 'DEPT'),
|
||
('admin', 'rules.binding.create', 'DEPT'),
|
||
('admin', 'rules.binding.update', 'DEPT'),
|
||
('admin', 'users.list', 'DEPT'),
|
||
('admin', 'users.update', 'DEPT'),
|
||
|
||
('common', 'auth.me', 'SELF'),
|
||
('common', 'documents.upload', 'SELF'),
|
||
('common', 'documents.list', 'SELF'),
|
||
('common', 'documents.detail', 'SELF'),
|
||
('common', 'documents.history', 'SELF'),
|
||
('common', 'audit.run', 'SELF'),
|
||
('common', 'audit.run.status', 'SELF'),
|
||
('common', 'audit.result', 'SELF'),
|
||
('common', 'rules.list', 'DEPT'),
|
||
('common', 'rules.version.list', 'DEPT'),
|
||
('common', 'rules.content', 'DEPT'),
|
||
('common', 'rules.binding.list', 'DEPT')
|
||
)
|
||
INSERT INTO role_permissions (role_id, permission_id, data_scope, created_at)
|
||
SELECT DISTINCT rm.id, pm.id, s.data_scope, NOW()
|
||
FROM seed s
|
||
JOIN role_map rm ON rm.role_name = s.role_name
|
||
JOIN perm_map pm ON pm.permission_code = s.permission_code
|
||
ON CONFLICT (role_id, permission_id) DO UPDATE SET
|
||
data_scope = EXCLUDED.data_scope;
|
||
|
||
COMMIT;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- 6) 使用示例（按需执行，不建议直接整段上线）
|
||
-- ------------------------------------------------------------
|
||
-- 将一个现有用户提为地区管理员：
|
||
-- INSERT INTO user_role (user_id, role_id, created_at)
|
||
-- SELECT 1001, id, NOW() FROM roles WHERE role_name = 'admin'
|
||
-- ON CONFLICT (user_id, role_id) DO NOTHING;
|
||
|
||
-- 查看角色及其默认数据范围：
|
||
-- SELECT role_name, data_scope FROM roles ORDER BY id;
|
||
|
||
-- 查看某角色已分配的权限点：
|
||
-- SELECT r.role_name, p.permission_code, rp.data_scope
|
||
-- FROM role_permissions rp
|
||
-- JOIN roles r ON r.id = rp.role_id
|
||
-- JOIN permissions p ON p.id = rp.permission_id
|
||
-- WHERE r.role_name = 'admin'
|
||
-- ORDER BY p.permission_code;
|