設計一個完美的用戶角色權限表需要考慮系統的安全性、靈活性和可擴展性。以下是一個詳細的用戶角色權限管理表設計方案，包含多個表結構和字段描述。

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 = 1UNIONSELECT 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 表中應設置為聯合唯一索引。

通過這個設計，可以靈活地管理用戶、角色和權限，確保系統的安全性和靈活性，滿足各種復雜的權限管理需求。