设计一个完美的用户角色权限表

设计一个完美的用户角色权限表需要考虑系统的安全性、灵活性和可扩展性。以下是一个详细的用户角色权限管理表设计方案，包含多个表结构和字段描述。

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 表中应设置为联合唯一索引。

通过这个设计，可以灵活地管理用户、角色和权限，确保系统的安全性和灵活性，满足各种复杂的权限管理需求。