如何编写查询语句获取带权限用户列表及角色权限与分配对象
带关联权限、同角色用户的Admin角色用户列表查询实现
前置依赖的RBAC模型表结构
基于常规权限系统设计,查询依赖以下核心表:
users:用户基础信息表,核心字段user_id(用户唯一主键)、username(登录名)roles:角色定义表,核心字段role_id(角色唯一主键)、role_code(角色编码,admin角色对应编码值为'ADMIN')user_roles:用户-角色关联映射表,核心字段user_id、role_idrole_permissions:角色-权限关联映射表,核心字段role_id、permission_idpermissions:权限定义表,核心字段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
相关产品推荐
相关产品推荐

