xiner/Edu
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
Edu/scripts/iam-init.sql
SpecialX a35e759d64 feat(iam): 完整实现 iam 身份认证与权限服务 
包含 jwt/jwks/audit/grpc、rbac、cache、redis/kafka 配置等完整实现
2026-07-10 19:09:39 +08:00

262 lines
16 KiB
SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
-- IAM 服务表结构（P2.1 + P2.2：身份 + RBAC + 审计 + 家长关系）
-- 对齐 coord-final-decisions I1-I8 + president §2.15/§2.16/§3.2/§5.5
-- ============================================================
-- 1. iam_users：用户主表
-- ============================================================
CREATE TABLE IF NOT EXISTS `iam_users` (
`id` CHAR(36) NOT NULL,
`email` VARCHAR(255) NOT NULL,
`password_hash` VARCHAR(255) NOT NULL,
`name` VARCHAR(100) NOT NULL,
`status` VARCHAR(20) NOT NULL DEFAULT 'active',
-- DataScope 6 级（president §3.2：SUBJECT 替代 DISTRICT）
`data_scope` ENUM('self','subject','class','grade','school','all') NOT NULL DEFAULT 'self',
-- 密码策略：记录上次修改时间，用于过期校验
`password_changed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `iam_users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- 2. iam_roles：角色表（三层角色模型）
-- ============================================================
CREATE TABLE IF NOT EXISTS `iam_roles` (
`id` CHAR(36) NOT NULL,
`name` VARCHAR(50) NOT NULL,
`description` VARCHAR(255) NULL,
-- 三层角色模型：system(系统预设) / organization(组织分配) / temporary(临时授权)
`role_type` ENUM('system','organization','temporary') NOT NULL DEFAULT 'system',
-- 三层优先级：system=0(最高) / organization=1 / temporary=2
`level` INT NOT NULL DEFAULT 0,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `iam_roles_name_unique` (`name`),
INDEX `idx_iam_roles_type` (`role_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- 3. iam_user_roles：用户-角色关联
-- ============================================================
CREATE TABLE IF NOT EXISTS `iam_user_roles` (
`user_id` CHAR(36) NOT NULL,
`role_id` CHAR(36) NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`, `role_id`),
INDEX `idx_iam_user_roles_role` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- 4. iam_permissions：权限点
-- ============================================================
CREATE TABLE IF NOT EXISTS `iam_permissions` (
`id` CHAR(36) NOT NULL,
`name` VARCHAR(100) NOT NULL,
`resource` VARCHAR(50) NOT NULL,
`action` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `iam_permissions_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- 5. iam_role_permissions：角色-权限关联
-- ============================================================
CREATE TABLE IF NOT EXISTS `iam_role_permissions` (
`role_id` CHAR(36) NOT NULL,
`permission_id` CHAR(36) NOT NULL,
PRIMARY KEY (`role_id`, `permission_id`),
INDEX `idx_iam_role_permissions_perm` (`permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- 6. iam_refresh_tokens：刷新令牌（含 jti 用于黑名单追踪）
-- ============================================================
CREATE TABLE IF NOT EXISTS `iam_refresh_tokens` (
`id` CHAR(36) NOT NULL,
`user_id` CHAR(36) NOT NULL,
`token_hash` VARCHAR(255) NOT NULL,
-- JWT ID，用于黑名单追踪（I7：JWT 黑名单）
`jti` VARCHAR(36) NULL,
`expires_at` TIMESTAMP NOT NULL,
`revoked_at` TIMESTAMP NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_iam_refresh_tokens_user` (`user_id`),
INDEX `idx_iam_refresh_tokens_jti` (`jti`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- 7. iam_role_viewports：视口配置表
-- 4 层模型：L1 导航 / L2 路由 / L3 组件 / L4 数据
-- ============================================================
CREATE TABLE IF NOT EXISTS `iam_role_viewports` (
`id` CHAR(36) NOT NULL,
`role_id` CHAR(36) NOT NULL,
`viewport_key` VARCHAR(50) NOT NULL,
`label` VARCHAR(100) NOT NULL,
`route` VARCHAR(200) NOT NULL,
`icon` VARCHAR(50) NULL,
`sort_order` VARCHAR(10) NOT NULL DEFAULT '0',
`required_permission` VARCHAR(100) NULL,
-- 视口层级：admin / teacher / student / parent
`level` ENUM('admin','teacher','student','parent') NOT NULL DEFAULT 'teacher',
-- L3 组件级配置（JSON）：控制组件内按钮/操作的显隐
`component_config` TEXT NULL,
PRIMARY KEY (`id`),
INDEX `idx_iam_role_viewports_role` (`role_id`),
INDEX `idx_iam_role_viewports_level` (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- 8. iam_student_guardians：学生-家长关系表（I6 裁决）
-- ============================================================
CREATE TABLE IF NOT EXISTS `iam_student_guardians` (
`id` CHAR(36) NOT NULL,
`student_id` CHAR(36) NOT NULL,
`guardian_id` CHAR(36) NOT NULL,
`relation` VARCHAR(20) NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_student_guardian` (`student_id`, `guardian_id`),
INDEX `idx_iam_student_guardians_guardian` (`guardian_id`),
INDEX `idx_iam_student_guardians_student` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- 9. iam_user_audit_log：审计日志表（president §5.5：归 iam）
-- ============================================================
CREATE TABLE IF NOT EXISTS `iam_user_audit_log` (
`id` CHAR(36) NOT NULL,
`actor_user_id` CHAR(36) NOT NULL,
`action` VARCHAR(50) NOT NULL,
`resource_type` VARCHAR(50) NOT NULL,
`resource_id` VARCHAR(36) NOT NULL,
`before_state` TEXT NULL,
`after_state` TEXT NULL,
`ip` VARCHAR(45) NULL,
`user_agent` VARCHAR(255) NULL,
`trace_id` VARCHAR(64) NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_iam_audit_actor` (`actor_user_id`),
INDEX `idx_iam_audit_resource` (`resource_type`, `resource_id`),
INDEX `idx_iam_audit_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- 10. iam_password_history：密码历史表（密码重用限制）
-- ============================================================
CREATE TABLE IF NOT EXISTS `iam_password_history` (
`id` CHAR(36) NOT NULL,
`user_id` CHAR(36) NOT NULL,
`password_hash` VARCHAR(255) NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_iam_password_history_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- 种子数据：4 个系统角色（三层角色模型 system 层）
-- ============================================================
INSERT IGNORE INTO `iam_roles` (`id`, `name`, `description`, `role_type`, `level`) VALUES
('00000000-0000-0000-0000-000000000001', 'admin', '系统管理员', 'system', 0),
('00000000-0000-0000-0000-000000000002', 'teacher', '教师', 'system', 0),
('00000000-0000-0000-0000-000000000003', 'student', '学生', 'system', 0),
('00000000-0000-0000-0000-000000000004', 'parent', '家长', 'system', 0);
-- ============================================================
-- 种子数据：权限点（固定 UUID 便于 role_permissions 引用）
-- ============================================================
INSERT IGNORE INTO `iam_permissions` (`id`, `name`, `resource`, `action`) VALUES
('00000000-0000-0000-0000-000000000101', 'classes:read', 'classes', 'read'),
('00000000-0000-0000-0000-000000000102', 'classes:create', 'classes', 'create'),
('00000000-0000-0000-0000-000000000103', 'classes:update', 'classes', 'update'),
('00000000-0000-0000-0000-000000000104', 'classes:delete', 'classes', 'delete'),
('00000000-0000-0000-0000-000000000201', 'iam:user:read', 'iam', 'user:read'),
('00000000-0000-0000-0000-000000000202', 'iam:user:manage', 'iam', 'user:manage'),
('00000000-0000-0000-0000-000000000203', 'iam:role:manage', 'iam', 'role:manage'),
('00000000-0000-0000-0000-000000000204', 'iam:audit:read', 'iam', 'audit:read'),
('00000000-0000-0000-0000-000000000205', 'iam:viewport:read', 'iam', 'viewport:read'),
('00000000-0000-0000-0000-000000000301', 'exam:read', 'exam', 'read'),
('00000000-0000-0000-0000-000000000302', 'exam:manage', 'exam', 'manage');
-- ============================================================
-- 种子数据：admin 角色权限（全部权限）
-- ============================================================
INSERT IGNORE INTO `iam_role_permissions` (`role_id`, `permission_id`) VALUES
('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000101'),
('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000102'),
('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000103'),
('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000104'),
('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000201'),
('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000202'),
('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000203'),
('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000204'),
('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000205'),
('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000301'),
('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000302');
-- ============================================================
-- 种子数据：teacher 角色权限（classes CRUD + user:read + exam:read）
-- ============================================================
INSERT IGNORE INTO `iam_role_permissions` (`role_id`, `permission_id`) VALUES
('00000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000101'),
('00000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000102'),
('00000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000103'),
('00000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000104'),
('00000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000201'),
('00000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000301');
-- ============================================================
-- 种子数据：student 角色权限（classes:read + exam:read）
-- ============================================================
INSERT IGNORE INTO `iam_role_permissions` (`role_id`, `permission_id`) VALUES
('00000000-0000-0000-0000-000000000003', '00000000-0000-0000-0000-000000000101'),
('00000000-0000-0000-0000-000000000003', '00000000-0000-0000-0000-000000000301');
-- ============================================================
-- 种子数据：parent 角色权限（classes:read + viewport:read）
-- ============================================================
INSERT IGNORE INTO `iam_role_permissions` (`role_id`, `permission_id`) VALUES
('00000000-0000-0000-0000-000000000004', '00000000-0000-0000-0000-000000000101'),
('00000000-0000-0000-0000-000000000004', '00000000-0000-0000-0000-000000000205');
-- ============================================================
-- 种子数据：admin 视口（L1 导航，level=admin）
-- ============================================================
INSERT IGNORE INTO `iam_role_viewports` (`id`, `role_id`, `viewport_key`, `label`, `route`, `icon`, `sort_order`, `required_permission`, `level`, `component_config`) VALUES
('00000000-0000-0000-0000-000000000b01', '00000000-0000-0000-0000-000000000001', 'dashboard', '仪表盘', '/dashboard', 'home', '0', NULL, 'admin', NULL),
('00000000-0000-0000-0000-000000000b02', '00000000-0000-0000-0000-000000000001', 'classes', '班级管理', '/classes', 'users', '1', 'classes:read', 'admin', NULL),
('00000000-0000-0000-0000-000000000b03', '00000000-0000-0000-0000-000000000001', 'iam', '用户管理', '/iam/users', 'shield', '2', 'iam:user:read', 'admin', NULL),
('00000000-0000-0000-0000-000000000b04', '00000000-0000-0000-0000-000000000001', 'audit', '审计日志', '/iam/audit', 'list', '3', 'iam:audit:read', 'admin', NULL),
('00000000-0000-0000-0000-000000000b05', '00000000-0000-0000-0000-000000000001', 'profile', '个人中心', '/profile', 'user', '9', NULL, 'admin', NULL);
-- ============================================================
-- 种子数据：teacher 视口（L1 导航，level=teacher）
-- ============================================================
INSERT IGNORE INTO `iam_role_viewports` (`id`, `role_id`, `viewport_key`, `label`, `route`, `icon`, `sort_order`, `required_permission`, `level`, `component_config`) VALUES
('00000000-0000-0000-0000-000000000a01', '00000000-0000-0000-0000-000000000002', 'dashboard', '仪表盘', '/dashboard', 'home', '0', NULL, 'teacher', NULL),
('00000000-0000-0000-0000-000000000a02', '00000000-0000-0000-0000-000000000002', 'classes', '班级管理', '/classes', 'users', '1', 'classes:read', 'teacher', NULL),
('00000000-0000-0000-0000-000000000a03', '00000000-0000-0000-0000-000000000002', 'exam', '考试管理', '/exam', 'file-text','2', 'exam:read', 'teacher', NULL),
('00000000-0000-0000-0000-000000000a04', '00000000-0000-0000-0000-000000000002', 'profile', '个人中心', '/profile', 'user', '9', NULL, 'teacher', NULL);
-- ============================================================
-- 种子数据：student 视口（L1 导航，level=student）
-- ============================================================
INSERT IGNORE INTO `iam_role_viewports` (`id`, `role_id`, `viewport_key`, `label`, `route`, `icon`, `sort_order`, `required_permission`, `level`, `component_config`) VALUES
('00000000-0000-0000-0000-000000000c01', '00000000-0000-0000-0000-000000000003', 'dashboard', '仪表盘', '/dashboard', 'home', '0', NULL, 'student', NULL),
('00000000-0000-0000-0000-000000000c02', '00000000-0000-0000-0000-000000000003', 'classes', '我的班级', '/classes', 'users', '1', 'classes:read', 'student', NULL),
('00000000-0000-0000-0000-000000000c03', '00000000-0000-0000-0000-000000000003', 'exam', '我的考试', '/exam', 'file-text','2', 'exam:read', 'student', NULL),
('00000000-0000-0000-0000-000000000c04', '00000000-0000-0000-0000-000000000003', 'profile', '个人中心', '/profile', 'user', '9', NULL, 'student', NULL);
-- ============================================================
-- 种子数据：parent 视口（L1 导航，level=parent）
-- ============================================================
INSERT IGNORE INTO `iam_role_viewports` (`id`, `role_id`, `viewport_key`, `label`, `route`, `icon`, `sort_order`, `required_permission`, `level`, `component_config`) VALUES
('00000000-0000-0000-0000-000000000d01', '00000000-0000-0000-0000-000000000004', 'dashboard', '仪表盘', '/dashboard', 'home', '0', NULL, 'parent', NULL),
('00000000-0000-0000-0000-000000000d02', '00000000-0000-0000-0000-000000000004', 'children', '我的孩子', '/children', 'users', '1', 'classes:read', 'parent', NULL),
('00000000-0000-0000-0000-000000000d03', '00000000-0000-0000-0000-000000000004', 'profile', '个人中心', '/profile', 'user', '9', NULL, 'parent', NULL);
Reference in New Issue View Git Blame Copy Permalink