设计一个完美的用户角色权限表-阿里云开发者社区

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

2024-07-01 253

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

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

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;