-- 检查对象权限 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; --遍历每个对象 <> 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; --遍历每个操作 <> 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;