1 package body bism_core as
2 /* $Header: bibcoreb.pls 120.3 2006/04/03 05:20:50 akbansal noship $ */
3
4 function get_next_element(str varchar2,delimiter varchar2,startpos in out nocopy integer)
5 return varchar2
6 is
7 len integer := 0;
8 newstr varchar2(32767) := '';
9 endpos integer := 0;
10 begin
11
12 -- caller must make sure that str is not null, we dont want to do that
13 -- check here because this could be called several times
14
15 endpos := instr(str,delimiter,startpos);
16 if endpos <> 0 then
17 len := endpos - startpos;
18 newstr := substr(str,startpos,len);
19 --dbms_output.put_line('getnextele new str= '||newstr || ' start pos = '||startpos || ' pos = '|| endpos);
20 startpos := endpos+length(delimiter);
21 return newstr;
22 else
23 if startpos = 1 then
24 startpos := 0; -- indicate that delimiter is not found
25 return str;
26 else
27 newstr := substr(str,startpos);
28 startpos := 0;
29 return newstr;
30 end if;
31 end if;
32 end;
33
34 function get_last_object_id(fid raw,path varchar2,startpos in out nocopy integer,myid raw)
35 return raw
36 is
37 oid bism_objects.object_id%type;
38 typeid bism_objects.object_type_id%type;
39 ret varchar2(1) := 'n';
40 newstr varchar2(2000) := '';
41 len integer:=0;
42 begin
43
44 if startpos = 0 then
45 return fid; -- ends the recursion
46 else
47 -- startpos gets updated during this call
48 -- when a last name is retreived, get_next_element sets
49 -- it to zero, indicating no more elements in the path
50 newstr := get_next_element(path,'/',startpos);
51 end if;
52
53 -- len should not be 0 because Java validates the given path
54 -- and makes sure there are no nulls and empty names constituting the path
55 -- but if it happens to be zero, throw an exception
56 len := nvl(length(newstr),0);
57
58 if len <> 0 then
59 begin
60 select object_id,object_type_id into oid,typeid from bism_objects where folder_id = fid and object_name = newstr and user_visible = 'Y';
61 ret := bism_core.check_lookup_access(oid,typeid,'Y',myid);
62 if ret = 'y' then
63 return get_last_object_id(oid,path,startpos,myid);
64 end if;
65 exception
66 when no_data_found then
67 raise_application_error(BISM_ERRORCODES.OBJECT_NOT_FOUND,'Object not found');
68 end;
69 else
70 raise_application_error(BISM_ERRORCODES.INVALID_FOLDER_PATH,'Invalid atomic name');
71 end if;
72
73 end;
74
75
76 procedure delete_folder(fid raw,myid raw)
77 is
78 ret varchar2(1);
79 begin
80 -- modified by ccchow, check delete access for objects also
81 for i in (select object_id,object_name from bism_objects where object_type_id = 100 and user_visible = 'Y' start with object_id = fid connect by folder_id = prior object_id)
82 loop
83 ret := check_del_access_for_folder(i.object_id,myid);
84 end loop;
85
86 delete from bism_objects where object_id = fid and object_type_id = 100 and user_visible = 'Y';
87 end delete_folder;
88
89 procedure delete_folder(fid raw,path varchar2,myid raw)
90 is
91 ret varchar2(1);
92 oid bism_objects.object_id%type;
93 startpos integer := 1; --must be initd to one
94 begin
95 oid := get_last_object_id(fid,path,startpos,myid);
96
97 -- modified by ccchow, check delete access for objects also
98 for i in (select object_id,object_name from bism_objects where object_type_id = 100 and user_visible = 'Y' start with object_id = oid connect by folder_id = prior object_id)
99 loop
100 ret := check_del_access_for_folder(i.object_id,myid);
101 end loop;
102
103 delete from bism_objects where object_id = oid and object_type_id = 100 and user_visible = 'Y';
104 end delete_folder;
105
106 -- new function added to check delete access of all objects within a folder
107 function check_del_access_for_folder(fid raw,myid raw)
108 return varchar2
109 is
110 ret varchar2(1);
111 begin
112 -- first check the folder itself
113 ret := bism_access_control.check_del_access(fid,null,'Y',null,myid);
114
115 -- now check its children
116 for i in (select object_id from bism_objects where folder_id = fid and user_visible = 'Y')
117 loop
118 ret := check_obj_del_access(i.object_id,fid,myid);
119 end loop;
120
121 return ret;
122 end check_del_access_for_folder;
123
124 -- common routine used by delete_object and check_del_access_for_folder
125 -- this function checks the access for deleting an object (ccchow)
126 function check_obj_del_access(oid raw,fid raw,myid raw)
127 return varchar2
128 is
129 ret varchar2(1);
130 have_del_access varchar2(1) := 'n';
131 insufficient_privileges EXCEPTION;
132 PRAGMA EXCEPTION_INIT(insufficient_privileges, -20400);
133 begin
134 -- check WRITE privilege on object and READ privilege on parent folder
135 -- or
136 -- check READ privilege on object and FULL CONTROL privilege on parent folder
137 begin
138 ret := bism_access_control.check_del_access(null,oid,'n',null,myid);
139 if ret = 'y' then
140 have_del_access := 'y';
141 end if;
142 exception
143 when insufficient_privileges then
144 begin
145 ret := bism_access_control.check_read_access(null,oid,'n',myid);
146 if ret = 'y' then
147 ret := bism_access_control.check_fullcontrol_access(fid,myid);
148 end if;
149 end;
150 end;
151
152 if have_del_access = 'y' then
153 ret := bism_access_control.check_read_access(null,fid,'n',myid);
154 end if;
155
156 return ret;
157 end check_obj_del_access;
158
159 -- modified for object level security (ccchow)
160 procedure delete_object(fid raw,objname varchar2,myid raw)
161 is
162 typeid bism_objects.object_type_id%type;
163 ret varchar2(1) := 'n';
164
165 -- added for obj level security
166 oid bism_objects.object_id%type;
167 have_del_access varchar2(1) := 'n';
168
169 -- new exception used for obj access control (ccchow)
170 insufficient_privileges EXCEPTION;
171 PRAGMA EXCEPTION_INIT(insufficient_privileges, -20400);
172 begin
173 begin
174 -- make sure that the object being delete is not folder
175 select object_type_id,object_id into typeid,oid from bism_objects where folder_id = fid and object_name = objname and user_visible='Y';
176 if typeid is null then
177 raise_application_error(BISM_ERRORCODES.OBJECT_NOT_FOUND,'Object not found');
178 end if;
179
180 if typeid = 100 then
181 raise_application_error(BISM_ERRORCODES.CANNOT_UNBIND_FOLDER,'Cannot unbind folder');
182 end if;
183
184 -- modified for obj access control (ccchow)
185 -- see comments on the new check_obj_del_access routine
186 ret := check_obj_del_access(oid,fid,myid);
187 exception
188 when no_data_found then
189 raise_application_error(BISM_ERRORCODES.OBJECT_NOT_FOUND,'Object not found');
190 end;
191
192 -- the following check if condition to be removed
193 if ret = 'y' then
194 delete from bism_objects where folder_id = fid and object_name = objname and user_visible= 'Y';
195 if bism_core.v_auto_commit = TRUE then
196 commit;
197 end if;
198 else
199 raise_application_error(BISM_ERRORCODES.INSUFFICIENT_PRIVILEGES,'Insufficient privilege');
200 end if;
201 end delete_object;
202
203 procedure set_privilege(fid raw,grantor raw,grantee_name varchar2,priv number)
204 is
205 p number(2);
206 ret varchar2(1) := 'n';
207 sub_id raw(16);
208 begin
209
210 begin
211 select PRIVILEGE_ID into p from bism_privileges where PRIVILEGE_ID = priv;
212 exception
213 when no_data_found then
214 raise_application_error(BISM_ERRORCODES.PRIVILEGE_NOT_UNDERSTOOD,'Privilege not understood');
215 end;
216
217 ret := bism_access_control.check_fullcontrol_access(fid,grantor);
218 if ret = 'y' then
219 select subject_id into sub_id from bism_subjects where subject_name = grantee_name;
220 insert into bism_permissions (subject_id,object_id,privilege) values(sub_id,fid,priv);
221 if bism_core.v_auto_commit = TRUE then
222 commit;
223 end if;
224
225 end if;
226 exception
227 when no_data_found then
228 raise_application_error(BISM_ERRORCODES.USER_NOT_FOUND,'Grantee not found in BISM_SUBJECTS table');
229 end set_privilege;
230
231
232
233 procedure delete_folder_wo_security(fid raw,myid raw)
234 is
235 begin
236 delete from bism_objects where object_id = fid and object_type_id = 100 and user_visible = 'Y';
237 end;
238
239 -- not used in 3.0, see new check_lookup_access method
240 function check_lookup_access(oid raw,fid raw,objtype number,visible varchar2,myid raw)
241 return varchar2
242 is
243 begin
244 if visible = 'N' then
245 return 'y';
246 end if;
247 -- if the object is a folder, then check for list access else look for
248 -- read access on the folder containing the object
249 -- Originally it was coded to check for READ access regardless of
250 -- object typebut then I am introducing this change in version 26
251 -- the reason for this change is because UI performs lookup operation
252 -- (and NOT list) to show folders and if folder only has LIST access
253 -- getObject/lookup on that folder will fail
254 -- Henry and I decided to change this behavior
255 if objtype = 100 then
256 return bism_access_control.check_list_access(oid,myid);
257 else
258 -- modified to check access of object instead of folder
259 return bism_access_control.check_read_access(null,fid,'n',myid);
260 end if;
261 end;
262
263 -- new check_lookup_access for object level security (ccchow)
264 function check_lookup_access(oid raw,objtype number,visible varchar2,myid raw)
265 return varchar2
266 is
267 begin
268 if visible = 'N' then
269 return 'y';
270 end if;
271
272 if objtype = 100 then
273 return bism_access_control.check_list_access(oid,myid);
274 else
275 return bism_access_control.check_read_access(null,oid,'n',myid);
276 end if;
277 end check_lookup_access;
278
279 function prepare_rebind(fid raw,oname varchar2,myid raw,ids out nocopy bism_object_ids, current_time out nocopy date,num number,status out nocopy integer)
280 return raw
281 is
282 typeid bism_objects.object_type_id%type;
283 oid bism_objects.object_id%type;
284 tempid bism_objects.object_id%type;
285 foldername bism_objects.object_name%type;
286 ret varchar2(1) := 'n';
287 begin
288 ids := bism_object_ids();
289 -- make sure the folder exists and that the user has delete
290 -- privilege on it
291 begin
292
293 select object_id into oid from bism_objects where object_id = fid;
294 --OK, now check the privilege
295 ret := bism_access_control.check_del_access(null,fid,'n',null,myid);
296 exception
297 when no_data_found then
298 status := BISM_CONSTANTS.PARENT_FOLDER_NOT_FOUND;
299 return null;
300 end;
301
302 begin
303 if ret = 'y' then
304 -- this user has access to the folder, let's see if object exists
305 select object_id,object_type_id into oid,typeid from bism_objects where folder_id = fid and object_name = oname and user_visible='Y';
306 -- ok the object exists
307
308
309 if typeid <> 100 then
310
311 -- NOTE : we need to delete the relationships between this object and its
312 -- first level named objects
313 delete from bism_aggregates where container_id = oid;
314
315 -- delete if it is an object and return the top level object_id
316 --delete from bism_objects where folder_id = fid and user_visible= 'Y' and object_name = oname;
317 --leave the top level object alone but delete every anonymous object underneath it
318
319 -- because of cascade effect all the anonymous sub objects of the
320 -- top level object will be deleted
321 delete from bism_objects where container_id = oid;
322
323 --everything went well so fetch ids and return it
324 begin
325 for i in 1..num-1 loop
326 select sys_guid() into tempid from dual;
327 ids.extend();
328 ids(i) := tempid;
329 end loop;
330 select sysdate into current_time from dual;
331 end;
332 status :=BISM_CONSTANTS.IS_OBJECT;
333 return oid;
334 else -- type is 100 , i.e it's a folder !
335 -- dont delete if it is a folder and return its object_id
336 status :=BISM_CONSTANTS.IS_FOLDER;
337 return oid;
338 end if;
339 -- do commit from jdbc
340 else
341 select object_name into foldername from bism_objects where object_id = fid;
342 status := BISM_CONSTANTS.INSUFFICIENT_PRIVILEGES;
343 return null;
344 end if;
345 exception
346 when no_data_found then
347 status := BISM_CONSTANTS.DATA_NOT_FOUND;
348 -- object does not exist, but we need to populate ids so that rebind
349 -- can come back to bind the object
350
351
352 begin
353 for i in 1..num loop
354 select sys_guid() into tempid from dual;
355 ids.extend();
356 ids(i) := tempid;
357 end loop;
358 select sysdate into current_time from dual;
359 end;
360 return null;
361 end;
362
363 end;
364
365 function prepare_rebind_30(fid raw,oname varchar2,myid raw,ids out nocopy raw, current_time out nocopy date,num number,status out nocopy integer)
366 return raw
367 is
368 typeid bism_objects.object_type_id%type;
369 oid bism_objects.object_id%type;
370 tempid bism_objects.object_id%type;
371 foldername bism_objects.object_name%type;
372 ret varchar2(1) := 'n';
373 begin
374 --ids := bism_object_ids();
375 -- the caller shouldn't ask for more than 2000 ids at a time
376 --select sysdate into current_time from dual;
377 --if num > 2000 then
378 -- return null;
379 -- make sure the folder exists and that the user has delete
380 -- privilege on it
381 begin
382
383 select object_id into oid from bism_objects where object_id = fid;
384 --OK, now check the privilege
385 ret := bism_access_control.check_del_access(null,fid,'n',null,myid);
386 exception
387 when no_data_found then
388 status := BISM_CONSTANTS.PARENT_FOLDER_NOT_FOUND;
389 return null;
390 end;
391
392 begin
393 if ret = 'y' then
394 -- this user has access to the folder, let's see if object exists
395 select object_id,object_type_id into oid,typeid from bism_objects where folder_id = fid and object_name = oname and user_visible='Y';
396 -- ok the object exists
397
398
399 if typeid <> 100 then
400
401 -- NOTE : we need to delete the relationships between this object and its
402 -- first level named objects
403 delete from bism_aggregates where container_id = oid;
404
405 -- delete if it is an object and return the top level object_id
406 --delete from bism_objects where folder_id = fid and user_visible= 'Y' and object_name = oname;
407 --leave the top level object alone but delete every anonymous object underneath it
408
409 -- because of cascade effect all the anonymous sub objects of the
410 -- top level object will be deleted
411 delete from bism_objects where container_id = oid;
412
413 --everything went well so fetch ids and return it
414 begin
415 for i in 1..num-1 loop
416 select sys_guid() into tempid from dual;
417 --ids.extend();
418 --ids(i) := tempid;
419 ids := ids || tempid;
420 end loop;
421 select sysdate into current_time from dual;
422 end;
423 status :=BISM_CONSTANTS.IS_OBJECT;
424 return oid;
425 else -- type is 100 , i.e it's a folder !
426 -- dont delete if it is a folder and return its object_id
427 status :=BISM_CONSTANTS.IS_FOLDER;
428 return oid;
429 end if;
430 -- do commit from jdbc
431 else
432 select object_name into foldername from bism_objects where object_id = fid;
433 status := BISM_CONSTANTS.INSUFFICIENT_PRIVILEGES;
434 return null;
435 end if;
436 exception
437 when no_data_found then
438 status := BISM_CONSTANTS.DATA_NOT_FOUND;
439 -- object does not exist, but we need to populate ids so that rebind
440 -- can come back to bind the object
441
442
443 begin
444 for i in 1..num loop
445 select sys_guid() into tempid from dual;
446 --ids.extend();
447 --ids(i) := tempid;
448 ids := ids || tempid;
449 end loop;
450 select sysdate into current_time from dual;
451 end;
452 return null;
453 end;
454
455 end;
456
457 procedure lookup_folder_wo_security(fid raw,path varchar2,a_objid out nocopy raw,myid raw,startpos in out nocopy integer)
458 is
459 oid bism_objects.object_id%type;
460 typeid bism_objects.object_type_id%type;
461 ret varchar2(1) := 'n';
462 newstr varchar2(2000) := '';
463 len integer :=0;
464 begin
465 -- using '\' as delimeter for now, this can be changed
466 -- note : the delimeter can be longer than one char
467 -- get_next_element will match the delimeter string
468 newstr := get_next_element(path,'/',startpos);
469 len := nvl(length(newstr),0);
470 if len <> 0 then
471 begin
472 select object_id into oid from bism_objects where folder_id = fid and object_name = newstr and object_type_id = 100 and user_visible = 'Y';
473
474 if startpos <> 0 then
475 lookup_folder_wo_security(oid,path,a_objid,myid,startpos);
476 else
477 a_objid := oid;
478 end if;
479 exception
480 when no_data_found then
481 raise_application_error(BISM_ERRORCODES.FOLDER_NOT_FOUND,'Folder not found');
482 end;
483 else
484 raise_application_error(BISM_ERRORCODES.INVALID_FOLDER_PATH,'Invalid atomic name');
485 end if;
486 end lookup_folder_wo_security;
487
488
489 -- new prepare rebind method
490 function prepare_rebind(fid raw,folder_path varchar2,oname varchar2,myid raw,ids out nocopy bism_object_ids, current_time out nocopy date,num number,status out nocopy integer,parentid out nocopy raw)
491 return raw
492 is
493 typeid bism_objects.object_type_id%type;
494 oid bism_objects.object_id%type;
495 tempid bism_objects.object_id%type;
496 pos integer := 1;
497 begin
498 ids := bism_object_ids();
499 -- make sure the folder exists
500 if folder_path is null then
501 parentid := fid;
502 else
503 begin
504 lookup_folder_wo_security(fid,folder_path,parentid,myid,pos);
505 exception
506 when others then
507 status := BISM_CONSTANTS.PARENT_FOLDER_NOT_FOUND;
508 return null;
509 end;
510 end if;
511
512 begin
513 -- check if object exists
514 select object_id,object_type_id into oid,typeid from bism_objects where folder_id = parentid and object_name = oname and user_visible='Y';
515
516 -- ok the object exists
517 if typeid <> 100 then
518
519 -- NOTE : we need to delete the relationships between this object and its
520 -- first level named objects
521 delete from bism_aggregates where container_id = oid;
522
523 -- delete if it is an object and return the top level object_id
524 --delete from bism_objects where folder_id = fid and user_visible= 'Y' and object_name = oname;
525 --leave the top level object alone but delete every anonymous object underneath it
526
527 -- because of cascade effect all the anonymous sub objects of the
528 -- top level object will be deleted
529 delete from bism_objects where container_id = oid;
530
531 --everything went well so fetch ids and return it
532 for i in 1..num-1
533 loop
534 select sys_guid() into tempid from dual;
535 ids.extend();
536 ids(i) := tempid;
537 end loop;
538 select sysdate into current_time from dual;
539
540 status :=BISM_CONSTANTS.IS_OBJECT;
541 return oid;
542 else -- type is 100 , i.e it's a folder !
543 -- dont delete if it is a folder and return its object_id
544 status :=BISM_CONSTANTS.IS_FOLDER;
545 return oid;
546 end if;
547 exception
548 when no_data_found then
549 status := BISM_CONSTANTS.DATA_NOT_FOUND;
550 -- object does not exist, but we need to populate ids so that rebind
551 -- can come back to bind the object
552
553 begin
554 for i in 1..num
555 loop
556 select sys_guid() into tempid from dual;
557 ids.extend();
558 ids(i) := tempid;
559 end loop;
560 select sysdate into current_time from dual;
561 end;
562 return null;
563 end;
564 end prepare_rebind;
565
566 -- new prepare rebind method without ADTs
567 function prepare_rebind_30(fid raw,folder_path varchar2,oname varchar2,myid raw,ids out nocopy raw, current_time out nocopy date,num number,status out nocopy integer,parentid out nocopy raw)
568 return raw
569 is
570 typeid bism_objects.object_type_id%type;
571 oid bism_objects.object_id%type;
572 tempid bism_objects.object_id%type;
573 pos integer := 1;
574 begin
575 --ids := bism_object_ids();
576 -- make sure the folder exists
577 if folder_path is null then
578 parentid := fid;
579 else
580 begin
581 lookup_folder_wo_security(fid,folder_path,parentid,myid,pos);
582 exception
583 when others then
584 status := BISM_CONSTANTS.PARENT_FOLDER_NOT_FOUND;
585 return null;
586 end;
587 end if;
588
589 begin
590 -- check if object exists
591 select object_id,object_type_id into oid,typeid from bism_objects where folder_id = parentid and object_name = oname and user_visible='Y';
592
593 -- ok the object exists
594 if typeid <> 100 then
595
596 -- NOTE : we need to delete the relationships between this object and its
597 -- first level named objects
598 delete from bism_aggregates where container_id = oid;
599
600 -- delete if it is an object and return the top level object_id
601 --delete from bism_objects where folder_id = fid and user_visible= 'Y' and object_name = oname;
602 --leave the top level object alone but delete every anonymous object underneath it
603
604 -- because of cascade effect all the anonymous sub objects of the
605 -- top level object will be deleted
606 delete from bism_objects where container_id = oid;
607
608 --everything went well so fetch ids and return it
609 for i in 1..num-1
610 loop
611 select sys_guid() into tempid from dual;
612 --ids.extend();
613 --ids(i) := tempid;
614 ids := ids || tempid;
615 end loop;
616 select sysdate into current_time from dual;
617
618 status :=BISM_CONSTANTS.IS_OBJECT;
619 return oid;
620 else -- type is 100 , i.e it's a folder !
621 -- dont delete if it is a folder and return its object_id
622 status :=BISM_CONSTANTS.IS_FOLDER;
623 return oid;
624 end if;
625 exception
626 when no_data_found then
627 status := BISM_CONSTANTS.DATA_NOT_FOUND;
628 -- object does not exist, but we need to populate ids so that rebind
629 -- can come back to bind the object
630
631 begin
632 for i in 1..num
633 loop
634 select sys_guid() into tempid from dual;
635 --ids.extend();
636 --ids(i) := tempid;
637 ids := ids || tempid;
638 end loop;
639 select sysdate into current_time from dual;
640 end;
641 return null;
642 end;
643 end prepare_rebind_30;
644
645 -- modified for object level security
646 function check_modify_attrs_access(fid raw,objname varchar2,myid raw,status out nocopy number,callerid in number)
647 return varchar2
648 is
649 ret varchar2(1) := 'n';
650 oid bism_objects.object_id%type;
651 type_id bism_objects.object_type_id%type;
652
653 -- added for obj access control (ccchow)
654 have_upd_access varchar2(1) := 'n';
655 insufficient_privileges EXCEPTION;
656 PRAGMA EXCEPTION_INIT(insufficient_privileges, -20400);
657 begin
658 -- I am not check for status any more
659 status := BISM_CONSTANTS.OK;
660
661 -- callerid = 0 represents rename java function
662 -- callerid = 1 represents modiAttributes java function
663
664
665 begin
666 select object_type_id,object_id into type_id,oid from bism_objects where folder_id = fid and object_name = objname and user_visible = 'Y';
667
668 if type_id = 100 then
669 -- if the object being renamed is a folder, we have two scenarios:
670 -- 1. if the parent folder has ADD_FOLDER (ADD and READ in NT) privilege
671 -- and subfolder (being renamed) has WRITE privilege, then RENAME is allowed
672 -- or --
673 -- 2. if the parent folder has FULL CONTROL privilege and subfolder (being renamed)
674 -- has at least LIST privilege, then RENAME is allowed
675
676 if callerid = 0 then
677 ret := 'n';
678 -- check if user has at least ADD_FOLDER (Add and Read on NT) priv on
679 -- parent folder
680 -- note : ADD_FOLDER privilege is the minimum privilege required
681 -- on parent folder for this operation to succeed, if this throws
682 -- exception, let it bubble up
683 ret := bism_access_control.check_ins_access(fid,myid);
684 begin
685 -- OK, if user has ADD_FOLDER on parent, check to see if subfolder has
686 -- at least WRITE (scenario 1 described above)
687 -- if subfolder does not have WRITE, then test scenario 2
688 -- which is checking parent folder for FULL CONTROL,
689 -- and sub folder for at least LIST privilege
690 -- scenario 2 handled in exception handler
691 if ret = 'y' then
692 have_upd_access := bism_access_control.check_upd_access(oid,null,'Y',myid) ;
693 return have_upd_access;
694 end if;
695 exception
696 when insufficient_privileges then
697 -- check if the user has full control on parent folder
698 ret := bism_access_control.check_fullcontrol_access(fid,myid) ;
699 -- if so, then as long as he has at least LIST privilege on subfolder,
700 -- he can rename the sub folder
701 if ret = 'y' then
702 return bism_access_control.check_list_access(oid,myid);
703 end if;
704 end;
705 end if;
706 if callerid = 1 then
707 -- if it is a folder, then user needs INSERT priv on that folder
708 -- parent folder priv do not play a role
709 return bism_access_control.check_ins_access(oid,myid) ;
710 end if;
711 else
712 if callerid = 0 then
713 -- renaming of an object
714 -- modified for obj access control (ccchow)
715 -- check WRITE privilege on object and INSERT privilege on parent folder
716 -- or
717 -- check READ privilege on object and FULL CONTROL privilege on parent folder
718 begin
719 ret := bism_access_control.check_upd_access(null,oid,'n',myid);
720 if ret = 'y' then
721 have_upd_access := 'y';
722 end if;
723 exception
724 when insufficient_privileges then
725 begin
726 ret := bism_access_control.check_read_access(null,oid,'n',myid);
727 if ret = 'y' then
728 ret := bism_access_control.check_fullcontrol_access(fid,myid);
729 end if;
730 end;
731 end;
732
733 if have_upd_access = 'y' then
734 ret := bism_access_control.check_ins_access(fid,myid);
735 end if;
736 end if;
737
738 if callerid = 1 then
739 -- modifying attributes of an object
740 -- modified for obj access control (ccchow)
741 -- check WRITE privilege on object and READ privilege on parent folder
742 -- or
743 -- check READ privilege on object and FULL CONTROL privilege on parent folder
744 begin
745 ret := bism_access_control.check_upd_access(null,oid,'n',myid);
746 if ret = 'y' then
747 have_upd_access := 'y';
748 end if;
749 exception
750 when insufficient_privileges then
751 begin
752 ret := bism_access_control.check_read_access(null,oid,'n',myid);
753 if ret = 'y' then
754 ret := bism_access_control.check_fullcontrol_access(fid,myid);
755 end if;
756 end;
757 end;
758
759 if have_upd_access = 'y' then
760 ret := bism_access_control.check_read_access(null,fid,'n',myid);
761 end if;
762 end if;
763
764 return ret;
765 end if;
766 exception
767 when no_data_found then
768 status := BISM_CONSTANTS.DATA_NOT_FOUND;
769 raise_application_error(BISM_ERRORCODES.OBJECT_NOT_FOUND,'Object not found');
770 end;
771 end check_modify_attrs_access;
772
773 function get_attributes(p_fid raw, p_objname varchar2, p_myid raw)
774 return myrctype
775 is
776 v_rc myrctype;
777 begin
778 open v_rc for
779 select USER_VISIBLE,OBJECT_TYPE_ID,VERSION,TIME_DATE_CREATED,TIME_DATE_MODIFIED,OBJECT_ID,
780 CONTAINER_ID,FOLDER_ID,
781 decode(CREATED_BY,CREATED_BY,(select subject_name from bism_subjects where subject_id = created_by and subject_type='u'),null),
782 decode(LAST_MODIFIED_BY,LAST_MODIFIED_BY,(select subject_name from bism_subjects where subject_id = LAST_MODIFIED_BY and subject_type='u'), null),
783 OBJECT_NAME,TITLE,APPLICATION,DATABASE,
784 DESCRIPTION,KEYWORDS,APPLICATION_SUBTYPE1,COMP_SUBTYPE1,COMP_SUBTYPE2,COMP_SUBTYPE3,TIME_DATE_LAST_ACCESSED
785 from bism_objects
786 where 'y' = bism_core.check_get_attrs_access(p_fid,p_objname,p_myid)
787 and user_visible = 'Y'
788 and folder_id = p_fid
789 and object_name = p_objname;
790 return v_rc;
791 end;
792
793 function check_get_attrs_access(fid raw,objname varchar2,myid raw)
794 return varchar2
795 is
796 type_id bism_objects.object_type_id%type;
797 begin
798
799 begin
800 -- determine the type of object the user is trying to do getAttributes on
801 -- if folder, do not do any security check
802 -- if it is an object, then the user MUST have atleast LIST priv on the
803 -- parent folder
804 select object_type_id into type_id from bism_objects where folder_id = fid and object_name = objname and user_visible = 'Y';
805 exception
806 when no_data_found then
807 -- we dont care whether th eobject is not found or folder is not found
808 raise_application_error(BISM_ERRORCODES.OBJECT_NOT_FOUND,'Object not found');
809 end;
810
811 -- if the object is not a folder, then
812 -- check to see if the user has atleast LIST access to the parent folder
813 -- (previously we used to check for read access on the folder
814 -- but then we decided to change it to list access only because
815 -- NT allows users with LIST access see the props of folder as well
816 -- as props of object within the folder)
817
818 if type_id <> 100 then
819 return bism_access_control.check_list_access(fid,myid) ;
820 else
821 -- this is an important change, according to NT, if the object is a folder
822 -- DO NOT CHECK for any privileges, every user is allowed to see the attribs
823 -- on a folder whether or not they have any prvbs on that folder
824 return 'y';
825 end if;
826
827 end;
828
829 --
830 -- check_user_privileges
831 --
832 function check_user_privileges(p_username varchar2, p_oid raw, p_myid raw)
833 return number
834 is
835 v_subid bism_subjects.subject_id%type;
836 v_priv bism_permissions.privilege%type;
837 begin
838 begin
839 select subject_id into v_subid from bism_subjects where subject_name = p_username and subject_type='u';
840 exception
841 when no_data_found then
842 raise_application_error(-20503,'User not found');
843 end;
844 begin
845 -- could remove acl checking since it has been done when looking up the object id (ccchow)
846 select nvl(max(privilege),0) into v_priv from bism_permissions where object_id = p_oid and subject_id in (select group_id from bism_groups where user_id = v_subid);
847 exception
848 when no_data_found then
849 v_priv := 0;
850 end;
851 return v_priv;
852 end;
853
854 --
855 -- entries --
856 --
857 function entries(p_oid in raw,p_myid in raw)
858 return myrctype
859 is
860 v_rc myrctype;
861 begin
862 open v_rc for
863 select decode(t1.subject_id, t1.subject_id,
864 (select subject_name from bism_subjects where subject_id = t1.subject_id),
865 null),
866 t1.privilege,
867 t2.subject_type
868 from bism_permissions t1,
869 bism_subjects t2
870 where 'y' = bism_access_control.check_show_entries_access(p_oid,p_myid)
871 and t1.object_id = p_oid
872 and t2.subject_id = t1.subject_id;
873 return v_rc;
874 end;
875
876 function add_entries(fid in raw,acllist in out nocopy bism_acl_obj_t,myid in raw,cascade in varchar2,topfolder in varchar2)
877 return bism_acl_obj_t
878 is
879 begin
880 return add_entries(fid,acllist,myid,cascade,'n',topfolder,'y');
881 end;
882
883 procedure add_entries_30(fid in raw,acllist in CLOB,myid in raw,cascade in varchar2,topfolder in varchar2, aclseparator in varchar2)
884 -- function add_entries(fid in raw,acllist in out nocopy bism_acl_obj_t,myid in raw,cascade in varchar2,topfolder in varchar2)
885 -- return bism_acl_obj_t
886 is
887 begin
888 add_entries_30(fid,acllist,myid,cascade,'N',topfolder,'Y',aclseparator);
889 end;
890
891 function remove_entries(fid in raw,acllist in out nocopy bism_acl_obj_t,myid in raw,cascade in varchar2,topfolder in varchar2)
892 return bism_chararray_t
893 is
894 begin
895 return remove_entries(fid,acllist,myid,cascade,'n',topfolder,'y');
896 end;
897
898 function remove_entries_30(fid in raw,acllist in out nocopy CLOB,myid in raw,cascade in varchar2,topfolder in varchar2, aclseparator in varchar2)
899 return varchar2
900 is
901 begin
902 return remove_entries_30(fid,acllist,myid,cascade,'N',topfolder,'Y',aclseparator);
903 end;
904
905 function is_src_ancestor_of_target(srcfid raw,tgtfid raw)
906 return boolean
907 is
908 v_var varchar2(1) := 'n';
909
910 begin
911
912 begin
913 -- check to see if the target folder is in the path of the source folder
914 -- if so we cannot move src folder to target folder
915 -- ex : assume : folder foo contains folder goo
916 -- user attempts to move foo to goo which is illegal
917 -- so check to see if target (goo) is in the path of source (foo)
918 select 'y' into v_var from dual where tgtfid = any ( select object_id from bism_objects where object_type_id = 100 start with object_id = srcfid connect by folder_id = prior object_id);
919 exception
920 when no_data_found then
921 return false;
922 end;
923
924 return true;
925 end;
926
927 procedure move(srcfid raw,tgtfid raw,objname varchar2,myid raw)
928 is
929 objid bism_objects.object_id%type:= null;
930 objtypeid bism_objects.object_type_id%type := 0;
931 begin
932
933 if srcfid = tgtfid then
934 return;
935 end if;
936
937 begin
938 -- make sure target folder exists
939 select object_id,object_type_id into objid,objtypeid from bism_objects where object_id = tgtfid and user_visible = 'Y';
940 if objtypeid <> 100 then
941 raise_application_error(BISM_ERRORCODES.TGT_IS_NOT_FOLDER,'Illegal move : Target object is not a folder');
942 end if;
943 exception
944 when no_data_found then
945 raise_application_error(BISM_ERRORCODES.TGT_FOLDER_NOT_FOUND,'Target folder not found');
946 end;
947
948 -- now make sure that the object exists and check its type
949
950 objtypeid := 0;
951 objid := null;
952
953 begin
954 select object_id,object_type_id into objid,objtypeid from bism_objects where folder_id = srcfid and object_name = objname and user_visible = 'Y';
955 if objtypeid = 100 then
956 move_folder(srcfid ,tgtfid ,objname ,objid, myid );
957 else
958 move_object(srcfid ,tgtfid ,objname ,objid, myid );
959 end if;
960 exception
961 when no_data_found then
962 raise_application_error(BISM_ERRORCODES.OBJECT_NOT_FOUND,'Object not found');
963 end;
964
965 if bism_core.v_auto_commit = TRUE then
966 commit;
967 end if;
968
969 end;
970
971 procedure move_folder(topfolderid raw,tgtfid raw,objname varchar2, srcfid raw,myid raw)
972 is
973 priv bism_permissions.privilege%type;
974 foldername bism_objects.object_name%type;
975 move_not_allowed boolean := true;
976 begin
977
978 move_not_allowed := is_src_ancestor_of_target(srcfid,tgtfid);
979 if move_not_allowed = true then
980 raise_application_error(BISM_ERRORCODES.ILLEGAL_MOVE,'Target folder is subfolder of source folder');
981 end if;
982
983 -- NT requires that the user posses List priv on Parent folder
984 -- and Change priv on the src folder (being moved) and Add priv
985 -- on the target folder
986
987 -- 1. lets check the parent folder access first
988 begin
989 priv := 0;
990 select max(privilege) into priv from bism_permissions where
991 object_id = topfolderid and subject_id in
992 (
993 select group_id from bism_groups where user_id = myid
994 );
995
996 -- if privilege does not exist on the folder, priv will be set to null
997 -- but it wont raise data not found exception
998 -- this can happen for 2 reasons
999 -- 1. if folder does not exist
1000 -- 2. if user does not have privilege on folder
1001 if priv is null then
1002 begin
1003 select object_name into foldername from bism_objects where object_id = srcfid;
1004 raise_application_error(BISM_ERRORCODES.NO_PRIVILEGES,'User has no privileges for parent folder');
1005 exception
1006 when no_data_found then
1007 raise_application_error(BISM_ERRORCODES.PARENT_FOLDER_NOT_FOUND ,'Parent folder not found');
1008 end;
1009 end if;
1010
1011
1012 if priv < 10 then
1013 raise_application_error(BISM_ERRORCODES.INSUFFICIENT_PRIVILEGES,'Insufficient privileges for parent folder');
1014 end if;
1015
1016 end;
1017
1018 -- 2. lets check the source folder access
1019
1020 begin
1021 priv := 0;
1022 select max(privilege) into priv from bism_permissions where
1023 object_id = srcfid and subject_id in
1024 (
1025 select group_id from bism_groups where user_id = myid
1026 );
1027
1028
1029 if priv is null then
1030 begin
1031 select object_name into foldername from bism_objects where object_id = srcfid;
1032 raise_application_error(BISM_ERRORCODES.NO_PRIVILEGES,'User has no privileges for source folder');
1033 exception
1034 when no_data_found then
1035 raise_application_error(BISM_ERRORCODES.SRC_FOLDER_NOT_FOUND ,'Source folder not found');
1036 end;
1037 end if;
1038
1039
1040 if priv < 40 then
1041 raise_application_error(BISM_ERRORCODES.INSUFFICIENT_PRIV_SRC_FOLDER,'Insufficient privileges for source folder');
1042 end if;
1043
1044 end;
1045
1046
1047 -- now test the target folder access
1048 begin
1049 priv:=0;
1050 select max(privilege) into priv from bism_permissions where
1051 object_id = tgtfid and subject_id in
1052 (
1053 select group_id from bism_groups where user_id = myid
1054 );
1055
1056 -- if privilege does not exist on the folder, priv will be set to null
1057 -- but it wont raise data not found exception
1058 if priv is null then
1059 begin
1060 select object_name into foldername from bism_objects where object_id = tgtfid;
1061 raise_application_error(BISM_ERRORCODES.NO_PRIVILEGES,'User has no privileges for target folder '||foldername);
1062 exception
1063 when no_data_found then
1064 raise_application_error(BISM_ERRORCODES.TGT_FOLDER_NOT_FOUND ,'Target folder not found');
1065 end;
1066 end if;
1067
1068 if priv < 30 then
1069 raise_application_error(BISM_ERRORCODES.INSUFFICIENT_PRIV_TGT_FOLDER,'Insufficient privileges for target folder');
1070 end if;
1071
1072 end;
1073
1074
1075 -- lastly check to make sure user atleast has LIST priv on all sub folders
1076 -- of source folder (this is what NT does)
1077 begin
1078 for i in (select object_id from bism_objects where object_type_id=100 start with object_id = srcfid connect by folder_id = prior object_id) loop
1079 priv := 0;
1080 select max(privilege) into priv from bism_permissions where
1081 object_id = i.object_id and subject_id in
1082 (
1083 select group_id from bism_groups where user_id = myid
1084 );
1085
1086 if priv is null then
1087 -- we know that the object exists because we got by doing a select above see for loop
1088 raise_application_error(BISM_ERRORCODES.NO_PRIVILEGES,'User has no privileges for a folder in the hierarchy');
1089 end if;
1090
1091 if priv < 10 then
1092 raise_application_error(BISM_ERRORCODES.INSUFFICIENT_PRIV_HIER_FOLDER,'Insufficient privileges for a folder in folder hierarchy');
1093 end if;
1094
1095 end loop;
1096 end;
1097
1098 -- if we got this far means that the user has privileges on both the src
1099 -- and target folder, now lets do the move operation
1100 -- NT preserves the time stamps when folder is moved
1101 update bism_objects set folder_id = tgtfid,last_modified_by = myid where object_id = srcfid;
1102
1103 end;
1104
1105 -- modified by ccchow, to support object level security
1106 procedure move_object(srcfid raw,tgtfid raw,objname varchar2,objid raw,myid raw)
1107 is
1108 priv bism_permissions.privilege%type;
1109 foldername bism_objects.object_name%type;
1110 begin
1111 -- first test the object itself (added - ccchow)
1112 begin
1113 priv := 0;
1114 select max(privilege) into priv from bism_permissions where object_id = objid and subject_id in (select group_id from bism_groups where user_id = myid);
1115
1116 if priv is null then
1117 begin
1118 select object_name into foldername from bism_objects where object_id = objid;
1119 raise_application_error(BISM_ERRORCODES.NO_PRIVILEGES,'User has no privileges for object');
1120 exception
1121 when no_data_found then
1122 raise_application_error(BISM_ERRORCODES.OBJECT_NOT_FOUND ,'Object not found');
1123 end;
1124 end if;
1125
1126 -- NT requires at least WRITE privilege on object
1127 if priv < 40 then
1128 raise_application_error(BISM_ERRORCODES.INSUFFICIENT_PRIVILEGES,'Insufficient privileges');
1129 end if;
1130 end;
1131
1132 -- now test the source folder
1133 begin
1134 priv := 0;
1135 select max(privilege) into priv from bism_permissions where object_id = srcfid and subject_id in (select group_id from bism_groups where user_id = myid);
1136
1137 -- if privilege does not exist on the folder, priv will be set to null
1138 -- but it wont raise data not found exception
1139 -- this can happen for 2 reasons
1140 -- 1. if folder does not exist
1141 -- 2. if user does not have privilege on folder
1142 if priv is null then
1143 begin
1144 select object_name into foldername from bism_objects where object_id = srcfid;
1145 raise_application_error(BISM_ERRORCODES.NO_PRIVILEGES,'User has no privileges for source folder '||foldername);
1146 exception
1147 when no_data_found then
1148 raise_application_error(BISM_ERRORCODES.SRC_FOLDER_NOT_FOUND ,'Source folder not found');
1149 end;
1150 end if;
1151
1152 -- deprecated since NT requires only LIST privilege on source folder for move
1153 --if priv < 40 then
1154 --raise_application_error(BISM_ERRORCODES.INSUFFICIENT_PRIV_SRC_FOLDER,'Insufficient privileges for source folder');
1155 --end if;
1156 end;
1157
1158 -- now test the target folder
1159 begin
1160 priv:=0;
1161 select max(privilege) into priv from bism_permissions where object_id = tgtfid and subject_id in (select group_id from bism_groups where user_id = myid);
1162
1163 -- if privilege does not exist on the folder, priv will be set to null
1164 -- but it wont raise data not found exception
1165 if priv is null then
1166 begin
1167 select object_name into foldername from bism_objects where object_id = tgtfid;
1168 raise_application_error(BISM_ERRORCODES.NO_PRIVILEGES,'User has no privileges for target folder '||foldername);
1169 exception
1170 when no_data_found then
1171 raise_application_error(BISM_ERRORCODES.TGT_FOLDER_NOT_FOUND ,'Target folder not found');
1172 end;
1173 end if;
1174
1175 if priv < 30 then
1176 raise_application_error(BISM_ERRORCODES.INSUFFICIENT_PRIV_TGT_FOLDER,'Insufficient privileges for target folder');
1177 end if;
1178 end;
1179
1180 -- if we got this far means that the user has privileges on both the src
1181 -- and target folder, now lets do the move operation
1182 --
1183 -- preserve the time stamps during move operation
1184 -- (ccchow) The ACL will also be preserved since we only updated the folder id etc.
1185 for i in (select object_id from bism_objects start with object_id = objid connect by container_id = prior object_id)
1186 loop
1187 update bism_objects set folder_id = tgtfid,last_modified_by = myid where bism_objects.object_id = i.object_id;
1188 end loop;
1189
1190 end move_object;
1191
1192 function can_copy_folder(srcfid raw,tgtfid raw,myid raw)
1193 return boolean
1194 is
1195 foldername bism_objects.object_name%type;
1196 priv bism_permissions.privilege%type := 0;
1197 copy_not_allowed boolean := true;
1198 begin
1199 -- make sure that the destination folder is not a sub folder of src folder
1200 copy_not_allowed := bism_core.is_src_ancestor_of_target(srcfid,tgtfid);
1201 if copy_not_allowed = true then
1202 raise_application_error(BISM_ERRORCODES.ILLEGAL_COPY,'Target folder is subfolder of source folder');
1203 end if;
1204 -- make sure user has atleast READ access on all sub folders
1205 begin
1206 for i in (select object_id from bism_objects where object_type_id=100 start with object_id = srcfid connect by folder_id = prior object_id) loop
1207 priv := 0;
1208 select max(privilege) into priv from bism_permissions where
1209 object_id = i.object_id and subject_id in
1210 (
1211 select group_id from bism_groups where user_id = myid
1212 );
1213
1214 if priv is null then
1215 -- we know that the object exists because we got by doing a select above see for loop
1216 raise_application_error(BISM_ERRORCODES.NO_PRIVILEGES,'User has no privileges for a folder in the hierarchy');
1217 end if;
1218
1219 if priv < 20 then
1220 raise_application_error(BISM_ERRORCODES.INSUFFICIENT_PRIV_HIER_FOLDER,'Insufficient privileges for folder hierarchy');
1221 end if;
1222
1223 end loop;
1224 end;
1225
1226 -- now test the target folder
1227 begin
1228 priv:=0;
1229 select max(privilege) into priv from bism_permissions where
1230 object_id = tgtfid and subject_id in
1231 (
1232 select group_id from bism_groups where user_id = myid
1233 );
1234
1235 -- if privilege does not exist on the folder, priv will be set to null
1236 -- but it wont raise data not found exception
1237 if priv is null then
1238 begin
1239 select object_name into foldername from bism_objects where object_id = tgtfid;
1240 raise_application_error(BISM_ERRORCODES.NO_PRIVILEGES,'User has no privileges for target folder '||foldername);
1241 exception
1242 when no_data_found then
1243 raise_application_error(BISM_ERRORCODES.TGT_FOLDER_NOT_FOUND,'Target folder not found');
1244 end;
1245 end if;
1246
1247 if priv < 30 then
1248 raise_application_error(BISM_ERRORCODES.INSUFFICIENT_PRIV_TGT_FOLDER,'Insufficient privileges for target folder');
1249 end if;
1250
1251 end;
1252
1253 return true;
1254 end;
1255
1256 procedure copy_folder(srcfid raw,tgtfid raw,destobjname varchar2,myid raw,copytype integer,first_level boolean)
1257 is
1258 agginfo bism_aggregates.aggregate_info%type;
1259 new_target_folder bism_objects.object_id%type;
1260 newguid bism_objects.object_id%type:= null;
1261 dummycounter integer := 0;
1262 begin
1263
1264 -- NOTE : dummycounter is not used any more after fixing bug # 4639756
1265 -- removing it from arguments would require signature change, so leaving it in..
1266
1267 -- note : srcfid is the id of the folder that needs to be copied to target location
1268 -- copy the top level folder first
1269 -- we allow user to specify name for the top folder being copied
1270 newguid := bism_utils.get_guid;
1271 if first_level = true then
1272 insert into BISM_OBJECTS
1273 (USER_VISIBLE, OBJECT_TYPE_ID, VERSION, TIME_DATE_CREATED,TIME_DATE_MODIFIED,OBJECT_ID,CONTAINER_ID,FOLDER_ID,CREATED_BY,LAST_MODIFIED_BY,
1274 OBJECT_NAME, TITLE, APPLICATION, DATABASE, DESCRIPTION,KEYWORDS, XML, APPLICATION_SUBTYPE1, COMP_SUBTYPE1, COMP_SUBTYPE2,COMP_SUBTYPE3,
1275 EXTENSIBLE_ATTRIBUTES,TIME_DATE_LAST_ACCESSED)
1276 select USER_VISIBLE, OBJECT_TYPE_ID, VERSION, sysdate,sysdate,newguid,utl_raw.cast_to_raw('0'),tgtfid,myid,myid,destobjname, TITLE,
1277 APPLICATION, DATABASE, DESCRIPTION,KEYWORDS, XML, APPLICATION_SUBTYPE1, COMP_SUBTYPE1, COMP_SUBTYPE2,COMP_SUBTYPE3,EXTENSIBLE_ATTRIBUTES,
1278 TIME_DATE_LAST_ACCESSED
1279 from bism_objects
1280 where object_id = srcfid;
1281 else
1282 insert into bism_objects
1283 (USER_VISIBLE, OBJECT_TYPE_ID, VERSION, TIME_DATE_CREATED,TIME_DATE_MODIFIED,OBJECT_ID,CONTAINER_ID,FOLDER_ID,CREATED_BY,LAST_MODIFIED_BY,
1284 OBJECT_NAME, TITLE, APPLICATION, DATABASE, DESCRIPTION,KEYWORDS, XML, APPLICATION_SUBTYPE1, COMP_SUBTYPE1, COMP_SUBTYPE2,COMP_SUBTYPE3,
1285 EXTENSIBLE_ATTRIBUTES,TIME_DATE_LAST_ACCESSED)
1286 select USER_VISIBLE, OBJECT_TYPE_ID, VERSION, sysdate,sysdate,newguid,utl_raw.cast_to_raw('0'),tgtfid,myid,myid,object_name, TITLE, APPLICATION,
1287 DATABASE, DESCRIPTION,KEYWORDS, XML, APPLICATION_SUBTYPE1, COMP_SUBTYPE1, COMP_SUBTYPE2,COMP_SUBTYPE3,EXTENSIBLE_ATTRIBUTES,TIME_DATE_LAST_ACCESSED
1288 from bism_objects
1289 where object_id = srcfid ;
1290 end if;
1291
1292 --above object_id becomes newTargetFolder for child objects
1293 new_target_folder := newguid;
1294 -- now start copying the contents of the source folder
1295 for i in (select object_id,object_type_id,object_name from bism_objects where folder_id = srcfid and user_visible='Y') loop
1296 if i.object_type_id = 100 then
1297 copy_folder(i.object_id,new_target_folder,null,myid,copytype,false);
1298 else
1299 copy_object(srcfid, new_target_folder, i.object_name, i.object_name, myid, copytype);
1300 end if;
1301 end loop;
1302
1303 end;
1304
1305
1306 procedure copy(srcfid raw,tgtfid raw,srcobjname varchar2,destobjname varchar2,myid raw,copytype integer)
1307 is
1308 objid bism_objects.object_id%type:= null;
1309 objtypeid bism_objects.object_type_id%type := 0;
1310 can_copy_folder_var boolean := false;
1311 begin
1312
1313 if copytype not in (0,1) then
1314 raise_application_error(BISM_ERRORCODES.INVALID_COPY_OPERATION,'Invalid copy type');
1315 end if;
1316
1317 begin
1318
1319 -- make sure target exists
1320 select object_id,object_type_id into objid,objtypeid from bism_objects where object_id = tgtfid;
1321 if objtypeid <> 100 then
1322 raise_application_error(BISM_ERRORCODES.TGT_IS_NOT_FOLDER,'Target object is not folder');
1323 end if;
1324 exception
1325 when no_data_found then
1326 raise_application_error(BISM_ERRORCODES.TGT_FOLDER_NOT_FOUND,'Target folder not found');
1327 end;
1328
1329 -- now make sure that the object exists, check its type and call approp. method
1330
1331 objtypeid := 0;
1332 objid := null;
1333
1334 begin
1335 -- note : srcfid here means the parent folder containing the folder/object that needs
1336 -- to be copied
1337 select object_id,object_type_id into objid,objtypeid from bism_objects where folder_id = srcfid and object_name = srcobjname and user_visible = 'Y';
1338 if objtypeid = 100 then
1339 --objid represents the folderid of the folder that needs to be copied
1340 can_copy_folder_var := can_copy_folder(objid,tgtfid,myid);
1341 -- if we cannot copy this folder, it will throw exception
1342 -- hence no need to check for return value
1343 copy_folder(objid ,tgtfid ,destobjname, myid,copytype,true);
1344 else
1345 copy_object(srcfid ,tgtfid ,srcobjname ,destobjname, myid,copytype );
1346 end if;
1347 exception
1348 when no_data_found then
1349 raise_application_error(BISM_ERRORCODES.OBJECT_NOT_FOUND,'Object not found');
1350 end;
1351
1352 if bism_core.v_auto_commit = TRUE then
1353 commit;
1354 end if;
1355
1356 end;
1357
1358 -- modified by ccchow to support object level security
1359 procedure copy_object(srcfid raw,tgtfid raw,srcobjname varchar2,destobjname varchar2,myid raw,copytype integer)
1360 is
1361 priv bism_permissions.privilege%type;
1362 foldername bism_objects.object_name%type;
1363 newguid bism_objects.object_id%type;
1364 toplevelobjid bism_objects.object_id%type;
1365 agginfo bism_aggregates.aggregate_info%type;
1366 uv bism_objects.USER_VISIBLE%type;
1367 oti bism_objects.object_type_id%type;
1368 ver bism_objects.VERSION%type;
1369 ttl bism_objects.TITLE%type;
1370 app bism_objects.APPLICATION%type;
1371 db bism_objects.DATABASE%type;
1372 dsc bism_objects.DESCRIPTION%type;
1373 kwds bism_objects.KEYWORDS%type;
1374 xml bism_objects.XML%type;
1375 as1 bism_objects.APPLICATION_SUBTYPE1%type;
1376 cs1 bism_objects.COMP_SUBTYPE1%type;
1377 cs2 bism_objects.COMP_SUBTYPE2%type;
1378 cs3 bism_objects.COMP_SUBTYPE3%type;
1379 ext bism_objects.EXTENSIBLE_ATTRIBUTES%type;
1380 pass_copytype integer;
1381 dummycounter integer := 0;
1382 begin
1383
1384 -- NOTE : dummycounter is not used any more after fixing bug # 4639756
1385 -- removing it from arguments would require signature change, so leaving it in..
1386
1387
1388 -- make sure the object exists before we attempt to do anything!
1389 begin
1390 select object_id into toplevelobjid from bism_objects where folder_id = srcfid and object_name = srcobjname and user_visible = 'Y';
1391 exception
1392 when no_data_found then
1393 raise_application_error(BISM_ERRORCODES.OBJECT_NOT_FOUND,'Object not found');
1394 end;
1395 -- OK, top level object is found !!
1396
1397 -- first check the privilege on the object (ccchow)
1398 begin
1399 priv := 0;
1400 select max(privilege) into priv from bism_permissions where object_id = toplevelobjid and subject_id in (select group_id from bism_groups where user_id = myid);
1401
1402 if priv is null then
1403 raise_application_error(BISM_ERRORCODES.NO_PRIVILEGES,'User has no privileges on object');
1404 end if;
1405
1406 -- NT requires READ priv on the object
1407 if priv < 20 then
1408 raise_application_error(BISM_ERRORCODES.INSUFFICIENT_PRIVILEGES,'Insufficient privileges');
1409 end if;
1410 end;
1411
1412 -- now check privileges on source and target folders
1413 begin
1414 priv := 0;
1415 select max(privilege) into priv from bism_permissions where object_id = srcfid and subject_id in (select group_id from bism_groups where user_id = myid);
1416
1417 -- if privilege does not exist on the folder, priv will be set to null
1418 -- but it wont raise data not found exception
1419 -- this can happen for 2 reasons
1420 -- 1. if folder does not exist
1421 -- 2. if user does not have privilege on folder
1422 if priv is null then
1423 begin
1424 select object_name into foldername from bism_objects where object_id = srcfid;
1425 raise_application_error(BISM_ERRORCODES.NO_PRIVILEGES,'User has no privileges for source folder '||foldername);
1426 exception
1427 when no_data_found then
1428 raise_application_error(BISM_ERRORCODES.SRC_FOLDER_NOT_FOUND,'Source folder not found');
1429 end;
1430 end if;
1431
1432 -- NT requires LIST priv on source folder only (NEW)
1433 -- (deprecated) NT only requires READ priv on source folder to be able to
1434 -- copy from source folder (OLD)
1435 -- if priv < 20 then
1436 -- raise_application_error(BISM_ERRORCODES.INSUFFICIENT_PRIV_SRC_FOLDER,'Insufficient privileges for source folder');
1437 -- end if;
1438 end;
1439
1440 -- now test the target folder
1441 begin
1442 priv:=0;
1443 select max(privilege) into priv from bism_permissions where object_id = tgtfid and subject_id in (select group_id from bism_groups where user_id = myid);
1444
1445 -- if privilege does not exist on the folder, priv will be set to null
1446 -- but it wont raise data not found exception
1447 if priv is null then
1448 begin
1449 select object_name into foldername from bism_objects where object_id = tgtfid;
1450 raise_application_error(BISM_ERRORCODES.NO_PRIVILEGES,'User has no privileges for target folder '||foldername);
1451 exception
1452 when no_data_found then
1453 raise_application_error(BISM_ERRORCODES.TGT_FOLDER_NOT_FOUND,'Target folder not found');
1454 end;
1455 end if;
1456
1457 if priv < 30 then
1458 raise_application_error(BISM_ERRORCODES.INSUFFICIENT_PRIV_TGT_FOLDER,'Insufficient privileges for target folder');
1459 end if;
1460 end;
1461
1462 -- if we got this far means that the user has privileges on both the src
1463 -- and target folder, lets start the copy operation
1464
1465 -- SHALLOW COPY
1466 -- if user wants a shallow copy, copy the top level object
1467 -- and all its anonymous objects to the target folder. dont copy
1468 -- named objects. leave them where they are and set up relationships
1469 -- between cloned anonymous and existing named objects by
1470 -- updating bism_aggregates accordingly
1471 -- copy_next_level will copy the rest of the hierarchy down
1472
1473 -- DEEP COPY
1474 -- if user wants a deep copy, copy all objects in the hierarchy including
1475 -- the named objects. the cloned instances of named sub objects will now
1476 -- become anonymous
1477 -- and top level object is the only visible object
1478 -- all named sub objects get a system generated name and their user_visible
1479 -- flag will be set to 'N'
1480 -- all the objects (named and anon) will be copied to the target folder
1481 -- copy_next_level will copy the rest of the hierarchy down
1482
1483 -- NOTE that privilege of the copied object will inherit from the new parent folder
1484 -- so here we don't need to do anything special (taken care of by the trigger) - ccchow
1485
1486 -- Copy the top level object first
1487 newguid := bism_utils.get_guid;
1488 -- the new instance gets new time stamps (time_date_created and time_date_modified) as per NT
1489 select USER_VISIBLE, OBJECT_TYPE_ID, VERSION, TITLE, APPLICATION, DATABASE, DESCRIPTION,KEYWORDS, XML, APPLICATION_SUBTYPE1, COMP_SUBTYPE1, COMP_SUBTYPE2,COMP_SUBTYPE3,EXTENSIBLE_ATTRIBUTES
1490 into uv, oti, ver, ttl, app, db, dsc, kwds, xml, as1, cs1, cs2, cs3, ext
1491 from bism_objects
1492 where object_id =toplevelobjid;
1493 insert into bism_objects (USER_VISIBLE, OBJECT_TYPE_ID, VERSION, TIME_DATE_CREATED,TIME_DATE_MODIFIED,OBJECT_ID,CONTAINER_ID,FOLDER_ID,CREATED_BY,LAST_MODIFIED_BY,
1494 OBJECT_NAME, TITLE, APPLICATION, DATABASE, DESCRIPTION,KEYWORDS, XML, APPLICATION_SUBTYPE1, COMP_SUBTYPE1, COMP_SUBTYPE2,COMP_SUBTYPE3,
1495 EXTENSIBLE_ATTRIBUTES,TIME_DATE_LAST_ACCESSED) values
1496 (uv, oti, ver, sysdate,sysdate,newguid,utl_raw.cast_to_raw('0'),tgtfid,myid,myid,destobjname, ttl, app, db, dsc, kwds, xml, as1, cs1, cs2, cs3, ext, sysdate);
1497 -- for shortcuts we always want to use shallow copy (only copy the shortcut, not the object that it points to)
1498 if oti = 200 then
1499 pass_copytype := 0;
1500 else
1501 pass_copytype := copytype;
1502 end if;
1503 select aggregate_info into agginfo from bism_aggregates where container_id = utl_raw.cast_to_raw('0') and containee_id = toplevelobjid;
1504 insert into bism_aggregates (CONTAINER_ID, CONTAINEE_ID, AGGREGATE_INFO) values (utl_raw.cast_to_raw('0'),newguid,agginfo);
1505 -- if we want to return the object id of the top level objec
1506 -- add select object_type_id to above select statement then concat
1507 -- rettypeid with retguid
1508 -- retguid := newguid;
1509 copy_next_level(toplevelobjid,newguid,tgtfid,myid,dummycounter,pass_copytype);
1510
1511 --return rettype||':'||retguid;
1512 end copy_object;
1513
1514 procedure copy_next_level(oldparentid raw,newparentid raw,tgtfid raw,myid raw,dummycounter in out nocopy integer,copytype integer)
1515 is
1516 anon_obj_name bism_objects.object_name%type;
1517 user_visible_var bism_objects.user_visible%type;
1518 newguid raw(16);
1519 begin
1520
1521 -- NOTE : dummycounter is not used any more after fixing bug # 4639756
1522 -- removing it from arguments would require signature change, so leaving it in..
1523
1524 -- NOTE : oldparentid is the object_id of the original object hierarchy.
1525 -- walk the old hierarchy using oldparentid and its children
1526 -- newparentid is the new instance object_id. use this to set up the graph
1527 -- between newly created objects
1528 for i in (select containee_id,aggregate_info from bism_aggregates where container_id = oldparentid) loop
1529 select user_visible into user_visible_var from bism_objects where object_id = i.containee_id;
1530 if user_visible_var = 'Y' then
1531 if copytype = 0 then
1532 -- this is a named object and if performing shallow copy, don't copy it,
1533 -- just set up relationshin between this and its parent
1534 -- and dont traverse its hierarchy
1535 insert into bism_aggregates (CONTAINER_ID, CONTAINEE_ID, AGGREGATE_INFO) values (newparentid,i.containee_id,i.aggregate_info);
1536 elsif copytype = 1 then
1537 -- user wants deep copy, so clone the named object as well and make it
1538 -- anonymous, give it a system generated name
1539 newguid := bism_utils.get_guid;
1540 anon_obj_name := 'SYS-'||newguid;
1541 -- the new instance gets new time stamps (time_date_created and time_date_modified) as per NT
1542 insert into bism_objects
1543 (USER_VISIBLE, OBJECT_TYPE_ID, VERSION, TIME_DATE_CREATED,TIME_DATE_MODIFIED,OBJECT_ID,CONTAINER_ID,FOLDER_ID,CREATED_BY,
1544 LAST_MODIFIED_BY, OBJECT_NAME, TITLE, APPLICATION, DATABASE, DESCRIPTION,KEYWORDS, XML, APPLICATION_SUBTYPE1, COMP_SUBTYPE1,
1545 COMP_SUBTYPE2,COMP_SUBTYPE3,EXTENSIBLE_ATTRIBUTES,TIME_DATE_LAST_ACCESSED)
1546 select 'N', OBJECT_TYPE_ID, VERSION, sysdate,sysdate,newguid,newparentid,tgtfid,myid,myid,anon_obj_name, TITLE, APPLICATION,
1547 DATABASE, DESCRIPTION,KEYWORDS, XML, APPLICATION_SUBTYPE1, COMP_SUBTYPE1, COMP_SUBTYPE2,COMP_SUBTYPE3,
1548 EXTENSIBLE_ATTRIBUTES,TIME_DATE_LAST_ACCESSED from bism_objects where object_id = i.containee_id ;
1549 insert into bism_aggregates (CONTAINER_ID, CONTAINEE_ID, AGGREGATE_INFO) values (newparentid,newguid,i.aggregate_info);
1550 copy_next_level(i.containee_id,newguid,tgtfid,myid,dummycounter,copytype);
1551 end if;
1552 elsif user_visible_var = 'N' then
1553 newguid := bism_utils.get_guid;
1554 anon_obj_name := 'SYS-'||newguid;
1555 if length(anon_obj_name) <= 64 then
1556 -- try to generate a new object name but there is no reason we cant use
1557 -- old anonymous name
1558 insert into bism_objects
1559 (USER_VISIBLE, OBJECT_TYPE_ID, VERSION, TIME_DATE_CREATED,TIME_DATE_MODIFIED,OBJECT_ID,CONTAINER_ID,FOLDER_ID,CREATED_BY,
1560 LAST_MODIFIED_BY, OBJECT_NAME, TITLE, APPLICATION, DATABASE, DESCRIPTION,KEYWORDS, XML, APPLICATION_SUBTYPE1,
1561 COMP_SUBTYPE1, COMP_SUBTYPE2,COMP_SUBTYPE3,EXTENSIBLE_ATTRIBUTES,TIME_DATE_LAST_ACCESSED)
1562 select USER_VISIBLE, OBJECT_TYPE_ID, VERSION, sysdate,sysdate,newguid,newparentid,tgtfid,myid,myid,anon_obj_name, TITLE,
1563 APPLICATION, DATABASE, DESCRIPTION,KEYWORDS, XML, APPLICATION_SUBTYPE1, COMP_SUBTYPE1, COMP_SUBTYPE2,COMP_SUBTYPE3,
1564 EXTENSIBLE_ATTRIBUTES,TIME_DATE_LAST_ACCESSED from bism_objects where object_id = i.containee_id ;
1565 insert into bism_aggregates (CONTAINER_ID, CONTAINEE_ID, AGGREGATE_INFO) values (newparentid,newguid,i.aggregate_info);
1566 else
1567 -- if anon name length generated here exceeds 64, copy the existing name
1568 insert into bism_objects
1569 (USER_VISIBLE, OBJECT_TYPE_ID, VERSION, TIME_DATE_CREATED,TIME_DATE_MODIFIED,OBJECT_ID,CONTAINER_ID,FOLDER_ID,CREATED_BY,
1570 LAST_MODIFIED_BY, OBJECT_NAME, TITLE, APPLICATION, DATABASE, DESCRIPTION,KEYWORDS, XML, APPLICATION_SUBTYPE1, COMP_SUBTYPE1,
1571 COMP_SUBTYPE2,COMP_SUBTYPE3,EXTENSIBLE_ATTRIBUTES,TIME_DATE_LAST_ACCESSED)
1572 select USER_VISIBLE, OBJECT_TYPE_ID, VERSION, sysdate,sysdate,newguid,newparentid,tgtfid,myid,myid,OBJECT_NAME, TITLE,
1573 APPLICATION, DATABASE, DESCRIPTION,KEYWORDS, XML, APPLICATION_SUBTYPE1, COMP_SUBTYPE1, COMP_SUBTYPE2,COMP_SUBTYPE3,
1574 EXTENSIBLE_ATTRIBUTES,TIME_DATE_LAST_ACCESSED from bism_objects where object_id = i.containee_id ;
1575 insert into bism_aggregates (CONTAINER_ID, CONTAINEE_ID, AGGREGATE_INFO) values (newparentid,newguid,i.aggregate_info);
1576 end if;
1577 copy_next_level(i.containee_id,newguid,tgtfid,myid,dummycounter,copytype);
1578 end if;
1579 end loop;
1580 end;
1581
1582 procedure lookuphelper(fid raw,path in bism_lookup_info_in_50_a,lookup_output out nocopy bism_lookup_info_out_50_a,myid raw)
1583 is
1584 i integer := 1; -- the top level frame initializes it to one
1585 begin
1586 lookup_output := bism_lookup_info_out_50_a();
1587 lookup(fid,path,lookup_output,i, myid);
1588 ----- COMMENTED CODE COMMENTED CODE COMMENTED CODE -------
1589 -- the foll code has been commented because returning the object
1590 -- from here as ref cursor is not performing well
1591 --if lookup_output(lookup_output.LAST).typeid <> 100 then
1592 ---- object_load does not check privileges because lookup would
1593 ---- have done it already
1594 --return object_load(lookup_output(lookup_output.LAST).objid,myid);
1595 --else
1596 --return v_cur; -- this is not initd, dont access it in Java
1597 --end if;
1598 --return v_cur;
1599 end;
1600
1601 procedure lookuphelper(fid raw,path varchar2, objname out nocopy varchar2,objid out nocopy raw,typeid out nocopy number, myid raw)
1602 is
1603 startpos integer := 1;-- unparsed string, so start at 1
1604 begin
1605 lookup(fid,path,objname,objid,typeid,myid,startpos);
1606 end;
1607
1608 procedure lookup(fid raw,path bism_lookup_info_in_50_a,lookup_output in out nocopy bism_lookup_info_out_50_a,idx in out nocopy integer,myid raw)
1609 is
1610 oid bism_objects.object_id%type;
1611 typeid bism_objects.object_type_id%type;
1612 oname bism_objects.object_name%type;
1613 ret varchar2(1) := 'n';
1614 visible bism_objects.user_visible%type;
1615 begin
1616
1617 if path.exists(idx) then
1618
1619 begin
1620 select object_id,object_type_id,object_name,user_visible into oid,typeid,oname,visible from bism_objects where folder_id = fid and object_name = path(idx).objname and user_visible = 'Y';
1621 ret := bism_core.check_lookup_access(oid,typeid,visible,myid);
1622 if ret = 'y' then
1623 lookup_output.extend();
1624 lookup_output(lookup_output.count) := bism_lookup_info_out(oname,oid,typeid);
1625 idx := idx + 1;
1626 lookup(oid,path,lookup_output,idx,myid);
1627 end if;
1628 exception
1629 when no_data_found then
1630 raise_application_error(BISM_ERRORCODES.OBJECT_NOT_FOUND,'Object not found');
1631 end;
1632 end if; --end if path(idx) exists
1633
1634 end;
1635
1636 procedure lookup(fid raw,path varchar2,a_objname out nocopy varchar2,a_objid out nocopy raw,a_typeid out nocopy number,myid raw,startpos in out nocopy integer)
1637 is
1638 oid bism_objects.object_id%type;
1639 typeid bism_objects.object_type_id%type;
1640 oname bism_objects.object_name%type;
1641 ret varchar2(1) := 'n';
1642 visible bism_objects.user_visible%type;
1643 newstr varchar2(2000) := '';
1644 len integer :=0;
1645 len1 integer :=0;
1646 begin
1647 -- using '\' as delimeter for now, this can be changed
1648 -- note : the delimeter can be longer than one char
1649 -- get_next_element will match the delimeter string
1650 newstr := get_next_element(path,'/',startpos);
1651 len := nvl(length(newstr),0);
1652 if len <> 0 then
1653 begin
1654 select object_id,object_type_id,object_name,user_visible into oid,typeid,oname,visible from bism_objects where folder_id = fid and object_name = newstr and user_visible = 'Y';
1655
1656 -- modified by ccchow, no need to check access of intermediate folders
1657 if startpos = 0 then
1658 ret := bism_core.check_lookup_access(oid,typeid,visible,myid);
1659 else
1660 ret := 'y';
1661 end if;
1662
1663 if ret = 'y' then
1664 -- when the given string does not contain the delimiter any more
1665 -- get_next_element function will set startpos to zero
1666 -- indicating end of path
1667 if startpos <> 0 then
1668 lookup(oid,path,a_objname,a_objid,a_typeid,myid,startpos);
1669 else
1670 -- if no more path, set the obj name, id and type
1671 -- of the last object in the path
1672 -- caller (Java) retreives this info
1673 a_objname := oname;
1674 a_objid := oid;
1675 a_typeid := typeid;
1676 end if;
1677 end if;
1678 exception
1679 when no_data_found then
1680 raise_application_error(BISM_ERRORCODES.OBJECT_NOT_FOUND,'Object not found');
1681 end;
1682 else
1683 raise_application_error(BISM_ERRORCODES.INVALID_FOLDER_PATH,'Invalid atomic name');
1684 end if;
1685
1686 end;
1687
1688 function check_access_super_tree(startoid raw,stopoid raw,myid raw)
1689 return varchar2
1690 is
1691 priv bism_permissions.privilege%type;
1692
1693 begin
1694
1695
1696 if startoid = stopoid then
1697 select max(privilege) into priv from bism_permissions where object_id = startoid and subject_id in (select group_id from bism_groups where user_id = myid);
1698 if priv is null then
1699 -- user has no access to this folder
1700 return 'n';
1701 else
1702 -- user has insufficient privileges
1703 if priv < 10 then
1704 return 'n';
1705 end if;
1706 end if;
1707 else
1708
1709 for i in( select object_id from bism_objects start with object_id = startoid connect by prior folder_id = object_id and object_id <> stopoid ) loop
1710
1711 select max(privilege) into priv from bism_permissions where object_id = i.object_id and subject_id in (select group_id from bism_groups where user_id = myid);
1712 if priv is null then
1713 -- user has no access to this folder
1714 return 'n';
1715 else
1716 -- user has insufficient privileges
1717 if priv < 10 then
1718 return 'n';
1719 end if;
1720 end if;
1721 end loop;
1722
1723 end if;
1724
1725 return 'y';
1726
1727
1728 end;
1729
1730 function get_object_full_name(oid raw)
1731 return varchar2
1732 is
1733 isfirst boolean := true;
1734 objfullname varchar2(2000) := ' ';
1735 begin
1736
1737 -- absolute path from the root not including it
1738 -- if greater than 2000 chars put ...
1739 for j in(select object_name from bism_objects start with object_id = oid connect by prior folder_id = object_id and object_id <> '31' order by level desc) loop
1740 if length(j.object_name) + length(objfullname) <= 2000 then
1741 if isfirst = true then
1742 objfullname := j.object_name ;
1743 isfirst := false;
1744 else
1745 objfullname := objfullname||'/'||j.object_name ;
1746 end if;
1747 else
1748 objfullname := objfullname||'...';
1749 goto exit_loop;
1750 end if;
1751 end loop;
1752 <<exit_loop>>
1753
1754 return objfullname;
1755 end;
1756
1757 function object_load (objid raw,myid raw) return myrctype
1758 is
1759 rc myrctype;
1760 begin
1761
1762 begin
1763 -- this method is not being used currently !!
1764 -- the foll. sql stmt is m_fetchObjectsSQL2 inside SQLBuilder
1765 open rc for SELECT /*+ NO_MERGE */ T.USER_VISIBLE, T.OBJECT_TYPE_ID, T.VERSION, T.TIME_DATE_CREATED,
1766 T.TIME_DATE_MODIFIED, T.OBJECT_ID,T.FOLDER_ID, T.CREATED_BY,
1767 T.LAST_MODIFIED_BY, T.OBJECT_NAME, T.TITLE, T.APPLICATION, T.DATABASE, T.DESCRIPTION,
1768 T.KEYWORDS, T.XML, T.APPLICATION_SUBTYPE1, T.COMP_SUBTYPE1, T.COMP_SUBTYPE2,T.COMP_SUBTYPE3,TIME_DATE_LAST_ACCESSED,
1769 T.container_id,T.aggregate_info from
1770 (
1771 SELECT /*+ NO_MERGE */ A.USER_VISIBLE, A.OBJECT_TYPE_ID, A.VERSION, A.TIME_DATE_CREATED,
1772 A.TIME_DATE_MODIFIED, A.OBJECT_ID,A.FOLDER_ID, A.CREATED_BY,
1773 A.LAST_MODIFIED_BY, A.OBJECT_NAME, A.TITLE, A.APPLICATION, A.DATABASE, A.DESCRIPTION,
1774 A.KEYWORDS, A.XML, A.APPLICATION_SUBTYPE1, A.COMP_SUBTYPE1, A.COMP_SUBTYPE2,A.COMP_SUBTYPE3,TIME_DATE_LAST_ACCESSED,
1775 T1.container_id,T1.aggregate_info
1776 from bism_objects A,
1777 (
1778 select distinct containee_id,container_id,aggregate_info from bism_aggregates start with containee_id = objid and container_id='30' connect by container_id = prior containee_id
1779 )
1780 T1
1781 where A.object_id=T1.containee_id
1782 )
1783 T ;
1784 return rc;
1785 exception
1786 when no_data_found then
1787 raise_application_error(BISM_ERRORCODES.OBJECT_NOT_FOUND,'Object not found');
1788 when others then
1789 raise;
1790 end;
1791
1792 end;
1793
1794 function get_folder(oid raw, myid raw)
1795 return myrctype
1796 is
1797 rc myrctype;
1798 begin
1799 begin
1800 open rc for
1801 select USER_VISIBLE, OBJECT_TYPE_ID, VERSION, TIME_DATE_CREATED, TIME_DATE_MODIFIED,
1802 OBJECT_ID, CONTAINER_ID, FOLDER_ID,
1803 decode(CREATED_BY, CREATED_BY,
1804 (select subject_name from bism_subjects where subject_id = CREATED_BY and subject_type='u'),
1805 null),
1806 decode(LAST_MODIFIED_BY, LAST_MODIFIED_BY,
1807 (select subject_name from bism_subjects where subject_id = LAST_MODIFIED_BY and subject_type='u'),
1808 null),
1809 OBJECT_NAME, TITLE, APPLICATION, DATABASE, DESCRIPTION, KEYWORDS, XML,
1810 APPLICATION_SUBTYPE1, COMP_SUBTYPE1, COMP_SUBTYPE2, COMP_SUBTYPE3,
1811 decode(USER_VISIBLE,'Y',bism_core.get_object_full_name(object_id),'') FULLNAME
1812 from bism_objects where
1813 -- folder does not have aggregate info
1814 object_id = oid and
1815 -- apply security check
1816 -- technically speaking we dont need this because
1817 -- user already had access that's how he could call getObject()
1818 -- call check_lookup_access instead of check_read_access (done previously)
1819 -- check_lookup_access now calls check_list_access if the obj type is 100
1820 -- else calls check_read_access
1821 -- Henry and Yekesa decided to put in this change to allow users with
1822 -- LIST access on a folder to be able to see the folder attribs (i.e
1823 -- LIST access should allow user to load the folder object)
1824 'y'=bism_core.check_lookup_access(oid,'100','y',myid);
1825 return rc;
1826 exception
1827 when no_data_found then
1828 raise_application_error(BISM_ERRORCODES.OBJECT_NOT_FOUND,'Object not found');
1829 when others then
1830 raise;
1831 end;
1832
1833 end get_folder;
1834
1835 function get_object(objid raw, myid raw, traceLastLoaded varchar2)
1836 return myrctype
1837 is
1838 rc myrctype;
1839 current_date date;
1840 begin
1841 begin
1842 open rc for
1843 select T1.USER_VISIBLE, T1.OBJECT_TYPE_ID, T1.VERSION, T1.TIME_DATE_CREATED, T1.TIME_DATE_MODIFIED,
1844 T1.OBJECT_ID, T1.CONTAINER_ID, T1.FOLDER_ID,
1845 decode(T1.CREATED_BY, T1.CREATED_BY,
1846 (select subject_name from bism_subjects where subject_id = T1.CREATED_BY and subject_type='u'),
1847 null),
1848 decode(T1.LAST_MODIFIED_BY, T1.LAST_MODIFIED_BY,
1849 (select subject_name from bism_subjects where subject_id = T1.LAST_MODIFIED_BY and subject_type='u'),
1850 null),
1851 T1.OBJECT_NAME, T1.TITLE, T1.APPLICATION, T1.DATABASE, T1.DESCRIPTION, T1.KEYWORDS, T1.XML,
1852 T1.APPLICATION_SUBTYPE1, T1.COMP_SUBTYPE1, T1.COMP_SUBTYPE2, T1.COMP_SUBTYPE3, T1.TIME_DATE_LAST_ACCESSED, T2.container_id,
1853 T2.containee_id,T2.AGGREGATE_INFO,
1854 decode(T1.USER_VISIBLE,'Y',bism_core.get_object_full_name(object_id),'') FULLNAME
1855 from bism_objects T1, bism_aggregates T2
1856 where
1857 /* make sure it is a visible object*/
1858 1 = (select '1' from bism_objects where object_id = objid and user_visible = 'Y') and
1859 /* fetch the entire hierachry looking up the aggregates table*/
1860 object_id in (
1861 select distinct containee_id
1862 from bism_aggregates
1863 start with containee_id = objid
1864 connect by container_id = prior containee_id
1865 ) and T1.object_id = T2.containee_id
1866 --apply security check
1867 /* technically speaking we dont have to check the privilege any more
1868 because th euser would have access to the OID only when could access that object*/
1869 and 'y' = bism_core.check_lookup_access(T1.object_id,T1.object_type_id,T1.user_visible,myid);
1870 if traceLastLoaded = '1' then
1871 select sysdate into current_date from dual;
1872 update bism_objects set TIME_DATE_LAST_ACCESSED = current_date where object_id = objid;
1873 end if;
1874
1875 return rc;
1876 exception
1877 when no_data_found then
1878 raise_application_error(BISM_ERRORCODES.OBJECT_NOT_FOUND,'Object not found');
1879 when others then
1880 raise;
1881 end;
1882 end get_object;
1883
1884 function fetch_objectsSQL1(cid raw, objid raw, myid raw)
1885 return myrctype
1886 is
1887 rc myrctype;
1888 begin
1889 begin
1890 open rc for
1891 SELECT /*+ NO_MERGE */ T1.USER_VISIBLE, T1.OBJECT_TYPE_ID, T1.VERSION, T1.TIME_DATE_CREATED,
1892 T1.TIME_DATE_MODIFIED, T1.OBJECT_ID, T1.CONTAINER_ID, T1.FOLDER_ID,
1893 decode(T1.CREATED_BY, T1.CREATED_BY,
1894 (select subject_name from bism_subjects where subject_id = T1.CREATED_BY and subject_type='u'),
1895 null),
1896 decode(T1.LAST_MODIFIED_BY, T1.LAST_MODIFIED_BY,
1897 (select subject_name from bism_subjects where subject_id = T1.LAST_MODIFIED_BY and subject_type='u'),
1898 null),
1899 T1.OBJECT_NAME, T1.TITLE, T1.APPLICATION, T1.DATABASE, T1.DESCRIPTION,
1900 T1.KEYWORDS, T1.XML, T1.APPLICATION_SUBTYPE1, T1.COMP_SUBTYPE1, T1.COMP_SUBTYPE2,T1.COMP_SUBTYPE3, T1.TIME_DATE_LAST_ACCESSED,
1901 T2.container_id, T2.containee_id, T2.AGGREGATE_INFO
1902 from (SELECT /*+ NO_MERGE */ USER_VISIBLE, OBJECT_TYPE_ID, VERSION, TIME_DATE_CREATED,
1903 TIME_DATE_MODIFIED, OBJECT_ID, CONTAINER_ID, FOLDER_ID, CREATED_BY,
1904 LAST_MODIFIED_BY, OBJECT_NAME, TITLE, APPLICATION, DATABASE, DESCRIPTION,
1905 KEYWORDS, XML, APPLICATION_SUBTYPE1, COMP_SUBTYPE1, COMP_SUBTYPE2, COMP_SUBTYPE3, TIME_DATE_LAST_ACCESSED
1906 from bism_objects where object_id in
1907 (select distinct containee_id
1908 from bism_aggregates start with containee_id = cid
1909 connect by container_id = prior containee_id
1910 )
1911 ) T1,
1912 (select distinct container_id,containee_id, aggregate_info
1913 from bism_aggregates start with containee_id= cid
1914 connect by container_id = prior containee_id
1915 ) T2
1916 where
1917 /* now apply these rules : 1. top level object must be user_visible */
1918 1 = (select '1' from bism_objects where object_id= objid and user_visible = 'Y')
1919 and
1920 /* 2. fetch only the require object hierarchy*/
1921 T1.object_id = T2.containee_id
1922 /*
1923 3. now apply the privileges, we dont care whether it is anony or named because
1924 the privileges are set at the folder level
1925 */
1926 and
1927 'y' = bism_core.check_lookup_access(T1.object_id,T1.object_type_id,T1.user_visible,myid);
1928 return rc;
1929 exception
1930 when no_data_found then
1931 raise_application_error(BISM_ERRORCODES.OBJECT_NOT_FOUND,'Object not found');
1932 when others then
1933 raise;
1934 end;
1935 end fetch_objectsSQL1;
1936
1937 function fetch_objectsSQL2(cid raw, myid raw, traceLastLoaded varchar2)
1938 return myrctype
1939 is
1940 rc myrctype;
1941 ids_array obj_ids := obj_ids();
1942 uvs_array obj_uvs := obj_uvs();
1943 otids_array obj_otids := obj_otids();
1944 current_date date;
1945 begin
1946 begin
1947 open rc for
1948 SELECT /*+ NO_MERGE */ T.USER_VISIBLE, T.OBJECT_TYPE_ID, T.VERSION, T.TIME_DATE_CREATED,
1949 T.TIME_DATE_MODIFIED, T.OBJECT_ID,T.FOLDER_ID,
1950 decode(T.CREATED_BY,T.CREATED_BY,(select subject_name from bism_subjects where subject_id = T.CREATED_BY and subject_type='u'),null),
1951 decode(T.LAST_MODIFIED_BY,T.LAST_MODIFIED_BY,(select subject_name from bism_subjects where subject_id = T.LAST_MODIFIED_BY and subject_type='u'),null),
1952 T.OBJECT_NAME, T.TITLE, T.APPLICATION, T.DATABASE, T.DESCRIPTION,
1953 T.KEYWORDS, T.XML, T.APPLICATION_SUBTYPE1, T.COMP_SUBTYPE1, T.COMP_SUBTYPE2,T.COMP_SUBTYPE3, T.TIME_DATE_LAST_ACCESSED,
1954 /*1. containee_id is not fetched from bism_aggregates because it is pulling several extra rows
1955 when an object is shared by multiple containers, however containee_id is same as object_id
1956 from bism_objects */
1957 T.container_id,T.aggregate_info,
1958 decode(T.USER_VISIBLE,'Y',bism_core.get_object_full_name(T.OBJECT_ID),'') FULLNAME
1959 from
1960 (
1961 SELECT /*+ NO_MERGE */ A.USER_VISIBLE, A.OBJECT_TYPE_ID, A.VERSION, A.TIME_DATE_CREATED,
1962 /*2. do not fetch container_id from bism_objects use container_id from bism_aggreagtes instead*/
1963 A.TIME_DATE_MODIFIED, A.OBJECT_ID, /*A.CONTAINER_ID, */ A.FOLDER_ID, A.CREATED_BY,
1964 A.LAST_MODIFIED_BY, A.OBJECT_NAME, A.TITLE, A.APPLICATION, A.DATABASE, A.DESCRIPTION,
1965 A.KEYWORDS, A.XML, A.APPLICATION_SUBTYPE1, A.COMP_SUBTYPE1, A.COMP_SUBTYPE2,A.COMP_SUBTYPE3, A.TIME_DATE_LAST_ACCESSED,
1966 T1.container_id,T1.aggregate_info
1967 from bism_objects A,
1968 (
1969 /* 3. distinct is required because there may be diamond relationships in the hierarchy
1970 and if so, we only want to fetch it once */
1971 /* 4. container_id = '30' is important because an object may have multiple containers
1972 in which case we end up fetching those rows as well, which is useless
1973 */
1974 select distinct containee_id,container_id,aggregate_info from bism_aggregates start with containee_id = cid and container_id='30' connect by container_id = prior containee_id
1975 )
1976 T1
1977 /* 5. fetch only the required object hierarchy */
1978 where A.object_id=T1.containee_id
1979 )
1980 T
1981 /* 6. the foll. security check is not needed, because lookuphelper the function that was
1982 called before this stmt gets executed has indeed checked for lookup_access
1983 but dropping this check does not seem to improve perf. so I am leaving it in
1984 for now */
1985 where
1986 'y' = bism_core.check_lookup_access(T.object_id, T.object_type_id, T.user_visible, myid);
1987
1988 /* now set lastLoaded attribute*/
1989 if traceLastLoaded = '1' then
1990 select sysdate into current_date from dual;
1991 --update bism_objects set TIME_DATE_LAST_ACCESSED = current_date where object_id = oid;
1992 open obj_ids_cursor(cid,myid);
1993 fetch obj_ids_cursor bulk collect into uvs_array, otids_array, ids_array;
1994 if ids_array.COUNT > 0 then
1995 for i in ids_array.FIRST..ids_array.LAST
1996 loop
1997 -- update last_loaded time for each
1998 update bism_objects set TIME_DATE_LAST_ACCESSED = current_date where object_id=ids_array(i);
1999 end loop;
2000 end if;
2001 close obj_ids_cursor;
2002 end if;
2003 return rc;
2004 exception
2005 when no_data_found then
2006 raise_application_error(BISM_ERRORCODES.OBJECT_NOT_FOUND,'Object not found');
2007 when others then
2008 raise;
2009 end;
2010 end fetch_objectsSQL2;
2011
2012 function rename_object(fid raw, objname varchar2, myid raw, callerid number, newobjname varchar2)
2013 return number
2014 is
2015 ret varchar2(1) := 'n';
2016 status number;
2017 begin
2018 ret := bism_core.check_modify_attrs_access(fid, objname, myid, status, callerid);
2019 if ret = 'y' then
2020 update bism_objects set object_name = newobjname ,
2021 time_date_modified = sysdate ,
2022 last_modified_by = myid
2023 where folder_id = fid and user_visible = 'Y' and object_name = objname ;
2024 end if;
2025
2026 return status;
2027
2028 end rename_object;
2029
2030 function list
2031 (p_fid bism_objects.FOLDER_ID%type,
2032 p_subid bism_subjects.SUBJECT_ID%type)
2033 return myrctype
2034 is
2035 rc myrctype;
2036 begin
2037 begin
2038 open rc for
2039 select object_name,object_type_id
2040 from bism_objects
2041 where folder_id = p_fid and
2042 user_visible = 'Y' and
2043 'y' = bism_access_control.check_list_access(p_fid,p_subid);
2044 return rc;
2045 exception
2046 when no_data_found then
2047 raise_application_error(BISM_ERRORCODES.OBJECT_NOT_FOUND,'Object not found');
2048 when others then
2049 raise;
2050 end;
2051
2052 end list;
2053
2054
2055 -- begin refactoring code for init, bind, rebind, createSubcontext
2056 function init(p_subname in bism_subjects.subject_name%type)
2057 return bism_subjects.subject_id%type
2058 is
2059 subid bism_subjects.subject_id%type;
2060 oid bism_objects.object_id%type := null;
2061 priv bism_permissions.privilege%type := 0;
2062 begin
2063 /* make sure root folder exists */
2064 begin
2065 select object_id into oid from bism_objects where folder_id='30' and object_name= 'ROOT' and user_visible = 'Y';
2066 exception
2067 when no_data_found then
2068 raise_application_error(BISM_ERRORCODES.ROOT_NOT_FOUND,'Root folder not found');
2069 end;
2070
2071 /* make sure user exists */
2072 begin
2073 select subject_id into subid from bism_subjects where subject_name = p_subname and subject_type= 'u';
2074 exception
2075 when no_data_found then
2076 raise_application_error (BISM_ERRORCODES.USER_NOT_IDENTIFIED,'User could not be identified');
2077 end;
2078
2079 /* make sure user has enough prvileges */
2080 begin
2081 select nvl(max(privilege),0) into priv from bism_permissions where object_id = '31' and subject_id in (select group_id from bism_groups where user_id = subid );
2082 exception
2083 when no_data_found then
2084 raise_application_error(BISM_ERRORCODES.NO_PRIVILEGES,'User has no privilege for root folder');
2085 end;
2086
2087 if priv < 10 then
2088 raise_application_error(BISM_ERRORCODES.NO_PRIVILEGES,'User does not have enough privileges for root folder');
2089 end if;
2090
2091 return subid;
2092 end init;
2093
2094 function create_subcontext
2095 (p_tempTimeC bism_objects.time_date_created%type,
2096 p_tempTimeM bism_objects.time_date_modified%type,
2097 p_creator bism_subjects.subject_name%type,
2098 p_modifier bism_subjects.subject_name%type,
2099 p_fid bism_objects.folder_id%type,
2100 p_subid bism_subjects.subject_id%type,
2101 p_version bism_objects.VERSION%type,
2102 p_object_name bism_objects.object_name%type,
2103 p_title bism_objects.title%type,
2104 p_application bism_objects.application%type,
2105 p_database bism_objects.database%type,
2106 p_desc bism_objects.description%type,
2107 p_keywords bism_objects.keywords%type,
2108 p_appsubtype1 bism_objects.application_subtype1%type,
2109 p_compsubtype1 bism_objects.COMP_SUBTYPE1%type,
2110 p_compsubtype2 bism_objects.COMP_SUBTYPE2%type,
2111 p_compsubtype3 bism_objects.COMP_SUBTYPE3%type)
2112 return bism_objects.object_id%type
2113 is
2114 sub_id bism_subjects.subject_id%type;
2115 oid bism_objects.object_id%type;
2116 oname bism_objects.object_name%type;
2117 fid bism_objects.folder_id%type;
2118 created_subid bism_subjects.subject_id%type;
2119 modified_subid bism_subjects.subject_id%type;
2120 timeC bism_objects.time_date_created%type := sysdate;
2121 timeM bism_objects.time_date_modified%type := sysdate;
2122 tempTimeC bism_objects.time_date_created%type := null;
2123 tempTimeM bism_objects.time_date_modified%type := null;
2124 ret varchar2(1):='n';
2125 begin
2126 begin
2127 tempTimeC := p_tempTimeC;
2128 tempTimeM := p_tempTimeM;
2129 select subject_id into created_subid from bism_subjects where subject_name = p_creator and subject_type = 'u';
2130 if p_creator <> p_modifier then
2131 select subject_id into modified_subid from bism_subjects where subject_name = p_modifier and subject_type = 'u';
2132 else
2133 modified_subid := created_subid;
2134 end if;
2135 exception
2136 when no_data_found then
2137 raise_application_error(-20503,'User not found');
2138 end;
2139
2140 ret := bism_access_control.check_ins_access(p_fid, p_subid);
2141 if ret = 'y' then
2142 if tempTimeC is not null then
2143 timeC := tempTimeC;
2144 else
2145 timeC := sysdate;
2146 end if;
2147
2148 if tempTimeM is not null then
2149 timeM := tempTimeM;
2150 else
2151 timeM := sysdate;
2152 end if;
2153
2154 insert into BISM_OBJECTS
2155 (USER_VISIBLE, OBJECT_TYPE_ID,VERSION, TIME_DATE_CREATED, TIME_DATE_MODIFIED, OBJECT_ID, CONTAINER_ID, FOLDER_ID, CREATED_BY,
2156 LAST_MODIFIED_BY, OBJECT_NAME, TITLE, APPLICATION, DATABASE, DESCRIPTION, KEYWORDS, XML, APPLICATION_SUBTYPE1, COMP_SUBTYPE1,
2157 COMP_SUBTYPE2, COMP_SUBTYPE3, TIME_DATE_LAST_ACCESSED) values ('Y',0100,p_version,timeC,timeM,bism_utils.get_guid,null,
2158 p_fid,created_subid,modified_subid,p_object_name,p_title,p_application,p_database,p_desc,p_keywords,null,p_appsubtype1,
2159 p_compsubtype1,p_compsubtype2,p_compsubtype3, timeC) returning object_id into oid;
2160 else
2161 raise_application_error(-20104,'Unexpected return value');
2162 end if;
2163
2164 return oid;
2165 end create_subcontext;
2166
2167 function create_subcontext_30
2168 (p_tempTimeC bism_objects.time_date_created%type,
2169 p_tempTimeM bism_objects.time_date_modified%type,
2170 p_oid bism_objects.object_id%type,
2171 p_creator bism_subjects.subject_name%type,
2172 p_modifier bism_subjects.subject_name%type,
2173 p_fid bism_objects.folder_id%type,
2174 p_subid bism_subjects.subject_id%type,
2175 p_version bism_objects.VERSION%type,
2176 p_object_name bism_objects.object_name%type,
2177 p_title bism_objects.title%type,
2178 p_application bism_objects.application%type,
2179 p_database bism_objects.database%type,
2180 p_desc bism_objects.description%type,
2181 p_keywords bism_objects.keywords%type,
2182 p_appsubtype1 bism_objects.application_subtype1%type,
2183 p_compsubtype1 bism_objects.COMP_SUBTYPE1%type,
2184 p_compsubtype2 bism_objects.COMP_SUBTYPE2%type,
2185 p_compsubtype3 bism_objects.COMP_SUBTYPE3%type,
2186 p_extAttrs_Clob CLOB)
2187 return bism_objects.object_id%type
2188 is
2189 sub_id bism_subjects.subject_id%type;
2190 oid bism_objects.object_id%type;
2191 oname bism_objects.object_name%type;
2192 fid bism_objects.folder_id%type;
2193 created_subid bism_subjects.subject_id%type;
2194 modified_subid bism_subjects.subject_id%type;
2195 timeC bism_objects.time_date_created%type := sysdate;
2196 timeM bism_objects.time_date_modified%type := sysdate;
2197 tempTimeC bism_objects.time_date_created%type := null;
2198 tempTimeM bism_objects.time_date_modified%type := null;
2199 p_extAttrs bism_objects.extensible_attributes%type := null;
2200
2201 ret varchar2(1):='n';
2202 begin
2203 begin
2204 tempTimeC := p_tempTimeC;
2205 tempTimeM := p_tempTimeM;
2206
2207 select subject_id into created_subid from bism_subjects where subject_name = p_creator and subject_type = 'u';
2208 if p_creator <> p_modifier then
2209 select subject_id into modified_subid from bism_subjects where subject_name = p_modifier and subject_type = 'u';
2210 else
2211 modified_subid := created_subid;
2212 end if;
2213 exception
2214 when no_data_found then
2215 raise_application_error(-20503,'User not found');
2216 end;
2217
2218 ret := bism_access_control.check_ins_access(p_fid, p_subid);
2219 -- if caller provides id for this folderr, use it
2220 IF p_oid is not null then
2221 oid := p_oid;
2222 ELSE
2223 oid := bism_utils.get_guid;
2224 END IF;
2225
2226 if ret = 'y' then
2227 if tempTimeC is not null then
2228 timeC := tempTimeC;
2229 else
2230 timeC := sysdate;
2231 end if;
2232
2233 if tempTimeM is not null then
2234 timeM := tempTimeM;
2235 else
2236 timeM := sysdate;
2237 end if;
2238 if p_extAttrs_Clob is not null then
2239 -- convert CLOB representation of extensible attributes into XMLType representation
2240 p_extAttrs := sys.xmltype.createXML(p_extAttrs_Clob);
2241 end if;
2242
2243 insert into BISM_OBJECTS
2244 (USER_VISIBLE, OBJECT_TYPE_ID,VERSION, TIME_DATE_CREATED, TIME_DATE_MODIFIED, OBJECT_ID, CONTAINER_ID, FOLDER_ID, CREATED_BY,
2245 LAST_MODIFIED_BY, OBJECT_NAME, TITLE, APPLICATION, DATABASE, DESCRIPTION, KEYWORDS, XML, APPLICATION_SUBTYPE1, COMP_SUBTYPE1,
2246 COMP_SUBTYPE2, COMP_SUBTYPE3,EXTENSIBLE_ATTRIBUTES,TIME_DATE_LAST_ACCESSED) values ('Y',0100,p_version,timeC,timeM,
2247 oid,null,p_fid,created_subid,modified_subid,p_object_name,p_title,p_application,p_database,p_desc,p_keywords,
2248 null,p_appsubtype1,p_compsubtype1,p_compsubtype2,p_compsubtype3,p_extAttrs,timeC) ;
2249 else
2250 raise_application_error(-20104,'Unexpected return value');
2251 end if;
2252
2253 return oid;
2254 end create_subcontext_30;
2255
2256 procedure bind
2257 (p_creator bism_subjects.SUBJECT_NAME%type,
2258 p_modifier bism_subjects.SUBJECT_NAME%type,
2259 p_subject_id bism_subjects.SUBJECT_ID%type,
2260 p_visible bism_objects.USER_VISIBLE%type,
2261 p_obj_type_id bism_objects.OBJECT_TYPE_ID%type,
2262 p_version bism_objects.VERSION%type,
2263 p_time_created bism_objects.TIME_DATE_CREATED%type,
2264 p_time_modified bism_objects.TIME_DATE_MODIFIED%type,
2265 p_oid bism_objects.OBJECT_ID%type,
2266 p_container_id bism_objects.CONTAINER_ID%type,
2267 p_fid bism_objects.FOLDER_ID%type,
2268 p_obj_name bism_objects.OBJECT_NAME%type,
2269 p_title bism_objects.TITLE%type,
2270 p_application bism_objects.APPLICATION%type,
2271 p_database bism_objects.DATABASE%type,
2272 p_desc bism_objects.DESCRIPTION%type,
2273 p_keywords bism_objects.KEYWORDS%type,
2274 p_xml bism_objects.XML%type,
2275 p_appsubtype1 bism_objects.APPLICATION_SUBTYPE1%type,
2276 p_compsubtype1 bism_objects.COMP_SUBTYPE1%type,
2277 p_compsubtype2 bism_objects.COMP_SUBTYPE2%type,
2278 p_compsubtype3 bism_objects.COMP_SUBTYPE3%type,
2279 p_container_id2 bism_aggregates.CONTAINER_ID%type,
2280 p_aggregate_info bism_aggregates.AGGREGATE_INFO%type,
2281 p_ext_attrs_clob CLOB,
2282 p_time_last_loaded bism_objects.TIME_DATE_LAST_ACCESSED%type
2283 )
2284 is
2285 ret varchar2(1);
2286 created_subid bism_subjects.subject_id%type;
2287 modified_subid bism_subjects.subject_id%type;
2288 p_ext_attrs bism_objects.extensible_attributes%type := null;
2289 begin
2290 begin
2291 select subject_id into created_subid from bism_subjects where subject_name = p_creator and subject_type ='u';
2292 if p_creator <> p_modifier then
2293 select subject_id into modified_subid from bism_subjects where subject_name = p_modifier and subject_type = 'u';
2294 else
2295 modified_subid := created_subid;
2296 end if;
2297 exception
2298 when no_data_found then
2299 raise_application_error(-20503,'User not found');
2300 /* let other exceptions bubble up */
2301 end;
2302
2303 begin
2304 ret := bism_access_control.check_ins_access(p_fid,p_subject_id);
2305 end;
2306
2307 if ret = 'y' then
2308 if p_ext_attrs_clob is not null then
2309 -- convert CLOB representation of extensible attributes into XMLType representation
2310 p_ext_attrs := sys.xmltype.createXML(p_ext_attrs_clob);
2311 end if;
2312
2313 insert into BISM_OBJECTS (USER_VISIBLE,OBJECT_TYPE_ID,VERSION,TIME_DATE_CREATED,TIME_DATE_MODIFIED,OBJECT_ID,CONTAINER_ID,FOLDER_ID,
2314 CREATED_BY,LAST_MODIFIED_BY,OBJECT_NAME,TITLE,APPLICATION,DATABASE,DESCRIPTION,KEYWORDS,XML,
2315 APPLICATION_SUBTYPE1,COMP_SUBTYPE1,COMP_SUBTYPE2,COMP_SUBTYPE3,EXTENSIBLE_ATTRIBUTES,TIME_DATE_LAST_ACCESSED)
2316 values
2317 (p_visible,p_obj_type_id,p_version,p_time_created,p_time_modified,p_oid,p_container_id,p_fid,created_subid,modified_subid,p_obj_name,
2318 p_title,p_application,p_database,p_desc,p_keywords,p_xml,p_appsubtype1,p_compsubtype1,p_compsubtype2,p_compsubtype3,p_ext_attrs,
2319 p_time_last_loaded);
2320 else
2321 raise_application_error(-20104,'Unexpected return value');
2322 end if;
2323
2324 /* need to use a different container id since bism_aggregate doesn't take null for container_id for top level object */
2325 bind_aggregate(p_container_id2,p_oid,p_aggregate_info);
2326 end bind;
2327
2328
2329 procedure bind
2330 (p_creator bism_subjects.SUBJECT_NAME%type,
2331 p_modifier bism_subjects.SUBJECT_NAME%type,
2332 p_subject_id bism_subjects.SUBJECT_ID%type,
2333 p_visible bism_objects.USER_VISIBLE%type,
2334 p_obj_type_id bism_objects.OBJECT_TYPE_ID%type,
2335 p_version bism_objects.VERSION%type,
2336 p_time_created bism_objects.TIME_DATE_CREATED%type,
2337 p_time_modified bism_objects.TIME_DATE_MODIFIED%type,
2338 p_oid bism_objects.OBJECT_ID%type,
2339 p_container_id bism_objects.CONTAINER_ID%type,
2340 p_fid bism_objects.FOLDER_ID%type,
2341 p_obj_name bism_objects.OBJECT_NAME%type,
2342 p_title bism_objects.TITLE%type,
2343 p_application bism_objects.APPLICATION%type,
2344 p_database bism_objects.DATABASE%type,
2345 p_desc bism_objects.DESCRIPTION%type,
2346 p_keywords bism_objects.KEYWORDS%type,
2347 p_xml bism_objects.XML%type,
2348 p_appsubtype1 bism_objects.APPLICATION_SUBTYPE1%type,
2349 p_compsubtype1 bism_objects.COMP_SUBTYPE1%type,
2350 p_compsubtype2 bism_objects.COMP_SUBTYPE2%type,
2351 p_compsubtype3 bism_objects.COMP_SUBTYPE3%type,
2352 p_container_id2 bism_aggregates.CONTAINER_ID%type,
2353 p_aggregate_info bism_aggregates.AGGREGATE_INFO%type
2354 )
2355 is
2356 ret varchar2(1);
2357 created_subid bism_subjects.subject_id%type;
2358 modified_subid bism_subjects.subject_id%type;
2359 begin
2360 begin
2361 select subject_id into created_subid from bism_subjects where subject_name = p_creator and subject_type ='u';
2362 if p_creator <> p_modifier then
2363 select subject_id into modified_subid from bism_subjects where subject_name = p_modifier and subject_type = 'u';
2364 else
2365 modified_subid := created_subid;
2366 end if;
2367 exception
2368 when no_data_found then
2369 raise_application_error(-20503,'User not found');
2370 /* let other exceptions bubble up */
2371 end;
2372
2373 begin
2374 ret := bism_access_control.check_ins_access(p_fid,p_subject_id);
2375 end;
2376
2377 if ret = 'y' then
2378 insert into BISM_OBJECTS (USER_VISIBLE,OBJECT_TYPE_ID,VERSION,TIME_DATE_CREATED,TIME_DATE_MODIFIED,OBJECT_ID,CONTAINER_ID,FOLDER_ID,
2379 CREATED_BY,LAST_MODIFIED_BY,OBJECT_NAME,TITLE,APPLICATION,DATABASE,DESCRIPTION,KEYWORDS,XML,
2380 APPLICATION_SUBTYPE1,COMP_SUBTYPE1,COMP_SUBTYPE2,COMP_SUBTYPE3) values (p_visible,p_obj_type_id,p_version,
2381 p_time_created,p_time_modified,p_oid,p_container_id,p_fid,created_subid,modified_subid,p_obj_name,p_title,
2382 p_application,p_database,p_desc,p_keywords,p_xml,p_appsubtype1,p_compsubtype1,p_compsubtype2,p_compsubtype3);
2383 else
2384 raise_application_error(-20104,'Unexpected return value');
2385 end if;
2386
2387 /* need to use a different container id since bism_aggregate doesn't take null for container_id for top level object */
2388 bind_aggregate(p_container_id2,p_oid,p_aggregate_info);
2389 end bind;
2390
2391 procedure bind_aggregate
2392 (p_container_id bism_aggregates.CONTAINER_ID%type,
2393 p_containee_id bism_aggregates.CONTAINEE_ID%type,
2394 p_aggregate_info bism_aggregates.AGGREGATE_INFO%type)
2395 is
2396 begin
2397 insert into bism_aggregates (container_id, containee_id, aggregate_info) values (p_container_id,p_containee_id,p_aggregate_info);
2398 end bind_aggregate;
2399
2400 function list_bindings
2401 (p_fid bism_objects.FOLDER_ID%type,
2402 p_subid bism_subjects.SUBJECT_ID%type)
2403 return myrctype
2404 is
2405 rc myrctype;
2406 begin
2407 open rc for select object_name,object_type_id,object_id from bism_objects where folder_id = p_fid and user_visible = 'Y' and 'y' = bism_access_control.check_list_access(p_fid,p_subid);
2408 return rc;
2409 end list_bindings;
2410
2411 procedure rebind
2412 (p_creator bism_subjects.SUBJECT_NAME%type,
2413 p_modifier bism_subjects.SUBJECT_NAME%type,
2414 p_subject_id bism_subjects.SUBJECT_ID%type,
2415 p_visible bism_objects.USER_VISIBLE%type,
2416 p_obj_type_id bism_objects.OBJECT_TYPE_ID%type,
2417 p_version bism_objects.VERSION%type,
2418 p_time_created bism_objects.TIME_DATE_CREATED%type,
2419 p_time_modified bism_objects.TIME_DATE_MODIFIED%type,
2420 p_oid bism_objects.OBJECT_ID%type,
2421 p_container_id bism_objects.CONTAINER_ID%type,
2422 p_fid bism_objects.FOLDER_ID%type,
2423 p_obj_name bism_objects.OBJECT_NAME%type,
2424 p_title bism_objects.TITLE%type,
2425 p_application bism_objects.APPLICATION%type,
2426 p_database bism_objects.DATABASE%type,
2427 p_desc bism_objects.DESCRIPTION%type,
2428 p_keywords bism_objects.KEYWORDS%type,
2429 p_xml bism_objects.XML%type,
2430 p_appsubtype1 bism_objects.APPLICATION_SUBTYPE1%type,
2431 p_compsubtype1 bism_objects.COMP_SUBTYPE1%type,
2432 p_compsubtype2 bism_objects.COMP_SUBTYPE2%type,
2433 p_compsubtype3 bism_objects.COMP_SUBTYPE3%type,
2434 p_ext_attrs_clob CLOB,
2435 p_time_last_loaded bism_objects.TIME_DATE_LAST_ACCESSED%type,
2436 p_aggregate_info bism_aggregates.AGGREGATE_INFO%type,
2437 p_obj_is_top_level varchar2)
2438 is
2439 ret varchar2(1);
2440 created_subid bism_subjects.subject_id%type;
2441 modified_subid bism_subjects.subject_id%type;
2442 p_ext_attrs bism_objects.extensible_attributes%type := null;
2443 begin
2444 begin
2445 select subject_id into created_subid from bism_subjects where subject_name = p_creator and subject_type ='u';
2446 select subject_id into modified_subid from bism_subjects where subject_name = p_modifier and subject_type ='u';
2447 exception
2448 when no_data_found then
2449 raise_application_error(-20503,'User not found');
2450 /* let other exceptions bubble up */
2451 end;
2452
2453 -- if the object is top level, we update the row, otherwise, we insert
2454 if p_obj_is_top_level = 'Y' then
2455 -- object level security, rebind requires WRITE privilege on the object ONLY
2456 -- nothing to do with parent folder
2457 ret := bism_access_control.check_upd_access(null,p_oid,'n',p_subject_id);
2458 if ret = 'y' then
2459 if p_ext_attrs_clob is not null then
2460 -- convert CLOB representation of extensible attributes into XMLType representation
2461 p_ext_attrs := sys.xmltype.createXML(p_ext_attrs_clob);
2462 end if;
2463
2464 update bism_objects
2465 set USER_VISIBLE=p_visible,
2466 OBJECT_TYPE_ID=p_obj_type_id,
2467 VERSION=p_version,
2468 TIME_DATE_CREATED=p_time_created,
2469 TIME_DATE_MODIFIED=p_time_modified,
2470 OBJECT_ID=p_oid,
2471 CONTAINER_ID=p_container_id,
2472 FOLDER_ID=p_fid,
2473 CREATED_BY=created_subid,
2474 LAST_MODIFIED_BY=modified_subid,
2475 OBJECT_NAME=p_obj_name,
2476 TITLE=p_title,
2477 APPLICATION=p_application,
2478 DATABASE=p_database,
2479 DESCRIPTION=p_desc,
2480 KEYWORDS=p_keywords,
2481 XML=p_xml,
2482 APPLICATION_SUBTYPE1=p_appsubtype1,
2483 COMP_SUBTYPE1=p_compsubtype1,
2484 COMP_SUBTYPE2=p_compsubtype2,
2485 COMP_SUBTYPE3=p_compsubtype3,
2486 EXTENSIBLE_ATTRIBUTES=p_ext_attrs,
2487 TIME_DATE_LAST_ACCESSED=p_time_last_loaded
2488 where user_visible = 'Y' and object_id = p_oid;
2489 end if;
2490
2491 else
2492 -- if rebind is called to insert an aggregate object, dont check privs
2493 if p_ext_attrs_clob is not null then
2494 -- convert CLOB representation of extensible attributes into XMLType representation
2495 p_ext_attrs := sys.xmltype.createXML(p_ext_attrs_clob);
2496 end if;
2497
2498 insert into bism_objects (USER_VISIBLE, OBJECT_TYPE_ID,VERSION, TIME_DATE_CREATED, TIME_DATE_MODIFIED, OBJECT_ID, CONTAINER_ID, FOLDER_ID, CREATED_BY,
2499 LAST_MODIFIED_BY, OBJECT_NAME, TITLE, APPLICATION, DATABASE, DESCRIPTION, KEYWORDS, XML, APPLICATION_SUBTYPE1, COMP_SUBTYPE1,
2500 COMP_SUBTYPE2, COMP_SUBTYPE3, EXTENSIBLE_ATTRIBUTES, TIME_DATE_LAST_ACCESSED)
2501 values
2502 (
2503 p_visible,p_obj_type_id,p_version,p_time_created,p_time_modified,p_oid,
2504 p_container_id,p_fid,created_subid,modified_subid,p_obj_name,p_title,p_application,
2505 p_database,p_desc,p_keywords,p_xml,p_appsubtype1,p_compsubtype1,p_compsubtype2,p_compsubtype3,
2506 p_ext_attrs,p_time_last_loaded
2507 );
2508 bind_aggregate(p_container_id,p_oid,p_aggregate_info);
2509 end if;
2510 end rebind;
2511
2512
2513 procedure rebind
2514 (p_creator bism_subjects.SUBJECT_NAME%type,
2515 p_modifier bism_subjects.SUBJECT_NAME%type,
2516 p_subject_id bism_subjects.SUBJECT_ID%type,
2517 p_visible bism_objects.USER_VISIBLE%type,
2518 p_obj_type_id bism_objects.OBJECT_TYPE_ID%type,
2519 p_version bism_objects.VERSION%type,
2520 p_time_created bism_objects.TIME_DATE_CREATED%type,
2521 p_time_modified bism_objects.TIME_DATE_MODIFIED%type,
2522 p_oid bism_objects.OBJECT_ID%type,
2523 p_container_id bism_objects.CONTAINER_ID%type,
2524 p_fid bism_objects.FOLDER_ID%type,
2525 p_obj_name bism_objects.OBJECT_NAME%type,
2526 p_title bism_objects.TITLE%type,
2527 p_application bism_objects.APPLICATION%type,
2528 p_database bism_objects.DATABASE%type,
2529 p_desc bism_objects.DESCRIPTION%type,
2530 p_keywords bism_objects.KEYWORDS%type,
2531 p_xml bism_objects.XML%type,
2532 p_appsubtype1 bism_objects.APPLICATION_SUBTYPE1%type,
2533 p_compsubtype1 bism_objects.COMP_SUBTYPE1%type,
2534 p_compsubtype2 bism_objects.COMP_SUBTYPE2%type,
2535 p_compsubtype3 bism_objects.COMP_SUBTYPE3%type)
2536 is
2537 ret varchar2(1);
2538 created_subid bism_subjects.subject_id%type;
2539 modified_subid bism_subjects.subject_id%type;
2540 begin
2541 begin
2542 select subject_id into created_subid from bism_subjects where subject_name = p_creator and subject_type ='u';
2543 select subject_id into modified_subid from bism_subjects where subject_name = p_modifier and subject_type ='u';
2544 exception
2545 when no_data_found then
2546 raise_application_error(-20503,'User not found');
2547 /* let other exceptions bubble up */
2548 end;
2549
2550 -- object level security, rebind requires WRITE privilege on the object ONLY
2551 -- nothing to do with parent folder
2552 ret := bism_access_control.check_upd_access(null,p_oid,'n',p_subject_id);
2553 if ret = 'y' then
2554 update bism_objects set USER_VISIBLE=p_visible,OBJECT_TYPE_ID=p_obj_type_id,VERSION=p_version,TIME_DATE_CREATED=p_time_created,
2555 TIME_DATE_MODIFIED=p_time_modified,OBJECT_ID=p_oid,CONTAINER_ID=p_container_id,FOLDER_ID=p_fid,
2556 CREATED_BY=created_subid,LAST_MODIFIED_BY=modified_subid,OBJECT_NAME=p_obj_name,TITLE=p_title,
2557 APPLICATION=p_application,DATABASE=p_database,DESCRIPTION=p_desc,KEYWORDS=p_keywords,XML=p_xml,
2558 APPLICATION_SUBTYPE1=p_appsubtype1,COMP_SUBTYPE1=p_compsubtype1,COMP_SUBTYPE2=p_compsubtype2,
2559 COMP_SUBTYPE3=p_compsubtype3 where user_visible = 'Y' and object_id = p_oid;
2560 end if;
2561 end rebind;
2562 -- end refactoring code for init, bind, rebind, createSubcontext
2563
2564
2565 -- new methods for object level security feature
2566 function add_entries(oid in raw,acllist in out nocopy bism_acl_obj_t,myid in raw,cascade_to_subfolders in varchar2,cascade_to_objs in varchar2,topfolder in varchar2,isfolder in varchar2)
2567 return bism_acl_obj_t
2568 is
2569 newguid bism_objects.object_id%type:= null;
2570 username bism_subjects.subject_name%type;
2571 objname bism_objects.object_name%type;
2572 priv number(2);
2573 grantorpriv number(2);
2574 newusers bism_acl_obj_t := bism_acl_obj_t();
2575 dummy bism_acl_obj_t := bism_acl_obj_t();
2576 sid raw(16);
2577 begin
2578 if acllist.count = 0 then
2579 return null;
2580 end if;
2581
2582 select max(privilege) into grantorpriv from bism_permissions where object_id = oid and subject_id in (select group_id from bism_groups where user_id = myid);
2583
2584 if grantorpriv is null then
2585 begin
2586 -- see if object exists
2587 select object_name into objname from bism_objects where object_id = oid;
2588 raise_application_error(BISM_ERRORCODES.NO_PRIVILEGES,'Grantor has no privileges for folder');
2589 exception
2590 when no_data_found then
2591 raise_application_error(BISM_ERRORCODES.OBJECT_NOT_FOUND,'Object not found');
2592 end;
2593 end if;
2594
2595 if grantorpriv < 50 then
2596 raise_application_error(BISM_ERRORCODES.INSUFFICIENT_PRIVILEGES,'Grantor has insufficient privileges');
2597 end if;
2598
2599 if topfolder = 'y' or topfolder = 'Y' then
2600 for i in 1..acllist.count
2601 loop
2602 begin
2603 select subject_id into sid from bism_subjects where subject_name = acllist(i).subjname;
2604 acllist(i).subjid := sid;
2605 exception
2606 when no_data_found then
2607 --generate a new id for the new user
2608 newguid := bism_utils.get_guid;
2609 -- dont throw exception, instead return the acl object of the new user to
2610 -- the caller so that the JdbcAdapter can cache the name to Id mapping
2611 newusers.extend();
2612 newusers(newusers.count) := bism_acl_obj(acllist(i).subjname,acllist(i).privilege, newguid);
2613 insert into bism_subjects (SUBJECT_ID, SUBJECT_NAME, SUBJECT_TYPE) values (newguid,acllist(i).subjname,'u');
2614 insert into bism_groups (USER_ID, GROUP_ID) values(newguid,newguid);
2615 acllist(i).subjid := newguid;
2616 end;
2617 end loop;
2618 end if;
2619
2620 -- ok the grantor has enough privilege, proceed with adding entries
2621 for i in 1..acllist.count
2622 loop
2623 if acllist(i).subjid is not null then
2624 delete from bism_permissions where object_id = oid and subject_id = acllist(i).subjid;
2625 end if;
2626 end loop;
2627
2628 for i in 1..acllist.count
2629 loop
2630 if acllist(i).subjid is not null then
2631 insert into bism_permissions (SUBJECT_ID, OBJECT_ID, PRIVILEGE) values(acllist(i).subjid,oid,acllist(i).privilege);
2632 end if;
2633 end loop;
2634
2635 -- do it for folder only
2636 if isfolder = 'y' or isfolder = 'Y' then
2637 -- new functionality (ccchow)
2638 -- cascading acl to all existing objects within the folder
2639 if cascade_to_objs = 'y' or cascade_to_objs = 'Y' then
2640 -- first find all the objects within the folder
2641 for i in (select object_id, object_type_id from bism_objects where folder_id = oid and user_visible = 'Y')
2642 loop
2643 -- remove all existing entries
2644 for j in 1..acllist.count
2645 loop
2646 if acllist(j).subjid is not null then
2647 delete from bism_permissions where object_id = i.object_id and subject_id = acllist(j).subjid;
2648 end if;
2649 end loop;
2650
2651 -- add the new entries
2652 for j in 1..acllist.count loop
2653 if acllist(j).subjid is not null then
2654 -- for non-folder, need to map ADDFOLDER to LIST and LIST to nothing
2655 if i.object_type_id = 100 then
2656 insert into bism_permissions (SUBJECT_ID, OBJECT_ID, PRIVILEGE) values(acllist(j).subjid,i.object_id,acllist(j).privilege);
2657 else
2658 if acllist(j).privilege > 10 then
2659 if acllist(j).privilege = 30 then
2660 insert into bism_permissions (SUBJECT_ID, OBJECT_ID, PRIVILEGE) values(acllist(j).subjid,i.object_id,20);
2661 else
2662 insert into bism_permissions (SUBJECT_ID, OBJECT_ID, PRIVILEGE) values(acllist(j).subjid,i.object_id,acllist(j).privilege);
2663 end if;
2664 end if;
2665 end if;
2666 end if;
2667 end loop;
2668 end loop;
2669 end if;
2670
2671 if cascade_to_subfolders = 'y' or cascade_to_subfolders = 'Y' then
2672 for i in (select object_id from bism_objects where folder_id = oid and object_type_id = 100 and user_visible='Y' )
2673 loop
2674 -- ignore the return value dummy because it should be null
2675 -- we only create new user in first level frame
2676 -- (ccchow) always cascade to existing objects in subfolders as well, as in NT
2677 dummy := add_entries(i.object_id,acllist,myid,cascade_to_subfolders,'y','n','y');
2678 end loop;
2679 end if;
2680 end if;
2681 return newusers;
2682 end add_entries;
2683
2684 procedure add_entries_30(oid in raw,acllist in CLOB,myid in raw,cascade_to_subfolders in varchar2,cascade_to_objs in varchar2,topfolder in varchar2,isfolder in varchar2, aclseparator in varchar2)
2685 -- return bism_acl_obj_t
2686 is
2687 newguid bism_objects.object_id%type:= null;
2688 username bism_subjects.subject_name%type;
2689 objname bism_objects.object_name%type;
2690 priv number(2);
2691 grantorpriv number(2);
2692 sid bism_subjects.SUBJECT_ID%TYPE;
2693 startpos INTEGER := 1;
2694 usrnm BISM_SUBJECTS.SUBJECT_NAME%TYPE;
2695 usrpriv BISM_PERMISSIONS.PRIVILEGE%TYPE;
2696 usrtype BISM_SUBJECTS.SUBJECT_TYPE%TYPE;
2697 begin
2698 if (acllist is null) or (DBMS_LOB.getlength(acllist) = 0) then
2699 return;
2700 end if;
2701
2702 select max(privilege) into grantorpriv from bism_permissions where object_id = oid and subject_id in (select group_id from bism_groups where user_id = myid);
2703
2704 if grantorpriv is null then
2705 begin
2706 -- see if object exists
2707 select object_name into objname from bism_objects where object_id = oid;
2708 raise_application_error(BISM_ERRORCODES.NO_PRIVILEGES,'Grantor has no privileges for folder');
2709 exception
2710 when no_data_found then
2711 raise_application_error(BISM_ERRORCODES.OBJECT_NOT_FOUND,'Object not found');
2712 end;
2713 end if;
2714
2715 if grantorpriv < 50 then
2716 raise_application_error(BISM_ERRORCODES.INSUFFICIENT_PRIVILEGES,'Grantor has insufficient privileges');
2717 end if;
2718
2719 loop
2720 if startpos <> 0 then
2721 begin
2722 -- parse acllist to get user name and privilege
2723 usrnm := get_next_element(acllist,aclseparator,startpos);
2724 usrpriv := get_next_element(acllist,aclseparator,startpos);
2725 usrtype := get_next_element(acllist,aclseparator,startpos);
2726 -- always do this for the top level objects: first time around
2727 if topfolder = 'Y' or topfolder = 'y' then
2728 begin
2729 select subject_id into sid from bism_subjects where subject_name = usrnm;
2730 exception
2731 when no_data_found then
2732 --generate a new id for the new user
2733 newguid := bism_utils.get_guid;
2734 insert into bism_subjects (SUBJECT_ID, SUBJECT_NAME, SUBJECT_TYPE) values (newguid,usrnm,usrtype);
2735 insert into bism_groups (USER_ID, GROUP_ID) values(newguid,newguid);
2736 sid := newguid;
2737 end;
2738 else
2739 -- same as in topfolder but there won't be an exception
2740 -- because we already inserted the user if he wasn't present
2741 select subject_id into sid from bism_subjects where subject_name = usrnm;
2742 end if;
2743
2744 -- ok the grantor has enough privilege, proceed with adding entries
2745 if sid is not null then
2746 delete from bism_permissions where object_id = oid and subject_id = sid;
2747 insert into bism_permissions (SUBJECT_ID, OBJECT_ID, PRIVILEGE) values(sid,oid,usrpriv);
2748 end if;
2749
2750 -- do it for folder only
2751 if isfolder = 'Y' or isfolder = 'y' then
2752 -- new functionality (ccchow)
2753 -- cascading acl to all existing objects within the folder
2754 if cascade_to_objs = 'Y' or cascade_to_objs = 'y' then
2755 -- first find all the objects within the folder
2756 for i in (select object_id, object_type_id from bism_objects where folder_id = oid and user_visible = 'Y')
2757 loop
2758 -- remove all existing entries
2759 if sid is not null then
2760 delete from bism_permissions where object_id = i.object_id and subject_id = sid;
2761 -- add the new entries
2762 -- for non-folder, need to map ADDFOLDER to LIST and LIST to nothing
2763 if i.object_type_id = 100 then
2764 insert into bism_permissions (SUBJECT_ID, OBJECT_ID, PRIVILEGE) values(sid,i.object_id,usrpriv);
2765 else
2766 if usrpriv > 10 then
2767 if usrpriv = 30 then
2768 insert into bism_permissions (SUBJECT_ID, OBJECT_ID, PRIVILEGE) values(sid,i.object_id,20);
2769 else
2770 insert into bism_permissions (SUBJECT_ID, OBJECT_ID, PRIVILEGE) values(sid,i.object_id,usrpriv);
2771 end if;
2772 end if;
2773 end if;
2774 end if;
2775 end loop;
2776 end if;
2777
2778 end if; -- end of if folders only
2779
2780 end; -- inside acllist loop
2781 else
2782 goto exit_loop;
2783 end if;
2784 end loop;
2785 <<exit_loop>>
2786
2787 -- do it for folder only
2788 if (isfolder = 'Y' or isfolder = 'y') and (cascade_to_subfolders = 'Y' or cascade_to_subfolders = 'y') then
2789 for i in (select object_id from bism_objects where folder_id = oid and object_type_id = 100 and user_visible='Y' )
2790 loop
2791 add_entries_30(i.object_id,acllist,myid,cascade_to_subfolders,'Y','N','Y',aclseparator);
2792 end loop;
2793 end if;
2794 -- return newusers;
2795 end add_entries_30;
2796
2797 function remove_entries(oid in raw,acllist in out nocopy bism_acl_obj_t,myid in raw,cascade_to_subfolders in varchar2,cascade_to_objs in varchar2,topfolder in varchar2,isfolder in varchar2)
2798 return bism_chararray_t
2799 is
2800 username bism_subjects.subject_name%type;
2801 objname bism_objects.object_name%type;
2802 priv number(2);
2803 grantorpriv number(2);
2804 errormsgs bism_chararray_t := bism_chararray_t();
2805 childerrormsgs bism_chararray_t := bism_chararray_t();
2806 errors_occured boolean;
2807 sid raw(16);
2808
2809 -- used for cascading acl to objects
2810 type oid_t is table of bism_objects.object_id%type;
2811 oid_var oid_t := oid_t();
2812 begin
2813 if acllist is null then
2814 return null;
2815 end if;
2816
2817 select max(privilege) into grantorpriv from bism_permissions where object_id = oid and subject_id in (select group_id from bism_groups where user_id = myid);
2818
2819 if grantorpriv is null then
2820 begin
2821 -- see if object exists
2822 select object_name into objname from bism_objects where object_id = oid;
2823 raise_application_error(BISM_ERRORCODES.NO_PRIVILEGES,'Grantor has no privileges for folder');
2824 exception
2825 when no_data_found then
2826 raise_application_error(BISM_ERRORCODES.OBJECT_NOT_FOUND,'Object not found');
2827 end;
2828 end if;
2829
2830 if grantorpriv < 50 then
2831 raise_application_error(BISM_ERRORCODES.INSUFFICIENT_PRIVILEGES,'Grantor has insufficient privileges');
2832 end if;
2833
2834 if topfolder = 'Y' or topfolder = 'y' then
2835 for i in 1..acllist.count
2836 loop
2837 begin
2838 select subject_id into sid from bism_subjects where subject_name = acllist(i).subjname;
2839 acllist(i).subjid := sid;
2840 exception
2841 when no_data_found then
2842 errors_occured := true;
2843 errormsgs.extend();
2844 errormsgs(errormsgs.count) := 'User '|| acllist(i).subjname || ' not found';
2845 acllist(i).subjid := null;
2846 acllist(i).subjname := null;
2847 acllist(i).privilege := 0;
2848 end;
2849 end loop;
2850 end if;
2851
2852 -- ok the user has enough privilege, proceed with removing entries
2853 for i in 1..acllist.count
2854 loop
2855 if acllist(i).subjid is not null then
2856 delete from bism_permissions where object_id = oid and subject_id = acllist(i).subjid;
2857 end if;
2858 end loop;
2859
2860 -- do it for folder only
2861 if isfolder = 'Y' or isfolder = 'y' then
2862 -- new functionality (ccchow)
2863 -- cascading acl to all existing objects within the folder
2864 if cascade_to_objs = 'Y' or cascade_to_objs = 'y' then
2865 -- first find all the objects within the folder
2866 select object_id bulk collect into oid_var from bism_objects where folder_id = oid and user_visible = 'Y';
2867
2868 if oid_var.COUNT > 0 then
2869 for i in oid_var.FIRST..oid_var.LAST
2870 loop
2871 -- remove the specified entries
2872 for j in 1..acllist.count
2873 loop
2874 if acllist(j).subjid is not null then
2875 delete from bism_permissions where object_id = oid_var(i) and subject_id = acllist(j).subjid;
2876 end if;
2877 end loop;
2878 end loop;
2879 end if;
2880 end if;
2881
2882 if cascade_to_subfolders = 'Y' or cascade_to_subfolders = 'y' then
2883 for i in (select object_id from bism_objects where folder_id = oid and object_type_id = 100 and user_visible='Y')
2884 loop
2885 -- ignore the return value childerrormsgs because it should be null
2886 -- we check for errors only when it is a top level folder and that means
2887 -- errormsgs in the top level frame should capture any errors
2888 childerrormsgs := remove_entries(i.object_id,acllist,myid,cascade_to_subfolders,'Y','N','Y');
2889 end loop;
2890 end if;
2891 end if;
2892
2893 return errormsgs;
2894 end remove_entries;
2895
2896 function remove_entries_30(oid in raw,acllist in out nocopy CLOB,myid in raw,cascade_to_subfolders in varchar2,cascade_to_objs in varchar2,topfolder in varchar2,isfolder in varchar2, aclseparator in varchar2)
2897 --return bism_chararray_t
2898 return varchar2
2899 is
2900 username bism_subjects.subject_name%type;
2901 objname bism_objects.object_name%type;
2902 priv number(2);
2903 grantorpriv number(2);
2904 errormsgs varchar2(32767) := '';
2905 childerrormsgs varchar2(32767) := '';
2906 -- errormsgs bism_chararray_t := bism_chararray_t();
2907 -- childerrormsgs bism_chararray_t := bism_chararray_t();
2908 errors_occured boolean;
2909 sid bism_subjects.SUBJECT_ID%TYPE;
2910 startpos INTEGER := 1;
2911 usrnm BISM_SUBJECTS.SUBJECT_NAME%TYPE;
2912 usrpriv BISM_PERMISSIONS.PRIVILEGE%TYPE;
2913
2914 -- used for cascading acl to objects
2915 type oid_t is table of bism_objects.object_id%type;
2916 oid_var oid_t := oid_t();
2917 begin
2918 if (acllist is null) or (DBMS_LOB.getlength(acllist) = 0) then
2919 return null;
2920 end if;
2921
2922 select max(privilege) into grantorpriv from bism_permissions where object_id = oid and subject_id in (select group_id from bism_groups where user_id = myid);
2923
2924 if grantorpriv is null then
2925 begin
2926 -- see if object exists
2927 select object_name into objname from bism_objects where object_id = oid;
2928 raise_application_error(BISM_ERRORCODES.NO_PRIVILEGES,'Grantor has no privileges for folder');
2929 exception
2930 when no_data_found then
2931 raise_application_error(BISM_ERRORCODES.OBJECT_NOT_FOUND,'Object not found');
2932 end;
2933 end if;
2934
2935 if grantorpriv < 50 then
2936 raise_application_error(BISM_ERRORCODES.INSUFFICIENT_PRIVILEGES,'Grantor has insufficient privileges');
2937 end if;
2938
2939 loop
2940 if startpos <> 0 then
2941 begin
2942 -- parse acllist to get user name and privilege
2943 usrnm := get_next_element(acllist,aclseparator,startpos);
2944 usrpriv := get_next_element(acllist,aclseparator,startpos);
2945 -- always do this for the top level objects: first time around
2946 begin
2947 select subject_id into sid from bism_subjects where subject_name = usrnm;
2948 exception
2949 when no_data_found then
2950 errors_occured := true;
2951 errormsgs := errormsgs || ' ' || usrnm;
2952 sid := null;
2953 usrnm := null;
2954 usrpriv := 0;
2955 end;
2956 -- end if;
2957
2958 -- ok the user has enough privilege, proceed with removing entries
2959 if sid is not null then
2960 delete from bism_permissions where object_id = oid and subject_id = sid;
2961 end if;
2962
2963 -- do it for folder only
2964 if isfolder = 'Y' or isfolder = 'y' then
2965 -- new functionality (ccchow)
2966 -- cascading acl to all existing objects within the folder
2967 if cascade_to_objs = 'Y' or cascade_to_objs = 'y' then
2968 -- first find all the objects within the folder
2969 select object_id bulk collect into oid_var from bism_objects where folder_id = oid and user_visible = 'Y';
2970
2971 if oid_var.COUNT > 0 then
2972 for i in oid_var.FIRST..oid_var.LAST
2973 loop
2974 -- remove the specified entries
2975 if sid is not null then
2976 delete from bism_permissions where object_id = oid_var(i) and subject_id = sid;
2977 end if;
2978 end loop;
2979 end if;
2980 end if;
2981
2982 end if; -- end of if folders only
2983
2984 end; -- inside acllist loop
2985 else
2986 goto exit_loop;
2987 end if;
2988 end loop;
2989 <<exit_loop>>
2990
2991 -- do it for folder only
2992 if (isfolder = 'Y' or isfolder = 'y') and (cascade_to_subfolders = 'Y' or cascade_to_subfolders = 'y') then
2993 for i in (select object_id from bism_objects where folder_id = oid and object_type_id = 100 and user_visible='Y')
2994 loop
2995 -- ignore the return value childerrormsgs because it should be null
2996 -- we check for errors only when it is a top level folder and that means
2997 -- errormsgs in the top level frame should capture any errors
2998 childerrormsgs := remove_entries_30(i.object_id,acllist,myid,cascade_to_subfolders,'Y','N','Y',aclseparator);
2999 end loop;
3000 end if;
3001
3002
3003 return errormsgs;
3004 end remove_entries_30;
3005
3006 -- new function (ccchow)
3007 -- basically, same as add_entires except replaces every acl
3008 function set_entries(oid in raw,acllist in out nocopy bism_acl_obj_t,myid in raw,cascade_to_subfolders in varchar2,cascade_to_objs in varchar2,topfolder in varchar2,isfolder in varchar2)
3009 return bism_acl_obj_t
3010 is
3011 newguid bism_objects.object_id%type:= null;
3012 objname bism_objects.object_name%type;
3013 username bism_subjects.subject_name%type;
3014 priv number(2);
3015 grantorpriv number(2);
3016 newusers bism_acl_obj_t := bism_acl_obj_t();
3017 dummy bism_acl_obj_t := bism_acl_obj_t();
3018 sid raw(16);
3019 begin
3020 -- make sure grantor has privilege and enough privilege
3021 select max(privilege) into grantorpriv from bism_permissions where object_id = oid and subject_id in (select group_id from bism_groups where user_id = myid);
3022
3023 if grantorpriv is null then
3024 begin
3025 -- see if object exists
3026 select object_name into objname from bism_objects where object_id = oid;
3027 raise_application_error(BISM_ERRORCODES.NO_PRIVILEGES,'Grantor has no privileges for folder');
3028 exception
3029 when no_data_found then
3030 raise_application_error(BISM_ERRORCODES.OBJECT_NOT_FOUND,'Object not found');
3031 end;
3032 end if;
3033
3034 if grantorpriv < 50 then
3035 raise_application_error(BISM_ERRORCODES.INSUFFICIENT_PRIVILEGES,'Grantor has insufficient privileges');
3036 end if;
3037
3038 if topfolder = 'Y' or topfolder = 'y' then
3039 for i in 1..acllist.count
3040 loop
3041 begin
3042 select subject_id into sid from bism_subjects where subject_name = acllist(i).subjname;
3043 acllist(i).subjid := sid;
3044 exception
3045 when no_data_found then
3046 --generate a new id for the new user
3047 newguid := bism_utils.get_guid;
3048 -- dont throw exception, instead return the acl object of the new user to
3049 -- the caller so that the JdbcAdapter can cache the name to Id mapping
3050 newusers.extend();
3051 newusers(newusers.count) := bism_acl_obj(acllist(i).subjname,acllist(i).privilege, newguid);
3052 insert into bism_subjects (SUBJECT_ID, SUBJECT_NAME, SUBJECT_TYPE) values (newguid,acllist(i).subjname,'u');
3053 insert into bism_groups (USER_ID, GROUP_ID) values(newguid,newguid);
3054 acllist(i).subjid := newguid;
3055 end;
3056 end loop;
3057 end if;
3058
3059 -- ok the grantor has enough privilege, proceed with setting entries
3060
3061 -- first remove all previous entries
3062 delete from bism_permissions where object_id = oid and subject_id <> myid;
3063
3064 -- insert entries for this object
3065 for i in 1..acllist.count loop
3066 if acllist(i).subjid is not null then
3067 insert into bism_permissions (SUBJECT_ID, OBJECT_ID, PRIVILEGE) values(acllist(i).subjid,oid,acllist(i).privilege);
3068 end if;
3069 end loop;
3070
3071 -- do it for folder only
3072 if isfolder = 'Y' or isfolder = 'y' then
3073 -- new functionality (ccchow)
3074 -- cascading acl to all existing objects within the folder
3075 if cascade_to_objs = 'Y' or cascade_to_objs = 'y' then
3076 -- first find all the objects within the folder
3077 for i in (select object_id, object_type_id from bism_objects where folder_id = oid and user_visible = 'Y')
3078 loop
3079 -- remove all existing entries
3080 delete from bism_permissions where object_id = i.object_id and subject_id <> myid;
3081
3082 -- add the new entries
3083 for j in 1..acllist.count
3084 loop
3085 if acllist(j).subjid is not null then
3086 -- for non-folder, need to map ADDFOLDER to LIST and LIST to nothing
3087 if i.object_type_id = 100 then
3088 insert into bism_permissions (SUBJECT_ID, OBJECT_ID, PRIVILEGE) values(acllist(j).subjid,i.object_id,acllist(j).privilege);
3089 else
3090 if acllist(j).privilege > 10 then
3091 if acllist(j).privilege = 30 then
3092 insert into bism_permissions (SUBJECT_ID, OBJECT_ID, PRIVILEGE) values(acllist(j).subjid,i.object_id,20);
3093 else
3094 insert into bism_permissions (SUBJECT_ID, OBJECT_ID, PRIVILEGE) values(acllist(j).subjid,i.object_id,acllist(j).privilege);
3095 end if;
3096 end if;
3097 end if;
3098 end if;
3099 end loop;
3100 end loop;
3101 end if;
3102
3103 if cascade_to_subfolders = 'Y' or cascade_to_subfolders = 'y' then
3104 for i in (select object_id from bism_objects where folder_id = oid and object_type_id = 100 and user_visible='Y' )
3105 loop
3106 -- ignore the return value dummy because it should be null
3107 -- we only create new user in first level frame
3108 -- (ccchow) always cascade to existing objects in subfolders as well, as in NT
3109 dummy := set_entries(i.object_id,acllist,myid,cascade_to_subfolders,'Y','N','Y');
3110 end loop;
3111 end if;
3112 end if;
3113
3114 return newusers;
3115 end set_entries;
3116
3117 -- new function (ccchow)
3118 -- basically, same as add_entires except replaces every acl
3119 procedure set_entries_30(oid in raw,acllist in out nocopy CLOB,myid in raw,cascade_to_subfolders in varchar2,cascade_to_objs in varchar2,topfolder in varchar2,isfolder in varchar2, aclseparator in varchar2)
3120 is
3121 newguid bism_objects.object_id%type:= null;
3122 objname bism_objects.object_name%type;
3123 username bism_subjects.subject_name%type;
3124 priv number(2);
3125 grantorpriv number(2);
3126 sid bism_subjects.SUBJECT_ID%TYPE;
3127 startpos INTEGER := 1;
3128 usrnm BISM_SUBJECTS.SUBJECT_NAME%TYPE;
3129 usrpriv BISM_PERMISSIONS.PRIVILEGE%TYPE;
3130 usrtype BISM_SUBJECTS.SUBJECT_TYPE%TYPE;
3131 firstTime boolean := true;
3132 begin
3133
3134 if (acllist is null) or (DBMS_LOB.getlength(acllist) = 0) then
3135 return;
3136 end if;
3137
3138 -- make sure grantor has privilege and enough privilege
3139 select max(privilege) into grantorpriv from bism_permissions where object_id = oid and subject_id in (select group_id from bism_groups where user_id = myid);
3140
3141 if grantorpriv is null then
3142 begin
3143 -- see if object exists
3144 select object_name into objname from bism_objects where object_id = oid;
3145 raise_application_error(BISM_ERRORCODES.NO_PRIVILEGES,'Grantor has no privileges for folder');
3146 exception
3147 when no_data_found then
3148 raise_application_error(BISM_ERRORCODES.OBJECT_NOT_FOUND,'Object not found');
3149 end;
3150 end if;
3151
3152 if grantorpriv < 50 then
3153 raise_application_error(BISM_ERRORCODES.INSUFFICIENT_PRIVILEGES,'Grantor has insufficient privileges');
3154 end if;
3155
3156 loop
3157 if startpos <> 0 then
3158 begin
3159 -- parse acllist to get user name and privilege
3160 usrnm := get_next_element(acllist,aclseparator,startpos);
3161 usrpriv := get_next_element(acllist,aclseparator,startpos);
3162 usrtype := get_next_element(acllist,aclseparator,startpos);
3163 -- always do this for the top level objects: first time around
3164 if topfolder = 'Y' or topfolder = 'y' then
3165 begin
3166 select subject_id into sid from bism_subjects where subject_name = usrnm;
3167 exception
3168 when no_data_found then
3169 --generate a new id for the new user
3170 newguid := bism_utils.get_guid;
3171 -- dont throw exception, instead return the acl object of the new user to
3172 -- the caller so that the JdbcAdapter can cache the name to Id mapping
3173 insert into bism_subjects (SUBJECT_ID, SUBJECT_NAME, SUBJECT_TYPE) values (newguid,usrnm,usrtype);
3174 insert into bism_groups (USER_ID, GROUP_ID) values(newguid,newguid);
3175 sid := newguid;
3176 end;
3177 else
3178 -- same as in topfolder but there won't be an exception
3179 -- because we already inserted the user if he wasn't present
3180 select subject_id into sid from bism_subjects where subject_name = usrnm;
3181 end if;
3182
3183 -- ok the grantor has enough privilege, proceed with setting entries
3184
3185 -- first remove all previous entries
3186 if firstTime = true then
3187 delete from bism_permissions where object_id = oid and subject_id <> myid;
3188 end if;
3189
3190 -- insert entries for this object
3191 if sid is not null then
3192 insert into bism_permissions (SUBJECT_ID, OBJECT_ID, PRIVILEGE) values(sid,oid,usrpriv);
3193 end if;
3194
3195 -- do it for folder only
3196 if isfolder = 'Y' or isfolder = 'y' then
3197 -- new functionality (ccchow)
3198 -- cascading acl to all existing objects within the folder
3199 if cascade_to_objs = 'Y' or cascade_to_objs = 'y' then
3200 -- first find all the objects within the folder
3201 for i in (select object_id, object_type_id from bism_objects where folder_id = oid and user_visible = 'Y')
3202 loop
3203 -- remove all existing entries
3204 if firstTime = true then
3205 delete from bism_permissions where object_id = i.object_id and subject_id <> myid;
3206 end if;
3207
3208 -- add the new entries
3209 if sid is not null then
3210 -- for non-folder, need to map ADDFOLDER to LIST and LIST to nothing
3211 if i.object_type_id = 100 then
3212 insert into bism_permissions (SUBJECT_ID, OBJECT_ID, PRIVILEGE) values(sid,i.object_id,usrpriv);
3213 else
3214 if usrpriv > 10 then
3215 if usrpriv = 30 then
3216 insert into bism_permissions (SUBJECT_ID, OBJECT_ID, PRIVILEGE) values(sid,i.object_id,20);
3217 else
3218 insert into bism_permissions (SUBJECT_ID, OBJECT_ID, PRIVILEGE) values(sid,i.object_id,usrpriv);
3219 end if;
3220 end if;
3221 end if;
3222 end if;
3223 end loop;
3224 end if;
3225 end if; -- end of if folders only
3226 firstTime := false;
3227
3228 end; -- inside acllist loop
3229 else
3230 goto exit_loop;
3231 end if;
3232 end loop;
3233 <<exit_loop>>
3234
3235 -- do it for folder only
3236 if (isfolder = 'Y' or isfolder = 'y') and (cascade_to_subfolders = 'Y' or cascade_to_subfolders = 'y') then
3237 for i in (select object_id from bism_objects where folder_id = oid and object_type_id = 100 and user_visible='Y' )
3238 loop
3239 set_entries_30(i.object_id,acllist,myid,cascade_to_subfolders,cascade_to_objs,'N','Y',aclseparator);
3240 -- set_entries_30(i.object_id,acllist,myid,cascade_to_subfolders,'Y','N','Y',aclseparator);
3241 end loop;
3242 end if;
3243
3244 end set_entries_30;
3245
3246 function get_privilege(oid raw, myid raw) return number
3247 is
3248 priv bism_permissions.privilege%type;
3249 begin
3250 select nvl(max(privilege),0) into priv from bism_permissions where object_id = oid and subject_id in (select group_id from bism_groups where user_id = myid);
3251 return priv;
3252 end get_privilege;
3253
3254 function list_dependents(p_fid bism_objects.FOLDER_ID%type, p_objname varchar2, p_myid raw)
3255 return myrctype
3256 is
3257 v_rc myrctype;
3258 begin
3259 open v_rc for
3260 with
3261 visible_table as
3262 ( -- find all objects that contain the object we are interested in.
3263 select /*+ INDEX(BISM_OBJECTS) */ object_name as object_name, object_id as object_id, object_type_id as object_type_id, user_visible as user_visible
3264 from bism_objects
3265 where object_id in
3266 (
3267 select /*+ INDEX(BISM_AGGREGATES) */ container_id from bism_aggregates
3268 where containee_id in
3269 (
3270 select /*+ INDEX(BISM_OBJECTS) */ object_id from bism_objects
3271 where
3272 folder_id = p_fid
3273 and
3274 object_name = p_objname
3275 and 'y' = bism_access_control.check_read_access(object_id, p_fid,'n', p_myid)
3276 )
3277 and container_id <> '30'
3278 )
3279 )
3280 select /*+ INDEX(BISM_OBJECTS) */ bism_core.get_object_full_name(object_id), object_type_id
3281 from bism_objects
3282 where
3283 object_id in ( select object_id from visible_table where user_visible = 'Y' )
3284 union
3285 select /*+ INDEX(BISM_OBJECTS) */ bism_core.get_object_full_name(object_id), object_type_id
3286 from bism_objects
3287 where object_id
3288 in
3289 (
3290 select distinct object_id from bism_objects c
3291 start with object_id
3292 in ( select object_id from visible_table where user_visible = 'N' )
3293 connect by prior container_id = object_id
3294 )
3295 and user_visible ='Y';
3296 return v_rc;
3297 end list_dependents;
3298
3299 procedure update_attribute(a_fid raw,a_obj_name varchar2,a_attr_name varchar2, a_attr_val varchar2, a_sub_id raw)
3300 is
3301 ret varchar2(1) := 'n';
3302 status number;
3303 v_uv bism_objects.user_visible%type := 'Y';
3304 created_subid raw(16) := null;
3305 modified_subid raw(16) := null;
3306 begin
3307
3308 if a_sub_id is null or length(a_sub_id) = 0 then
3309 raise_application_error(-20503,'User not found');
3310 end if;
3311
3312 ret := bism_core.check_modify_attrs_access(a_fid, a_obj_name,a_sub_id , status, 1);
3313
3314 if ret = 'y' then
3315 -- if created by and modified by were specified, look them up, we need them later
3316 if a_attr_name = 'created_by' or a_attr_name = 'CREATED_BY' then
3317 if a_attr_val is not null then
3318 begin
3319 select subject_id into created_subid from bism_subjects
3320 where
3321 subject_name = a_attr_val and subject_type ='u';
3322 EXECUTE IMMEDIATE 'update bism_objects set created_by = :1 where folder_id = :2 and user_visible = :3 and object_name = :4 '
3323 using created_subid,a_fid, v_uv , a_obj_name;
3324 exception
3325 when no_data_found then
3326 raise_application_error(-20503,'User not found');
3327 end;
3328 end if;
3329 elsif a_attr_name = 'last_modified_by' or a_attr_name = 'LAST_MODIFIED_BY' then
3330 if a_attr_val is not null then
3331 begin
3332 select subject_id into modified_subid from bism_subjects
3333 where
3334 subject_name = a_attr_val and subject_type ='u';
3335 EXECUTE IMMEDIATE 'update bism_objects set last_modified_by = :1
3336 where folder_id = :2 and user_visible = :3 and object_name = :4'
3337 using modified_subid, a_fid, v_uv, a_obj_name;
3338 exception
3339 when no_data_found then
3340 raise_application_error(-20503,'User not found');
3341 end;
3342 else
3343 -- use current user id
3344 EXECUTE IMMEDIATE 'update bism_objects set last_modified_by = :1
3345 where folder_id = :2 and user_visible = :3 and object_name = :4 '
3346 using a_sub_id,a_fid,v_uv,a_obj_name;
3347 end if;
3348
3349 else
3350
3351 -- if it is not created_by or last_modified_by, issue the update command
3352 EXECUTE IMMEDIATE 'update bism_objects set '|| a_attr_name||' = :1
3353 where folder_id = :2 and user_visible = :3 and object_name = :4 '
3354 using a_attr_val,a_fid,v_uv,a_obj_name;
3355 end if;
3356 end if;
3357
3358 end update_attribute;
3359
3360 procedure update_date_attribute(a_fid raw,a_obj_name varchar2,a_attr_name varchar2, a_attr_val date, a_sub_id raw)
3361 is
3362 ret varchar2(1) := 'n';
3363 status number;
3364 b_var char(1);
3365 v_uv bism_objects.user_visible%type:='Y';
3366 created_subid raw(16) := null;
3367 modified_subid raw(16) := null;
3368 begin
3369
3370 if a_sub_id is null or length(a_sub_id) = 0 then
3371 raise_application_error(-20503,'User not found');
3372 end if;
3373
3374 ret := bism_core.check_modify_attrs_access(a_fid, a_obj_name,a_sub_id , status, 1);
3375 if ret = 'y' then
3376 begin
3377 if a_attr_val is not null then
3378 if a_attr_name = 'time_date_created' then
3379 EXECUTE IMMEDIATE 'update bism_objects set time_date_created = :1
3380 where folder_id = :2 and user_visible = :3 and object_name = :4 '
3381 using a_attr_val,a_fid,v_uv,a_obj_name;
3382 elsif a_attr_name = 'time_date_modified' then
3383 EXECUTE IMMEDIATE 'update bism_objects set time_date_modified = :1
3384 where folder_id = :2 and user_visible = :3 and object_name = :4 '
3385 using a_attr_val,a_fid,v_uv,a_obj_name;
3386 elsif a_attr_name = 'time_date_last_accessed' then
3387 EXECUTE IMMEDIATE 'update bism_objects set time_date_last_accessed = :1
3388 where folder_id = :2 and user_visible = :3 and object_name = :4 '
3389 using a_attr_val,a_fid,v_uv,a_obj_name;
3390 end if;
3391 else
3392 if a_attr_name = 'time_date_created' then
3393 EXECUTE IMMEDIATE 'update bism_objects set time_date_created = :1
3394 where folder_id = :2 and user_visible = :3 and object_name = :4 '
3395 using sysdate, a_fid, v_uv,a_obj_name;
3396 elsif a_attr_name = 'time_date_modified' then
3397 EXECUTE IMMEDIATE 'update bism_objects set time_date_modified = :1
3398 where folder_id = :2 and user_visible = :3 and object_name = :4 '
3399 using sysdate, a_fid, v_uv,a_obj_name;
3400 elsif a_attr_name = 'time_date_last_accessed' then
3401 EXECUTE IMMEDIATE 'update bism_objects set time_date_last_accessed = :1
3402 where folder_id = :2 and user_visible = :3 and object_name = :4 '
3403 using sysdate, a_fid, v_uv,a_obj_name;
3404 end if;
3405 end if;
3406 end;
3407
3408 end if;
3409
3410 end update_date_attribute;
3411
3412 procedure update_attribute(a_fid raw,a_obj_name varchar2,a_ext_attr_xml varchar2, a_sub_id raw)
3413 is
3414 ret varchar2(1) := 'n';
3415 status number;
3416 b_var char(1);
3417 created_subid raw(16) := null;
3418 modified_subid raw(16) := null;
3419 v_attrs_xml varchar2(4000);
3420 v_uv bism_objects.user_visible%type:='Y';
3421
3422 begin
3423
3424 if a_ext_attr_xml is null or length(a_ext_attr_xml) = 0 then
3425 return;
3426 end if;
3427
3428 if a_sub_id is null or length(a_sub_id) = 0 then
3429 raise_application_error(-20503,'User not found');
3430 end if;
3431
3432 ret := bism_core.check_modify_attrs_access(a_fid, a_obj_name,a_sub_id , status, 1);
3433 if ret = 'y' then
3434 begin
3435 -- there should be three bind variables in the incoming update statement
3436 -- from modifyATtributes API
3437 -- this is on purpose to prevent users from updating objects that they dont have access to
3438 EXECUTE IMMEDIATE a_ext_attr_xml using a_fid, v_uv,a_obj_name;
3439
3440 end;
3441 end if;
3442
3443 end update_attribute;
3444
3445 procedure set_auto_commit(p_val varchar2)
3446 is
3447 begin
3448 if p_val = 'Y' or p_val = 'y' then
3449 v_auto_commit := TRUE;
3450 else
3451 v_auto_commit := FALSE;
3452 end if;
3453 end;
3454 end bism_core;
[Home] [Help]