-- 检查对象权限
|
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;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
