权限五张表

重点：权限五张表的设计 核心概念：

在权限管理系统中，经典的设计通常涉及五张表，分别是用户表、角色表、权限表、用户角色表和角色权限表。这五张表的设计可以有效地管理用户的权限，确保系统的安全性和灵活性。

用户（User）：系统的使用者，每个用户有唯一的身份标识。角色（Role）：一组权限的集合，用于简化权限分配。权限（Permission）：系统中具体的操作或资源访问权限（如读取、写入、删除等）。用户角色关联：将用户与角色关联起来，表示用户拥有某个角色的权限。角色权限关联：将角色与权限关联起来，表示角色拥有哪些权限。 表之间的关系

用户与角色：

一个用户可以有多个角色，一个角色可以分配给多个用户。通过user_role表实现多对多关系。

角色与权限：

一个角色可以有多个权限，一个权限可以分配给多个角色。通过role_permission表实现多对多关系。

权限层级：

用户通过角色间接拥有权限。查询用户权限时，需要先查询用户所属角色，再查询角色对应的权限。

---

1.表设计 1.1 用户表（user） 用户表用于存储系统中的用户信息，包括用户ID、用户名和用户密码等。每个用户在登录时都会通过该表进行验证，以确保其合法性。 CREATE TABLE `user` ( `id` INT PRIMARY KEY AUTO_INCREMENT, `username` VARCHAR(50) NOT NULL UNIQUE, `password` VARCHAR(255) NOT NULL, `email` VARCHAR(100), `active` TINYINT(1) NOT NULL DEFAULT 1, `version` INT NOT NULL DEFAULT 0, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

---

1.2 角色表（role） 角色表用于定义系统中的各种角色，例如管理员、普通用户等。每个角色代表一组权限，用户通过分配角色来获得相应的权限。 CREATE TABLE `role` ( `id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL UNIQUE, `description` VARCHAR(255), `active` TINYINT(1) NOT NULL DEFAULT 1, `version` INT NOT NULL DEFAULT 0, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

---

1.3 权限表（permission） 权限表用于存储系统中的各种资源和操作，例如页面访问、按钮点击等。每个权限项描述了一个具体的操作，用户只有拥有相应的权限才能执行该操作。 CREATE TABLE `permission` ( `id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL UNIQUE, `description` VARCHAR(255), `active` TINYINT(1) NOT NULL DEFAULT 1, `version` INT NOT NULL DEFAULT 0, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

---

1.4 用户角色关联表（user_role） 用户角色表用于建立用户与角色之间的关系。通过该表，可以将用户分配到不同的角色，从而获得相应的权限。 CREATE TABLE `user_role` ( `user_id` INT NOT NULL, `role_id` INT NOT NULL, `active` TINYINT(1) NOT NULL DEFAULT 1, `version` INT NOT NULL DEFAULT 0, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`user_id`, `role_id`), FOREIGN KEY (`user_id`) REFERENCES `user`(`id`), FOREIGN KEY (`role_id`) REFERENCES `role`(`id`) );

---

1.5 角色权限关联表（role_permission） 角色权限表用于建立角色与权限之间的关系。通过该表，可以为每个角色分配不同的权限，从而实现用户到角色，角色到权限的转换。 CREATE TABLE `role_permission` ( `role_id` INT NOT NULL, `permission_id` INT NOT NULL, `active` TINYINT(1) NOT NULL DEFAULT 1, `version` INT NOT NULL DEFAULT 0, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`role_id`, `permission_id`), FOREIGN KEY (`role_id`) REFERENCES `role`(`id`), FOREIGN KEY (`permission_id`) REFERENCES `permission`(`id`) );

---

2.实现案例

场景：学生管理系统中有管理员、教师、学生三种角色。

管理员可以管理所有数据。教师可以管理学生成绩。学生只能查看自己的成绩。 2.1 查询用户的角色 SELECT r.name FROM `user` u JOIN `user_role` ur ON u.id = ur.user_id JOIN `role` r ON ur.role_id = r.id WHERE u.username = 'test_user' AND u.active = 1 AND r.active = 1; 2.2 查询用户的权限 SELECT p.name AS permission_name FROM user u JOIN user_role ur ON u.id = ur.user_id JOIN role_permission rp ON ur.role_id = rp.role_id JOIN permission p ON rp.permission_id = p.id WHERE u.username = 'student1'; 2.3 查询所有教师角色 SELECT u.username FROM user u JOIN user_role ur ON u.id = ur.user_id JOIN role r ON ur.role_id = r.id WHERE r.name = 'teacher'; 2.4 逻辑删除用户 UPDATE `user` SET active = 0 WHERE id = 1; 2.5 使用乐观锁更新用户信息 UPDATE user SET email = 'new_teacher1@test ', version = version + 1 WHERE username = 'teacher1' AND version = 0; -- 假设当前版本号为0

---

MySQL中的权限五张表（了解，选择性观看！） 1. 了解MySQL权限系统

​ MySQL通过权限系统控制用户对数据库、表、列等的访问权限。权限信息存储在mysql数据库的五张表中：user、db、tables_priv、columns_priv、procs_priv。这些表是层级关系，MySQL在检查权限时从上到下依次检查。

SELECT * FROM mysql.user WHERE User = 'test_user';

2.权限五张表 表1：user 表

作用：存储全局权限，即用户对所有数据库的权限。在任一数据库中都可以使用

关键字段：

Host：允许用户连接的主机（如localhost、%）。User：用户名。权限字段：如Select_priv、Insert_priv、Update_priv等，表示用户是否拥有对应的全局权限。Grant_priv:用户是否可以授权其他用户权限

示例：

SELECT Host, User, Select_priv, Insert_priv FROM mysql.user WHERE User = 'test_user';

解释：这条查询会显示用户test_user在所有数据库上的SELECT和INSERT权限。

---

表2：db 表

作用：

存储用户对特定数据库的权限。如果用户在user表中没有全局权限，MySQL会检查db表，判断用户是否有权限访问某个数据库。

关键字段：

Host、User：与user表相同。Db：数据库名称。权限字段：如Select_priv、Insert_priv等，表示用户在该数据库上的权限。

示例：

SELECT Host, User, Db, Select_priv, Insert_priv FROM mysql.db WHERE User = 'test_user';

解释：这条查询会显示用户test_user在test_db数据库上的权限。

---

表3：tables_priv 表

作用：存储用户对特定表的权限。

关键字段：

Host、User、Db：与db表相同。Table_name：表名称。Table_priv：用户在该表上的权限（如Select、Insert等）。Column_priv：用户在该表的列上的权限（如果有）。

示例：

SELECT Host, User, Db, Table_name, Table_priv FROM mysql.tables_priv WHERE User = 'test_user';

解释：这条查询会显示用户test_user在test_db.test_table表上的权限。

---

表4：columns_priv 表

作用：存储用户对特定列的权限。

关键字段：

Host、User、Db、Table_name：与tables_priv表相同。Column_name：列名称。Column_priv：用户在该列上的权限（如Select、Insert等）。

示例：

SELECT Host, User, Db, Table_name, Column_name, Column_priv FROM mysql.columns_priv WHERE User = 'test_user';

解释：这条查询会显示用户test_user在test_db.test_table表中特定列上的权限。

---

表5：procs_priv 表

作用：

存储用户对存储过程和函数的权限。如果用户需要执行存储过程或函数，MySQL会检查procs_priv表。

关键字段：

Host、User、Db：与db表相同。Routine_name：存储过程或函数的名称。Routine_type：类型（PROCEDURE或FUNCTION）Proc_priv：用户对该存储过程或函数的权限（如Execute）。

示例：

SELECT Host, User, Db, Routine_name, Routine_type, Proc_priv FROM mysql.procs_priv WHERE User = 'test_user';

解释：这条查询会显示用户test_user在test_db数据库中存储过程和函数上的权限。

---

3.权限检查流程 MySQL在检查用户权限时，按照以下顺序检查： 检查user表中的全局权限。检查db表中的数据库权限。检查tables_priv表中的表权限。检查columns_priv表中的列权限。检查procs_priv表中的存储过程和函数权限。 **注意：**如果某一层级的权限满足要求，MySQL就不会继续检查下一层级。