You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
43 lines
1.7 KiB
43 lines
1.7 KiB
-- 创建用户并设置密码
|
|
CREATE USER 'admin_user'@'localhost' IDENTIFIED BY 'AdminPass';
|
|
CREATE USER 'coach_user'@'localhost' IDENTIFIED BY 'CoachPass';
|
|
CREATE USER 'student_user'@'localhost' IDENTIFIED BY 'StudentPass';
|
|
|
|
-- 授予管理员权限
|
|
GRANT ALL PRIVILEGES ON fitness_center.* TO 'admin_user'@'localhost';
|
|
|
|
-- 创建视图供教练使用
|
|
CREATE VIEW coach_appointments AS
|
|
SELECT * FROM APPOINTMENT WHERE coach_id = SUBSTRING_INDEX(USER(), '@', 1);
|
|
|
|
CREATE VIEW coach_training_records AS
|
|
SELECT * FROM TRAINING_RECORD WHERE coach_id = SUBSTRING_INDEX(USER(), '@', 1);
|
|
|
|
-- 授予教练权限
|
|
GRANT SELECT, INSERT ON fitness_center.FEEDBACK TO 'coach_user'@'localhost';
|
|
GRANT SELECT, UPDATE ON fitness_center.coach_appointments TO 'coach_user'@'localhost';
|
|
GRANT SELECT ON fitness_center.STUDENT TO 'coach_user'@'localhost';
|
|
GRANT SELECT ON fitness_center.coach_training_records TO 'coach_user'@'localhost';
|
|
|
|
-- 创建视图供学生使用
|
|
CREATE VIEW student_appointments AS
|
|
SELECT * FROM APPOINTMENT WHERE student_id = SUBSTRING_INDEX(USER(), '@', 1);
|
|
|
|
CREATE VIEW student_personal_info AS
|
|
SELECT * FROM PERSONAL_INFO WHERE student_id = SUBSTRING_INDEX(USER(), '@', 1);
|
|
|
|
CREATE VIEW student_training_records AS
|
|
SELECT * FROM TRAINING_RECORD WHERE student_id = SUBSTRING_INDEX(USER(), '@', 1);
|
|
|
|
-- 授予学生权限
|
|
GRANT SELECT, INSERT ON fitness_center.FEEDBACK TO 'student_user'@'localhost';
|
|
GRANT SELECT, UPDATE ON fitness_center.student_appointments TO 'student_user'@'localhost';
|
|
GRANT SELECT ON fitness_center.student_personal_info TO 'student_user'@'localhost';
|
|
GRANT SELECT ON fitness_center.student_training_records TO 'student_user'@'localhost';
|
|
|
|
-- 刷新权限
|
|
FLUSH PRIVILEGES;
|
|
|
|
|
|
|