You need to enable JavaScript to run this app. 如何编写查询语句获取带权限用户列表及角色权限与分配对象
优惠活动
大模型
产品
解决方案
定价
更多

如何编写查询语句获取带权限用户列表及角色权限与分配对象

带关联权限、同角色用户的Admin角色用户列表查询实现

前置依赖的RBAC模型表结构

基于常规权限系统设计,查询依赖以下核心表:

  • users:用户基础信息表,核心字段user_id(用户唯一主键)、username(登录名)
  • roles:角色定义表,核心字段role_id(角色唯一主键)、role_code(角色编码,admin角色对应编码值为'ADMIN')
  • user_roles:用户-角色关联映射表,核心字段user_id、role_id
  • role_permissions:角色-权限关联映射表,核心字段role_id、permission_id
  • permissions:权限定义表,核心字段permission_id(权限唯一主键)、permission_code(权限标识)、permission_name(权限描述)

满足需求的核心查询语句

由于需要同步带出admin角色的全量权限、admin角色下所有已分配用户,直接做连表查询会产生大量笛卡尔积重复数据,因此通过聚合函数将关联数据整合为结构化列表,避免冗余:

SELECT
  -- 当前行关联的admin角色所属用户基础信息
  u.user_id,
  u.username,
  -- 聚合admin角色绑定的全量权限
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'permission_id', p.permission_id,
      'permission_code', p.permission_code,
      'permission_name', p.permission_name
    )
  ) AS admin_role_full_permissions,
  -- 聚合admin角色下所有已分配的用户对象
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'user_id', same_role_user.user_id,
      'username', same_role_user.username
    )
  ) AS admin_role_all_assigned_users
FROM user_roles current_user_role
-- 关联用户基础信息
INNER JOIN users u 
  ON current_user_role.user_id = u.user_id
-- 关联角色信息,过滤出admin角色
INNER JOIN roles r 
  ON current_user_role.role_id = r.role_id
  AND r.role_code = 'ADMIN'
-- 关联admin角色对应的全量权限
LEFT JOIN role_permissions rp 
  ON r.role_id = rp.role_id
LEFT JOIN permissions p 
  ON rp.permission_id = p.permission_id
-- 关联admin角色下的所有已分配用户
LEFT JOIN user_roles same_role_relation 
  ON r.role_id = same_role_relation.role_id
INNER JOIN users same_role_user 
  ON same_role_relation.user_id = same_role_user.user_id
-- 按用户维度分组,消除连表产生的重复数据
GROUP BY u.user_id, u.username;

落地注意事项

  • 若使用PostgreSQL数据库,可将MySQL专属的JSON聚合函数替换为json_agg()、json_build_object()实现完全一致的查询效果
  • 若业务需要二维表格式的返回结果,可移除聚合逻辑直接连表查询,但需对重复的权限、用户数据做去重处理
  • 关联查询时建议给user_roles、role_permissions表的关联字段建联合索引,避免全表扫描拖慢查询速度

内容的提问来源于stack exchange,提问作者Jagdeep

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.16 15:24:02