1 PACKAGE BODY FRM_SECURITY_PKG AS
2 /* $Header: frmsecb.pls 120.0.12010000.3 2010/02/25 15:50:23 rgurusam noship $ */
3
4 --------------------------------------------------------------------------------
5 -- PACKAGE: FRM_SECURITY_PKG --
6 -- --
7 -- DESCRIPTION: --
8 -- --
9 -- MODIFICATION HISTORY --
10 -- Date Username Description --
11 -- 25-FEB-2010 RGURUSAM Created for securing reports repository. --
12 --------------------------------------------------------------------------------
13
14 --------------------------------------------------------------------------------
15 -- FUNCTION: IS_MENU_ACCESSIBLE --
16 -- --
17 -- DESCRIPTION: Validates the access rules to ensure the --
18 -- supplied menu is accessible by the user. --
19 -- --
20 -- --
21 -- MODIFICATION HISTORY --
22 -- Date Username Description --
23 -- 16-FEB-2010 RGURUSAM Created for securing reports repository. --
24 --------------------------------------------------------------------------------
25 FUNCTION IS_MENU_ACCESSIBLE (P_USER_ID IN NUMBER, P_MENU_ID IN NUMBER, P_MENU_TYPE IN VARCHAR2) RETURN VARCHAR2
26 IS
27
28 TYPE TYPE_PARENT_CURSOR IS REF CURSOR;
29 C_PARENT_LIST TYPE_PARENT_CURSOR;
30 VN_RECORD_CNT1 NUMBER;
31 VN_RECORD_CNT2 NUMBER;
32 VN_RECORD_CNT3 NUMBER;
33
34 VN_DIRECTORY_ID NUMBER;
35 VN_PARNT_DIR_ID NUMBER;
36 VN_LEVEL NUMBER;
37
38 VN_MENU_ACCESS VARCHAR2(2) := 'N';
39 VN_PARNT_ACCESS VARCHAR2(2) := 'Y';
40 VN_RECURSE_FLAG VARCHAR2(2) := 'N';
41
42 BEGIN
43
44 IF NOT UPPER(P_MENU_TYPE) IN ('DOCUMENT', 'DIRECTORY') THEN
45 --Raising error makes HGrid query to result in OAException hence return N
46 --RAISE_APPLICATION_ERROR( -20000,'The supplied menu type is invalid. We support the following menu types: Document, Directory');
47 --DBMS_OUTPUT.PUT_LINE('The supplied menu type is invalid. We support the following menu types: Document, Directory');
48 RETURN 'N';
49 END IF;
50
51 -- Validate Menu Id
52
53 IF NOT IS_VALID_MENU_ID (P_MENU_ID, P_MENU_TYPE) THEN
54 --RAISE_APPLICATION_ERROR( -20000,'The supplied menu id ' || P_MENU_ID ||', menu type ' || P_MENU_TYPE || ' is invalid.');
55 --DBMS_OUTPUT.PUT_LINE('The supplied menu id ' || P_MENU_ID ||', menu type ' || P_MENU_TYPE || ' is invalid.');
56 RETURN 'N';
57 END IF;
58
59 IF UPPER(P_MENU_TYPE) = 'DOCUMENT' THEN
60
61 SELECT DOC.DIRECTORY_ID
62 INTO VN_DIRECTORY_ID
63 FROM FRM_DOCUMENTS_VL DOC
64 WHERE DOC.DOCUMENT_ID = P_MENU_ID
65 AND ((DOC.END_DATE IS NULL OR DOC.END_DATE > SYSDATE)
66 OR (DOC.DOCUMENT_ID IN (SELECT PUB.DOCUMENT_ID FROM FRM_DOC_PUB_OPTIONS PUB
67 WHERE PUB.DOCUMENT_ID = P_MENU_ID AND PUB.END_DATE > SYSDATE)));
68 ELSE
69
70 VN_DIRECTORY_ID := P_MENU_ID;
71
72 END IF;
73
74 -- Validate User Id
75
76 IF NOT IS_VALID_USER_ID (P_USER_ID) THEN
77 --RAISE_APPLICATION_ERROR( -20000,'The supplied user id ' || P_USER_ID ||' is invalid.');
78 --DBMS_OUTPUT.PUT_LINE('The supplied user id ' || P_USER_ID ||' is invalid.');
79 RETURN 'N';
80 END IF;
81
82 IF P_MENU_ID = 0 AND P_MENU_TYPE = 'DIRECTORY' THEN
83 RETURN 'Y';
84 END IF;
85
86 IF UPPER(NVL(FND_PROFILE.VALUE('FRM_SECURITY_OWNER'), 'N')) = 'Y' THEN
87 RETURN 'Y';
88 END IF;
89
90 -- Get Menu Access List Record Count
91
92 SELECT COUNT(DISTINCT MAP.NODE_ID)
93 INTO VN_RECORD_CNT1
94 FROM FRM_MENU_USER_MAPPINGS MAP
95 WHERE UPPER(MAP.NODE_TYPE) = UPPER(P_MENU_TYPE)
96 AND MAP.NODE_ID = P_MENU_ID;
97
98 -- Get Menu Parents Access List Record Count
99
100 SELECT COUNT(DISTINCT MAP.NODE_ID)
101 INTO VN_RECORD_CNT2
102 FROM FRM_MENU_USER_MAPPINGS MAP
103 WHERE MAP.NODE_ID IN
104 (SELECT DIR.PARENT_ID
105 FROM FRM_DIRECTORY_VL DIR
106 START WITH DIR.DIRECTORY_ID = VN_DIRECTORY_ID
107 CONNECT BY PRIOR DIR.PARENT_ID = DIR.DIRECTORY_ID
108 AND DIR.PARENT_ID <> -1
109 ) OR MAP.NODE_ID = VN_DIRECTORY_ID
110 AND UPPER(MAP.NODE_TYPE) = 'DIRECTORY';
111
112 IF VN_RECORD_CNT1 = 0 AND VN_RECORD_CNT2 = 0 THEN
113 -- CASE 1 : Neither Menu nor it's parents are secured
114 -- Return true for unsecured menus.
115 RETURN 'Y';
116 ELSE
117
118 --Either Menu or one of it's parent or both are secured
119
120 --If menu is secured and no parents are secured.
121 --Return Y If
122 -- CASE 2 : User is in menu's access list and no parents are secured.
123
124 --If menu is not secured and anyone or all parents are secured.
125 --Return Y If
126 -- CASE 3 : User is in a menu parent's access list with recursive permission and all
127 -- it's parents are accessible by user.
128 -- CASE 4 : User is in all of it's secured parent's access list.
129
130 --If both menu and anyone or all parents are secured.
131 -- CASE 3 : User is in a menu parent's access list with recursive permission and all
132 -- it's parents are accessible by user.
133 -- CASE 5 : User is in all of it's secured parent's access list and the menu's access list.
134
135 IF VN_RECORD_CNT1 = 0 THEN
136 -- Menu is not secured hence accessible.
137 VN_MENU_ACCESS := 'Y';
138 ELSE
139
140 -- Menu is secured and user is in menu access list.
141
142 SELECT COUNT(1)
143 INTO VN_RECORD_CNT1
144 FROM FRM_MENU_USER_MAPPINGS MAP
145 WHERE UPPER(MAP.NODE_TYPE) = UPPER(P_MENU_TYPE)
146 AND MAP.NODE_ID = P_MENU_ID
147 AND MAP.USER_ID = P_USER_ID;
148
149
150 IF VN_RECORD_CNT1 > 0 THEN
151 VN_MENU_ACCESS := 'Y';
152 END IF;
153
154 END IF;
155
156 IF VN_RECORD_CNT2 > 0 THEN
157
158 -- Retrieve Parent Menu Id's into a cursor
159 -- Iterate through each parent starting from root folder i.e Reports Repository
160
161 -- If parent is not secured then move to the next parent in the list.
162 -- If parent is secured and user can access the parent
163 -- with recursive permission then return Y.
164 -- with out recursive then move to next parent.
165 -- If parent is secured and user is not in it's access list
166 -- then if recurse variable is 'Y' then move to next parent in list.
167 -- otherwise exit the loop with parent access flag to 'N'
168 -- if all the parent's are accessible and document is also accessible then return 'Y'.
169
170 OPEN C_PARENT_LIST FOR SELECT * FROM (SELECT DIR.PARENT_ID AS DIRECTORY_ID, LEVEL + 1 AS DIR_LEVEL
171 FROM FRM_DIRECTORY_VL DIR
172 START WITH DIR.DIRECTORY_ID = VN_DIRECTORY_ID
173 CONNECT BY PRIOR DIR.PARENT_ID = DIR.DIRECTORY_ID
174 AND DIR.PARENT_ID <> -1
175 UNION
176 SELECT DIR.DIRECTORY_ID AS DIRECTORY_ID, 1 AS DIR_LEVEL
177 FROM FRM_DIRECTORY_VL DIR
178 WHERE DIR.DIRECTORY_ID = VN_DIRECTORY_ID)
179 ORDER BY DIR_LEVEL DESC;
180
181 LOOP
182 FETCH C_PARENT_LIST INTO VN_PARNT_DIR_ID, VN_LEVEL;
183
184 EXIT WHEN C_PARENT_LIST%NOTFOUND;
185
186 -- If Root Folder then move to next parent in list
187 IF NOT VN_PARNT_DIR_ID = 0 THEN
188
189 -- If not Root Folder then check if the folder is secured
190
191 SELECT COUNT(DISTINCT MAP.NODE_ID)
192 INTO VN_RECORD_CNT3
193 FROM FRM_MENU_USER_MAPPINGS MAP
194 WHERE MAP.NODE_ID = VN_PARNT_DIR_ID
195 AND UPPER(MAP.NODE_TYPE) = 'DIRECTORY';
196
197 IF NOT VN_RECORD_CNT3 = 0 THEN
198
199 -- If folder is secured then check if the folder is accessible by user.
200
201 SELECT COUNT(DISTINCT MAP.NODE_ID)
202 INTO VN_RECORD_CNT3
203 FROM FRM_MENU_USER_MAPPINGS MAP
204 WHERE MAP.NODE_ID = VN_PARNT_DIR_ID
205 AND UPPER(MAP.NODE_TYPE) = 'DIRECTORY'
206 AND MAP.USER_ID = P_USER_ID;
207
208 IF VN_RECORD_CNT3 = 0 THEN
209 -- Folder is secured and not accessible by user.
210 IF VN_RECURSE_FLAG = 'N' THEN
211 -- User is not in any one of current folder's parent access list with
212 -- recursive permission
213 VN_PARNT_ACCESS := 'N';
214 EXIT;
215 END IF;
216 ELSE
217
218 -- Folder is secured and accessible by user then verify the RECURSIVE flag.
219
220 SELECT DISTINCT UPPER(NVL(MAP.RECURSIVE, 'N'))
221 INTO VN_RECURSE_FLAG
222 FROM FRM_MENU_USER_MAPPINGS MAP
223 WHERE MAP.NODE_ID = VN_PARNT_DIR_ID
224 AND UPPER(MAP.NODE_TYPE) = 'DIRECTORY'
225 AND MAP.USER_ID = P_USER_ID
226 AND ROWNUM = 1;
227
228 IF VN_RECURSE_FLAG = 'Y' THEN
229 -- Folder is secured and accessible by user with recursive permission then return 'Y'.
230 VN_PARNT_ACCESS := 'Y';
231 EXIT;
232 END IF;
233
234 END IF;
235
236 END IF;
237
238 END IF;
239
240 END LOOP;
241
242 CLOSE C_PARENT_LIST;
243
244 IF VN_PARNT_ACCESS = 'Y' AND VN_RECURSE_FLAG = 'Y' THEN
245 -- Parent is recursively accessible
246 RETURN 'Y';
247 ELSIF VN_PARNT_ACCESS = 'Y' AND VN_MENU_ACCESS = 'Y' THEN
248 -- Both menu and all it's parent are accessible
249 RETURN 'Y';
250 ELSE
251 RETURN 'N';
252 END IF;
253
254 ELSE
255 -- CASE 2 : User is in menu's access list and no parents are secured.
256 RETURN VN_MENU_ACCESS;
257 END IF;
258
259 END IF;
260
261 RETURN 'N';
262
263 END IS_MENU_ACCESSIBLE;
264
265 --------------------------------------------------------------------------------
266 -- FUNCTION: IS_MENU_OWNER --
267 -- --
268 -- DESCRIPTION: Validates the access rules to ensure the --
269 -- supplied menu is owned by the user. --
270 -- --
271 -- --
272 -- MODIFICATION HISTORY --
273 -- Date Username Description --
274 -- 16-FEB-2010 RGURUSAM Created for securing reports repository. --
275 --------------------------------------------------------------------------------
276 FUNCTION IS_MENU_OWNER (P_USER_ID IN NUMBER, P_MENU_ID IN NUMBER, P_MENU_TYPE IN VARCHAR2) RETURN VARCHAR2
277 IS
278
279 TYPE TYPE_PARENT_CURSOR IS REF CURSOR;
280 C_PARENT_LIST TYPE_PARENT_CURSOR;
281 VN_RECORD_CNT1 NUMBER;
282 VN_RECORD_CNT2 NUMBER;
283 VN_RECORD_CNT3 NUMBER;
284
285 VN_DIRECTORY_ID NUMBER;
286 VN_PARNT_DIR_ID NUMBER;
287 VN_LEVEL NUMBER;
288
289 VN_MENU_OWNER VARCHAR2(2) := 'N';
290 VN_PARNT_OWNER VARCHAR2(2) := 'O';
291 VN_RECURSE_FLAG VARCHAR2(2) := 'N';
292
293 BEGIN
294
295 IF NOT UPPER(P_MENU_TYPE) IN ('DOCUMENT', 'DIRECTORY') THEN
296 --RAISE_APPLICATION_ERROR( -20000,'The supplied menu type is invalid. We support the following menu types: Document, Directory');
297 --DBMS_OUTPUT.PUT_LINE('The supplied menu type is invalid. We support the following menu types: Document, Directory');
298 RETURN 'N';
299 END IF;
300
301 -- Validate Menu Id
302
303 IF NOT IS_VALID_MENU_ID (P_MENU_ID, P_MENU_TYPE) THEN
304 --RAISE_APPLICATION_ERROR( -20000,'The supplied menu id ' || P_MENU_ID ||', menu type ' || P_MENU_TYPE || ' is invalid.');
305 --DBMS_OUTPUT.PUT_LINE('The supplied menu id ' || P_MENU_ID ||', menu type ' || P_MENU_TYPE || ' is invalid.');
306 RETURN 'N';
307 END IF;
308
309 IF UPPER(P_MENU_TYPE) = 'DOCUMENT' THEN
310
311 SELECT DOC.DIRECTORY_ID
312 INTO VN_DIRECTORY_ID
313 FROM FRM_DOCUMENTS_VL DOC
314 WHERE DOC.DOCUMENT_ID = P_MENU_ID
315 AND ((DOC.END_DATE IS NULL OR DOC.END_DATE > SYSDATE)
316 OR (DOC.DOCUMENT_ID IN (SELECT PUB.DOCUMENT_ID FROM FRM_DOC_PUB_OPTIONS PUB
317 WHERE PUB.DOCUMENT_ID = P_MENU_ID AND PUB.END_DATE > SYSDATE)));
318 ELSE
319
320 SELECT DIR.PARENT_ID
321 INTO VN_DIRECTORY_ID
322 FROM FRM_DIRECTORY_VL DIR
323 WHERE DIR.DIRECTORY_ID = P_MENU_ID
324 AND (DIR.END_DATE IS NULL OR DIR.END_DATE > SYSDATE);
325
326 END IF;
327
328 -- Validate User Id
329
330 IF NOT IS_VALID_USER_ID (P_USER_ID) THEN
331 --RAISE_APPLICATION_ERROR( -20000,'The supplied user id is invalid.');
332 --DBMS_OUTPUT.PUT_LINE('The supplied user id is invalid.');
333 RETURN 'N';
334 END IF;
335
336 IF P_MENU_ID = 0 AND P_MENU_TYPE = 'DIRECTORY' THEN
337 RETURN 'Y';
338 END IF;
339
340 IF UPPER(NVL(FND_PROFILE.VALUE('FRM_SECURITY_OWNER'), 'N')) = 'Y' THEN
341 RETURN 'Y';
342 END IF;
343
344 -- Get Menu Access List Record Count
345
346 SELECT COUNT(DISTINCT MAP.NODE_ID)
347 INTO VN_RECORD_CNT1
348 FROM FRM_MENU_USER_MAPPINGS MAP
349 WHERE UPPER(MAP.NODE_TYPE) = UPPER(P_MENU_TYPE)
350 AND MAP.NODE_ID = P_MENU_ID;
351
352 -- Get Menu Parents Access List Record Count
353
354 SELECT COUNT(DISTINCT MAP.NODE_ID)
355 INTO VN_RECORD_CNT2
356 FROM FRM_MENU_USER_MAPPINGS MAP
357 WHERE MAP.NODE_ID IN
358 (SELECT DIR.PARENT_ID
359 FROM FRM_DIRECTORY_VL DIR
360 START WITH DIR.DIRECTORY_ID = VN_DIRECTORY_ID
361 CONNECT BY PRIOR DIR.PARENT_ID = DIR.DIRECTORY_ID
362 AND DIR.PARENT_ID <> -1
363 ) OR MAP.NODE_ID = VN_DIRECTORY_ID
364 AND UPPER(MAP.NODE_TYPE) = 'DIRECTORY';
365
366 IF VN_RECORD_CNT1 = 0 AND VN_RECORD_CNT2 = 0 THEN
367 -- CASE 1 : Neither Menu nor it's parents are secured
368 -- Return true for unsecured menus.
369 RETURN 'Y';
370 ELSE
371
372 --Either Menu or one of it's parent or both are secured
373
374 --If menu is secured and no parents are secured.
375 --Return Y If
376 -- CASE 2 : User is in menu's access list with owner permission
377 -- and no parents are secured.
378
379 --If menu is not secured and anyone or all parents are secured.
380 --Return Y If
381 -- CASE 3 : User is in a menu parent's access list with owner permission and
382 -- recursive flag set and all it's parents are owned by user.
383 -- CASE 4 : User is in all of it's secured parent's access list with owner permission.
384
385 --If both menu and anyone or all parents are secured.
386 -- CASE 3 : User is in a menu parent's access list with owner permission and recursive
387 -- flag set and all it's parents are owned by user.
388 -- CASE 5 : User is in all of it's secured parent's access list with owner permission
389 -- and the menu's access list with owner permission.
390
391 IF VN_RECORD_CNT1 = 0 THEN
392 -- Menu is not secured hence owned by user.
393 VN_MENU_OWNER := 'U'; --Unsecured
394 ELSE
395
396 -- Menu is secured and user is in menu access list with owner permission.
397
398 SELECT COUNT(1)
399 INTO VN_RECORD_CNT1
400 FROM FRM_MENU_USER_MAPPINGS MAP
401 WHERE UPPER(MAP.NODE_TYPE) = UPPER(P_MENU_TYPE)
402 AND MAP.NODE_ID = P_MENU_ID
403 AND MAP.USER_ID = P_USER_ID;
404
405
406 IF VN_RECORD_CNT1 > 0 THEN
407
408 SELECT UPPER(MAP.PERMISSION_CODE)
409 INTO VN_MENU_OWNER
410 FROM FRM_MENU_USER_MAPPINGS MAP
411 WHERE UPPER(MAP.NODE_TYPE) = UPPER(P_MENU_TYPE)
412 AND MAP.NODE_ID = P_MENU_ID
413 AND MAP.USER_ID = P_USER_ID
414 AND ROWNUM = 1;
415
416 ELSE
417 VN_MENU_OWNER := 'N'; --Not Accessible
418 END IF;
419
420 END IF;
421
422 IF VN_RECORD_CNT2 > 0 THEN
423
424 -- Retrieve Parent Menu Id's into a cursor
425 -- Iterate through each parent starting from root folder i.e Reports Repository
426
427 -- For each parent
428
429 -- If parent is not secured then move to the next parent in the list.
430
431 -- If parent is secured
432
433 -- User is in access list
434
435 -- User is OWNER then set parent access flag 'O' and then
436
437 -- Check recursive flag if it is 'Y' then set recurse variable
438 -- to 'Y' and exit the loop
439
440 -- User is VIEWER then set parent access flag 'V' and then
441
442 -- Check recursive flag if it is 'Y' then set recurse variable
443 -- to 'Y' and move to next parent in the list.
444
445 -- User is not in access list
446
447 -- then if recurse variable is 'Y' then move to next parent in list.
448
449 -- otherwise exit the loop with parent access flag to 'N'
450
451
452
453 -- if parent is owned with recurse flag 'Y' then return 'Y'
454 -- if both parent and menu is owned then return 'Y'
455 -- if parent is accessible and menu is owned then return 'Y'
456 -- for all other cases return 'N'
457
458
459 OPEN C_PARENT_LIST FOR SELECT * FROM (SELECT DIR.PARENT_ID AS DIRECTORY_ID, LEVEL + 1 AS DIR_LEVEL
460 FROM FRM_DIRECTORY_VL DIR
461 START WITH DIR.DIRECTORY_ID = VN_DIRECTORY_ID
462 CONNECT BY PRIOR DIR.PARENT_ID = DIR.DIRECTORY_ID
463 AND DIR.PARENT_ID <> -1
464 UNION
465 SELECT DIR.DIRECTORY_ID AS DIRECTORY_ID, 1 AS DIR_LEVEL
466 FROM FRM_DIRECTORY_VL DIR
467 WHERE DIR.DIRECTORY_ID = VN_DIRECTORY_ID)
468 ORDER BY DIR_LEVEL DESC;
469
470 LOOP
471 FETCH C_PARENT_LIST INTO VN_PARNT_DIR_ID, VN_LEVEL;
472
473 EXIT WHEN C_PARENT_LIST%NOTFOUND;
474
475 -- If Root Folder then move to next parent in list
476 IF NOT VN_PARNT_DIR_ID = 0 THEN
477
478 -- If not Root Folder then check if the folder is secured
479
480 SELECT COUNT(DISTINCT MAP.NODE_ID)
481 INTO VN_RECORD_CNT3
482 FROM FRM_MENU_USER_MAPPINGS MAP
483 WHERE MAP.NODE_ID = VN_PARNT_DIR_ID
484 AND UPPER(MAP.NODE_TYPE) = 'DIRECTORY';
485
486 IF NOT VN_RECORD_CNT3 = 0 THEN
487
488 -- If folder is secured then check if the folder is accessible by user.
489
490 SELECT COUNT(DISTINCT MAP.NODE_ID)
491 INTO VN_RECORD_CNT3
492 FROM FRM_MENU_USER_MAPPINGS MAP
493 WHERE MAP.NODE_ID = VN_PARNT_DIR_ID
494 AND UPPER(MAP.NODE_TYPE) = 'DIRECTORY'
495 AND MAP.USER_ID = P_USER_ID;
496
497 IF VN_RECORD_CNT3 = 0 THEN
498 -- Folder is secured and not accessible by user.
499 IF VN_RECURSE_FLAG = 'N' THEN
500 -- User is not in any one of current folder's parent access list with
501 -- recursive permission
502 VN_PARNT_OWNER := 'N';
503 EXIT;
504 END IF;
505 ELSE
506
507 -- Folder is secured and accessible by user then verify the
508 -- PERMISSION flag and RECURSIVE flag.
509
510 SELECT UPPER(MAP.PERMISSION_CODE),
511 UPPER(NVL(MAP.RECURSIVE, 'N'))
512 INTO VN_PARNT_OWNER,
513 VN_RECURSE_FLAG
514 FROM FRM_MENU_USER_MAPPINGS MAP
515 WHERE MAP.NODE_ID = VN_PARNT_DIR_ID
516 AND UPPER(MAP.NODE_TYPE) = 'DIRECTORY'
517 AND MAP.USER_ID = P_USER_ID
518 AND ROWNUM = 1;
519
520
521 IF VN_RECURSE_FLAG = 'Y' AND VN_PARNT_OWNER = 'O' THEN
522 -- Folder is secured and owned by user with recursive permission then return 'Y'.
523 VN_PARNT_OWNER := 'O';
524 EXIT;
525 END IF;
526
527 END IF;
528
529 END IF;
530
531 END IF;
532
533 END LOOP;
534
535 CLOSE C_PARENT_LIST;
536
537 IF VN_PARNT_OWNER = 'N' THEN
538 -- Either parent or menu is not accessible by user
539 RETURN 'N';
540 ELSIF VN_PARNT_OWNER = 'O' AND VN_RECURSE_FLAG ='Y' THEN
541 -- Parent is recursively owned by user
542 RETURN 'Y';
543 ELSIF VN_PARNT_OWNER = 'O' AND VN_MENU_OWNER = 'U' THEN
544 -- Parent is owned by user and menu is un secured
545 RETURN 'Y';
546 ELSIF VN_PARNT_OWNER = 'O' AND VN_MENU_OWNER ='O' THEN
547 -- Both parent and menu is owned by user
548 RETURN 'Y';
549 ELSIF VN_PARNT_OWNER = 'V' AND VN_MENU_OWNER ='O' THEN
550 -- Parent is accessible and menu is owned by user
551 RETURN 'Y';
552 ELSE
553 -- For all other cases return 'N'
554 RETURN 'N';
555 END IF;
556 ELSE
557 -- CASE 2 : User is in menu's access list and no parents are secured.
558 IF VN_MENU_OWNER = 'O' THEN
559 RETURN 'Y';
560 ELSE
561 RETURN 'N';
562 END IF;
563 END IF;
564
565 END IF;
566
567 RETURN 'N';
568
569 END IS_MENU_OWNER;
570
571 --------------------------------------------------------------------------------
572 -- FUNCTION: IS_VALID_MENU_ID --
573 -- --
574 -- DESCRIPTION: Validates the MENU_ID to ensure the menu is either --
575 -- valid document or directory in reports repository. --
576 -- --
577 -- --
578 -- --
579 -- MODIFICATION HISTORY --
580 -- Date Username Description --
581 -- 16-FEB-2010 rgurusam Created for securing reports repository. --
582 --------------------------------------------------------------------------------
583
584 FUNCTION IS_VALID_MENU_ID (P_MENU_ID IN NUMBER, P_MENU_TYPE IN VARCHAR2) RETURN BOOLEAN
585 IS
586 VN_MENU_ID NUMBER;
587 BEGIN
588
589 VN_MENU_ID := -1;
590
591 BEGIN
592
593 IF UPPER(P_MENU_TYPE) = 'DOCUMENT' THEN
594
595 SELECT DOC.DOCUMENT_ID
596 INTO VN_MENU_ID
597 FROM FRM_DOCUMENTS_VL DOC
598 WHERE DOC.DOCUMENT_ID = P_MENU_ID
599 AND ((DOC.END_DATE IS NULL OR DOC.END_DATE > SYSDATE)
600 OR (DOC.DOCUMENT_ID IN (SELECT PUB.DOCUMENT_ID FROM FRM_DOC_PUB_OPTIONS PUB
601 WHERE PUB.DOCUMENT_ID = P_MENU_ID AND PUB.END_DATE > SYSDATE)));
602
603 ELSE
604
605 SELECT DIR.DIRECTORY_ID
606 INTO VN_MENU_ID
607 FROM FRM_DIRECTORY_VL DIR
608 WHERE DIR.DIRECTORY_ID = P_MENU_ID
609 AND (DIR.END_DATE IS NULL OR DIR.END_DATE > SYSDATE);
610
611 END IF;
612
613 EXCEPTION
614 WHEN NO_DATA_FOUND THEN NULL;
615
616 END;
617
618 IF (VN_MENU_ID = -1) THEN
619 -- if the MENU_ID was not found.
620 RETURN FALSE;
621 ELSE
622 -- the MENU_ID is defined in reports repository.
623 RETURN TRUE;
624 END IF;
625
626 END IS_VALID_MENU_ID;
627
628 --------------------------------------------------------------------------------
629 -- FUNCTION: IS_VALID_USER_ID --
630 -- --
631 -- DESCRIPTION: Validates the USER_ID to ensure the user is defined --
632 -- in Oracle Applications. --
633 -- --
634 -- --
635 -- --
636 -- MODIFICATION HISTORY --
637 -- Date Username Description --
638 -- 16-FEB-2010 rgurusam Created for securing reports repository. --
639 --------------------------------------------------------------------------------
640
641 FUNCTION IS_VALID_USER_ID (P_USER_ID IN NUMBER) RETURN BOOLEAN
642 IS
643 VN_USER_ID NUMBER;
644 BEGIN
645
646 VN_USER_ID := -1;
647
648 BEGIN
649
650 SELECT USER_ID
651 INTO VN_USER_ID
652 FROM FND_USER
653 WHERE USER_ID = P_USER_ID
654 AND (END_DATE IS NULL OR END_DATE > SYSDATE);
655
656 EXCEPTION
657 WHEN NO_DATA_FOUND THEN NULL;
658
659 END;
660
661 IF (VN_USER_ID = -1) THEN
662 -- if the USER_ID was not found.
663 RETURN FALSE;
664 ELSE
665 -- the USER_ID is defined in Oracle Applications.
666 RETURN TRUE;
667 END IF;
668
669 END IS_VALID_USER_ID;
670
671 END FRM_SECURITY_PKG;
[Home] [Help]