用户权限查询函数

用户表
角色表
用户表和角色表的关系表
权限表(功能表，动作表)
角色-权限表(功能表，动作表)
特殊用户表
特殊用户表-权限表(功能表，动作表)

我现在的解决方法是用户信息、角色、权限三表，这三个都只存储自己本身的信息。然后还有三个表，分别是两两之间关联，这样的话扩展性也很强。

一对一或者一对多或者多对一关联都可以转换成外键连接,多对多关联可以多设一个关联表.  
   
  我觉得  
  设一个用户基本信息表  
  再设一个用户权限表,外键关联到用户基本信息表,就解决了用户权限的量还没决定下来的问题.  
   
  用户基本信息表里存储每一个用户的信息,所以用户可以随时增加,为每一个用户分配一个唯一ID.  
  用户权限表关联到用户基本表的ID上,每增加一个权限,就在权限表里增加一项,并用关联ID的那个字段指明是哪个用户的权限,这样就可以随时增加权限的数量了.  
   
  SELECT   *   FROM   user_table,user_pri_table   WHERE   user_pri_table.user_id=user_table.id  
  查用户所有权限.

 

CREATE OR REPLACE FUNCTION FN_RolePurviewTree(
  corpGUID  HY_TradeBoundSet.scorpguid%TYPE,
  roleGUID  HY_RolePurview.sroleguid%TYPE,
  iFlag INT
)
RETURN SYS_REFCURSOR
AS
  P_RESULT_SET SYS_REFCURSOR; --定义游标对象
BEGIN

IF iFlag=1 THEN
  OPEN P_RESULT_SET FOR select sGUID as id,sLogicID as slogicid,sNodeName as snodename,
    sParentNodeID as sparentnodeid,sIsCheck as sischeck from (
    SELECT DISTINCT B.sGUID, B.sMenuID AS sLogicID, B.sMenuName AS sNodeName,
    B.sParentGUID AS sParentNodeID,(CASE WHEN(NVL(A.sMenuID,null) IS null) THEN 'N' ELSE 'Y' END) AS sIsCheck
    FROM (
    SELECT DISTINCT AB.sMenuID FROM HY_RolePurview AA
      LEFT OUTER JOIN HY_Purview BB ON AA.sPurviewGUID=BB.sGUID
     LEFT OUTER JOIN HY_Menu AB ON BB.sMenuGUID=AB.sGUID
    WHERE AA.sRoleGUID=roleGUID
    ) A RIGHT OUTER JOIN HY_Menu B ON A.sMenuID LIKE (B.sMenuID || '%')
    LEFT JOIN (
      select distinct X.* from hy_menu X
        left join (
        select distinct Y.smenuid,Z.bisuse from hy_tradeboundset Z
        left join hy_menu Y on Z.strademodeguid=Y.sguid AND Y.smenuid is not null
        where Z.scorpguid=corpGUID
        ) W ON X.smenuid like (W.smenuid || '%')
        where W.bisuse=1 or W.bisuse is null
    ) C ON B.sGUID=C.sGUID
    WHERE C.sGUID IS NOT NULL

    UNION

    SELECT A.sGUID, A.sPurID AS sLogicID, A.sPurName AS sNodeName,
    A.sMenuGUID AS sParentNodeID, (CASE WHEN(NVL(B.sPurviewGUID,null) IS null) THEN 'N' ELSE 'Y' END) AS sIsCheck
    FROM HY_Purview A LEFT OUTER JOIN (
    SELECT DISTINCT sPurviewGUID FROM HY_RolePurview
    WHERE sRoleGUID=roleGUID
    ) B ON A.sGUID=B.sPurviewGUID
    LEFT JOIN (
      select distinct X.* from hy_menu X
        left join (
        select distinct Y.smenuid,Z.bisuse from hy_tradeboundset Z
        left join hy_menu Y on Z.strademodeguid=Y.sguid AND Y.smenuid is not null
        where Z.scorpguid=corpGUID
        ) W ON X.smenuid like (W.smenuid || '%')
        where W.bisuse=1 or W.bisuse is null
    ) C ON A.sMenuGUID=C.sGUID
    WHERE C.sGUID IS NOT NULL
  ) TAB WHERE TAB.sLogicID='00';
ELSIF iFlag=2 THEN
  OPEN P_RESULT_SET FOR select sGUID as id,sLogicID as slogicid,sNodeName as snodename,
    sParentNodeID as sparentnodeid,sIsCheck as sischeck from (
    SELECT DISTINCT B.sGUID, B.sMenuID AS sLogicID, B.sMenuName AS sNodeName,
    B.sParentGUID AS sParentNodeID,(CASE WHEN(NVL(A.sMenuID,null) IS null) THEN 'N' ELSE 'Y' END) AS sIsCheck
    FROM (
    SELECT DISTINCT AB.sMenuID FROM HY_RolePurview AA
      LEFT OUTER JOIN HY_Purview BB ON AA.sPurviewGUID=BB.sGUID
     LEFT OUTER JOIN HY_Menu AB ON BB.sMenuGUID=AB.sGUID
    WHERE AA.sRoleGUID=roleGUID
    ) A RIGHT OUTER JOIN HY_Menu B ON A.sMenuID LIKE (B.sMenuID || '%')
    LEFT JOIN (
      select distinct X.* from hy_menu X
        left join (
        select distinct Y.smenuid,Z.bisuse from hy_tradeboundset Z
        left join hy_menu Y on Z.strademodeguid=Y.sguid AND Y.smenuid is not null
        where Z.scorpguid=corpGUID
        ) W ON X.smenuid like (W.smenuid || '%')
        where W.bisuse=1 or W.bisuse is null
    ) C ON B.sGUID=C.sGUID
    WHERE C.sGUID IS NOT NULL

    UNION

    SELECT A.sGUID, A.sPurID AS sLogicID, A.sPurName AS sNodeName,
    A.sMenuGUID AS sParentNodeID, (CASE WHEN(NVL(B.sPurviewGUID,null) IS null) THEN 'N' ELSE 'Y' END) AS sIsCheck
    FROM HY_Purview A LEFT OUTER JOIN (
    SELECT DISTINCT sPurviewGUID FROM HY_RolePurview
    WHERE sRoleGUID=roleGUID
    ) B ON A.sGUID=B.sPurviewGUID
    LEFT JOIN (
      select distinct X.* from hy_menu X
        left join (
        select distinct Y.smenuid,Z.bisuse from hy_tradeboundset Z
        left join hy_menu Y on Z.strademodeguid=Y.sguid AND Y.smenuid is not null
        where Z.scorpguid=corpGUID
        ) W ON X.smenuid like (W.smenuid || '%')
        where W.bisuse=1 or W.bisuse is null
    ) C ON A.sMenuGUID=C.sGUID
    WHERE C.sGUID IS NOT NULL
  ) TAB WHERE TAB.sLogicID<>'00';
END IF;
RETURN P_RESULT_SET;   --返回游标

END Fn_RolePurviewTree;