【EAS 权限】通过脚本查询用户已分配的角色原创
1人赞赏了该文章
313次浏览
编辑于2022年12月13日 21:06:06
可在查询分析器查询,但一次性最多只能查6500条数据,若数据量较大,建议适用第三方工具连接数据库查询
主要涉及的表:
用户角色权限表 T_PM_USERROLEORG
用户表 T_PM_User
角色表 T_PM_Role
----查询每个用户分配了多少个角色【不包含已删除的用户、不显示组织】
SELECT U.FNUMBER AS 用户编码, U.FNAME_L2 AS 用户名称, COUNT(UR.FROLEID) AS 用户已分配的角色数量 FROM T_PM_USERROLEORG UR INNER JOIN T_PM_User U ON UR.FUSERID = U.FID AND FISDELETE =0 GROUP BY U.FNUMBER,U.FNAME_L2 ORDER BY U.FNUMBER
-----查询每个用户在对应组织下已分配的角色【不包含已删除的用户】
SELECT "USERID".FNumber AS "USERID.NUMBER", "USERID".FNAME_L2 AS "USERID.NAME", "ORGID".FNumber AS "ORGID.NUMBER", "ORGID".FNAME_L2 AS "ORGID.NAME", "ROLEID".FName_l2 AS "ROLEID.NAME" FROM T_PM_UserRoleOrg AS "USERROLEPERM" INNER JOIN T_PM_Role AS "ROLEID" ON "USERROLEPERM".FRoleID = "ROLEID".FID INNER JOIN T_PM_User AS "USERID" ON "USERROLEPERM".FUserID = "USERID".FID INNER JOIN T_ORG_BaseUnit AS "ORGID" ON "USERROLEPERM".FOrgID = "ORGID".FID INNER JOIN T_ORG_BaseUnit AS "DEFORGUNIT" ON "USERID".FDefOrgUnitID = "DEFORGUNIT".FID WHERE "USERID".FIsDelete = 0 ORDER BY "USERID.NUMBER" ASC, "ORGID.NUMBER" ASC, "ROLEID.NAME" ASC
推荐阅读