1 PACKAGE BODY pa_security AS
2 /* $Header: PAPLSECB.pls 120.1 2007/10/24 04:23:16 rballamu ship $ */
3
4
5 /* ----------------------------------------------------------------------
6 ||
7 || Function Name: Initialize
8 ||
9 || Input Parameters:
10 || X_user_id <-- identifier of the application user
11 || X_calling_module <-- hard-coded string that refers to the
12 || module that is calling pa_security
13 || functions
14 ||
15 || Description:
16 || This function is called to initialize package globals that are
17 || referenced by the security functions. Each form that uses
18 || views that are secured MUST execute this procedure during form
19 || startup, otherwise the logic in the security functions built into
20 || secured views is not executed, and the views return all rows.
21 ||
22 || This built-in default (return unsecured data if package globals
23 || are not enabled) enables system administrators using SQL*Plus to
24 || query data to have access all data.
25 ||
26 || In order to secure data in external applications or custom
27 || modules that are not part of core PA code, this procedure must
28 || be called before querying secured views.
29 ||
30 || ---------------------------------------------------------------------
31 */
32
33 PROCEDURE Initialize ( X_user_id IN NUMBER
34 , X_calling_module IN VARCHAR2 )
35 IS
36 v_resp_id NUMBER;
37 v_resp_appl_id NUMBER;
38 BEGIN
39
40 IF ( X_user_id IS NOT NULL ) THEN
41 G_user_id := X_user_id;
42 G_person_id := pa_utils.GetEmpIdFromUser( G_user_id );
43 ELSE
44 G_person_id := NULL;
45 END IF;
46
47 v_resp_id := fnd_global.resp_id;
48 v_resp_appl_id := fnd_global.resp_appl_id;
49
50 IF fnd_profile.value_specific('PA_SUPER_PROJECT',x_user_id, v_resp_id, v_resp_appl_id) = 'Y' THEN
51 G_cross_project_user := 'Y';
52 ELSE
53 G_cross_project_user := 'N';
54 END IF;
55
56 IF fnd_profile.value_specific('PA_SUPER_PROJECT_VIEW',x_user_id, v_resp_id, v_resp_appl_id) = 'Y' THEN
57 G_cross_project_view := 'Y';
58 ELSE
59 G_cross_project_view := 'N';
60 END IF;
61
62 G_module_name := X_calling_module;
63
64 IF ( G_module_name = 'PAXTRAPE.CROSS-PROJECT' ) THEN
65 G_query_allowed := 'Y';
66 G_update_allowed := 'Y';
67 G_view_labor_costs := 'Y';
68 ELSE
69 G_view_labor_costs := NULL;
70 G_query_allowed := NULL;
71 G_update_allowed := NULL;
72 END IF;
73
74 END Initialize;
75
76
77
78 /* ----------------------------------------------------------------------
79 ||
80 || Function Name: allow_query
81 ||
82 || Input Parameters:
83 || X_project_id <-- project identifier
84 ||
85 || Description:
86 || This function determines whether a user has query access to a
87 || particular project.
88 ||
89 || The function first checks if the package variable G_query_allowed
90 || has been initiated with a value. This global variable is set
91 || during the Initialize procedure and is used to override normal
92 || validation in the allow_query function (this enables users who
93 || connect to the database in custom modules or in SQL*Plus to
94 || access all data in secured views without enforcing project-based
95 || security).
96 ||
97 || If the global variable is not set, then this function calls the
98 || client extension API to determine whether or not the user has
99 || query privileges for the given project.
100 ||
101 || The default validation that PA seeds in the client extension for
102 || 'ALLOW_QUERY' is to verify that the user has cross project view/update
103 || access, or has the project authority role in the organizational domain
104 || of this project, or is a key member of the project.
105 ||
106 || There are only two valid values returned from the extension
107 || procedure: 'Y' or 'N'. If the value returned is not one of
108 || these values, then this function returns 'Y'.
109 || -- changed the return value to 'N' for bug 2635016
110 ||
111 || ---------------------------------------------------------------------
112 */
113
114 FUNCTION allow_query ( X_project_id IN NUMBER) RETURN VARCHAR2
115 IS
116 V_allow_query VARCHAR2(1);
117 V_allow_update VARCHAR2(1); /* added for bug 2686117 */
118
119 BEGIN
120
121 /*
122 * Bug# 918652
123 */
124 IF ( G_module_name IS NULL ) THEN
125 RETURN('Y');
126 END IF;
127
128 /* Bug 2686117 Start */
129 IF ( G_update_allowed = 'Y') THEN
130 RETURN( G_update_allowed );
131 END IF;
132
133 pa_security_extn.check_project_access(
134 X_project_id
135 , G_person_id
136 , G_cross_project_user
137 , G_module_name
138 , 'ALLOW_UPDATE'
139 , V_allow_update );
140
141 IF ( V_allow_update = 'Y') THEN
142 RETURN( V_allow_update );
143 /* Bug 2686117 End */
144
145 ELSE
146
147 IF ( G_query_allowed IS NOT NULL ) THEN
148 RETURN( G_query_allowed );
149 END IF;
150
151 pa_security_extn.check_project_access(
152 X_project_id
153 , G_person_id
154 , G_cross_project_user
155 , G_module_name
156 , 'ALLOW_QUERY'
157 , V_allow_query
158 , G_cross_project_view );
159
160 IF ( V_allow_query IN ('Y', 'N') ) THEN
161 RETURN( V_allow_query );
162 ELSE
163 /* changed the return value from 'Y' to N for bug 2635016 */
164 RETURN( 'N' );
165 END IF;
166 END IF;
167 END allow_query;
168
169
170
171
172 /* ----------------------------------------------------------------------
173 ||
174 || Function Name: allow_update
175 ||
176 || Input Parameters:
177 || X_project_id <-- project identifier
178 ||
179 || Description:
180 || This function determines whether a user has update privileges for
181 || a particular project.
182 ||
183 || The structure is identical to the allow_query function. The default
184 || validation that PA seeds in the client extension for 'ALLOW_QUERY' is
185 || to verify that the user has cross project update access, or has the
186 || project authority role in the organizational domain of this project,
187 || or is a key member of the project.
188 ||
189 || ---------------------------------------------------------------------
190 */
191
192 FUNCTION allow_update ( X_project_id IN NUMBER) RETURN VARCHAR2
193 IS
194 V_allow_update VARCHAR2(1);
195
196 BEGIN
197
198 /*
199 * Bug# 918652
200 */
201 IF ( G_module_name IS NULL ) THEN
202 RETURN('Y');
203 END IF;
204
205 IF ( G_update_allowed IS NOT NULL ) THEN
206 RETURN( G_update_allowed );
207 END IF;
208
209 pa_security_extn.check_project_access(
210 X_project_id
211 , G_person_id
212 , G_cross_project_user
213 , G_module_name
214 , 'ALLOW_UPDATE'
215 , V_allow_update );
216
217 IF ( V_allow_update IN ('Y', 'N') ) THEN
218 RETURN( V_allow_update );
219 ELSE
220 /* changed the return value from 'Y' to 'N' for bug 2635016 */
221 RETURN( 'N' );
222 END IF;
223
224 END allow_update;
225
226
227
228 /* ----------------------------------------------------------------------
229 ||
230 || Function Name: view_labor_costs
231 ||
232 || Input Parameters:
233 || X_project_id <-- project identifier
234 ||
235 || Description:
236 || This function determines whether or not labor cost amounts are
237 || displayed when the user queries detail project expenditure items.
238 ||
239 || The structure of the function is identical to the allow_query
240 || function. The default validation that PA seeds in the client
241 || extension for 'VIEW_LABOR_COSTS' is to verify that the user
242 || is a valid key member for the project and that his/her project
243 || role type of this assignment is defined with Query Labor Cost
244 || privilege.
245 ||
246 || ---------------------------------------------------------------------
247 */
248
249 FUNCTION view_labor_costs ( X_project_id IN NUMBER) RETURN VARCHAR2
250 IS
251 V_view_labor_costs VARCHAR2(1);
252
253 BEGIN
254
255 /*
256 * Bug# 918652
257 */
258 IF ( G_module_name IS NULL ) THEN
259 RETURN('Y');
260 END IF;
261
262 IF ( G_view_labor_costs IS NOT NULL ) THEN
263 RETURN( G_view_labor_costs );
264 END IF;
265
266 pa_security_extn.check_project_access(
267 X_project_id
268 , G_person_id
269 , G_cross_project_user
270 , G_module_name
271 , 'VIEW_LABOR_COSTS'
272 , V_view_labor_costs );
273
274 IF ( V_view_labor_costs IN ('N', 'Y') ) THEN
275 RETURN( V_view_labor_costs );
276 ELSE
277 RETURN( 'Y' );
278 END IF;
279
280 END view_labor_costs;
281
282
283
284 /* ----------------------------------------------------------------------
285 ||
286 || Procedure Name: set_value
287 ||
288 || Input Parameters:
289 || X_security_level <-- Hard-Coded value to specify which
290 || level of security to set global values
291 || X_value <-- The value to assign to the package
292 || global. Once set, this package global
293 || is returned when the security function
294 || for that level is called instead of
295 || executing the function validation code.
296 ||
297 || Description:
298 || This procedure is called to assign to a given value to the
299 || security package global variable specified. It is used in
300 || forms that drilldown to details for a specific project so that
301 || security validation is only executed once.
302 ||
303 || For example, labor cost security is implemented by embedding the
304 || view_labor_costs function in the view definition that displays
305 || expenditure item details for a project. Since the function is
306 || row dependent (ie, based on project and expenditure type), it is
307 || executed for each record queried. Since the Expenditure Inquiry
308 || form (PROJECT mode) queries expenditure items for a
309 || specific project, the logic in the view_labor_costs function
310 || needs to be executed only once. Therefore, the package global
311 || variable for view labor cost security is initialized when
312 || the project number/name specified in the form is validated. When
313 || expenditure items are queried, the security function returns
314 || the global value instead of executing its validation logic.
315 ||
316 || ---------------------------------------------------------------------
317 */
318
319 PROCEDURE set_value ( X_security_level IN VARCHAR2
320 , X_value IN VARCHAR2 )
321 IS
322 BEGIN
323
324 IF ( X_security_level = 'VIEW_LABOR_COSTS' ) THEN
325 IF ( X_value IS NULL ) THEN
326 G_view_labor_costs := NULL;
327 ELSE
328 G_view_labor_costs := X_value;
329 END IF;
330 ELSIF ( X_security_level = 'ALLOW_UPDATE' ) THEN
331 IF ( X_value IS NULL ) THEN
332 G_update_allowed := NULL;
333 ELSE
334 G_update_allowed := X_value;
335 END IF;
336 ELSIF ( X_security_level = 'ALLOW_QUERY' ) THEN
337 IF ( X_value IS NULL ) THEN
338 G_query_allowed := NULL;
339 ELSE
340 G_query_allowed := X_value;
341 END IF;
342 END IF;
343
344 END set_value;
345
346
347
348 /* ----------------------------------------------------------------------
349 ||
350 || Function Name: check_key_member
351 ||
352 || Input Parameters:
353 || X_person_id <-- Identifier of the person
354 || X_project_id <-- Identifier of the project
355 ||
356 || Return value:
357 || Y <-- Indicates that the person specified is an active key
358 || member for the project specified as of the current date
359 || N <-- The person is not an active key member for the project
360 || NULL <-- Return value if either input parameter is not given
361 ||
362 || ---------------------------------------------------------------------
363 */
364
365 FUNCTION check_key_member ( X_person_id IN NUMBER
366 , X_project_id IN NUMBER ) RETURN VARCHAR2
367 IS
368 dummy NUMBER;
369
370 BEGIN
371 IF ( X_person_id IS NULL OR X_project_id IS NULL ) THEN
372 RETURN( NULL );
373 END IF;
374
375 BEGIN
376 SELECT 1
377 INTO dummy
378 FROM pa_project_players
379 WHERE project_id = X_project_id
380 AND person_id = X_person_id
381 AND TRUNC(sysdate) >= trunc(start_date_active)
382 AND TRUNC(sysdate) <= trunc(NVL(end_date_active, sysdate+1));
383 RETURN( 'Y' );
384
385 EXCEPTION
386 WHEN NO_DATA_FOUND THEN
387 RETURN( 'N' );
388 WHEN TOO_MANY_ROWS THEN
389 RETURN ('Y');
390 WHEN OTHERS THEN
391 RETURN ('N');
392
393 END;
394
395 END check_key_member;
396
397
398
399 /* ----------------------------------------------------------------------
400 ||
401 || Function Name: check_labor_cost_access
402 ||
403 || Input Parameters:
404 || X_person_id <-- Identifier of the person
405 || X_project_id <-- Identifier of the project
406 ||
407 || Return value:
408 || Y <-- Indicates that the person specified is a key member
409 || with a project role type that allows query of labor costs
410 || for the specified project
411 || N <-- The person is not an active key member for the project or
412 || is an active key member but with a project role type that
413 || does not permit query of labor cost amounts
414 || NULL <-- Return value if either input parameter is not given
415 ||
416 || ---------------------------------------------------------------------
417 */
418
419 FUNCTION check_labor_cost_access ( X_person_id IN NUMBER
423 dummy NUMBER;
420 , X_project_id IN NUMBER )
421 RETURN VARCHAR2
422 IS
424 BEGIN
425 IF ( X_person_id IS NULL OR X_project_id IS NULL ) THEN
426 RETURN( NULL );
427 END IF;
428
429 BEGIN
430 SELECT 1
431 INTO dummy
432 --FROM pa_project_role_types rt --bug 4004821
433 FROM pa_project_role_types_b rt
434 , pa_project_players pp
435 , pa_role_controls rc -- Added for bug 3058844
436 WHERE rt.project_role_type = pp.project_role_type
437 /* Below code added for bug 3058844 */
438 AND rt.project_role_id = rc.project_role_id
439 AND rc.role_control_code = 'ALLOW_QUERY_LABOR_COST'
440 /* Code addition ends for bug 3058844 */
441 AND TRUNC(sysdate) >= trunc(pp.start_date_active)
442 AND TRUNC(sysdate) <= trunc(NVL(pp.end_date_active, sysdate+1))
443 AND pp.person_id = X_person_id
444 AND pp.project_id = X_project_id;
445
446 RETURN( 'Y' );
447
448 EXCEPTION
449 WHEN NO_DATA_FOUND THEN
450 RETURN( 'N' );
451 WHEN TOO_MANY_ROWS THEN
452 RETURN ('Y');
453 WHEN OTHERS THEN
454 RETURN ('N');
455
456 END;
457
458 END check_labor_cost_access;
459
460
461 /* ----------------------------------------------------------------------
462 ||
463 || Function Name: check_project_authority
464 ||
465 || Input Parameters:
466 || X_person_id <-- Identifier of the person
467 || X_project_id <-- Identifier of the project
468 ||
469 || Return value:
470 || Y <-- Indicates that the person specified has the project authority
471 || role that permits access to the specified project
472 || N <-- The person is not a project authority, or is a project authority
473 || for an organization that does not encompass the specified project
474 || NULL <-- Return value if either input parameter is not given
475 ||
476 || ---------------------------------------------------------------------
477 */
478
479 FUNCTION check_project_authority ( X_person_id IN NUMBER,
480 X_project_id IN NUMBER ) RETURN VARCHAR2
481 IS
482 CURSOR c1 IS
483 SELECT '1'
484 FROM pa_projects_all ppa,
485 pa_project_role_types_b ppr, -- Added for bug 3224170
486 fnd_grants fg,
487 fnd_objects fo
488 WHERE
489 --fg.grantee_key = 'PER:'||X_person_id and /* commenting this line for 11.5.10 security changes */
490 fg.grantee_key = PA_SECURITY_PVT.get_grantee_key( 'PERSON', X_person_id, 'N') and
491 /* replaced the above line with this call. The last paramater will assert that the function will not write to database. */
492 fg.grantee_type = 'USER' and
493 fg.menu_id = ppr.menu_id and -- Added for bug 3224170
494 ppr.project_role_id = 3 and -- Added for bug 3224170
495 ppa.project_id = X_project_id and
496 to_char(ppa.carrying_out_organization_id) = fg.instance_pk1_value and -- bug 2777621
497 fg.object_id = fo.object_id and
498 fo.obj_name = 'ORGANIZATION' and
499 fg.instance_type = 'INSTANCE' and
500 TRUNC(sysdate) >= trunc(fg.start_date) and
501 trunc(sysdate) <= trunc(NVL(fg.end_date, sysdate+1));
502
503 v_dummy VARCHAR2(1);
504 BEGIN
505 IF( X_person_id IS NULL or X_project_id IS NULL ) THEN
506 RETURN( NULL );
507 END IF;
508
509 open c1;
510 fetch c1 into v_dummy;
511 IF c1%notfound THEN
512 close c1;
513 RETURN( 'N' );
514 ELSE
515 close c1;
516 RETURN( 'Y' );
517 END IF;
518 EXCEPTION
519 WHEN OTHERS THEN
520 RETURN( 'N' );
521 END check_project_authority;
522
523 /* ----------------------------------------------------------------------
524 ||
525 || Function Name: check_key_member_no_dates
526 ||
527 || Input Parameters:
528 || X_person_id <-- Identifier of the person
529 || X_project_id <-- Identifier of the project
530 ||
531 || Return value:
532 || Y <-- Indicates that the person specified is a
533 || member for the project.
534 || N <-- The person is not a member for the project.
535 || NULL <-- Return value if either input parameter is not given
536 ||
537 || ---------------------------------------------------------------------
538 */
539
540 FUNCTION check_key_member_no_dates ( X_person_id IN NUMBER
541 , X_project_id IN NUMBER ) RETURN VARCHAR2
542 IS
543 dummy NUMBER;
544
545 BEGIN
546 IF ( X_person_id IS NULL OR X_project_id IS NULL ) THEN
547 RETURN( NULL );
548 END IF;
549
550 BEGIN
551 SELECT 1
552 INTO dummy
553 FROM pa_project_players
554 WHERE project_id = X_project_id
555 AND person_id = X_person_id;
556
557 RETURN( 'Y' );
558
559 EXCEPTION
560 WHEN NO_DATA_FOUND THEN
561 RETURN( 'N' );
562 WHEN TOO_MANY_ROWS THEN
563 RETURN ('Y');
564 WHEN OTHERS THEN
565 RETURN ('N');
566 END;
567
568 END check_key_member_no_dates;
569
570 /* ----------------------------------------------------------------------
571 ||
572 || Function Name: check_forecast_authority
573 ||
574 || Input Parameters:
575 || p_person_id <-- Identifier of the person
576 || p_project_id <-- Identifier of the project
577 ||
578 || Return value:
579 || Y <-- Indicates that the person specified has the forecast authority
580 || role that permits access to the specified project
581 || N <-- The person is not a forecast authority
582 || NULL <-- Return value if either input parameter is not given
583 ||
584 || ---------------------------------------------------------------------
585 */
586
587 /*Enhancement 6519194 - commenting out this function*/
588 /*FUNCTION check_forecast_authority ( X_person_id IN NUMBER,
589 X_project_id IN NUMBER ) RETURN VARCHAR2
590 IS
591 CURSOR c1 IS
592 SELECT '1'
593 FROM pa_projects_all ppa,
594 fnd_grants fg,
595 fnd_objects fo,
596 fnd_menus fm
597 WHERE */
598 --fg.grantee_key = 'PER:'||X_person_id and /* commenting this line for 11.5.10 security changes */
599 /* fg.grantee_key = PA_SECURITY_PVT.get_grantee_key( 'PERSON', X_person_id, 'N') and*/
600 /* replaced the above line with this call. The last paramater will assert that the function will not write to database. */
601 /* fg.grantee_type = 'USER' and
602 ppa.project_id = X_project_id and
603 to_char(ppa.carrying_out_organization_id) = fg.instance_pk1_value and -- bug2777621
604 fg.object_id = fo.object_id and
605 fm.menu_name = 'PA_PRM_FCST_AUTH' and
606 fg.menu_id = fm.menu_id and
607 fo.obj_name = 'ORGANIZATION' and
608 fg.instance_type = 'INSTANCE' and
609 TRUNC(sysdate) >= trunc(fg.start_date) and
610 trunc(sysdate) <= trunc(NVL(fg.end_date, sysdate+1));
611
612 v_dummy VARCHAR2(1);
613 BEGIN
614
615 IF( X_person_id IS NULL or X_project_id IS NULL ) THEN
616 RETURN( NULL );
617 END IF;
618
619 open c1;
620 fetch c1 into v_dummy;
621 IF c1%notfound THEN
622 close c1;
623 RETURN( 'N' );
624 ELSE
625 close c1;
626 RETURN( 'Y' );
627 END IF;
628
629 EXCEPTION
630 WHEN OTHERS THEN
631 RETURN( 'N' );
632
633 END check_forecast_authority;
634 */
635 END pa_security;
[Home] [Help]