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

CREATE OR REPLACE PACKAGE BODY ManagePrivilegeFromDB AS

/*
 1. only user with full control pos is 1 can do the job
 2.
*/

TYPE TTYPE is REF CURSOR;

/*
* the global variables are valid through the whole session of
* OWBclient,  hence the reserved chunk will be used for the whole owb
*client session(Connection estanblished).
*/
--minElementId NUMBER :=-1;
maxElementId NUMBER :=0;
nextElementId NUMBER :=1;
elemId_increment NUMBER :=0;
 m_default_inc number :=10;

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;

/*
*1.if user is administrtor, return true always(todo)
*2. otherwise, check granted role or himself
*/
FUNCTION grantorHasPermission(userId in NUMBER,
			      fcoId in NUMBER,
			      full_controlPos in NUMBER
			     )
RETURN BOOLEAN IS
charmap varchar2(200);
permitted number ;
realPos number;
isAdmin number;
BEGIN

--1 	
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 true;
  end if;

--2 check user or user's granted role has the privilege ?
 permitted :=0;	
 realPos := full_controlPos+1; --substr starts from 1

select count(*) into permitted from cmpAccessControlList_v list,CMPACLContainer_v
con,FirstClassObject_v fco
where fco.elementid = fcoId and
fco.elementid = con.realFCO and
list.aclcontainer = con.elementid and
SUBSTR(list.accesscharmap,realPos,1) = '1'  and
(list.privilegeOwner = userId or
  list.privilegeOwner in (select wbrole from cmproleassignment_v where wbuser = userId )
);

if(permitted >= 1) then
 return true;
else
 return false;
end if;

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

END grantorHasPermission;

procedure doChangeAccessCharMap(fcoId in NUMBER,
			      granteeId in NUMBER,
			     listOfPosNeedToChange in VARCHAR2,
			     valueToBeSet in NUMBER)
IS
 charmap varchar2(200);
 done boolean ;
 seperator_pos number;
 pos number;	
 loopNum number;
 currentList VARCHAR2(100);
 posStr varchar2(20);
 secondPartCharMap varchar2(200);
 charToBeSet CHAR(1);
BEGIN
	  --MyDebug('4.2.0  DO acl exist,come to doChangeAccessCharMap,fcoId:'||fcoId||',granteeId:'||granteeId||',listOfPosToChange:'||listOfPosNeedToChange||',valuetobeset:'||valueToBeSet);

select accesscharmap into charmap from cmpAccessControlList_v list,CMPACLContainer_v
	con,FirstClassObject_v fco
	where fco.elementid = fcoId and
	fco.elementid = con.realFCO and
	list.aclcontainer = con.elementid and
	list.privilegeOwner = granteeId;

--	 MyDebug('4.2.1  DO acl exist,old charmap:'||charmap);

done := false;
	currentList := listOfPosNeedToChange;
	if(valueToBeSet = 1) then
		charToBeSet := '1';
	else
		charToBeSet := '0';
	
	end if;

--	 MyDebug('4.2.2  DO acl exist,listOfPosneedToChange:'||currentList);
	
	loopNum := 0;
	LOOP
	
		loopNum :=loopNum+1;
               --MyDebug('4.2.2.0  DO acl exist,loopnum::'||loopNum);

--MyDebug('4.2.2.1  DO acl exist,listOfPosneedToChange:'||currentList);

EXIT WHEN  (LENGTH(currentList) IS NULL);

seperator_pos := INSTR(currentList,'_',1,1);	   	
		if(seperator_pos = 0) then
			
		  pos := TO_NUMBER(currentList)+1;--substr is starting from 1
		  currentList := ''; --to nothing		
		else
		   posStr := SUBSTR(currentList,1,seperator_pos-1);
  		   pos := TO_NUMBER(posStr)+1; --substr is starting from 1
 		   currentList := SUBSTR( currentList, seperator_pos+1);
		end if;
                --MyDebug('4.2.2.2  DO acl exist,listOfPosneedToChange:'||currentList);

secondPartCharMap := SUBSTR(charmap,pos+1);

charmap := CONCAT(CONCAT(SUBSTR(charmap,1,pos-1) , charToBeSet),secondPartCharMap);						       --MyDebug('4.2.2.2  DO acl exist,crrentcharmap:'||charmap);

END LOOP;

--MyDebug('4.2.3  DO acl exist, after loop, charmap:'||charmap);

--now the charmap is what we needed, need to update the column
	update cmpAccessControlList_v set accesscharmap = charmap
	where cmpAccessControlList_v.elementid =(
	select list.elementid from
	cmpAccessControlList_v list,CMPACLContainer_v
	con,FirstClassObject_v fco
	where fco.elementid = fcoId and
	fco.elementid = con.realFCO and
	list.aclcontainer = con.elementid and
	list.privilegeOwner = granteeId);
	
	 --MyDebug('4.2.4  DO acl exist, after rell update charmap!!!!');

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

end doChangeAccessCharMap;

FUNCTION getElementId
return NUMBER
IS
elemId number;
id_inc number;
begin
	-- whenever the requested seq ids(elemId_increment chunk) used up,we need
	-- to request more
	
	nextElementId:=nextElementId+1;

if(elemId_increment =0) then 	
		begin
        	select increment_by into id_inc  from user_sequences where sequence_name = 'CWMSEQ';

elemId_increment:= id_inc;

exception
            	When NO_DATA_FOUND then
              	 elemId_increment:= m_default_inc;
		end;
	end if;

if(nextElementId >= maxElementId ) then
		select cwmseq.nextval into nextElementId  from dual;
		maxElementId := nextElementId+elemId_increment;		
		
	end if;

return nextElementId;

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

END getElementId;

procedure insertNewCharMap(fcoId in number,
			   granteeId in number,
			   charMap in VARCHAR2)

userName varchar2(30) ;

aclContainer NUMBER;
theUOID  varchar2(200);
elemId number;

begin
	elemId := getElementId();

select sys_context('userenv','current_user') into userName from dual;

select sys_guid() into theUOID from dual;

--fix bug 4058928: the select will retrun >1 row if
	--cmpaclcontainer_v have >1 row with realfco =fcoId(not sure
	--when this will happen)
	--select elementid into aclContainer from
	--cmpACLContainer_v where realFCO = fcoId;
	select 	ACLCONTAINER into aclContainer from firstclassobject_v where elementid =fcoId;

--select elementid into aclContainer from
	--cmpACLContainer_v where realFCO = fcoId;
	
	insert into CMPAccessControlList_v(CLASSNAME,COMPLETED,CREATEDBY,CREATIONTIMESTAMP,
	CUSTOMERDELETABLE,CUSTOMERRENAMABLE,DESCRIPTION,
	EDITABLE,ELEMENTID,IMPORTED,LOGICALNAME,NAME,PERSISTENT,SEEDED,UOID,
	UPDATEDBY,UPDATETIMESTAMP,FIRSTCLASSOBJECT,ACCESSCHARMAP,ACLCONTAINER,PRIVILEGEOWNER)
	values('CMPAccessControlList','0',userName,
	sysdate,'1','0','autocreatedinplsql',
	'1',elemId,'0','a','a','1','0',theUOID,userName,sysdate,
	aclContainer,charMap,aclContainer,granteeId);

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

end insertNewCharMap;

procedure addNewCharMapBySettingBit(fco in NUMBER,
			granteeId in NUMBER,
			listOfPosNeedToChange in VARCHAR2,
			valueToBeSet in NUMBER,
			charMapLength in NUMBER)

newCharMap varchar2(200);

BEGIN

newCharMap := '0';
	for i IN 1..charMapLength-1 LOOP
		newCharMap :=CONCAT(newCharMap,'0');
	END LOOP;

insertNewCharMap(fco,granteeId,newCharMap);

doChangeAccessCharMap(fco, granteeId , listOfPosNeedToChange, valueToBeSet );

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

end addNewCharMapBySettingBit;

/*
* if acl exist for this granteeid,then need to go through the
*listOfPos to set the positions to valueToBeSet
*else need to first create a acl and set to correct value
*/
procedure changeAccessCharMap(fcoId in NUMBER,
			      granteeId in NUMBER,
			     listOfPosNeedToChange VARCHAR2,
			     valueToBeSet in NUMBER,
			      charMapLength in NUMBER)
IS
   charmap varchar2(200);
   existing number;

BEGIN
	--1. check whether the acl exist
  	--MyDebug('4.1 check whether the acl exist');
	 existing:=0;
	select count(*) into existing from cmpAccessControlList_v list,CMPACLContainer_v
	con,FirstClassObject_v fco
	where fco.elementid = fcoId and
	fco.elementid = con.realFCO and
	list.aclcontainer = con.elementid and
	list.privilegeOwner = granteeId;

--2 if exist,change it, otherwise, add new charmap
  	if(existing != 0) then
  	--MyDebug('4.2  DO acl exist');
		doChangeAccessCharMap(fcoId,granteeId,listOfPosNeedToChange,valueToBeSet);	
	elsif(valueToBeSet = 1) then --only grant need to add new charmap, revoke just ignore
	 --MyDebug('4.3  NOT acl exist');
	
         addNewCharMapBySettingBit(fcoId,granteeId,listOfPosNeedToChange,valueToBeSet,charMapLength);
	end if;

EXCEPTION
 WHEN OTHERS THEN

raise_application_error(-20003, 'OWB has encounter database exception:'||SUBSTR(SQLERRM, 1, 200));

end changeAccessCharMap;

PROCEDURE checkSize(granteeIdList in SEC_STRINGLIST,
						listOfPosNeedToChangeArray in SEC_STRINGLIST)

IS
 granteeIdSize number;
 lisfOfPosArraySize number;
BEGIN
	granteeIdSize := granteeIdList.COUNT;
       lisfOfPosArraySize:=listOfPosNeedToChangeArray.COUNT;
	if(granteeIdSize != lisfOfPosArraySize) then
	raise_application_error(-20006,'ManagePrivilegeOnFolderCascade found the passed in arguments of granteed List and privs list  are not the same.');
	end if;

END 	checkSize;

/*
granteeIdList and ListOfPosNeedToChangeArray both are OWB defined
collection. and their order matches each other that is: the first
element in granteeIdList will be granted(revoked) the privileges
represented by the first element of listOfPosneedToChangeArray
*/

PROCEDURE doManagePrivOnFolderCascade(outcome out VARCHAR2,
			            folderId in NUMBER,
			            userId in NUMBER,
                                    granteeIdList in SEC_STRINGLIST,
				    full_controlPos in NUMBER,
				    listOfPosNeedToChangeArray in SEC_STRINGLIST,				
				    valueToBeSet in NUMBER,
				    charMapLength in NUMBER)
IS

fcoId number;	
  fcocursor TTYPE;  	
 -- currentList VARCHAR2(2000);
 seperator_pos number;
 granteeId number;
 granteeIdStr VARCHAR2(100);
listOfPosNeedToChange VARCHAR2(200);
BEGIN

--first check whether the granteeIdList and
        --ListOfPosNeedToChangeArray size are same
	--MyDebug('--->sochen: come to pl/sql');

checkSize(granteeIdList,listOfPosNeedToChangeArray);	
	--MyDebug('--->sochen: after scheck size ');

--FOR i IN listOfPosNeedToChangeArray.FIRST..listOfPosNeedToChangeArray.LAST LOOP
	
	--MyDebug('--->sochen: array at index:'||i||',it is:'||listOfPosNeedToChangeArray(i));
	--END LOOP;

--listOfPosNeedToChange := listOfPosNeedToChangeArray(1);

open fcocursor for
	select elementid from firstclassobject_v start with
	elementid=folderId connect by prior elementid = owningfolder;
	loop
	   begin
		fetch fcocursor into fcoId;
		exit when fcocursor%NOTFOUND;
		
		--currentList := granteeIdList;

--MyDebug('1,loop for each fcoId::'||fcoId);
		--MyDebug('1.5,original grantee list is:'||currentList);

if(grantorHasPermission(userId,fcoId,full_controlPos)) then
        		--MyDebug('2,after check permission');
			 --loop for each granteeid
			FOR i IN granteeIdList.FIRST..granteeIdList.LAST LOOP

--MyDebug('2.haha, loop for each granteeid');

granteeIdStr := granteeIdList(i);
			granteeId := TO_NUMBER(granteeIdStr);
		        listOfPosNeedToChange := listOfPosNeedToChangeArray(i);
			--MyDebug('-->loop for user:'|| granteeId||',priv pos:'|| listOfPosNeedToChange);

/*
	     	   	LOOP --for each granteeid

EXIT WHEN  (LENGTH(currentList) IS NULL);

seperator_pos := INSTR(currentList,'_',1,1);	   	
			if(seperator_pos = 0) then
			
			  granteeId := TO_NUMBER(currentList);--+1;--substr is starting from 1
		  	  currentList := ''; --to nothing		
			else
		          granteeIdStr := SUBSTR(currentList,1,seperator_pos-1);
  		          granteeId := TO_NUMBER(granteeIdStr);--+1; --substr is starting from 1
 		          currentList := SUBSTR( currentList, seperator_pos+1);
			end if;

--for each granteeid, really do the job asfollowing

--MyDebug('3,after get rid of 1st digit,now current granttee list:'||currentList);

--MyDebug('4,loop for each grantee'||granteeId);
			*/
	
			changeAccessCharMap(fcoId,granteeId,listOfPosNeedToChange,valueToBeSet,charMapLength);

--MyDebug('5,after reall change charmap for the grantee'||granteeId);

END LOOP;

end if;	
          end;
        end loop;
       close fcocursor;
       outcome:='SUCCESS';
EXCEPTION
        WHEN OTHERS THEN
	outcome:= 'error:'||SUBSTR(SQLERRM, 1, 200);
raise_application_error(-20001, 'OWB has encounter database exception:'||SUBSTR(SQLERRM, 1, 200));

END doManagePrivOnFolderCascade;

PROCEDURE cleanACLAfterSnapshotRestore(deletedFCOIDs in SEC_INTEGERLIST)
IS

fcoId NUMBER;
ACLCONTAINERId number;
BEGIN

-- MyDebug('----01: come into db,deal with cleanACLAfterSnapshotRestore');

if(deletedFCOIDs.COUNT >0) then
		
	   for i in deletedFCOIDs.FIRST..deletedFCOIDs.LAST LOOP

--always user bind variable to speed up for performance
		fcoId:=deletedFCOIDs(i);
                 --MyDebug('----01.1: come into cleanACLAfterSnapshotRestore on id:'||fcoId);

begin
			select elementid into ACLCONTAINERId from cmpaclcontainer_v where realFCO = fcoId;
        	         --MyDebug('----01.2: find the acl container on id:'||fcoId||', it is:'||ACLCONTAINERId);

--first delete sco
			delete from cmpaccesscontrollist_v where firstclassobject = ACLCONTAINERId;

--then delete fco
        		delete from cmpaclcontainer_v where elementid = ACLCONTAINERId;
                	exception
			when NO_DATA_FOUND then
					null; -- if there is no corresponding ACL , do nothing...
		        end;
   	   end LOOP;
  end if;

EXCEPTION
  WHEN OTHERS THEN
	raise_application_error(-20007, 'OWB has encounter sql exception:'||SUBSTR(SQLERRM, 1, 200));

END cleanACLAfterSnapshotRestore;

PROCEDURE createACLContainer(fcoId in number)

elemId number;
workspaceId number;
fcoName VARCHAR2(255);
userName VARCHAR2(30);
theUOID varchar2(255);
existed number;
BEGIN
		
	elemId :=getElementId();
	select name into fcoName from firstclassobject_v where elementid = fcoId;
	
	select sys_context('owb_workspace','workspaceID') into workspaceId from dual;
	select sys_context('userenv','current_user') into userName from dual;
        select sys_guid() into theUOID from dual;

insert into CMPACLContainer_v(CLASSNAME,COMPLETED,CREATEDBY,CREATIONTIMESTAMP,
	CUSTOMERDELETABLE,CUSTOMERRENAMABLE,DESCRIPTION,
	EDITABLE,ELEMENTID,IMPORTED,LOGICALNAME,NAME,PERSISTENT,SEEDED,UOID,
	UPDATEDBY,UPDATETIMESTAMP,ISSECURITYPOLICYSET,REALFCO,OWNINGFOLDER)
	values('CMPACLContainer','0',userName,sysdate,'1','0','autocreatedinplsql',
	'1',elemId,'0',fcoName,fcoName,'1','0',theUOID,userName,sysdate,'1',fcoId,workspaceId);

--then update the corresponding fco to record the acl container id
	select count(*) into existed from firstclassobject_v where elementid =fcoId;

--MyDebug('no object existed ?'||existed|| ' for elementid: '||fcoId);

--MyDebug('update firstclassobject_v set ACLCONTAINER ='||elemId||' where elementid = '|| fcoId||'-->to be executed...');
	update firstclassobject_v set ACLCONTAINER = elemId where elementid = fcoId;
		
EXCEPTION
	WHEN OTHERS THEN
		raise_application_error(-20010, 'OWB has encounter database exception:'||SUBSTR(SQLERRM, 1, 200));

END createACLContainer;

/*
*For each new fco
*
* 3. then need to create 1 cmpaccesscontrolist for each of
*    granteeIdList according to listOfPosNeedToChangeArray
*/
PROCEDURE createInitialACL(fcoId in number,
			   granteeIdList IN SEC_STRINGLIST,
			   charMapsArray in SEC_STRINGLIST,					
			   charMapLength in number,
			   userId in number)
IS

granteeId number;
newCharMap varchar2(200);
charMap varchar2(200);
BEGIN
	
	if(granteeIdList.COUNT != charMapsArray.COUNT) then
			raise_application_error(-20014, 'Grantee number and privilege list number do NOT match');
	end if;
	--MyDebug('granteeidList count:'||granteeIdList.COUNT||',charMaparr count:'||charMapsArray.COUNT);

--3 for each obj in granteeIdList create acl
	if(granteeIdList.COUNT > 0) then
		For i in granteeIdList.FIRST..granteeIdList.LAST LOOP
			granteeId := TO_NUMBER(granteeIdList(i));	
		        charMap :=charMapsArray(i);
			--MyDebug('Charmap is:'||charMap||',granteeid:'||granteeId);
			insertNewCharMap(fcoId,granteeId,charMap);

END LOOP;
	end if;

EXCEPTION
	WHEN OTHERS THEN
		raise_application_error(-20012, 'OWB has encounter database exception:'||SUBSTR(SQLERRM, 1, 200));

END createInitialACL;

/*
*For each new fco
*
* 3. then need to create 1 cmpaccesscontrolist for each of
*    granteeIdList according to listOfPosNeedToChangeArray
*NOTE: charMapsArray contains charmap for the corresponding grantee
* of the granteeIdList
*/

PROCEDURE createACLAfterSnapshotRestore(createdFCOIDs in SEC_INTEGERLIST,
					granteeIdList in SEC_STRINGLIST,
					charMapsArray in SEC_STRINGLIST,					
					charMapLength in number,
					userId in number)
IS

fcoId number;
BEGIN

MyDebug('----01: come into db,deal with createACLAfterSnapshotRestore');

if(createdFCOIDs.COUNT > 0) then
		For i in createdFCOIDs.FIRST..createdFCOIDs.LAST LOOP
			fcoId := createdFCOIDs(i);

--MyDebug('----01: before create acl container for: '||fcoId);			
			--1.create 1 acl container
			createACLContainer(fcoId);	
			--MyDebug('----02: after create acl container for: '||fcoId);	
			--2. then create a list of  accesscontrolist
			createInitialACL(fcoId,granteeIdList, charMapsArray,charMapLength,userId);
			--MyDebug('----02: after create acls for: '||fcoId);

END LOOP;
	end if;

EXCEPTION
  WHEN OTHERS THEN
	raise_application_error(-20008, 'OWB has encounter sql exception:'||SUBSTR(SQLERRM, 1, 200));

END createACLAfterSnapshotRestore;

FUNCTION getRestoreStartingPointUOIDs(theSnapshotId in number,
				      mcmCompUOIDArray in SEC_STRINGLIST)
return SEC_STRINGLIST
IS

fco_x_cursor TTYPE;
theParentUOID varchar2(255);
isExisted number;
found number := 0;
theUOID varchar2(255);
result SEC_STRINGLIST:= SEC_STRINGLIST();
BEGIN

if(mcmCompUOIDArray.COUNT <1) then
		 return result;
	end if;

for i in mcmCompUOIDArray.FIRST..mcmCompUOIDArray.LAST LOOP
		theUOID:= mcmCompUOIDArray(i);
		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;

result.EXTEND;
		result(result.COUNT):=theParentUOID; --if go to the root not found,record the root uoid
	END LOOP;		
	
	return result;

EXCEPTION
 WHEN OTHERS THEN
	raise_application_error(-20015, 'OWB has encounter sql exception:'||SUBSTR(SQLERRM, 1, 200));

END getRestoreStartingPointUOIDs;

--1. delete fcoId info from FCO_READ_LOOKUP table
--2. insert whatever exist for this fco
PROCEDURE updateFCOReadLookUp(firstclassObjId in number, readPosition in number)
IS

checkPos number :=readPosition+1; --+1 because substr starts from 1

BEGIN
	 delete from  FCO_READ_LOOKUP where fcoid = firstclassObjId;

insert into FCO_READ_LOOKUP select  fco.elementid, list.privilegeOwner from
	 cmpAccessControlList_v list,CMPACLContainer_v
	con,FirstClassObject_v fco
	where
	fco.elementid = firstclassObjId and
	fco.elementid = con.realFCO  and
	con.elementid = list.aclcontainer  and
	SUBSTR(list.accesscharmap,checkPos,1) = '1' ;

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

end updateFCOReadLookUp;
--used for snapshot restore and paste
PROCEDURE updateFCOReadLookupForRestore(FCOIDs in SEC_INTEGERLIST,
				        updateMode in number,
					readPos in number)
IS

theFcoId number;
	checkPos number:=readPos+1; --STR starts from 1.
BEGIN
	
	if(FCOIDs.COUNT >0) then
		For i in FCOIDs.FIRST..FCOIDs.LAST LOOP
			theFcoId := FCOIDs(i);
			-- 1.delete it anyway
			delete from FCO_READ_LOOKUP where FCOID = theFcoId;

--2 if updateMode = 1, insert it
	
			if(updateMode = 1) then
			insert into FCO_READ_LOOKUP select distinct fco.elementid, list.privilegeOwner from
				cmpAccessControlList_v list,CMPACLContainer_v
				con,FirstClassObject_v fco
			where
				fco.elementid = theFcoId and
				fco.elementid = con.realFCO  and
				con.elementid = list.aclcontainer  and
				SUBSTR(list.accesscharmap,checkPos,1) = '1' ;

end if;

end loop;

end if;

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

END updateFCOReadLookupForRestore;
--used for undelete sindelete done from DB ...
--updateMode: 1: insert new info; 0: delete those info
PROCEDURE updateAllFCOReadLookupOfFolder(folderId in number,
						  updateMode in number,
	 					   readPos in number
							)
IS
	 checkPos number := readPos+1;
	 theFcoId number;	
  	 fcocursor TTYPE;

BEGIN

open fcocursor for
		select elementid from firstclassobject_v start with
		elementid=folderId connect by prior elementid = owningfolder;
		loop
	   		begin
				fetch fcocursor into theFcoId;
				exit when fcocursor%NOTFOUND;
				--1. delete all the info regarding this fco	
				delete from FCO_READ_LOOKUP where
				fcoId = theFcoId;

--2. if updateMode = 1, insert it
				if(updateMode = 1) then
				insert into FCO_READ_LOOKUP select distinct fco.elementid, list.privilegeOwner from
					cmpAccessControlList_v list,CMPACLContainer_v
					con,FirstClassObject_v fco
				where
					fco.elementid = theFcoId and
					fco.elementid = con.realFCO  and
					con.elementid = list.aclcontainer  and
					SUBSTR(list.accesscharmap,checkPos,1) = '1' ;
										
				end if;

end;
		end loop;
	close fcocursor;

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

END updateAllFCOReadLookupOfFolder;

PROCEDURE fixOneLostACL (fcoId in number)
IS
containerId number;
BEGIN
	--MyDebug('---->fixOneLostACL 1. come to fixOneLostACL on fcoid:'||fcoId);
	select elementid into containerId  from cmpaclcontainer_v where realFCO= fcoId;
        --MyDebug('---->fixOneLostACL 2. found acl'||containerId ||' for fcoid:'||fcoId);

update firstclassobject_v set aclcontainer = containerId  where elementid = fcoId;
        --MyDebug('---->fixOneLostACL 3. fix the fco to set the containerid to the found one');

EXCEPTION
	 when NO_DATA_FOUND then
	  null; -- if there is no corresponding ACL , do nothing...
  WHEN OTHERS THEN
	raise_application_error(-20011, 'OWB has encounter sql exception:'||SUBSTR(SQLERRM, 1, 400));

end fixOneLostACL;

PROCEDURE fixLostACLAfterSnapshotRestore(FCOIDs in SEC_INTEGERLIST)
IS
fcoId NUMBER;

existing number;

BEGIN

if(FCOIDs.COUNT >0) then
	for i in FCOIDs.FIRST..FCOIDs.LAST LOOP
	    fcoid := FCOIDs(i);
	    select aclcontainer into existing from firstclassobject_v where elementid = fcoId;
	    if(existing is null) then
	       fixOneLostACL(fcoId);	
	    end if;
	END LOOP;

end if;

EXCEPTION 	
WHEN OTHERS THEN
	raise_application_error(-20010, 'OWB has encounter sql exception:'||SUBSTR(SQLERRM, 1, 400));

END fixLostACLAfterSnapshotRestore;

END ManagePrivilegeFromDB;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de