Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\reposasst\SecurityCheckCascadeInDB.pkb

/*
This package provide a generic solution to help check
privilege(represent by positiontoCheck) on a list of objects(represent
by theObjectIdList) with the corresponding cascade type(-1: cascade
down, 0: no cascade; 1: cascade up; 2: cascade up and down)
*/

CREATE OR REPLACE PACKAGE BODY SecurityCheckCascade AS

TYPE TTYPE is REF CURSOR;
reposSysPrivIndicator Varchar2(30):= 'OWB$SYSPRIVS';
--repositoryName := 'OWB$_REPOSITORY';

PROCEDURE MyDebug(debugMessage VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN 
	
	--	insert into sochen_debug(t,info) values(to_date(sysdate,'yyyy/mm/dd:hh:mi:ss'),debugMessage);
	COMMIT;

END MyDebug;

PROCEDURE checkSize(theObjectIdList  in SEC_INTEGERLIST,
		    theCascadeTypeList in SEC_INTEGERLIST)

IS
 theObjectIdListSize number;
 theCascadeTypeListSize number;
BEGIN
	theObjectIdListSize := theObjectIdList.COUNT;
        theCascadeTypeListSize:=theCascadeTypeList.COUNT;

if(theObjectIdListSize != theCascadeTypeListSize) then
	raise_application_error(-20002,'SecurityCheckCascade found the passed in arguments of objectId List and cascade type list  are not the same.');
	end if;

END checkSize;

PROCEDURE securityCheckOnEachObject(positionToCheck in number ,					
				    fcoId in number,
				    userId in number,
				    result_list IN OUT NOCOPY SEC_OBJECTINFOLIST)
AS

isAdmin number;
permitted number ;

fcoClassName varchar2(255);
fcoName varchar2(255);
fcoUOID varchar2(255);
wksp varchar2(255);

BEGIN

MyDebug('4. come into real check on EACH OBJ, userid:'||userId||',fcoId:'||fcoId||',positionToCheck:'||positionToCheck);

--0 in tokyo, if come to check workspace, just return 
	select  className  into wksp from firstclassobject_v where elementid = fcoId;
	if(wksp = 'CMPWorkspace') then 
		return;
 	end if;

--1  check the user is admin ? 	
	select count(*) into isAdmin from cmpRoleAssignment_v assign,
	cmpwbrole_v wbrole where assign.wbuser = userId and assign.wbrole
	= wbrole.elementid and  wbrole.isAdmin = '1';

if(isAdmin = 1) then
		return; -- do nothing
	end if;
	MyDebug('5. come into real check on EACH OBJ, userid is not admin');

--2 check user or user's granted role has the privilege ?	
	 permitted :=0;

--fix bug 4646333: if  no 
	--acl  point back to the fco , this situation  is
	--due to corrupted repos... hence retrieve info from fco-->acl
	--container..(not otherway around)
	
	select count(*) into permitted from cmpAccessControlList_v list,CMPACLContainer_v
	con,FirstClassObject_v fco
	where fco.elementid = fcoId and
	fco.aclcontainer = con.elementid and 
        con.elementid= list.aclcontainer  and 
	SUBSTR(list.accesscharmap,positionToCheck,1) = '1'  and
	(list.privilegeOwner = userId or 
  	list.privilegeOwner in (select wbrole from cmproleassignment_v where wbuser = userId )
	);

MyDebug('5. come into real check on EACH OBJ,  permitted:'||permitted);

if(permitted >= 1) then 
	 	return; --do nothing
	end if;

MyDebug('6. come into real check on EACH OBJ, oho,user has no permission');

-- 3. if come here, user do not has the checked permission, need to add the info into the result_list
	select name, className ,uoid into fcoName,fcoClassName,fcoUOID from firstclassobject_v where elementid = fcoId;
	
	result_list.EXTEND;

-- since jdbc can not retrieve array of user-defined type,
	--here we user concated string to return all info 
	result_list(result_list.COUNT) := fcoId||'_'||fcoUOID||'_'||fcoClassName||'_'||fcoName;

EXCEPTION 
	when others then	
	raise_application_error(-20003,'SecurityCheckCascade found the SQL error:'||SUBSTR(SQLERRM,1,200));

END securityCheckOnEachObject;

--cascadeType: -1: cascade down; 0: no cascade ; 1: cascade up
PROCEDURE securityCheckCascade(positionToCheck in number, 
			       objId in number, 
			       cascadeType in number,
				userId in number,
				theResult_list IN OUT NOCOPY SEC_OBJECTINFOLIST)

AS 
	
fcocursor TTYPE;  	
fcoId number;

BEGIN

MyDebug('3. come into real check, userid:'||userId||',objId:'||objId||',positionToCheck:'||positionToCheck);

--open cursor correspondingly
	if(cascadeType = -1 ) then --cascade down
	MyDebug('3.1: isCascadeDown is true');

open fcocursor for
		select elementid from firstclassobject_v start with
		elementid=objId connect by prior elementid = owningfolder;		
	elsif(cascadeType = 1) then --cascade up

MyDebug('3.2: isCascadeDown is false');

open fcocursor for
		    select elementid from firstclassobject_v start with
		    elementid=objId connect by prior  owningfolder =elementid;		
	else --no cascade
	   	
	open fcocursor for
		select elementid from firstclassobject_v where 
		    elementid=objId ;
	end if;

loop 
	    begin 
		fetch fcocursor into fcoId; 
		exit when fcocursor%NOTFOUND;

--now really do the checkup
		securityCheckOnEachObject(positionToCheck,fcoId, userId, theResult_list);

end;
 	end loop;
	
      close fcocursor;	--must close cursor otherwise will exceed resource limit

EXCEPTION
	WHEN OTHERS THEN
	raise_application_error(-20004,'SecurityCheckCascade found the SQL error:'||SUBSTR(SQLERRM,1,200));

END securityCheckCascade;

FUNCTION lookForClosestExistParentUOID(theSnapshotId in number ,
						theUOID in varchar2,
					tempSysPrivList in out NOCOPY SEC_OBJECTINFOLIST)
   return Varchar2

IS
   existingUOID VARCHAR2(255) ;
   fco_x_cursor TTYPE;
   theParentUOID varchar2(255);
   rootClassName varchar2(255);
   found number := 0; 
   isExisted number :=0;

BEGIN 
		
	open fco_x_cursor for 
		select uoid from firstclassobject_x where snapshotid
		=theSnapshotId  start with uoid =
 		theUOID and snapshotid =theSnapshotId connect by prior 
		owningfolder = elementid and snapshotid =theSnapshotId;

loop 
		begin 
			 fetch fco_x_cursor into theParentUOID;
			 exit when fco_x_cursor%NOTFOUND;

select count(*) into isExisted from firstclassobject_v where
			 uoid = theParentUOID; 
			 if(isExisted > 0) then 
				found := 1; 
				exit;
			 end if;
			 	
		end;

end loop;

close fco_x_cursor;

if(found = 1) then

return theParentUOID;
	end if;
	
	if(found  = 0) then 
	--todo: add info to result set record no parent exist and
	  --fix: theUOID responding to the leaf class, not the root class, need to changte to:theParentUOID 
		select className into rootClassName from firstclassobject_x where snapshotid =
		theSnapshotId and uoid = theParentUOID;--theUOID;

tempSysPrivList.EXTEND; 	
		tempSysPrivList(tempSysPrivList.COUNT) :=0||'_'||0||'_'||rootClassName||'_'||reposSysPrivIndicator;--rootClassName;

return NULL;
	end if;

EXCEPTION 
	WHEN OTHERS THEN
		raise_application_error(-20007,'SecurityCheckCascade found the SQL error:'||SUBSTR(SQLERRM,1,200));

END lookForClosestExistParentUOID;

--NOCOPY is a compilation hint: ask for: pass by reference
PROCEDURE findCorrespondingExistIDs(  snapshotId in number,
					theUOIDList in SEC_UOIDLIST,
					 theCascadeTypeList in SEC_INTEGERLIST,
					 existingIDs in out NOCOPY SEC_INTEGERLIST,
					 newCascadeTypeList in out NOCOPY SEC_INTEGERLIST,
						tempSysPrivArray in out NOCOPY SEC_OBJECTINFOLIST		
		)

isExisted NUMBER:=0;
theUOID VARCHAR2(255);
theCascadeType NUMBER;
theObjId NUMBER;

BEGIN

MyDebug('---100: come to findCorrespondingExistIDs ');

FOR i IN theUOIDList.FIRST..theUOIDList.LAST LOOP
		
		
		select count(*) into isExisted from FirstClassObject_V
		where uoid = theUOIDList(i);
		MyDebug('---101: isExisted: '|| isExisted);

if(isExisted = 0) then
		MyDebug('---102: isExisted is 0,goto find out pUOID ');

theUOID :=lookForClosestExistParentUOID(snapshotId,theUOIDList(i),tempSysPrivArray);
				MyDebug('---103: isExisted is 0,goto find out pUOID is: '||theUOID);

theCascadeType := theCascadeTypeList(i); --itself
				MyDebug('---103.1: isExisted is 0, come to get the cascade type: '||theCascadeType);

if(theUOID IS NULL) then 
				MyDebug('---103.2: isExisted is 0, here find theUOID is null');

null; --do nothing 			
			elsif(theUOID != theUOIDList(i)) then 
			       MyDebug('---103.3');

if(theCascadeTypeList(i) = 2) then 
			     MyDebug('---103.4');

theCascadeType := 1;
				elsif(theCascadeTypeList(i) = -1) then 
			       MyDebug('---103.5');

theCascadeType := 0;				
			       MyDebug('---103.6');

end if;
				
			end if;
		else
			theUOID:=theUOIDList(i);
			theCascadeType := theCascadeTypeList(i);

end if;
		
		-- no parent exist in repository, need to check user can create project or not ??		
 		if(theUOID is NULL) then 
					       MyDebug('---103.7');

null;--now do nothing, now (do sth during lookup)

else
			   --update the new arrays	
		         select elementid into theObjId	from firstclassobject_v where uoid = theUOID;
	
			 existingIDs.EXTEND;
			 existingIDs(existingIDs.COUNT) := theObjId;
			 newCascadeTypeList.EXTEND;
        		 newCascadeTypeList(newCascadeTypeList.COUNT):= theCascadeType;

MyDebug('---104: correspong objId: '||theObjId||',cascadetype:'||theCascadeType);

end if;

isExisted := 0;	
	end loop;

EXCEPTION 
	WHEN OTHERS THEN
		raise_application_error(-20006,'SecurityCheckCascade found the SQL error:'||SUBSTR(SQLERRM,1,200));

end findCorrespondingExistIDs;

/*
*This method could return duplicate item due to the fact one object
*will be checked casacde up and cascade down, the caller should filter
*out duplicated
*/

FUNCTION getFailedObjectList(positionToCheck in NUMBER,
			     theObjectIdList in SEC_INTEGERLIST,
			     theCascadeTypeList in SEC_INTEGERLIST,
			      userId in number)
RETURN SEC_OBJECTINFOLIST

IS
objId number;
objName VARCHAR2(30);
objUOID VARCHAR2(255);
objClassName VARCHAR2(255);
cascadeType number;
result_list SEC_OBJECTINFOLIST := SEC_OBJECTINFOLIST();
cascadeDownList SEC_INTEGERLIST;

BEGIN

MyDebug('1. come into getFailedObjectList,positionToCheck:'||positionToCheck||',usrId:'||userId);

--first make sure this 2 are in same size
	checkSize(theObjectIdList,theCascadeTypeList);

if(theObjectIdList.count =0) then 
		return  result_list;
	end if;
	MyDebug('1.2, after check size');

cascadeDownList := SEC_INTEGERLIST();

-- now do the cascade up
	
	FOR i IN theObjectIdList.FIRST..theObjectIdList.LAST LOOP

MyDebug('2. for each obj in objectlist, objid is:'||theObjectIdList(i)||',cascade type is:'||theCascadeTypeList(i));
		if(theCascadeTypeList(i) = 1 OR
                   theCascadeTypeList(i) = 2) then
                        cascadeType := 1;
			securityCheckCascade(positionToCheck,theObjectIdList(i), cascadeType, userId, result_list);
		end if;

-- then cascade down

if(theCascadeTypeList(i) = -1 OR
        	    theCascadeTypeList(i) = 2) then

cascadeType := -1;
		    securityCheckCascade(positionToCheck,theObjectIdList(i), cascadeType, userId,result_list);

end if;
		
		-- no cascade (down or up actually cover no cascade
		-- due to hte hierachy query stmt
		if(theCascadeTypeList(i) =0 ) then		
		    cascadeType := 0;
		    securityCheckCascade(positionToCheck,theObjectIdList(i), cascadeType, userId,result_list);
		end if;

END LOOP;
	
	
	return result_list;

EXCEPTION
	WHEN OTHERS THEN
	raise_application_error(-20001,'SecurityCheckCascade found the SQL error:'||SUBSTR(SQLERRM,1,200));

END getFailedObjectList;

FUNCTION getFailedObjectListOnRestore(positionToCheck in NUMBER,
			     theUOIDList in SEC_UOIDLIST,
			     theCascadeTypeList in SEC_INTEGERLIST,
			      snapshotId in number,
			     userId in number)
RETURN SEC_OBJECTINFOLIST

existingIDs SEC_INTEGERLIST := SEC_INTEGERLIST();
newCascadeTypeList SEC_INTEGERLIST := SEC_INTEGERLIST();
result SEC_OBJECTINFOLIST ;
tempSysPrivList SEC_OBJECTINFOLIST :=SEC_OBJECTINFOLIST();
BEGIN
	
	if(theUOIDList.count != theCascadeTypeList.count OR
		theUOIDList.count = 0) then
 		raise_application_error(-20008,'SecurityCheckCascade found the passed in arguments of theUOID List and cascade type list are not the same in number or size is 0.');	
	end if;

findCorrespondingExistIDs(snapshotId,theUOIDList,theCascadeTypeList,existingIDs,newCascadeTypeList,tempSysPrivList);
	--findCorrespondingExistIDs(snapshotId,theUOIDList,theCascadeTypeList);

result :=getFailedObjectList(positionToCheck,existingIDs,newCascadeTypeList,userId);
	
	MyDebug('--110: after getFailedObjectList, tempSysPrivListcount is:'||tempSysPrivList.COUNT);

if(tempSysPrivList.COUNT > 0) then --otherwise,numeric exception
		for i in tempSysPrivList.FIRST..tempSysPrivList.LAST LOOP
		MyDebug('--111: come to put sysprivList to result...');

result.EXTEND;
			result(result.COUNT) := tempSysPrivList(i);	
		end LOOP;
	end if;

return result;
	--return null;

EXCEPTION
	WHEN OTHERS THEN
		raise_application_error(-20005,'SecurityCheckCascade found the SQL error:'||SUBSTR(SQLERRM,1,200));

END getFailedObjectListOnRestore;

FUNCTION  findModuleId(objId in number) 
return number
AS 
fcocursor TTYPE;  	
fcoid number;
findModule number:=0;

begin

open fcocursor for
		select elementid  from firstClassObject_V start with elementid =  objId
            	 connect by prior owningFolder =elementid;
       	 loop	
		begin
		    fetch fcocursor into fcoid;
		    exit when fcocursor%NOTFOUND;
 		    select count(*) into findModule from CMPInstalledModule_v where elementid = fcoid;
		    if(findModule = 1) then
                      exit;
		    end if;
		end;
	 end loop;
      
       close fcocursor;	--must close cursor otherwise will exceed resource limit
       --if found..
      if(findModule=1) then 
	return fcoid;
      end if;

return 0;

exception 
	WHEN OTHERS THEN
		raise_application_error(-20012,'SecurityCheckCascade found the SQL error:'||SUBSTR(SQLERRM,1,200));

end findModuleId;

PROCEDURE  retrieveOneObjectInfo(objId in number,  theResult_list IN OUT NOCOPY SEC_OBJECTINFOLIST)
AS 
moduleId number;
moduleUOID varchar2(255);
projectUOID varchar2(255);
objectUOID varchar2(255);
objectClassName varchar2(255);
moduleStatus varchar2(255);
BEGIN
     --1. find the module+status and proj info
          moduleId := findModuleId(objId);
	  if(moduleId !=0) then 
	   begin 
	   select UOID, status into moduleUOID, moduleStatus from cmpinstalledmodule_v where elementid = moduleId;
	   select UOID into projectUOID from cmpwbproject_v where elementid=(
            select owningfolder from cmpinstalledmodule_v where elementid=moduleId);
           exception 
	     when NO_DATA_FOUND then
		moduleUOID := ' ';
		moduleStatus :=' ';
                projectUOID :=' ';
           end;

end if;

--2 find obj info
     begin 
     select uoid,classname into objectUOID , objectClassName from firstclassobject_v where elementid=objId; 
     exception 
       when NO_DATA_FOUND then
            objectUOID :=' ';
            objectClassName :=' ';
     end;
 --3 contruct objInfo
    theResult_list.EXTEND;
	theResult_list(theResult_list.COUNT):=objId||'_'||projectUOID||'_'||moduleUOID||'_'||moduleStatus||'_'||objectUOID||'_'||objectClassName;

EXCEPTION 
	WHEN OTHERS THEN
	raise_application_error(-20011,'SecurityCheckCascade found the SQL error:'||SUBSTR(SQLERRM,1,200));

END retrieveOneObjectInfo;

FUNCTION retrieveObjectInfo (theObjectIdList in SEC_INTEGERLIST)

RETURN SEC_OBJECTINFOLIST
IS

result_list SEC_OBJECTINFOLIST := SEC_OBJECTINFOLIST();

BEGIN

if(theObjectIdList.count =0) then
      raise_application_error(-20010,'SecurityCheckCascade found the passed in arguments of objectId List count is 0.');
   end if; 
   
   FOR i IN theObjectIdList.FIRST..theObjectIdList.LAST LOOP 
       retrieveOneObjectInfo(theObjectIdList(i), result_list);

END LOOP;
	  
   return result_list;

EXCEPTION 
	WHEN OTHERS THEN
		raise_application_error(-20009,'SecurityCheckCascade found the SQL error:'||SUBSTR(SQLERRM,1,200));

END retrieveObjectInfo;

END SecurityCheckCascade;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de