226 lines
11 KiB
Go
226 lines
11 KiB
Go
-- =============================================
|
||
-- 学习系统角色权限配置SQL脚本
|
||
-- 创建教师角色和学生角色，并分配相应的菜单和API权限
|
||
-- 注意：请先执行 learning_system_init.sql 再执行此文件
|
||
-- =============================================
|
||
|
||
-- 1. 创建角色
|
||
INSERT INTO `sys_authorities` (`authority_id`, `authority_name`, `parent_id`, `default_router`) VALUES
|
||
(1001, '教师', 0, 'learning'),
|
||
(1002, '学生', 0, 'student');
|
||
|
||
-- 2. 获取菜单ID
|
||
SET @learning_menu_id = (SELECT id FROM sys_base_menus WHERE name = 'learning' AND menu_level = 0);
|
||
SET @student_menu_id = (SELECT id FROM sys_base_menus WHERE name = 'student' AND menu_level = 0);
|
||
|
||
-- 获取教学管理子菜单ID
|
||
SET @course_menu_id = (SELECT id FROM sys_base_menus WHERE name = 'course' AND parent_id = @learning_menu_id);
|
||
SET @chapter_menu_id = (SELECT id FROM sys_base_menus WHERE name = 'chapter' AND parent_id = @learning_menu_id);
|
||
SET @knowledge_menu_id = (SELECT id FROM sys_base_menus WHERE name = 'knowledgePoint' AND parent_id = @learning_menu_id);
|
||
SET @question_menu_id = (SELECT id FROM sys_base_menus WHERE name = 'question' AND parent_id = @learning_menu_id);
|
||
SET @exam_menu_id = (SELECT id FROM sys_base_menus WHERE name = 'exam' AND parent_id = @learning_menu_id);
|
||
SET @records_menu_id = (SELECT id FROM sys_base_menus WHERE name = 'learningRecords' AND parent_id = @learning_menu_id);
|
||
|
||
-- 获取在线学习子菜单ID
|
||
SET @dashboard_menu_id = (SELECT id FROM sys_base_menus WHERE name = 'dashboard' AND parent_id = @student_menu_id);
|
||
SET @my_courses_menu_id = (SELECT id FROM sys_base_menus WHERE name = 'myCourses' AND parent_id = @student_menu_id);
|
||
SET @online_exam_menu_id = (SELECT id FROM sys_base_menus WHERE name = 'onlineExam' AND parent_id = @student_menu_id);
|
||
SET @wrong_questions_menu_id = (SELECT id FROM sys_base_menus WHERE name = 'wrongQuestions' AND parent_id = @student_menu_id);
|
||
|
||
-- 3. 为教师角色分配菜单权限
|
||
INSERT INTO `sys_authority_menus` (`sys_base_menu_id`, `sys_authority_authority_id`) VALUES
|
||
-- 教学管理主菜单
|
||
(@learning_menu_id, 1001),
|
||
-- 教学管理子菜单
|
||
(@course_menu_id, 1001),
|
||
(@chapter_menu_id, 1001),
|
||
(@knowledge_menu_id, 1001),
|
||
(@question_menu_id, 1001),
|
||
(@exam_menu_id, 1001),
|
||
(@records_menu_id, 1001);
|
||
|
||
-- 4. 为学生角色分配菜单权限
|
||
INSERT INTO `sys_authority_menus` (`sys_base_menu_id`, `sys_authority_authority_id`) VALUES
|
||
-- 在线学习主菜单
|
||
(@student_menu_id, 1002),
|
||
-- 在线学习子菜单
|
||
(@dashboard_menu_id, 1002),
|
||
(@my_courses_menu_id, 1002),
|
||
(@online_exam_menu_id, 1002),
|
||
(@wrong_questions_menu_id, 1002);
|
||
|
||
-- 5. 为教师角色分配API权限
|
||
INSERT INTO `casbin_rule` (`ptype`, `v0`, `v1`, `v2`) VALUES
|
||
-- 课程管理API权限
|
||
('p', '1001', '/course/createCourse', 'POST'),
|
||
('p', '1001', '/course/deleteCourse', 'DELETE'),
|
||
('p', '1001', '/course/deleteCourseByIds', 'DELETE'),
|
||
('p', '1001', '/course/updateCourse', 'PUT'),
|
||
('p', '1001', '/course/findCourse', 'GET'),
|
||
('p', '1001', '/course/getCourseList', 'GET'),
|
||
('p', '1001', '/course/getPublishedCourses', 'GET'),
|
||
|
||
-- 章节管理API权限
|
||
('p', '1001', '/chapter/createChapter', 'POST'),
|
||
('p', '1001', '/chapter/deleteChapter', 'DELETE'),
|
||
('p', '1001', '/chapter/deleteChapterByIds', 'DELETE'),
|
||
('p', '1001', '/chapter/updateChapter', 'PUT'),
|
||
('p', '1001', '/chapter/findChapter', 'GET'),
|
||
('p', '1001', '/chapter/getChapterList', 'GET'),
|
||
('p', '1001', '/chapter/getChaptersByCourse', 'GET'),
|
||
|
||
-- 知识点管理API权限
|
||
('p', '1001', '/knowledgePoint/createKnowledgePoint', 'POST'),
|
||
('p', '1001', '/knowledgePoint/deleteKnowledgePoint', 'DELETE'),
|
||
('p', '1001', '/knowledgePoint/deleteKnowledgePointByIds', 'DELETE'),
|
||
('p', '1001', '/knowledgePoint/updateKnowledgePoint', 'PUT'),
|
||
('p', '1001', '/knowledgePoint/findKnowledgePoint', 'GET'),
|
||
('p', '1001', '/knowledgePoint/getKnowledgePointList', 'GET'),
|
||
('p', '1001', '/knowledgePoint/getKnowledgePointsByChapter', 'GET'),
|
||
|
||
-- 题目管理API权限
|
||
('p', '1001', '/question/createQuestion', 'POST'),
|
||
('p', '1001', '/question/deleteQuestion', 'DELETE'),
|
||
('p', '1001', '/question/deleteQuestionByIds', 'DELETE'),
|
||
('p', '1001', '/question/updateQuestion', 'PUT'),
|
||
('p', '1001', '/question/findQuestion', 'GET'),
|
||
('p', '1001', '/question/getQuestionList', 'GET'),
|
||
('p', '1001', '/question/getQuestionsByKnowledgePoint', 'GET'),
|
||
|
||
-- 考试管理API权限
|
||
('p', '1001', '/exam/createExam', 'POST'),
|
||
('p', '1001', '/exam/deleteExam', 'DELETE'),
|
||
('p', '1001', '/exam/deleteExamByIds', 'DELETE'),
|
||
('p', '1001', '/exam/updateExam', 'PUT'),
|
||
('p', '1001', '/exam/publishExam', 'PUT'),
|
||
('p', '1001', '/exam/findExam', 'GET'),
|
||
('p', '1001', '/exam/getExamList', 'GET'),
|
||
('p', '1001', '/exam/getActiveExams', 'GET'),
|
||
('p', '1001', '/exam/getExamsByCourse', 'GET'),
|
||
('p', '1001', '/exam/getExamStatistics', 'GET'),
|
||
|
||
-- 学习记录管理API权限
|
||
('p', '1001', '/userLearning/createUserLearning', 'POST'),
|
||
('p', '1001', '/userLearning/deleteUserLearning', 'DELETE'),
|
||
('p', '1001', '/userLearning/deleteUserLearningByIds', 'DELETE'),
|
||
('p', '1001', '/userLearning/updateUserLearning', 'PUT'),
|
||
('p', '1001', '/userLearning/findUserLearning', 'GET'),
|
||
('p', '1001', '/userLearning/getUserLearningList', 'GET'),
|
||
('p', '1001', '/userLearning/getUserProgress', 'GET'),
|
||
('p', '1001', '/userLearning/getLearningStatistics', 'GET'),
|
||
|
||
-- 错题本管理API权限
|
||
('p', '1001', '/wrongQuestion/createWrongQuestion', 'POST'),
|
||
('p', '1001', '/wrongQuestion/deleteWrongQuestion', 'DELETE'),
|
||
('p', '1001', '/wrongQuestion/deleteWrongQuestionByIds', 'DELETE'),
|
||
('p', '1001', '/wrongQuestion/updateWrongQuestion', 'PUT'),
|
||
('p', '1001', '/wrongQuestion/markAsMastered', 'PUT'),
|
||
('p', '1001', '/wrongQuestion/findWrongQuestion', 'GET'),
|
||
('p', '1001', '/wrongQuestion/getWrongQuestionList', 'GET'),
|
||
('p', '1001', '/wrongQuestion/getUserWrongQuestions', 'GET'),
|
||
('p', '1001', '/wrongQuestion/getWrongQuestionStatistics', 'GET'),
|
||
|
||
-- 用户考试记录管理API权限
|
||
('p', '1001', '/userExam/createUserExam', 'POST'),
|
||
('p', '1001', '/userExam/deleteUserExam', 'DELETE'),
|
||
('p', '1001', '/userExam/deleteUserExamByIds', 'DELETE'),
|
||
('p', '1001', '/userExam/updateUserExam', 'PUT'),
|
||
('p', '1001', '/userExam/startExam', 'POST'),
|
||
('p', '1001', '/userExam/submitExam', 'PUT'),
|
||
('p', '1001', '/userExam/findUserExam', 'GET'),
|
||
('p', '1001', '/userExam/getUserExamList', 'GET'),
|
||
('p', '1001', '/userExam/getUserExamHistory', 'GET'),
|
||
('p', '1001', '/userExam/getUserExamStatistics', 'GET'),
|
||
('p', '1001', '/userExam/getExamRanking', 'GET');
|
||
|
||
-- 6. 为学生角色分配API权限（只读权限和学生相关功能）
|
||
INSERT INTO `casbin_rule` (`ptype`, `v0`, `v1`, `v2`) VALUES
|
||
-- 课程查看权限
|
||
('p', '1002', '/course/findCourse', 'GET'),
|
||
('p', '1002', '/course/getPublishedCourses', 'GET'),
|
||
|
||
-- 章节查看权限
|
||
('p', '1002', '/chapter/findChapter', 'GET'),
|
||
('p', '1002', '/chapter/getChaptersByCourse', 'GET'),
|
||
|
||
-- 知识点查看权限
|
||
('p', '1002', '/knowledgePoint/findKnowledgePoint', 'GET'),
|
||
('p', '1002', '/knowledgePoint/getKnowledgePointsByChapter', 'GET'),
|
||
|
||
-- 题目查看权限
|
||
('p', '1002', '/question/findQuestion', 'GET'),
|
||
('p', '1002', '/question/getQuestionsByKnowledgePoint', 'GET'),
|
||
|
||
-- 考试相关权限
|
||
('p', '1002', '/exam/findExam', 'GET'),
|
||
('p', '1002', '/exam/getActiveExams', 'GET'),
|
||
('p', '1002', '/exam/getExamsByCourse', 'GET'),
|
||
|
||
-- 学习记录权限（学生可以创建和查看自己的学习记录）
|
||
('p', '1002', '/userLearning/createUserLearning', 'POST'),
|
||
('p', '1002', '/userLearning/updateUserLearning', 'PUT'),
|
||
('p', '1002', '/userLearning/findUserLearning', 'GET'),
|
||
('p', '1002', '/userLearning/getUserProgress', 'GET'),
|
||
('p', '1002', '/userLearning/getLearningStatistics', 'GET'),
|
||
|
||
-- 错题本权限（学生可以管理自己的错题）
|
||
('p', '1002', '/wrongQuestion/createWrongQuestion', 'POST'),
|
||
('p', '1002', '/wrongQuestion/updateWrongQuestion', 'PUT'),
|
||
('p', '1002', '/wrongQuestion/markAsMastered', 'PUT'),
|
||
('p', '1002', '/wrongQuestion/findWrongQuestion', 'GET'),
|
||
('p', '1002', '/wrongQuestion/getUserWrongQuestions', 'GET'),
|
||
('p', '1002', '/wrongQuestion/getWrongQuestionStatistics', 'GET'),
|
||
|
||
-- 用户考试记录权限（学生可以参加考试和查看自己的考试记录）
|
||
('p', '1002', '/userExam/startExam', 'POST'),
|
||
('p', '1002', '/userExam/submitExam', 'PUT'),
|
||
('p', '1002', '/userExam/findUserExam', 'GET'),
|
||
('p', '1002', '/userExam/getUserExamHistory', 'GET'),
|
||
('p', '1002', '/userExam/getUserExamStatistics', 'GET'),
|
||
('p', '1002', '/userExam/getExamRanking', 'GET');
|
||
|
||
-- =============================================
|
||
-- 创建测试用户（可选）
|
||
-- =============================================
|
||
|
||
-- 创建教师测试用户
|
||
-- INSERT INTO `sys_users` (`uuid`, `username`, `password`, `nick_name`, `side_mode`, `header_img`, `base_color`, `authority_id`, `phone`, `email`) VALUES
|
||
-- ('teacher-uuid-001', 'teacher', '$2a$10$we19gyu.geUOEhrMkrVzz.VT64GdGC6IThOvAbf8hlkOOZRxdCdli', '张老师', 'dark', 'https://qmplusimg.henrongyi.top/gva_header.jpg', '#fff', 1001, '13800000001', 'teacher@example.com');
|
||
|
||
-- 创建学生测试用户
|
||
-- INSERT INTO `sys_users` (`uuid`, `username`, `password`, `nick_name`, `side_mode`, `header_img`, `base_color`, `authority_id`, `phone`, `email`) VALUES
|
||
-- ('student-uuid-001', 'student', '$2a$10$we19gyu.geUOEhrMkrVzz.VT64GdGC6IThOvAbf8hlkOOZRxdCdli', '李同学', 'dark', 'https://qmplusimg.henrongyi.top/gva_header.jpg', '#fff', 1002, '13800000002', 'student@example.com');
|
||
|
||
-- =============================================
|
||
-- 验证查询
|
||
-- =============================================
|
||
|
||
-- 验证角色是否创建成功
|
||
-- SELECT authority_id, authority_name, parent_id, default_router FROM sys_authorities WHERE authority_id IN (1001, 1002);
|
||
|
||
-- 验证菜单权限是否分配成功
|
||
-- SELECT sa.authority_name, sbm.title, sbm.path
|
||
-- FROM sys_authority_menus sam
|
||
-- JOIN sys_authorities sa ON sam.sys_authority_authority_id = sa.authority_id
|
||
-- JOIN sys_base_menus sbm ON sam.sys_base_menu_id = sbm.id
|
||
-- WHERE sa.authority_id IN (1001, 1002)
|
||
-- ORDER BY sa.authority_id, sbm.sort;
|
||
|
||
-- 验证API权限是否分配成功
|
||
-- SELECT v0 as role, COUNT(*) as api_count
|
||
-- FROM casbin_rule
|
||
-- WHERE v0 IN ('1001', '1002') AND ptype = 'p'
|
||
-- GROUP BY v0;
|
||
|
||
-- =============================================
|
||
-- 使用说明
|
||
-- =============================================
|
||
-- 1. 执行此脚本前请确保已执行 learning_system_init.sql
|
||
-- 2. 此脚本创建了两个角色：1001（教师）和 1002（学生）
|
||
-- 3. 教师角色拥有教学管理的完整权限
|
||
-- 4. 学生角色拥有在线学习的相关权限
|
||
-- 5. 可以取消注释测试用户部分来创建测试账号
|
||
-- 6. 默认密码为：123456
|
||
-- 7. 教师登录后会看到"教学管理"菜单
|
||
-- 8. 学生登录后会看到"在线学习"菜单并跳转到学习首页
|