MySQL RBAC 权限表设计方案

左Python右Java
151 阅读5分钟

MySQL RBAC 权限表设计方案

RBAC（Role-Based Access Control，基于角色的访问控制）是一种经典的权限管理模型，通过角色作为用户与权限之间的桥梁，实现灵活且易于维护的权限控制。以下是一套完整的RBAC权限表设计方案。

一、核心表结构设计

1. 用户表（users）

CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
  username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
  password VARCHAR(255) NOT NULL COMMENT '密码（建议加密存储）',
  nickname VARCHAR(50) DEFAULT NULL COMMENT '昵称',
  email VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
  mobile VARCHAR(20) DEFAULT NULL COMMENT '手机号',
  status TINYINT DEFAULT 1 COMMENT '状态（1=正常，0=禁用）',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  INDEX idx_username (username),
  INDEX idx_mobile (mobile),
  INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

2. 角色表（roles）

CREATE TABLE roles (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '角色ID',
  role_name VARCHAR(50) NOT NULL UNIQUE COMMENT '角色名称',
  description VARCHAR(255) DEFAULT NULL COMMENT '角色描述',
  sort_order INT DEFAULT 0 COMMENT '排序号',
  status TINYINT DEFAULT 1 COMMENT '状态（1=启用，0=禁用）',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  INDEX idx_role_name (role_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色表';

3. 权限表（permissions）

CREATE TABLE permissions (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '权限ID',
  permission_name VARCHAR(100) NOT NULL COMMENT '权限名称',
  permission_code VARCHAR(100) NOT NULL UNIQUE COMMENT '权限编码',
  type TINYINT NOT NULL COMMENT '权限类型（1=菜单权限，2=操作权限，3=数据权限）',
  parent_id BIGINT DEFAULT 0 COMMENT '父权限ID（用于构建权限树）',
  path VARCHAR(255) DEFAULT NULL COMMENT '菜单路径（仅菜单权限有效）',
  icon VARCHAR(50) DEFAULT NULL COMMENT '图标（仅菜单权限有效）',
  component VARCHAR(255) DEFAULT NULL COMMENT '组件路径（仅菜单权限有效）',
  sort_order INT DEFAULT 0 COMMENT '排序号',
  status TINYINT DEFAULT 1 COMMENT '状态（1=启用，0=禁用）',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  INDEX idx_permission_code (permission_code),
  INDEX idx_parent_id (parent_id),
  INDEX idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权限表';

4. 用户-角色关系表（user_roles）

CREATE TABLE user_roles (
  user_id BIGINT NOT NULL COMMENT '用户ID',
  role_id BIGINT NOT NULL COMMENT '角色ID',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  INDEX idx_user_id (user_id),
  INDEX idx_role_id (role_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户-角色关系表';

5. 角色-权限关系表（role_permissions）

CREATE TABLE role_permissions (
  role_id BIGINT NOT NULL COMMENT '角色ID',
  permission_id BIGINT NOT NULL COMMENT '权限ID',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (role_id, permission_id),
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE,
  INDEX idx_role_id (role_id),
  INDEX idx_permission_id (permission_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色-权限关系表';

二、扩展表设计（可选）

1. 部门表（departments）- 用于实现数据权限

CREATE TABLE departments (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID',
  department_name VARCHAR(100) NOT NULL COMMENT '部门名称',
  parent_id BIGINT DEFAULT 0 COMMENT '父部门ID',
  description VARCHAR(255) DEFAULT NULL COMMENT '部门描述',
  leader_id BIGINT DEFAULT NULL COMMENT '部门负责人ID',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  INDEX idx_department_name (department_name),
  INDEX idx_parent_id (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';

2. 数据权限规则表（data_permissions）- 高级数据权限控制

CREATE TABLE data_permissions (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '数据权限规则ID',
  name VARCHAR(100) NOT NULL COMMENT '规则名称',
  type TINYINT NOT NULL COMMENT '规则类型（1=本部门，2=本部门及子部门，3=自定义部门，4=本人数据）',
  rule_content JSON DEFAULT NULL COMMENT '规则内容（JSON格式存储自定义条件）',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  INDEX idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数据权限规则表';

三、表关系图

用户表(users) --- user_roles --- 角色表(roles) --- role_permissions --- 权限表(permissions)
      |
      |
部门表(departments) --- 数据权限规则表(data_permissions)

四、RBAC核心SQL查询示例

1. 查询用户拥有的所有角色

SELECT r.id, r.role_name, r.description
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
WHERE u.username = 'admin' AND r.status = 1;

2. 查询角色拥有的所有权限

SELECT p.id, p.permission_name, p.permission_code, p.type
FROM roles r
JOIN role_permissions rp ON r.id = rp.role_id
JOIN permissions p ON rp.permission_id = p.id
WHERE r.role_name = '管理员' AND p.status = 1;

3. 查询用户拥有的所有权限（去重）

SELECT DISTINCT p.id, p.permission_name, p.permission_code, p.type
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
JOIN role_permissions rp ON r.id = rp.role_id
JOIN permissions p ON rp.permission_id = p.id
WHERE u.username = 'admin' AND r.status = 1 AND p.status = 1;

4. 检查用户是否拥有特定权限

SELECT COUNT(1) > 0 AS has_permission
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
JOIN role_permissions rp ON r.id = rp.role_id
JOIN permissions p ON rp.permission_id = p.id
WHERE u.username = 'admin'
  AND p.permission_code = 'user:create'
  AND r.status = 1
  AND p.status = 1;

五、权限管理最佳实践

1. 权限编码规范

建议采用 资源:操作 的格式，例如：

  • user:view - 查看用户
  • user:create - 创建用户
  • user:update - 更新用户
  • user:delete - 删除用户
  • role:list - 列出角色

2. 菜单权限层级设计

通过 parent_id 字段构建权限树，例如：

  • 系统管理 (id: 1, parent_id: 0)
    • 用户管理 (id: 2, parent_id: 1)
      • 查看用户 (id: 3, parent_id: 2)
      • 创建用户 (id: 4, parent_id: 2)
    • 角色管理 (id: 5, parent_id: 1)

3. 安全性建议

  • 密码必须加密存储（如使用bcrypt、MD5+盐等）
  • 权限验证应在服务端完成，不要只依赖前端控制
  • 敏感操作需要记录操作日志
  • 定期审查用户权限，及时清理不必要的权限

4. 性能优化

  • 为常用查询字段建立索引
  • 考虑使用缓存存储用户权限信息
  • 避免频繁的权限查询，可在用户登录时加载权限信息到会话

这套RBAC权限表设计覆盖了基本的权限管理需求，同时也考虑了数据权限扩展，可以根据实际项目需求进行适当调整和扩展。

avatar
文章
阅读
粉丝