应用系统—用户权限跟踪模块设计模型
需求场景:有一个系统,用户能够跟踪到别的系统的权限,就是说,领导想要看员工当前干的事情处理情况。
系统的用户、组织机构、用户组的对应关系:
1、用户与组织机构关联关系为,一对多,涉及表:nismp_e_user、NISMP_E_ORGANIZATION。
2、用户与组关联关系为,多对多,涉及表:nismp_e_user、nismp_e_group、NISMP_E_GROUP_USER。
跟踪权限涉及3张表如下:
--跟踪权限范围表 create table NISMP_E_WorkOrder_Range ( UUID VARCHAR2(32) not null, worder_name VARCHAR2(200), worder_type VARCHAR2(32) , worder_desc VARCHAR2(500), createUser VARCHAR2(200), createTime VARCHAR2(50) ); --跟踪人表 create table NISMP_E_WorkOrder_TrackPerson ( UUID VARCHAR2(32) not null, worderUuid VARCHAR2(32), wotpType VARCHAR2(32), wotpObjUuid VARCHAR2(32) ); --跟踪对象 create table NISMP_E_WorkOrder_TrackObj ( UUID VARCHAR2(32) not null, worderUuid VARCHAR2(32), wotoType VARCHAR2(32), wotoObjUuid VARCHAR2(32) );
wotoType类型: 1、代表人,2、代表组织机构,3代表用户组。
当设置权限范围时,用户需要展示出自己的权限,以及跟踪用户、跟踪组织机构对应人、跟踪用户组对应人的权限:
------------------------------------------------------------------------------------------------- 一、用户ID直接查 ------------------------------------------------------------------------------------------------- --1.查询出人(根据跟单人ID,查询出跟单对象的用户ID) select woto11.wotoObjUuid as userId from NISMP_E_WorkOrder_TrackObj woto11 inner join NISMP_E_WorkOrder_Range wor11 on wor11.uuid=woto11.worderUuid and woto11.wotoType='1' inner join NISMP_E_WorkOrder_TrackPerson wotp11 on wor11.uuid=wotp11.worderUuid where wotp11.wotpObjUuid='ding_sz' and wotp11.wotpType='1'; --2.查询出人(根据跟单人ID,查询出跟单对象的组织机构关联的用户ID) select u12.userId from nismp_e_user u12 inner join NISMP_E_ORGANIZATION org12 on u12.ORGUUID=org12.ORGUUID inner join NISMP_E_WorkOrder_TrackObj woto12 on woto12.wotoObjUuid=org12.ORGUUID inner join NISMP_E_WorkOrder_Range wor12 on wor12.uuid=woto12.worderUuid and woto12.wotoType='2' inner join NISMP_E_WorkOrder_TrackPerson wotp12 on wor12.uuid=wotp12.worderUuid where wotp12.wotpObjUuid='ding_sz' and wotp12.wotpType='1'; --3.查询出人(根据跟单人ID,查询出跟单对象的组织机构关联的用户ID) select u13.userId from nismp_e_user u13 inner join NISMP_E_GROUP_USER ug13 on u13.userId=ug13.user_uuid inner join nismp_e_group g13 on g13.uuid = ug13.group_uuid inner join NISMP_E_WorkOrder_TrackObj woto13 on woto13.wotoObjUuid=g13.uuid inner join NISMP_E_WorkOrder_Range wor13 on wor13.uuid=woto13.worderUuid and woto13.wotoType='3' inner join NISMP_E_WorkOrder_TrackPerson wotp13 on wor13.uuid=wotp13.worderUuid where wotp13.wotpObjUuid='ding_sz' and wotp13.wotpType='1'; ------------------------------------------------------------------------------------------------- 二、用户所属的组织机构直接查 ------------------------------------------------------------------------------------------------- --1.查询出人(根据跟组织机构,查询出跟单对象的用户ID) select woto21.wotoObjUuid as userId from NISMP_E_WorkOrder_TrackObj woto21 inner join NISMP_E_WorkOrder_Range wor21 on wor21.uuid=woto21.worderUuid and woto21.wotoType='2' inner join NISMP_E_WorkOrder_TrackPerson wotp21 on wor21.uuid=wotp21.worderUuid inner join NISMP_E_ORGANIZATION org21p on wotp21.wotpObjUuid=org21p.orguuid inner join nismp_e_user u21p on u21p.ORGUUID=org21p.ORGUUID where u21p.userId='ding_sz'; --2.查询出人(根据跟单人ID,查询出跟单对象的组织机构关联的用户ID) select u22.userId from nismp_e_user u22 inner join NISMP_E_ORGANIZATION org22 on u22.ORGUUID=org22.ORGUUID inner join NISMP_E_WorkOrder_TrackObj woto22 on woto22.wotoObjUuid=org22.ORGUUID inner join NISMP_E_WorkOrder_Range wor22 on wor22.uuid=woto22.worderUuid and woto22.wotoType='2' inner join NISMP_E_WorkOrder_TrackPerson wotp22 on wor22.uuid=wotp22.worderUuid inner join NISMP_E_ORGANIZATION org22p on wotp22.wotpObjUuid=org22p.orguuid inner join nismp_e_user u22p on u22p.ORGUUID=org22p.ORGUUID where u22p.userId='ding_sz'; --3.查询出人(根据跟单人ID,查询出跟单对象的组织机构关联的用户ID) select u23.userId from nismp_e_user u23 inner join NISMP_E_GROUP_USER ug23 on u23.userId=ug23.user_uuid inner join nismp_e_group g23 on g23.uuid = ug23.group_uuid inner join NISMP_E_WorkOrder_TrackObj woto23 on woto23.wotoObjUuid=g23.uuid inner join NISMP_E_WorkOrder_Range wor23 on wor23.uuid=woto23.worderUuid and woto23.wotoType='2' inner join NISMP_E_WorkOrder_TrackPerson wotp23 on wor23.uuid=wotp23.worderUuid inner join NISMP_E_ORGANIZATION org23p on wotp23.wotpObjUuid=org23p.orguuid inner join nismp_e_user u23p on u23p.ORGUUID=org23p.ORGUUID where u23p.userId='ding_sz'; ------------------------------------------------------------------------------------------------- 三、用户所属的用户组直接查 ------------------------------------------------------------------------------------------------- --1.查询出人(根据跟单人ID,查询出跟单对象的用户ID) select woto31.wotoObjUuid as userId from NISMP_E_WorkOrder_TrackObj woto31 inner join NISMP_E_WorkOrder_Range wor31 on wor31.uuid=woto31.worderUuid and woto31.wotoType='3' inner join NISMP_E_WorkOrder_TrackPerson wotp31 on wor31.uuid=wotp31.worderUuid inner join nismp_e_group g31p on wotp31.worderUuid=g31p.uuid inner join NISMP_E_GROUP_USER gu31p on gu31p.group_uuid=g31p.uuid inner join nismp_e_user u31p on u31p.userId = gu31p.user_uuid where u31p.userId='ding_sz'; --2.查询出人(根据跟单人ID,查询出跟单对象的组织机构关联的用户ID) select u32.userId from nismp_e_user u32 inner join NISMP_E_ORGANIZATION org32 on u32.ORGUUID=org32.ORGUUID inner join NISMP_E_WorkOrder_TrackObj woto32 on woto32.wotoObjUuid=org32.ORGUUID inner join NISMP_E_WorkOrder_Range wor32 on wor32.uuid=woto32.worderUuid and woto32.wotoType='3' inner join NISMP_E_WorkOrder_TrackPerson wotp32 on wor32.uuid=wotp32.worderUuid inner join nismp_e_group g32p on wotp32.worderUuid=g32p.uuid inner join NISMP_E_GROUP_USER gu32p on gu32p.group_uuid=g32p.uuid inner join nismp_e_user u32p on u32p.userId = gu32p.user_uuid where u32p.userId='ding_sz'; --3.查询出人(根据跟单人ID,查询出跟单对象的组织机构关联的用户ID) select u33.userId from nismp_e_user u33 inner join NISMP_E_GROUP_USER ug33 on u33.userId=ug33.user_uuid inner join nismp_e_group g33 on g33.uuid = ug33.group_uuid inner join NISMP_E_WorkOrder_TrackObj woto33 on woto33.wotoObjUuid=g33.uuid inner join NISMP_E_WorkOrder_Range wor33 on wor33.uuid=woto33.worderUuid and woto33.wotoType='3' inner join NISMP_E_WorkOrder_TrackPerson wotp33 on wor33.uuid=wotp33.worderUuid inner join nismp_e_group g33p on wotp33.worderUuid=g33p.uuid inner join NISMP_E_GROUP_USER gu33p on gu33p.group_uuid=g33p.uuid inner join nismp_e_user u33p on u33p.userId = gu33p.user_uuid where u33p.userId='ding_sz'; -------------------------------------------------------------------------------------------------
SQL写的非常复杂,根据当前用户ID,查询出对应跟踪的用户列表。
评论