-- 检查对象权限
CREATE OR REPLACE procedure CheckObjectsRight(users        in varchar2,
                                              roles        in varchar2,
                                              userGroups   in varchar2,
                                              paramValues  in varchar2,
                                              businesstype in varchar2,
                                              opname       in varchar2,
                                              objectoids   in varchar2,
                                              objectroid   in varchar2,
                                              objectmoid   in varchar2,
                                              checkResult  out varchar2) is

begin
  declare
    type cur is ref cursor;
    --判断参数
    tmp         number;
    all_not     number;
    reject      number;
    all_has     number;
    has         number;
    exeSQL      varchar2(1500);
    sence_type  cur;
    subject_SQL varchar2(4500);
    --全部无权
    all_not_num number;
    --全部有权
    all_has_num     number;
    temp_expression varchar2(4500);
    --拒绝
    rej_expression     varchar2(4500);
    rej_expression_cur cur;
    --允许
    has_expression     varchar2(4500);
    has_expression_cur cur;

    --索引
    firstIndex number;
    lastIndex  number;
    objectoid  varchar2(100);

    results     varchar2(4500);
    isContained number; --是否包含everyone
    linkoid     varchar2(150);
  begin
    --遍历每个对象
    firstIndex  := 1;
    lastIndex   := 1;
    subject_SQL := getsunject(users, roles, userGroups); --主体SQL脚本
    if length(objectoids) <> 0 then
      loop
        tmp       := -1;
        all_not   := -1;
        reject    := -1;
        all_has   := -1;
        has       := -1;
        lastIndex := instr(objectoids || ',', ',', firstIndex);
        exit when lastIndex = 0;
        objectoid := substr(objectoids, firstIndex, lastIndex - firstIndex);
        --------------------------------------------------------------------
        --everyone
        isContained := getEveryOne(businesstype, opname);
        if (isContained = 1) then
          subject_SQL := '1=1';
        end if;
        --场景分析

        exeSQL := 'SELECT distinct ruletype FROM PL_TYPERIGHT WHERE identifier like ' || '''' ||
                  businesstype || '_' || opname || '%' || '''' ||
                  ' AND isgrant = 1 AND (' || subject_SQL || ') ';
        open sence_type for(exeSQL);
        loop
          fetch sence_type
            into tmp;
          exit when sence_type%NOTFOUND;
          if tmp = 2 then
            all_not := tmp;
          end if;
          if tmp = 3 then
            reject := tmp;
          end if;
          if tmp = 1 then
            has := tmp;
          end if;
          if tmp = 0 then
            all_has := tmp;
          end if;
        end loop;
        close sence_type;
        --零时变量初始化
        tmp := 0;
        -----------------------------------------------------------------------------
        ----------0未授权
        if all_not = -1 and reject = -1 and all_has = -1 and has = -1 then
          checkResult := '';
          checkResult := objectoid || ',0';
          goto NEXT; --return; --鉴权结束
        end if;
        ----------1存在全部无权
        if all_not <> -1 then
          checkResult := '';
          --全部无权
          exeSQL := 'SELECT count(*) FROM PL_TYPERIGHT WHERE identifier like ' || '''' ||
                    businesstype || '_' || opname || '%' || '''' ||
                    ' AND ruletype = 2 AND isgrant = 1 AND  (' ||
                    subject_SQL || ') ';
          execute immediate (exeSQL)
            into all_not_num;
          if all_not_num <> 0 then
            checkResult := objectoid || ',0';
            goto NEXT; -- return; --鉴权结束
          end if;
        end if;
        -----------2只有全部有权，允许规则
        if all_has <> -1 and reject = -1 and has <> -1 and all_not = -1 then
          checkResult := '';
          checkResult := objectoid || ',1';
          goto NEXT; --return; --鉴权结束

        end if;
        -----------3只有全部有权
        if all_has <> -1 and reject = -1 and has = -1 and all_not = -1 then
          checkResult := '';
          checkResult := objectoid || ',1';
          goto NEXT; --return; --鉴权结束
        end if;
        -----------4只有全部有权、拒绝规则、允许规则
        if all_has <> -1 and reject <> -1 and has <> -1 and all_not = -1 then
          checkResult := '';
          --拒绝规则
          checkResult := getRejectSQLScript('expressiontosql',
                                            businesstype,
                                            opname,
                                            subject_SQL);
          --允许规则
          checkResult := checkResult ||
                         getHasSQLScript('expressiontosql',
                                         businesstype,
                                         opname,
                                         subject_SQL);
          --全部有权
          exeSQL := 'SELECT count(*) FROM PL_TYPERIGHT WHERE identifier like ' || '''' ||
                    businesstype || '_' || opname || '%' || '''' ||
                    ' AND ruletype = 0 AND isgrant = 1 AND (' ||
                    subject_SQL || ') ';
          execute immediate (exeSQL)
            into all_has_num;
          if all_has_num <> 0 then
            checkResult := checkResult || ' and 1=1';
          end if;
          if LENGTH(checkResult) <> 0 then
            tmp := getObject(businesstype,
                             getOwer(checkResult, paramValues),
                             getFirstString(objectoid, ';'),
                             objectroid,
                             objectmoid);
            if tmp = 1 then
              --业务类型作为客体拥有对相关数据的操作权限后，继续鉴权链接数据的权限
              linkoid := replace(objectoid,
                                 getFirstString(objectoid, ';'),
                                 '');
              if length(linkoid) <> 0 then
                --拒绝规则
                checkResult := getRejectSQLScript('lexpressiontosql',
                                                  businesstype,
                                                  opname,
                                                  subject_SQL);
                --允许规则
                checkResult := checkResult ||
                               getHasSQLScript('lexpressiontosql',
                                               businesstype,
                                               opname,
                                               subject_SQL);
                tmp         := getLinkObject(getFirstString(opname, '.'),
                                             substr(linkoid,
                                                    2,
                                                    LENGTH(linkoid)),
                                             getOwer(checkResult, paramValues));

                if tmp = 1 then
                  checkResult := objectoid || ',1';
                else
                  checkResult := objectoid || ',0';
                end if;
              else
                checkResult := objectoid || ',1';
              end if;
            else
              checkResult := objectoid || ',0';
            end if;
          end if;
          goto NEXT; ---return; --鉴权结束
        end if;
        -------------5只有拒绝规则、全部有权

        if all_has <> -1 and reject <> -1 and has = -1 and all_not = -1 then
          checkResult := '';
          --拒绝规则
          checkResult := getRejectSQLScript('expressiontosql',
                                            businesstype,
                                            opname,
                                            subject_SQL);
          --全部有权
          exeSQL := 'SELECT count(*) FROM PL_TYPERIGHT WHERE identifier like ' || '''' ||
                    businesstype || '_' || opname || '%' || '''' ||
                    ' AND ruletype = 0 AND isgrant = 1 AND (' ||
                    subject_SQL || ') ';
          execute immediate (exeSQL)
            into all_has_num;
          if all_has_num <> 0 then
            checkResult := checkResult || ' and 1=1';
          end if;
          if LENGTH(checkResult) <> 0 then
            tmp := getObject(businesstype,
                             getOwer(checkResult, paramValues),
                             getFirstString(objectoid, ';'),
                             objectroid,
                             objectmoid);
            if tmp = 1 then
              --业务类型作为客体拥有对相关数据的操作权限后，继续鉴权链接数据的权限
              linkoid := replace(objectoid,
                                 getFirstString(objectoid, ';'),
                                 '');
              if length(linkoid) <> 0 then
                --拒绝规则
                checkResult := getRejectSQLScript('lexpressiontosql',
                                                  businesstype,
                                                  opname,
                                                  subject_SQL);

                tmp := getLinkObject(getFirstString(opname, '.'),
                                     substr(linkoid, 2, LENGTH(linkoid)),
                                     getOwer(checkResult, paramValues));
                if tmp = 1 then
                  checkResult := objectoid || ',1';
                else
                  checkResult := objectoid || ',0';
                end if;
              else
                checkResult := objectoid || ',1';
              end if;
            else
              checkResult := objectoid || ',0';
            end if;
          end if;
          goto NEXT; ---return; --鉴权结束
        end if;
        ------------6只有拒绝规则
        if all_not = -1 and reject <> -1 and all_has = -1 and has = -1 then
          checkResult := '';
          --拒绝规则

          checkResult := objectoid || ',0';
          goto NEXT; --return; --鉴权结束
        end if;
        ---------------7只有允许规则
        if all_not = -1 and reject = -1 and all_has = -1 and has <> -1 then
          checkResult := '';
          checkResult := getHasSQLScript('expressiontosql',
                                         businesstype,
                                         opname,
                                         subject_SQL);

          if LENGTH(checkResult) <> 0 then
            tmp := getObject(businesstype,
                             getOwer(checkResult, paramValues),
                             getFirstString(objectoid, ';'),
                             objectroid,
                             objectmoid);

            if tmp = 1 then
              --业务类型作为客体拥有对相关数据的操作权限后，继续鉴权链接数据的权限
              linkoid := replace(objectoid,
                                 getFirstString(objectoid, ';'),
                                 '');
              if LENGTH(linkoid) <> 0 then
                --允许规则
                checkResult := getHasSQLScript('lexpressiontosql',
                                               businesstype,
                                               opname,
                                               subject_SQL);
                tmp         := getLinkObject(getFirstString(opname, '.'),
                                             substr(linkoid,
                                                    2,
                                                    LENGTH(linkoid)),
                                             getOwer(checkResult, paramValues));

                if tmp = 1 then
                  checkResult := objectoid || ',1';
                else
                  checkResult := objectoid || ',0';
                end if;
              else
                checkResult := objectoid || ',1';
              end if;
            else
              checkResult := objectoid || ',0';
            end if;
            goto NEXT; -- return; --鉴权结束
          else
            checkResult := objectoid || ',0';
          end if;
        end if;
        ----------------8场景只有允许规则、拒绝规则
        if all_not = -1 and reject <> -1 and all_has = -1 and has <> -1 then
          checkResult := '';
          --拒绝规则
          checkResult := getRejectSQLScript('expressiontosql',
                                            businesstype,
                                            opname,
                                            subject_SQL);
          --允许规则
          checkResult := checkResult ||
                         getHasSQLScript('expressiontosql',
                                         businesstype,
                                         opname,
                                         subject_SQL);
          if LENGTH(checkResult) <> 0 then
            tmp := getObject(businesstype,
                             getOwer(checkResult, paramValues),
                             getFirstString(objectoid, ';'),
                             objectroid,
                             objectmoid);
            if tmp = 1 then
              --业务类型作为客体拥有对相关数据的操作权限后，继续鉴权链接数据的权限
              linkoid := replace(objectoid,
                                 getFirstString(objectoid, ';'),
                                 '');
              if LENGTH(linkoid) <> 0 then
                --拒绝规则
                checkResult := getRejectSQLScript('lexpressiontosql',
                                                  businesstype,
                                                  opname,
                                                  subject_SQL);
                --允许规则
                checkResult := checkResult ||
                               getHasSQLScript('lexpressiontosql',
                                               businesstype,
                                               opname,
                                               subject_SQL);
                tmp         := getLinkObject(getFirstString(opname, '.'),
                                             substr(linkoid,
                                                    2,
                                                    LENGTH(linkoid)),
                                             getOwer(checkResult, paramValues));
                if tmp = 1 then
                  checkResult := objectoid || ',1';
                else
                  checkResult := objectoid || ',0';
                end if;
              else
                checkResult := objectoid || ',1';
              end if;
              -- checkResult := opname || ',1';
            else
              checkResult := objectoid || ',0';
            end if;
            goto NEXT; --return; --鉴权结束
          end if;
        end if;
        --遍历每个对象
        <<NEXT>>
        results    := results || ':' || checkResult;
        firstIndex := lastIndex + 1;
      end loop;
    end if; --遍历结束
    checkResult := results;
  end;
end CheckObjectsRight;

-- 检查一般性权限
CREATE OR REPLACE procedure CheckOrdinaryRight(users        in varchar2,
                                               roles        in varchar2,
                                               userGroups   in varchar2,
                                               paramValues  in varchar2,
                                               businesstype in varchar2,
                                               opnames      in varchar2,
                                               objectoid    in varchar2,
                                               objectroid   in varchar2,
                                               objectmoid   in varchar2,
                                               checkResult  out varchar2) is
begin
  declare
    type cur is ref cursor;
    --判断参数
    tmp         number;
    all_not     number;
    reject      number;
    all_has     number;
    has         number;
    exeSQL      varchar2(1500);
    sence_type  cur;
    subject_SQL varchar2(4500);
    --全部无权
    all_not_num number;
    --全部有权
    all_has_num     number;
    temp_expression varchar2(4500);
    --拒绝
    rej_expression     varchar2(4500);
    rej_expression_cur cur;
    --允许
    has_expression     varchar2(4500);
    has_expression_cur cur;
    --索引
    firstIndex  number;
    lastIndex   number;
    opname      varchar2(150);
    results     varchar2(4500);
    isContained number; --是否包含everyone

    linkoid varchar2(150);
  begin
    --初始化参数
    linkoid := '';
    tmp     := -1;
    all_not := -1;
    reject  := -1;
    all_has := -1;
    has     := -1;

    subject_SQL := getsunject(users, roles, userGroups); --主体SQL脚本
    --遍历每个操作
    firstIndex := 1;
    lastIndex  := 1;

    if length(opnames) <> 0 then
      loop
        tmp       := -1;
        all_not   := -1;
        reject    := -1;
        all_has   := -1;
        has       := -1;
        lastIndex := instr(opnames || ',', ',', firstIndex);
        exit when lastIndex = 0;
        opname := substr(opnames, firstIndex, lastIndex - firstIndex);
        --everyone
        isContained := getEveryOne(businesstype, opname);
        if (isContained = 1) then
          subject_SQL := '1=1';
        end if;
        --场景分析

        exeSQL := 'SELECT distinct ruletype FROM PL_TYPERIGHT WHERE identifier like ' || '''' ||
                  businesstype || '_' || opname || '%' || '''' ||
                  ' AND isgrant = 1 AND (' || subject_SQL || ') ';
        open sence_type for(exeSQL);
        loop
          fetch sence_type
            into tmp;
          exit when sence_type%NOTFOUND;
          if tmp = 2 then
            all_not := tmp;
          end if;
          if tmp = 3 then
            reject := tmp;
          end if;
          if tmp = 1 then
            has := tmp;
          end if;
          if tmp = 0 then
            all_has := tmp;
          end if;
        end loop;
        close sence_type;
        --零时变量初始化
        tmp := 0;
        ----0未授权
        if all_not = -1 and reject = -1 and all_has = -1 and has = -1 then
          checkResult := '';
          checkResult := opname || ',0';
          goto NEXT; --return; --鉴权结束
        end if;
        ----1存在全部无权
        if all_not <> -1 then
          checkResult := '';
          --全部无权
          exeSQL := 'SELECT count(*) FROM PL_TYPERIGHT WHERE identifier like ' || '''' ||
                    businesstype || '_' || opname || '%' || '''' ||
                    ' AND ruletype = 2 AND isgrant = 1 AND  (' ||
                    subject_SQL || ') ';
          execute immediate (exeSQL)
            into all_not_num;
          if all_not_num <> 0 then
            checkResult := opname || ',0';
            goto NEXT; -- return; --鉴权结束
          end if;
        end if;
        ----2只有全部有权，允许规则
        if all_has <> -1 and reject = -1 and has <> -1 and all_not = -1 then
          checkResult := '';
          checkResult := opname || ',1';
          goto NEXT; --return; --鉴权结束

        end if;
        ----3只有全部有权
        if all_has <> -1 and reject = -1 and has = -1 and all_not = -1 then
          checkResult := '';
          checkResult := opname || ',1';
          goto NEXT; --return; --鉴权结束
        end if;
        ----4只有全部有权、拒绝规则、允许规则
        if all_has <> -1 and reject <> -1 and has <> -1 and all_not = -1 then
          checkResult := '';
          --拒绝规则
          checkResult := getRejectSQLScript('expressiontosql',
                                            businesstype,
                                            opname,
                                            subject_SQL);
          --允许规则
          checkResult := checkResult ||
                         getHasSQLScript('expressiontosql',
                                         businesstype,
                                         opname,
                                         subject_SQL);
          --全部有权
          exeSQL := 'SELECT count(*) FROM PL_TYPERIGHT WHERE identifier like ' || '''' ||
                    businesstype || '_' || opname || '%' || '''' ||
                    ' AND ruletype = 0 AND isgrant = 1 AND (' ||
                    subject_SQL || ') ';
          execute immediate (exeSQL)
            into all_has_num;
          if all_has_num <> 0 then
            checkResult := checkResult || ' and 1=1';
          end if;
          if LENGTH(checkResult) <> 0 then
            tmp := getObject(businesstype,
                             getOwer(checkResult, paramValues),
                             getFirstString(objectoid, ';'),
                             objectroid,
                             objectmoid);
            if tmp = 1 then
              --业务类型作为客体拥有对相关数据的操作权限后，继续鉴权链接数据的权限
              linkoid := replace(objectoid,
                                 getFirstString(objectoid, ';'),
                                 '');
              if length(linkoid) <> 0 then
                --拒绝规则
                checkResult := getRejectSQLScript('lexpressiontosql',
                                                  businesstype,
                                                  opname,
                                                  subject_SQL);
                --允许规则
                checkResult := checkResult ||
                               getHasSQLScript('lexpressiontosql',
                                               businesstype,
                                               opname,
                                               subject_SQL);
                tmp         := getLinkObject(getFirstString(opname, '.'),
                                             substr(linkoid,
                                                    2,
                                                    LENGTH(linkoid)),
                                             getOwer(checkResult,
                                                     paramValues));
                if tmp = 1 then
                  checkResult := opname || ',1';
                else
                  checkResult := opname || ',0';
                end if;
              else
                checkResult := opname || ',1';
              end if;
            else
              checkResult := opname || ',0';
            end if;
          end if;
          goto NEXT; ---return; --鉴权结束
        end if;
        ----5只有拒绝规则、全部有权

        if all_has <> -1 and reject <> -1 and has = -1 and all_not = -1 then
          checkResult := '';
          --拒绝规则
          checkResult := getRejectSQLScript('expressiontosql',
                                            businesstype,
                                            opname,
                                            subject_SQL);
          --全部有权
          exeSQL := 'SELECT count(*) FROM PL_TYPERIGHT WHERE identifier like ' || '''' ||
                    businesstype || '_' || opname || '%' || '''' ||
                    ' AND ruletype = 0 AND isgrant = 1 AND (' ||
                    subject_SQL || ') ';
          execute immediate (exeSQL)
            into all_has_num;
          if all_has_num <> 0 then
            checkResult := checkResult || ' and 1=1';
          end if;
          if LENGTH(checkResult) <> 0 then
            tmp := getObject(businesstype,
                             getOwer(checkResult, paramValues),
                             getFirstString(objectoid, ';'),
                             objectroid,
                             objectmoid);
            if tmp = 1 then
              --业务类型作为客体拥有对相关数据的操作权限后，继续鉴权链接数据的权限
              linkoid := replace(objectoid,
                                 getFirstString(objectoid, ';'),
                                 '');
              if length(linkoid) <> 0 then
                --拒绝规则
                checkResult := getRejectSQLScript('lexpressiontosql',
                                                  businesstype,
                                                  opname,
                                                  subject_SQL);

                tmp := getLinkObject(getFirstString(opname, '.'),
                                     substr(linkoid, 2, LENGTH(linkoid)),
                                     getOwer(checkResult, paramValues));
                if tmp = 1 then
                  checkResult := opname || ',1';
                else
                  checkResult := opname || ',0';
                end if;
              else
                checkResult := opname || ',1';
              end if;
            else
              checkResult := opname || ',0';
            end if;
          end if;
          goto NEXT; ---return; --鉴权结束
        end if;
        ----6只有拒绝规则
        if all_not = -1 and reject <> -1 and all_has = -1 and has = -1 then
          checkResult := '';
          --拒绝规则

          checkResult := opname || ',0';
          goto NEXT; --return; --鉴权结束
        end if;
        ------7只有允许规则
        if all_not = -1 and reject = -1 and all_has = -1 and has <> -1 then
          checkResult := '';
          checkResult := getHasSQLScript('expressiontosql',
                                         businesstype,
                                         opname,
                                         subject_SQL);

          if LENGTH(checkResult) <> 0 then
            tmp := getObject(businesstype,
                             getOwer(checkResult, paramValues),
                             getFirstString(objectoid, ';'),
                             objectroid,
                             objectmoid);

            if tmp = 1 then
              --业务类型作为客体拥有对相关数据的操作权限后，继续鉴权链接数据的权限
              linkoid := replace(objectoid,
                                 getFirstString(objectoid, ';'),
                                 '');
              if LENGTH(linkoid) <> 0 then
                --允许规则
                checkResult := getHasSQLScript('lexpressiontosql',
                                               businesstype,
                                               opname,
                                               subject_SQL);
                tmp         := getLinkObject(getFirstString(opname, '.'),
                                             substr(linkoid,
                                                    2,
                                                    LENGTH(linkoid)),
                                             getOwer(checkResult,
                                                     paramValues));
                if tmp = 1 then
                  checkResult := opname || ',1';
                else
                  checkResult := opname || ',0';
                end if;
              else
                checkResult := opname || ',1';
              end if;
            else
              checkResult := opname || ',0';
            end if;
            goto NEXT; -- return; --鉴权结束
          else
            checkResult := opname || ',0';
          end if;
        end if;
        ------8场景只有允许规则、拒绝规则
        if all_not = -1 and reject <> -1 and all_has = -1 and has <> -1 then
          checkResult := '';
          --拒绝规则
          checkResult := getRejectSQLScript('expressiontosql',
                                            businesstype,
                                            opname,
                                            subject_SQL);
          --允许规则
          checkResult := checkResult ||
                         getHasSQLScript('expressiontosql',
                                         businesstype,
                                         opname,
                                         subject_SQL);
          if LENGTH(checkResult) <> 0 then
            tmp := getObject(businesstype,
                             getOwer(checkResult, paramValues),
                             getFirstString(objectoid, ';'),
                             objectroid,
                             objectmoid);
            if tmp = 1 then
              --业务类型作为客体拥有对相关数据的操作权限后，继续鉴权链接数据的权限
              linkoid := replace(objectoid,
                                 getFirstString(objectoid, ';'),
                                 '');
              if LENGTH(linkoid) <> 0 then
                --拒绝规则
                checkResult := getRejectSQLScript('lexpressiontosql',
                                                  businesstype,
                                                  opname,
                                                  subject_SQL);
                --允许规则
                checkResult := checkResult ||
                               getHasSQLScript('lexpressiontosql',
                                               businesstype,
                                               opname,
                                               subject_SQL);
                tmp         := getLinkObject(getFirstString(opname, '.'),
                                             substr(linkoid,
                                                    2,
                                                    LENGTH(linkoid)),
                                             getOwer(checkResult,
                                                     paramValues));
                if tmp = 1 then
                  checkResult := opname || ',1';
                else
                  checkResult := opname || ',0';
                end if;
              else
                checkResult := opname || ',1';
              end if;
            else
              checkResult := opname || ',0';
            end if;
            goto NEXT; --return; --鉴权结束
          end if;
        end if;
        --遍历每个操作
        <<NEXT>>
        results    := results || ':' || checkResult;
        firstIndex := lastIndex + 1;
      end loop;
    end if; --遍历结束
    checkResult := results;
  end;

end CheckOrdinaryRight;

 
 
-- 检查查询权限
CREATE OR REPLACE procedure CheckQueryRight(users        in varchar2,
                                            roles        in varchar2,
                                            userGroups   in varchar2,
                                            paramValues  in varchar2,
                                            businesstype in varchar2,
                                            opname       in varchar2,
                                            objectoid    in varchar2,
                                            objectroid   in varchar2,
                                            objectmoid   in varchar2,
                                            checkwhere   out varchar2) is
begin
  declare
    type cur is ref cursor;
    --判断参数
    tmp        number;
    all_not    number;
    reject     number;
    all_has    number;
    has        number;
    exeSQL     varchar2(1500);
    sence_type cur;
    --全部无权
    all_not_num number;
    --全部有权
    all_has_num     number;
    temp_expression varchar2(4500);
    --拒绝
    rej_expression     varchar2(4500);
    rej_expression_cur cur;
    --允许
    has_expression     varchar2(4500);
    has_expression_cur cur;
    subject_SQL        varchar2(4500);
    isContained        number; --是否包含everyone
  begin
    --初始化参数
    tmp         := -1;
    all_not     := -1;
    reject      := -1;
    all_has     := -1;
    has         := -1;
    subject_SQL := getsunject(users, roles, userGroups); --主体SQL脚本

    --everyone
    isContained := getEveryOne(businesstype, opname);
    if (isContained = 1) then
      subject_SQL := '1=1';
    end if;
    --场景分析

    exeSQL := 'SELECT distinct ruletype FROM PL_TYPERIGHT WHERE identifier like ' || '''' ||
              businesstype || '_' || opname || '%' || '''' ||
              ' AND isgrant = 1 AND (' || subject_SQL || ') ';
    open sence_type for(exeSQL);
    loop
      fetch sence_type
        into tmp;
      exit when sence_type%NOTFOUND;
      if tmp = 2 then
        all_not := tmp;
      end if;
      if tmp = 3 then
        reject := tmp;
      end if;
      if tmp = 1 then
        has := tmp;
      end if;
      if tmp = 0 then
        all_has := tmp;
      end if;
    end loop;
    close sence_type;
    ------场景0 默认
    if all_not = -1 and reject = -1 and all_has = -1 and has = -1 then
      checkwhere := '';
      checkwhere := ' and 1 = 0 ';
      return; --鉴权结束
    end if;
    ------场景1存在全部无权
    if all_not <> -1 then
      checkwhere := '';
      --全部无权
      exeSQL := 'SELECT count(*) FROM PL_TYPERIGHT WHERE identifier like ' || '''' ||
                businesstype || '_' || opname || '%' || '''' ||
                ' AND ruletype = 2 AND isgrant = 1 AND  (' || subject_SQL || ') ';
      execute immediate (exeSQL)
        into all_not_num;
      if all_not_num <> 0 then
        checkwhere := ' and 0 = 1 ';
        return; --鉴权结束
      else
        checkwhere := ' and 1 = 1 ';
      end if;
    end if;

    ------场景2全部有权，允许规则
    if all_has <> -1 and reject = -1 and has <> -1 and all_not = -1 then
      checkwhere := ' and 1 = 1 ';
      return; --鉴权结束
    end if;

    ----场景3只有全部有权
    if all_has <> -1 and reject = -1 and has = -1 and all_not = -1 then
      checkwhere := ' and 1 = 1 ';
      return; --鉴权结束
    end if;
    ----场景4只有全部有权、拒绝规则、允许规则
    if all_has <> -1 and reject <> -1 and has <> -1 and all_not = -1 then
      checkwhere := '';
      --拒绝规则
      temp_expression := '';
      rej_expression  := '';
      exeSQL          := 'SELECT expressiontosql FROM PL_TYPERIGHT WHERE identifier like ' || '''' ||
                         businesstype || '_' || opname || '%' || '''' ||
                         ' AND ruletype = 3 AND isgrant = 1 AND (' ||
                         subject_SQL || ') ';
      open rej_expression_cur for(exeSQL);
      loop
        fetch rej_expression_cur
          into temp_expression;
        exit when rej_expression_cur%NOTFOUND;
        if LENGTH(temp_expression) <> 0 then
          rej_expression := rej_expression || '(' || temp_expression || ')' ||
                            ' OR ';
        end if;
      end loop;
      close rej_expression_cur;
      if LENGTH(rej_expression) <> 0 then
        rej_expression := rej_expression || '0=1';
        checkwhere     := checkwhere || ' AND ' || ' not (' ||
                          rej_expression || ')';
        --主体变量替换
        checkwhere := getOwer(checkwhere, paramValues);
      end if;
      --允许规则
      temp_expression := '';
      has_expression  := '';
      exeSQL          := 'SELECT expressiontosql FROM PL_TYPERIGHT WHERE identifier like ' || '''' ||
                         businesstype || '_' || opname || '%' || '''' ||
                         ' AND ruletype = 1 AND isgrant = 1 AND (' ||
                         subject_SQL || ') ';
      open has_expression_cur for(exeSQL);
      loop
        fetch has_expression_cur
          into temp_expression;
        exit when has_expression_cur%NOTFOUND;
        if LENGTH(temp_expression) <> 0 then
          has_expression := has_expression || '(' || temp_expression || ')' ||
                            ' OR ';
        end if;
      end loop;
      close has_expression_cur;
      if LENGTH(has_expression) <> 0 then
        has_expression := has_expression || '0=1';
        checkwhere     := checkwhere || ' AND ' || '(' || has_expression || ')';
        --主体变量替换
        checkwhere := getOwer(checkwhere, paramValues);
      end if;
      --全部有权
      exeSQL := 'SELECT count(*) FROM PL_TYPERIGHT WHERE identifier like ' || '''' ||
                businesstype || '_' || opname || '%' || '''' ||
                ' AND ruletype = 0 AND isgrant = 1 AND (' || subject_SQL || ') ';
      execute immediate (exeSQL)
        into all_has_num;
      if all_has_num <> 0 then
        checkwhere := checkwhere || ' and 1=1';
      end if;
      if LENGTH(checkwhere) <> 0 then
        return; --鉴权结束
      end if;
    end if;

    ------场景5只有拒绝规则
    if all_not = -1 and reject <> -1 and all_has = -1 and has = -1 then
      checkwhere := '';
      --拒绝规则

      checkwhere := ' and 0= 1 ';

      return; --鉴权结束
    end if;
    ------场景6只有允许规则
    if all_not = -1 and reject = -1 and all_has = -1 and has <> -1 then
      checkwhere := '';
      --允许规则
      temp_expression := '';
      exeSQL          := 'SELECT expressiontosql FROM PL_TYPERIGHT WHERE identifier like ' || '''' ||
                         businesstype || '_' || opname || '%' || '''' ||
                         ' AND ruletype = 1 AND isgrant = 1 AND (' ||
                         subject_SQL || ') ';
      open has_expression_cur for(exeSQL);
      loop
        fetch has_expression_cur
          into temp_expression;
        exit when has_expression_cur%NOTFOUND;
        if LENGTH(temp_expression) <> 0 then
          has_expression := has_expression || '(' || temp_expression || ')' ||
                            ' OR ';
        end if;
      end loop;
      close has_expression_cur;
      if LENGTH(has_expression) <> 0 then
        has_expression := has_expression || '0=1';
        checkwhere     := checkwhere || ' AND ' || '(' || has_expression || ')';
        --主体变量替换
        checkwhere := getOwer(checkwhere, paramValues);
        return; --鉴权结束
      end if;
    end if;
    ------场景7只有允许规则、拒绝规则
    if all_not = -1 and reject <> -1 and all_has = -1 and has <> -1 then
      checkwhere := '';
      --拒绝规则
      temp_expression := '';
      exeSQL          := 'SELECT expressiontosql FROM PL_TYPERIGHT WHERE identifier like ' || '''' ||
                         businesstype || '_' || opname || '%' || '''' ||
                         ' AND ruletype = 3 AND isgrant = 1 AND (' ||
                         subject_SQL || ') ';
      open rej_expression_cur for(exeSQL);
      loop
        fetch rej_expression_cur
          into temp_expression;
        exit when rej_expression_cur%NOTFOUND;
        if LENGTH(temp_expression) <> 0 then
          rej_expression := rej_expression || '(' || temp_expression || ')' ||
                            ' OR ';
        end if;
      end loop;
      close rej_expression_cur;
      if LENGTH(rej_expression) <> 0 then
        rej_expression := rej_expression || '0=1';
        checkwhere     := checkwhere || ' AND ' || ' not (' ||
                          rej_expression || ')';
        --主体变量替换
        checkwhere := getOwer(checkwhere, paramValues);
      end if;
    end if;
    --允许规则
    temp_expression := '';

    exeSQL := 'SELECT expressiontosql FROM PL_TYPERIGHT WHERE identifier like ' || '''' ||
              businesstype || '_' || opname || '%' || '''' ||
              ' AND ruletype = 1 AND isgrant = 1 AND (' || subject_SQL || ') ';
    open has_expression_cur for(exeSQL);
    loop
      fetch has_expression_cur
        into temp_expression;
      exit when has_expression_cur%NOTFOUND;
      if LENGTH(temp_expression) <> 0 then
        has_expression := has_expression || '(' || temp_expression || ')' ||
                          ' OR ';
      end if;
    end loop;
    close has_expression_cur;

    if LENGTH(has_expression) <> 0 then
      has_expression := has_expression || '0=1';
      checkwhere     := checkwhere || ' AND ' || '(' || has_expression || ')';
      --主体变量替换
      checkwhere := getOwer(checkwhere, paramValues);
      return; --鉴权结束
    end if;
    ----场景8只有拒绝规则、全部有权

    if all_has <> -1 and reject <> -1 and has = -1 and all_not = -1 then
      checkwhere := '';
      --拒绝规则
      temp_expression := '';
      rej_expression  := '';
      exeSQL          := 'SELECT expressiontosql FROM PL_TYPERIGHT WHERE identifier like ' || '''' ||
                         businesstype || '_' || opname || '%' || '''' ||
                         ' AND ruletype = 3 AND isgrant = 1 AND (' ||
                         subject_SQL || ') ';
      open rej_expression_cur for(exeSQL);
      loop
        fetch rej_expression_cur
          into temp_expression;
        exit when rej_expression_cur%NOTFOUND;
        if LENGTH(temp_expression) <> 0 then
          rej_expression := rej_expression || '(' || temp_expression || ')' ||
                            ' OR ';
        end if;
      end loop;
      close rej_expression_cur;
      if LENGTH(rej_expression) <> 0 then
        rej_expression := rej_expression || '0=1';
        checkwhere     := checkwhere || ' AND ' || ' not (' ||
                          rej_expression || ')';
        --主体变量替换
        checkwhere := getOwer(checkwhere, paramValues);
      end if;
      --全部有权
      exeSQL := 'SELECT count(*) FROM PL_TYPERIGHT WHERE identifier like ' || '''' ||
                businesstype || '_' || opname || '%' || '''' ||
                ' AND ruletype = 0 AND isgrant = 1 AND (' || subject_SQL || ') ';
      execute immediate (exeSQL)
        into all_has_num;
      if all_has_num <> 0 then
        checkwhere := checkwhere || ' and 1=1';
      end if;
      if LENGTH(checkwhere) <> 0 then
        return;
      end if;

    end if;

  end;
end CheckQueryRight;

 
 
 
 
 

 
 
 
  
 

 
 
 
 