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

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

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

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

相关文章
|
数据安全/隐私保护
经典权限系统设计(五张表)
经典权限系统设计(五张表)
|
设计模式 算法 安全
【设计模式】RBAC 模型详解
随着软件系统的复杂性和规模的不断增长,权限管理成为了一个至关重要的问题。在大型多人协作的系统中,如何有效地管理不同用户的访问权限,确保系统的安全性和稳定性,是每一个开发者都需要面对的挑战。为了解决这一问题,业界提出了一种被广泛应用的权限管理模型——基于角色的访问控制(Role-Based Access Control,简称RBAC)。希望通过本篇博客的学习,您能够深入了解RBAC模型的核心思想和实现原理,掌握如何在实际项目中应用RBAC模型来提高系统的安全性和可维护性。
3507 1
|
负载均衡 Cloud Native Java
【云原生】Spring Cloud Alibaba 之 Gateway 服务网关实战开发
【云原生】Spring Cloud Alibaba 之 Gateway 服务网关实战开发
4485 0
|
4月前
|
SQL 缓存 运维
RBAC 权限模型实战:从 ACL 踩坑到 RBAC3 落地(2026)
写过 ACL 才懂 RBAC 的好。本文把 RBAC 权限模型、RBAC0~3、角色继承、互斥约束、用户组、四类权限粒度一次讲透,并附可落地的表设计
1535 0
|
安全 Java 数据库
Spring Security 实战指南:从入门到精通
本文详细介绍了Spring Security在Java Web项目中的应用,涵盖登录、权限控制与安全防护等功能。通过Filter Chain过滤器链实现请求拦截与认证授权,核心组件包括AuthenticationProvider和UserDetailsService,负责用户信息加载与密码验证。文章还解析了项目结构,如SecurityConfig配置类、User实体类及自定义登录逻辑,并探讨了Method-Level Security、CSRF防护、Remember-Me等进阶功能。最后总结了Spring Security的核心机制与常见配置,帮助开发者构建健壮的安全系统。
2814 0
|
传感器 物联网 生物认证
【免费开源】基于STM32的智慧门禁系统设计与实现(附源码)
基于STM32的智慧门禁系统,整合了RFID、密码、指纹等多种身份验证方式,实现门锁的智能化控制。通过模块化设计,系统易扩展,可接入更多智能设备,如远程监控、访客记录上传云端等。该项目不仅适用于小型办公场所、社区门禁,也可作为智能家居控制系统的一部分,具有良好的推广价值和实用性。
【免费开源】基于STM32的智慧门禁系统设计与实现(附源码)
|
SQL JSON 前端开发
若依RuoYi脚手架二次开发教程(二次开发必学技能)
本次我们将通过一个菜品管理模块开发的案例,来演示拿到若依框架后,如何在若依管理系统上进行二次开发,升级改造为自己的管理系统。适合以若依作为项目脚手架的公司开发人员、毕业设计的学生及开源项目学习者。
11156 1
若依RuoYi脚手架二次开发教程(二次开发必学技能)
|
数据采集 监控 Java
SpringBoot日志全方位超详细手把手教程,零基础可学习 日志如何配置及SLF4J的使用......
本文是关于SpringBoot日志的详细教程,涵盖日志的定义、用途、SLF4J框架的使用、日志级别、持久化、文件分割及格式配置等内容。
1954 3
SpringBoot日志全方位超详细手把手教程,零基础可学习 日志如何配置及SLF4J的使用......
|
存储 JavaScript 前端开发
Vue3权限控制全攻略:路由与组件层面的用户角色与权限管理方法深度解析
Vue3权限控制全攻略:路由与组件层面的用户角色与权限管理方法深度解析
2133 2
|
数据库 数据安全/隐私保护
RBAC用户权限管理数据库设计
原文来自:http://minjiechenjava.iteye.com/blog/1759482 最近正在为下一项目版本设计权限管理的。看到了这篇文章,可以参考参考! RBAC(Role-Based Access Control,基于角色的访问控制),就是用户通过角色与权限进行关联。
8885 1