1. 用户表（Users Table）
存储用户的基本信息。
CREATE TABLE users ( user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL, email VARCHAR(100) UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
2. 角色表（Roles Table）
存储系统中定义的角色。
CREATE TABLE roles ( role_id INT AUTO_INCREMENT PRIMARY KEY, role_name VARCHAR(50) NOT NULL UNIQUE, description VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
3. 权限表（Permissions Table）
存储系统中定义的权限。
CREATE TABLE permissions ( permission_id INT AUTO_INCREMENT PRIMARY KEY, permission_name VARCHAR(50) NOT NULL UNIQUE, description VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
4. 角色权限表（Role Permissions Table）
定义每个角色所拥有的权限。
CREATE TABLE role_permissions ( role_permission_id INT AUTO_INCREMENT PRIMARY KEY, role_id INT NOT NULL, permission_id INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (role_id) REFERENCES roles(role_id), FOREIGN KEY (permission_id) REFERENCES permissions(permission_id), UNIQUE (role_id, permission_id) );
5. 用户角色表（User Roles Table）
定义每个用户所分配的角色。
CREATE TABLE user_roles ( user_role_id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, role_id INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(user_id), FOREIGN KEY (role_id) REFERENCES roles(role_id), UNIQUE (user_id, role_id) );
6. 用户权限表（User Permissions Table）
直接赋予用户特定权限，可以覆盖角色权限。
CREATE TABLE user_permissions ( user_permission_id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, permission_id INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(user_id), FOREIGN KEY (permission_id) REFERENCES permissions(permission_id), UNIQUE (user_id, permission_id) );
数据插入示例
插入角色
INSERT INTO roles (role_name, description) VALUES ('Admin', 'Administrator with full access'), ('Editor', 'Can edit content'), ('Viewer', 'Can view content');
插入权限
INSERT INTO permissions (permission_name, description) VALUES ('view_dashboard', 'View the dashboard'), ('edit_content', 'Edit content'), ('delete_content', 'Delete content');
分配角色权限
INSERT INTO role_permissions (role_id, permission_id) VALUES (1, 1), -- Admin can view_dashboard (1, 2), -- Admin can edit_content (1, 3), -- Admin can delete_content (2, 1), -- Editor can view_dashboard (2, 2), -- Editor can edit_content (3, 1); -- Viewer can view_dashboard
分配用户角色
INSERT INTO user_roles (user_id, role_id) VALUES (1, 1), -- User 1 is an Admin (2, 2), -- User 2 is an Editor (3, 3); -- User 3 is a Viewer
分配用户权限
INSERT INTO user_permissions (user_id, permission_id) VALUES (2, 3); -- User 2 can delete content directly
查询示例
查询用户的所有权限
SELECT p.permission_name FROM users u JOIN user_roles ur ON u.user_id = ur.user_id JOIN roles r ON ur.role_id = r.role_id JOIN role_permissions rp ON r.role_id = rp.role_id JOIN permissions p ON rp.permission_id = p.permission_id WHERE u.user_id = 1 UNION SELECT p.permission_name FROM user_permissions up JOIN permissions p ON up.permission_id = p.permission_id WHERE up.user_id = 1;
索引和优化
- user_id 和 role_id 应该设置为外键，确保数据的完整性和一致性。
- role_id 和 permission_id 在 role_permissions 表中应设置为联合唯一索引。
- user_id 和 role_id 在 user_roles 表中应设置为联合唯一索引。
- user_id 和 permission_id 在 user_permissions 表中应设置为联合唯一索引。
通过这个设计，可以灵活地管理用户、角色和权限，确保系统的安全性和灵活性，满足各种复杂的权限管理需求。
