[Home] [Help]
PACKAGE BODY: APPS.AMW_SECURITY_PUB
Source
1 PACKAGE BODY AMW_SECURITY_PUB as
2 /*$Header: amwpsecb.pls 120.2 2006/09/14 10:32:11 yreddy noship $*/
3
4
5
6 C_PKG_NAME CONSTANT VARCHAR2(30) := 'AMW_SECURITY_PUB';
7 C_TYPE_SET CONSTANT VARCHAR2(30) := 'SET';
8 C_TYPE_GLOBAL CONSTANT VARCHAR2(30) := 'GLOBAL';
9 C_TYPE_INSTANCE CONSTANT VARCHAR2(30) := 'INSTANCE';
10 C_TYPE_UNIVERSAL CONSTANT VARCHAR2(30) := 'UNIVERSAL';
11
12 C_NULL_STR CONSTANT VARCHAR2(30) := '*NULL*';
13
14 C_AMP_STR CONSTANT VARCHAR2(30) := '&';
15 C_GRANT_ALIAS_TOK CONSTANT VARCHAR2(30) := C_AMP_STR || 'GRANT_ALIAS.';
16 C_TABLE_ALIAS_TOK CONSTANT VARCHAR2(30) := C_AMP_STR || 'TABLE_ALIAS.';
17
18
19 c_pred_buf_size CONSTANT NUMBER := 32767;
20 /* This is the VPD size limit of predicates in the database. */
21 /* In 8.1.7 databases the limit is 4k, and in 8.2 it will be 32k. */
22 /* Once we no longer support 8.1.7 then we can increase this to 32,000 */
23 c_vpd_buf_limit CONSTANT NUMBER := 32*1024;
24
25 /* One level cache for get_object_id() */
26 g_obj_id_cache NUMBER := NULL;
27 g_obj_name_cache VARCHAR2(30) := NULL;
28
29 /* One level cache for get_function_id() */
30 g_func_id_cache NUMBER := NULL;
31 g_func_name_cache VARCHAR2(30) := NULL;
32
33 /* One level cache for get_security_predicate */
34 g_gsp_function VARCHAR2(30) := '*EMPTY*';
35 g_gsp_object_name VARCHAR2(30) := '*EMPTY*';
36 g_gsp_grant_instance_type VARCHAR2(30) := '*EMPTY*';
37 g_gsp_user_name VARCHAR2(255) := '*EMPTY*';
38 g_gsp_statement_type VARCHAR2(30) := '*EMPTY*';
39 g_gsp_predicate VARCHAR2(32767):= '*EMPTY*';
40 g_gsp_return_status VARCHAR2(30) := '*EMPTY*';
41 g_gsp_table_alias VARCHAR2(255) := '*EMPTY*';
42 g_gsp_bind_order VARCHAR2(255) := '*EMPTY*';
43 g_gsp_with_binds VARCHAR2(30) := '*EMPTY*';
44 g_gsp_context_user_id NUMBER := -11111;
45 g_gsp_context_secgrpid NUMBER := -11111;
46 g_gsp_context_resp_id NUMBER := -11111;
47 g_gsp_context_resp_appl_id NUMBER := -11111;
48 g_gsp_context_org_id NUMBER := -11111;
49 g_gsp_object_id NUMBER := -11111;
50 g_gsp_function_id NUMBER := -11111;
51
52 /* One level cache for CHECK_USER_ROLE() */
53 g_ck_user_role_result VARCHAR2(1) := NULL;
54 g_ck_user_role_name VARCHAR2(255) := NULL;
55
56
57
58 ------------------------------------
59 -- Directly copied from fnd_data_security, slightly modified
60 ------------------------------------
61
62 function replace_str(in_pred in varchar2, /* must be uppercased */
63 from_str in varchar2,
64 to_str in varchar2) return varchar2 is
65 punctuation varchar2(255);
66 justspaces varchar2(255);
67 compare_pred varchar2(32767);
68 out_pred varchar2(32767);
69 pred_frag varchar2(32767);
70 out_offset number; /* difference in sizes as we are replacing smaller */
71 /* strings with bigger strings */
72 xoldlen number;
73 xnewlen number;
74 i number;
75 j number;
76 maxlen number := 32767; /* maximum length of predicate */
77 before_char varchar2(30);
78 match boolean;
79 begin
80 /* convert punctuation in predicate to spaces for comparison */
81 punctuation := fnd_global.newline||'`~!@#$%^*()-=+|][{}\";:,<>/?''.';
82 justspaces := ' ';
83 compare_pred := upper(in_pred);
84 out_pred := in_pred;
85 xoldlen := LENGTH(from_str);
86 xnewlen := LENGTH(to_str);
87
88 i:= 99999;
89 j:= 1;
90 out_offset := 0;
91 while (i<>0) loop
92 i := instr(compare_pred, from_str, 1, j);
93 if i=0 then
94 exit;
95 end if;
96
97 if i<>1 then
98 /* Make sure the character before the X isnt alphanumeric */
99 /* or underscore, which would mean this is not a match */
100 before_char := substr(compare_pred, i-1, 1);
101 before_char := translate(before_char, punctuation, justspaces);
102 if before_char = ' ' then
103 match := TRUE;
104 else
105 match := FALSE;
106 end if;
107 else
108 match := TRUE;
109 end if;
110
111 if (match) then
112 /* Replace the string in the output
113 ** predicate. Clip the string to the max byte size allowed.
114 */
115 out_pred := substrb( substr(out_pred, 1, i + out_offset - 1)
116 || to_str
117 || substr(out_pred, i + out_offset + xoldlen),
118 1, maxlen);
119 out_offset := out_offset + xnewlen - xoldlen ;
120 end if;
121
122 j := j + 1;
123 end loop;
124
125 return out_pred;
126 end;
127
128
129 ------------------------------------
130 -- Directly copied from fnd_data_security, slightly modified
131 ------------------------------------
132
133 function get_to_char(x_column_name in varchar2,
134 x_column_type in varchar2) return varchar2 is
135 retval varchar2(255);
136 begin
137 /* INTEGER type... no format mask needed. NUMBER is an obsolete type */
138 /* that we support like INTEGER for backward compatibility. */
139 if (x_column_type = 'INTEGER') or (x_column_type = 'NUMBER') then
140 retval := 'TO_CHAR('||x_column_name||')';
141 elsif (x_column_type = 'FLOAT') then
142 retval := 'TO_CHAR('||x_column_name||
143 ', ''FM999999999999999999999.99999999999999999999'')';
144 elsif (x_column_type = 'DATE') then
145 retval := 'TO_CHAR('||x_column_name||', ''YYYY/MM/DD HH24:MI:SS'')';
146 else
147 retval := '/* ERROR_UNK_TYPE:'||x_column_type
148 ||' */ TO_CHAR('||x_column_name||')';
149 end if;
150 return retval;
151 end;
152
153
154 ------------------------------------
155 -- Directly copied from fnd_data_security, slightly modified
156 ------------------------------------
157
158 Function get_object_id(p_object_name in varchar2
159 ) return number is
160 v_object_id number;
161 l_api_name CONSTANT VARCHAR2(30) := 'GET_OBJECT_ID';
162 Begin
163 if (p_object_name = g_obj_name_cache) then
164 v_object_id := g_obj_id_cache; /* If we have it cached, use value */
165 else /* not cached, hit db */
166 select object_id
167 into v_object_id
168 from fnd_objects
169 where obj_name=p_object_name;
170
171 /* Store in cache */
172 g_obj_id_cache := v_object_id;
173 g_obj_name_cache := p_object_name;
174 end if;
175
176 return v_object_id;
177 exception
178 when no_data_found then
179 return null;
180 end;
181
182
183 ------------------------------------
184 -- Directly copied from fnd_data_security, slightly modified
185 ------------------------------------
186
187 Function get_function_id(p_function_name in varchar2
188 ) return number is
189 v_function_id number;
190 l_api_name CONSTANT VARCHAR2(30) := 'GET_FUNCTION_ID';
191 Begin
192 if (p_function_name = g_func_name_cache) then
193 v_function_id := g_func_id_cache; /* If we have it cached, use value */
194 else /* not cached, hit db */
195 select function_id
196 into v_function_id
197 from fnd_form_functions
198 where function_name=p_function_name;
199
200 /* Store in cache */
201 g_func_id_cache := v_function_id;
202 g_func_name_cache := p_function_name;
203 end if;
204
205 return v_function_id;
206 exception
207 when no_data_found then
208 return null;
209 end;
210
211
212 ------------------------------------
213 -- Directly copied from fnd_data_security, slightly modified
214 ------------------------------------
215
216 function CHECK_USER_ROLE(P_USER_NAME in varchar2)
217 return varchar2 /* T/F */
218 is
219 l_dummy number := 0;
220 colon pls_integer;
221
222 begin
223
224 if( (g_ck_user_role_name is not NULL)
225 and (g_ck_user_role_name = p_user_name)) then
226 return g_ck_user_role_result;
227 end if;
228
229 select 1
230 into l_dummy
231 from wf_user_roles
232 where user_name = p_user_name
233 and rownum = 1;
234
235 g_ck_user_role_result := 'T';
236 g_ck_user_role_name := p_user_name;
237 return g_ck_user_role_result;
238
239 exception when no_data_found then
240 g_ck_user_role_result := 'F';
241 g_ck_user_role_name := p_user_name;
242 return g_ck_user_role_result;
243
244 end CHECK_USER_ROLE;
245
246
247 ------------------------------------
248 -- Directly copied from fnd_data_security, slightly modified
249 ------------------------------------
250
251
252 procedure get_name_bind(p_user_name in VARCHAR2,
253 x_user_name_bind out NOCOPY varchar2) is
254 l_api_name CONSTANT VARCHAR2(30) := 'GET_NAME_BIND';
255 colon pls_integer;
256 l_unfound BOOLEAN;
257 x_user_id number;
258 x_is_per_person number;
259 begin
260
261 if ((p_user_name is NULL) or (p_user_name = 'GLOBAL')) then
262 x_user_name_bind := ''''|| replace(p_user_name, '''','''''')||'''';
263 return;
264 end if;
265
266 if (p_user_name = SYS_CONTEXT('FND','USER_NAME')) then
267 x_user_name_bind := 'SYS_CONTEXT(''FND'',''USER_NAME'')';
268 return;
269 else
270 x_user_name_bind := ''''||replace(p_user_name, '''', '''''')||'''';
271 return;
272 end if;
273
274 /* This line should never be reached. */
275 x_user_name_bind := 'ERROR_IN_GET_NAME_BIND';
276 return;
277
278 end;
279
280
281 ------------------------------------
282 -- Directly copied from fnd_data_security, slightly modified
283 ------------------------------------
284
285 function get_pk_information(p_object_name in VARCHAR2,
286 x_pk1_column_name out NOCOPY varchar2,
287 x_pk2_column_name out NOCOPY varchar2,
288 x_pk3_column_name out NOCOPY varchar2,
289 x_pk4_column_name out NOCOPY varchar2,
290 x_pk5_column_name out NOCOPY varchar2,
291 x_pk_column out NOCOPY varchar2,
292 x_ik_clause out NOCOPY varchar2,
293 x_exact_clause out NOCOPY varchar2,
294 x_orig_pk_column out NOCOPY varchar2,
295 x_database_object_name out NOCOPY varchar2,
296 x_table_alias in varchar2,
297 x_grant_alias in varchar2)
298 return VARCHAR2 IS
299 l_api_name CONSTANT VARCHAR2(30) := 'GET_PK_INFORMATION';
300 x_pk1_column_type varchar2(8);
301 x_pk2_column_type varchar2(8);
302 x_pk3_column_type varchar2(8);
303 x_pk4_column_type varchar2(8);
304 x_pk5_column_type varchar2(8);
305 l_table_alias varchar2(255);
306 l_grant_alias varchar2(255);
307 cursor c_pk is
308 SELECT pk1_column_name
309 ,pk2_column_name
310 ,pk3_column_name
311 ,pk4_column_name
312 ,pk5_column_name
313 ,pk1_column_type
314 ,pk2_column_type
315 ,pk3_column_type
316 ,pk4_column_type
317 ,pk5_column_type
318 , database_object_name
319 FROM fnd_objects
320 WHERE obj_name=p_object_name ;
321 begin
322
323 if(x_table_alias is NULL) then
324 l_table_alias := NULL;
325 else
326 l_table_alias := x_table_alias || '.';
327 end if;
328
329 if(x_grant_alias is NULL) then
330 l_grant_alias := NULL;
331 else
332 l_grant_alias := x_grant_alias || '.';
333 end if;
334
335 open c_pk;
336 fetch c_pk into
337 x_pk1_column_name ,
338 x_pk2_column_name ,
339 x_pk3_column_name ,
340 x_pk4_column_name ,
341 x_pk5_column_name ,
342 x_pk1_column_type ,
343 x_pk2_column_type ,
344 x_pk3_column_type ,
345 x_pk4_column_type ,
346 x_pk5_column_type ,
347 x_database_object_name;
348
349 IF(c_pk%NOTFOUND) THEN
350 return 'U';
351 end if;
352
353 CLOSE c_pk;
354
355 -- Build up the list of column names without 'X.' (table alias)
356 x_orig_pk_column := NULL;
357 if( (x_pk1_column_name is not NULL)
358 AND (x_pk1_column_name <> C_NULL_STR)) then
359 x_orig_pk_column := x_orig_pk_column ||x_pk1_column_name;
360 end if;
361 if( (x_pk2_column_name is not NULL)
362 AND (x_pk2_column_name <> C_NULL_STR)) then
363 x_orig_pk_column := x_orig_pk_column || ', ' || x_pk2_column_name;
364 end if;
365 if( (x_pk3_column_name is not NULL)
366 AND (x_pk3_column_name <> C_NULL_STR)) then
367 x_orig_pk_column := x_orig_pk_column || ', ' || x_pk3_column_name;
368 end if;
369 if( (x_pk4_column_name is not NULL)
370 AND (x_pk4_column_name <> C_NULL_STR)) then
371 x_orig_pk_column := x_orig_pk_column || ', ' || x_pk4_column_name;
372 end if;
373 if( (x_pk5_column_name is not NULL)
374 AND (x_pk5_column_name <> C_NULL_STR)) then
375 x_orig_pk_column := x_orig_pk_column || ', ' || x_pk5_column_name;
376 end if;
377
378
379
380 -- Build up the x_pk_column and x_ik_clause lists
381 -- by adding values for each column name.
382 x_ik_clause := '(('||l_grant_alias||'INSTANCE_TYPE = ''INSTANCE'')';
383 x_exact_clause := '(';
384
385 if ( (x_pk1_column_name is not null)
386 AND (x_pk1_column_name <> C_NULL_STR))then
387 x_pk_column := x_pk_column||l_table_alias||x_pk1_column_name;
388 x_ik_clause := x_ik_clause||' AND ('||l_grant_alias
389 ||'INSTANCE_PK1_VALUE' ||
390 ' = '||get_to_char(l_table_alias|| x_pk1_column_name,
391 x_pk1_column_type)
392 || ')';
393 x_exact_clause := x_exact_clause||
394 ' ( :pk1 = '||l_table_alias|| x_pk1_column_name || ')';
395 if ( (x_pk2_COLUMN_name is not null)
396 AND (x_pk2_column_name <> C_NULL_STR)) then
397 x_pk_column:=
398 x_pk_column||', '||l_table_alias||x_pk2_COLUMN_name;
402 || x_pk2_column_name, x_pk2_column_type)
399 x_ik_clause := x_ik_clause||' AND ('||
400 l_grant_alias ||'INSTANCE_PK2_VALUE'||
401 ' = '||get_to_char(l_table_alias
403 || ')';
404 x_exact_clause := x_exact_clause||
405 ' AND ( :pk2 = '||l_table_alias
406 || x_pk2_column_name || ')';
407 if ( (x_pk3_COLUMN_name is not null)
408 AND (x_pk3_column_name <> C_NULL_STR)) then
409 x_pk_column :=
410 x_pk_column||', '||l_table_alias||x_pk3_COLUMN_name;
411 x_ik_clause := x_ik_clause||' AND ('||l_grant_alias
412 ||'INSTANCE_PK3_VALUE'||
413 ' = '||get_to_char(l_table_alias||
414 x_pk3_column_name,
415 x_pk3_column_type)
416 || ')';
417 x_exact_clause := x_exact_clause||
418 ' AND ( :pk3 = '||l_table_alias
419 || x_pk3_column_name || ')';
420 if ( (x_pk4_COLUMN_name is not null)
421 AND (x_pk4_column_name <> C_NULL_STR)) then
422 x_pk_column:=
423 x_pk_column||', '||l_table_alias||x_pk4_COLUMN_name;
424 x_ik_clause := x_ik_clause||' AND ('||
425 l_grant_alias
426 ||'INSTANCE_PK4_VALUE'||
427 ' = '||get_to_char(l_table_alias||
428 x_pk4_column_name,
429 x_pk4_column_type)
430 || ')';
431 x_exact_clause := x_exact_clause||
432 ' AND ( :pk4 = '||l_table_alias
433 || x_pk4_column_name || ')';
434 if ( (x_pk5_COLUMN_name is not null)
435 AND (x_pk5_column_name <> C_NULL_STR)) then
436 x_pk_column:=
437 x_pk_column||', '||l_table_alias||x_pk5_COLUMN_name;
438 x_ik_clause := x_ik_clause||' AND ('|| l_grant_alias
439 ||'INSTANCE_PK5_VALUE'||
440 ' = '||get_to_char(l_table_alias|| x_pk5_column_name,
441 x_pk5_column_type)
442 || ')';
443 x_exact_clause := x_exact_clause||
444 ' AND ( :pk5 = '||l_table_alias
445 || x_pk5_column_name || ')';
446 end if;
447 end if;
448 end if;
449 end if;
450 end if;
451
452 x_ik_clause := x_ik_clause||' )';
453 x_exact_clause := x_exact_clause||' )';
454
455 return 'T';
456 end;
457
458
459 ------------------------------------
460 -- Directly copied from fnd_data_security, slightly modified
461 ------------------------------------
462
463 FUNCTION upgrade_predicate(in_pred in varchar2) return VARCHAR2 is
464 xpos number;
465 gpos number;
466 compare_pred varchar2(32767);
467 out_pred varchar2(32767);
468 xoldval varchar2(255) := 'X.';
469 goldval varchar2(255) := 'G.PARAMETER';
470 xnewval varchar2(255) := C_TABLE_ALIAS_TOK;
471 gnewval varchar2(255) := C_GRANT_ALIAS_TOK||'PARAMETER';
472 begin
473 /* upper case the predicate for comparison */
474 compare_pred := UPPER(in_pred);
475 xpos := INSTR(compare_pred, xoldval);
476 gpos := INSTR(compare_pred, goldval);
477 if (xpos = 0 and gpos = 0) then
478 return in_pred; /* Short circuit return if no upgrade candidates */
479 end if;
480
481 out_pred := in_pred;
482 if(xpos <> 0) then
483 out_pred := replace_str(out_pred, xoldval, xnewval);
484 end if;
485 if(gpos <> 0) then
486 out_pred := replace_str(out_pred, goldval, gnewval);
487 end if;
488 return out_pred;
489 end upgrade_predicate;
490
491
492 ------------------------------------
493 -- Directly copied from fnd_data_security, slightly modified
494 ------------------------------------
495
496 FUNCTION substitute_predicate(in_pred in varchar2,
497 in_table_alias in varchar2) return VARCHAR2 is
498 out_pred varchar2(32767);
499 maxlen number := 32767;
500 gsubval varchar2(255) := 'GNT.';
501 l_table_alias varchar2(255);
502 begin
503 if (in_table_alias is not NULL) then
504 l_table_alias := in_table_alias || '.';
505 else
506 l_table_alias := NULL;
507 end if;
508
509 out_pred := in_pred;
510 out_pred := substrb(replace(out_pred, C_TABLE_ALIAS_TOK,
511 l_table_alias), 1, maxlen);
512
513 out_pred := substrb(replace(out_pred, C_GRANT_ALIAS_TOK,
514 gsubval), 1, maxlen);
515
516 return out_pred;
517 end substitute_predicate;
518
519 ------------------------------------
520 -- Grant Role
521 ------------------------------------
522 PROCEDURE grant_role_guid
523 (
524 p_api_version IN NUMBER,
525 p_role_name IN VARCHAR2,
526 p_object_name IN VARCHAR2,
527 p_instance_type IN VARCHAR2,
528 p_instance_set_id IN NUMBER,
529 p_instance_pk1_value IN VARCHAR2,
530 p_instance_pk2_value IN VARCHAR2,
531 p_instance_pk3_value IN VARCHAR2,
532 p_instance_pk4_value IN VARCHAR2,
533 p_instance_pk5_value IN VARCHAR2,
534 p_party_id IN NUMBER,
538 x_errorcode OUT NOCOPY NUMBER,
535 p_start_date IN DATE,
536 p_end_date IN DATE,
537 x_return_status OUT NOCOPY VARCHAR2,
539 x_grant_guid OUT NOCOPY RAW,
540 p_check_for_existing IN VARCHAR2 := FND_API.G_TRUE
541 )
542 IS
543
544 --x_grant_guid fnd_grants.grant_guid%TYPE;
545 l_grantee_type hz_parties.party_type%TYPE;
546 l_instance_type fnd_grants.instance_type%TYPE;
547 l_grantee_key fnd_grants.grantee_key%TYPE;
548 l_dummy VARCHAR2(1);
549 l_not_found boolean := true;
550 CURSOR get_party_type (cp_party_id NUMBER)
551 IS
552 SELECT party_type
553 FROM hz_parties
554 WHERE party_id=cp_party_id;
555
556 CURSOR check_fnd_grant_exist (cp_grantee_key VARCHAR2,
557 cp_grantee_type VARCHAR2,
558 cp_menu_name VARCHAR2,
559 cp_object_name VARCHAR2,
560 cp_instance_type VARCHAR2,
561 cp_instance_pk1_value VARCHAR2,
562 cp_instance_pk2_value VARCHAR2,
563 cp_instance_pk3_value VARCHAR2,
564 cp_instance_pk4_value VARCHAR2,
565 cp_instance_pk5_value VARCHAR2,
566 cp_instance_set_id NUMBER,
567 cp_start_date DATE,
568 cp_end_date DATE) IS
569
570 SELECT 'X'
571 FROM fnd_grants grants,
572 fnd_objects obj,
573 fnd_menus menus
574 WHERE grants.grantee_key=cp_grantee_key
575 AND grants.grantee_type=cp_grantee_type
576 AND grants.menu_id=menus.menu_id
577 AND menus.menu_name=cp_menu_name
578 AND grants.object_id = obj.object_id
579 AND obj.obj_name=cp_object_name
580 AND grants.instance_type=cp_instance_type
581 AND ((grants.instance_pk1_value=cp_instance_pk1_value )
582 OR((grants.instance_pk1_value = '*NULL*') AND (cp_instance_pk1_value IS NULL)))
583 AND ((grants.instance_pk2_value=cp_instance_pk2_value )
584 OR((grants.instance_pk2_value = '*NULL*') AND (cp_instance_pk2_value IS NULL)))
585 AND ((grants.instance_pk3_value=cp_instance_pk3_value )
586 OR((grants.instance_pk3_value = '*NULL*') AND (cp_instance_pk3_value IS NULL)))
587 AND ((grants.instance_pk4_value=cp_instance_pk4_value )
588 OR((grants.instance_pk4_value = '*NULL*') AND (cp_instance_pk4_value IS NULL)))
589 AND ((grants.instance_pk5_value=cp_instance_pk5_value )
590 OR((grants.instance_pk5_value = '*NULL*') AND (cp_instance_pk5_value IS NULL)))
591 AND ((grants.instance_set_id=cp_instance_set_id )
592 OR((grants.instance_set_id IS NULL ) AND (cp_instance_set_id IS NULL)))
593 AND (((grants.start_date<=cp_start_date )
594 AND (( grants.end_date IS NULL) OR (cp_start_date <=grants.end_date )))
595 OR ((grants.start_date >= cp_start_date )
596 AND (( cp_end_date IS NULL) OR (cp_end_date >=grants.start_date))));
597
598 v_start_date DATE := sysdate;
599
600 BEGIN
601 if (p_start_date IS NULL) THEN
602 v_start_date := sysdate;
603 else
604 v_start_date := p_start_date;
605 end if;
606
607 IF( p_instance_type <> 'INSTANCE') THEN
608 l_instance_type:='SET';
609 ELSE
610 l_instance_type:=p_instance_type;
611 END IF;
612 OPEN get_party_type (cp_party_id =>p_party_id);
613 FETCH get_party_type INTO l_grantee_type;
614 CLOSE get_party_type;
615 IF( p_party_id = -1000) THEN
616 l_grantee_type :='GLOBAL';
617 l_grantee_key:='HZ_GLOBAL:'||p_party_id;
618 ELSIF (l_grantee_type ='PERSON') THEN
619 l_grantee_type:='USER';
620 l_grantee_key:='HZ_PARTY:'||p_party_id;
621 ELSIF (l_grantee_type ='GROUP') THEN
622 l_grantee_type:='GROUP';
623 l_grantee_key:='HZ_GROUP:'||p_party_id;
624 ELSIF (l_grantee_type ='ORGANIZATION') THEN
625 l_grantee_type:='COMPANY';
626 l_grantee_key:='HZ_COMPANY:'||p_party_id;
627 ELSE
628 null;
629 END IF;
630 IF (p_check_for_existing = FND_API.G_TRUE ) THEN
631 OPEN check_fnd_grant_exist(cp_grantee_key => l_grantee_key,
632 cp_grantee_type => l_grantee_type,
633 cp_menu_name => p_role_name,
634 cp_object_name => p_object_name,
635 cp_instance_type => l_instance_type,
636 cp_instance_pk1_value => p_instance_pk1_value,
637 cp_instance_pk2_value => p_instance_pk2_value,
638 cp_instance_pk3_value => p_instance_pk3_value,
639 cp_instance_pk4_value => p_instance_pk4_value,
640 cp_instance_pk5_value => p_instance_pk5_value,
641 cp_instance_set_id => p_instance_set_id,
642 cp_start_date => v_start_date,
643 cp_end_date => p_end_date);
644
645 FETCH check_fnd_grant_exist INTO l_dummy;
646 IF( check_fnd_grant_exist%FOUND) THEN
647 l_not_found := false;
648 END IF;
649 CLOSE check_fnd_grant_exist;
650 END IF;
651 IF (l_not_found) THEN
652 fnd_grants_pkg.grant_function(
653 p_api_version => 1.0,
654 p_menu_name => p_role_name ,
658 p_instance_pk1_value => p_instance_pk1_value,
655 p_object_name => p_object_name,
656 p_instance_type => l_instance_type,
657 p_instance_set_id => p_instance_set_id,
659 p_instance_pk2_value => p_instance_pk2_value,
660 p_instance_pk3_value => p_instance_pk3_value,
661 p_instance_pk4_value => p_instance_pk4_value,
662 p_instance_pk5_value => p_instance_pk5_value,
663 p_grantee_type => l_grantee_type,
664 p_grantee_key => l_grantee_key,
665 p_start_date => v_start_date,
666 p_end_date => p_end_date,
667 p_program_name => null,
668 p_program_tag => null,
669 x_grant_guid => x_grant_guid,
670 x_success => x_return_status,
671 x_errorcode => x_errorcode
672 );
673 if(p_instance_type = 'INSTANCE') then
674 AMW_SECURITY_UTILS_PVT.give_dependant_grants (
675 p_grant_guid => x_grant_guid,
676 p_parent_obj_name => p_object_name,
677 p_parent_role => p_role_name,
678 p_parent_pk1 => p_instance_pk1_value,
679 p_parent_pk2 => p_instance_pk2_value,
680 p_parent_pk3 => p_instance_pk3_value,
681 p_parent_pk4 => p_instance_pk4_value,
682 p_parent_pk5 => p_instance_pk5_value,
683 p_grantee_type => l_grantee_type,
684 p_grantee_key => l_grantee_key,
685 p_start_date => v_start_date,
686 p_end_date => p_end_date,
687 x_success => x_return_status,
688 x_errorcode => x_errorcode);
689 end if;
690
691
692 ELSE
693 x_return_status:='F';
694 END IF;
695
696
697
698 END grant_role_guid;
699
700
701 ------------------------------------
702 -- Grant Privilege
703 ------------------------------------
704 PROCEDURE grant_role_guid
705 (
706 p_api_version IN NUMBER,
707 p_role_name IN VARCHAR2,
708 p_object_name IN VARCHAR2,
709 p_instance_type IN VARCHAR2,
710 p_object_key IN NUMBER,
711 p_party_id IN NUMBER,
712 p_start_date IN DATE,
713 p_end_date IN DATE,
714 x_return_status OUT NOCOPY VARCHAR2,
715 x_errorcode OUT NOCOPY NUMBER,
716 x_grant_guid OUT NOCOPY RAW
717 )
718 IS
719 -- Start OF comments
720 -- API name : Grant
721 -- TYPE : Public
722 -- Pre-reqs : None
723 -- FUNCTION : Grant a Role on object instances to a Party.
724 -- If this operation fails then the grant is not
725 -- done and error code is returned.
726 --
727 -- Version: Current Version 0.1
728 -- Previous Version : None
729 -- Notes :
730 --
731 -- END OF comments
732 l_instance_set_id fnd_grants.instance_set_id%TYPE;
733 l_instance_pk1_value fnd_grants.instance_pk1_value%TYPE;
734 v_start_date DATE := sysdate;
735
736 BEGIN
737 IF( p_instance_type ='SET') THEN
738 l_instance_set_id:=p_object_key;
739 l_instance_pk1_value:= null;
740 ELSE
741 l_instance_set_id:=null;
742 l_instance_pk1_value:= to_char(p_object_key);
743 END IF;
744
745 if (p_start_date IS NULL) THEN
746 v_start_date := sysdate;
747 else
748 v_start_date := p_start_date;
749 end if;
750
751 grant_role_guid
752 (
753 p_api_version => p_api_version,
754 p_role_name => p_role_name,
755 p_object_name => p_object_name,
756 p_instance_type => p_instance_type,
757 p_instance_set_id => l_instance_set_id,
758 p_instance_pk1_value => l_instance_pk1_value,
759 p_instance_pk2_value => null,
760 p_instance_pk3_value => null,
761 p_instance_pk4_value => null,
762 p_instance_pk5_value => null,
763 p_party_id => p_party_id,
764 p_start_date => v_start_date,
765 p_end_date => p_end_date,
766 x_return_status => x_return_status,
767 x_errorcode => x_errorcode,
768 x_grant_guid => x_grant_guid
769 );
770
771 END grant_role_guid;
772
773
774 --------------------------
775 -- Revoke Grant
776 --------------------------
777 PROCEDURE revoke_grant
778 (
779 p_api_version IN NUMBER,
780 p_grant_guid IN VARCHAR2,
781 x_return_status OUT NOCOPY VARCHAR2,
782 x_errorcode OUT NOCOPY NUMBER
783 )
784 IS
785 -- Start OF comments
786 -- API name : Revoke
787 -- TYPE : Public
788 -- Pre-reqs : None
789 -- FUNCTION : Revoke a Party's role on object instances.
790 -- If this operation fails then the revoke is
791 -- done and error code is returned.
792 --
793 -- Version: Current Version 0.1
794 -- Previous Version : None
795 -- Notes :
796 --
797 -- END OF comments
798
802 SELECT grant_guid
799 l_grant_guid fnd_grants.grant_guid%TYPE;
800 CURSOR get_grant_guid(cp_grant_id VARCHAR2)
801 IS
803 FROM fnd_grants
804 WHERE grant_guid=HEXTORAW(cp_grant_id);
805
806 BEGIN
807 OPEN get_grant_guid(cp_grant_id=>p_grant_guid);
808 FETCH get_grant_guid INTO l_grant_guid;
809 CLOSE get_grant_guid;
810
811 AMW_SECURITY_UTILS_PVT.revoke_dependant_grants(
812 p_grant_guid => l_grant_guid,
813 x_success => x_return_status,
814 x_errorcode => x_errorcode);
815
816 fnd_grants_pkg.revoke_grant(
817 p_api_version => p_api_version,
818 p_grant_guid => l_grant_guid ,
819 x_success => x_return_status,
820 x_errorcode => x_errorcode
821 );
822
823 END revoke_grant;
824
825
826 ------------------------------------
827 -- Set end date to a grant
828 ------------------------------------
829 PROCEDURE set_grant_date
830 (
831 p_api_version IN NUMBER,
832 p_grant_guid IN VARCHAR2,
833 p_start_date IN DATE,
834 p_end_date IN DATE,
835 x_return_status OUT NOCOPY VARCHAR2
836 )IS
837 -- Start OF comments
838 -- API name : SET_GRANT_DATE
839 -- TYPE : Public
840 -- Pre-reqs : None
841 -- FUNCTION :sets start date and end date to a grant
842 --
843 --
844 --
845 -- Version: Current Version 1.0
846 -- Previous Version : None
847 -- Notes :
848 --
849 -- END OF comments
850
851 --x_success VARCHAR2(2);
852 l_dummy VARCHAR2(1);
853 l_grant_guid fnd_grants.grant_guid%TYPE;
854 CURSOR get_grant_guid(cp_grant_id VARCHAR2,
855 cp_start_date DATE,
856 cp_end_date DATE)
857 IS
858 SELECT g1.grant_guid
859 FROM fnd_grants g1, fnd_grants g2
860 WHERE g1.grant_guid=HEXTORAW(cp_grant_id)
861 AND g2.grant_guid<>HEXTORAW(cp_grant_id)
862 AND g1.object_id=g2.object_id
863 AND g1.menu_id=g2.menu_id
864 AND g1.instance_type=g2.instance_type
865 AND g1.instance_pk1_value=g2.instance_pk1_value
866 AND g1.grantee_type=g2.grantee_type
867 AND g1.grantee_key=g2.grantee_key
868 AND (
869 ((g2.start_date<=cp_start_date )
870 AND (( g2.end_date IS NULL) OR (cp_start_date<=g2.end_date )))
871 OR ((g2.start_date >= cp_start_date )
872 AND (( cp_end_date IS NULL) OR (cp_end_date>=g2.start_date)))
873 );
874
875 BEGIN
876 OPEN get_grant_guid(cp_grant_id=>p_grant_guid,
877 cp_start_date=>p_start_date,
878 cp_end_date=>p_end_date);
879 FETCH get_grant_guid INTO l_grant_guid;
880
881 IF( get_grant_guid%NOTFOUND) THEN
882 fnd_grants_pkg.update_grant (
883 p_api_version => p_api_version,
884 p_grant_guid => HEXTORAW(p_grant_guid),
885 p_start_date => p_start_date,
886 p_end_date => p_end_date,
887 x_success => x_return_status
888 );
889
890 AMW_SECURITY_UTILS_PVT.update_dependant_grants(
891 p_grant_guid => HEXTORAW(p_grant_guid),
892 p_new_start_date => p_start_date,
893 p_new_end_date => p_end_date,
894 x_success => x_return_status);
895
896 ELSE
897 x_return_status:='F';
898
899 END IF;
900
901 CLOSE get_grant_guid;
902
903 END set_grant_date;
904
905
906 -- abedajna: basically copied from fnd_data_security
907 -- gets rid of some useless backsupport stuff
908 -- and uses a different of constructing predicate
909 -- such the the 32k buffer limit is never hit.
910
911 PROCEDURE get_security_predicate_intrnl(
912 p_api_version IN NUMBER,
913 p_function IN VARCHAR2,
914 p_object_name IN VARCHAR2,
915 p_grant_instance_type IN VARCHAR2,/* SET, INSTANCE*/
916 p_user_name IN VARCHAR2,
917 /* stmnt_type: 'OTHER', 'VPD'=VPD, 'EXISTS'= for checking existence. */
918 p_statement_type IN VARCHAR2,
919 p_table_alias IN VARCHAR2,
920 p_with_binds IN VARCHAR2,
921 x_predicate out NOCOPY varchar2,
922 x_return_status out NOCOPY varchar2,
923 x_function_id out NOCOPY NUMBER,
924 x_object_id out NOCOPY NUMBER,
925 x_bind_order out NOCOPY VARCHAR2
926 ) IS
927
928 l_api_name CONSTANT VARCHAR2(30) := 'GET_SECURITY_PREDICATE_INTRNL';
929
930 l_api_version CONSTANT NUMBER := 1.0;
931 l_sysdate DATE := Sysdate;
932 l_aggregate_predicate VARCHAR2(32767); /* Must match c_pred_buf_size*/
933 l_instance_predicate VARCHAR2(32767); /* Must match c_pred_buf_size*/
934 l_instance_flag BOOLEAN := TRUE;
935 l_instance_set_flag BOOLEAN := TRUE;
936 l_inst_group_grantee_type BOOLEAN := FALSE;
937 l_inst_global_grantee_type BOOLEAN := FALSE;
938 l_set_group_grantee_type BOOLEAN := FALSE;
939 l_set_global_grantee_type BOOLEAN := FALSE;
940 l_inst_instance_type BOOLEAN := FALSE;
941 l_set_instance_type BOOLEAN := FALSE;
942 l_global_instance_type BOOLEAN := FALSE;
943 l_db_object_name varchar2(30);
944 l_db_pk1_column varchar2(256);
945 l_db_pk2_column varchar2(256);
949 l_pk_column_names varchar2(512);
946 l_db_pk3_column varchar2(256);
947 l_db_pk4_column varchar2(256);
948 l_db_pk5_column varchar2(256);
950 l_pk_orig_column_names varchar2(512);
951 l_ik_clause varchar2(2048);
952 l_exact_clause varchar2(2048);
953 l_user_name_bind varchar2(255);
954 l_user_name varchar2(80);
955 l_nrows pls_integer;
956 l_table_alias varchar2(256);
957 l_last_instance_set_id NUMBER;
958 l_last_pred varchar2(32767);
959 l_need_to_close_pred BOOLEAN;
960 l_refers_to_grants BOOLEAN;
961 l_last_was_hextoraw BOOLEAN;
962 l_pred varchar2(32767);
963 l_uses_params BOOLEAN;
964 d_predicate VARCHAR2(32767);
965 d_instance_set_id number;
966 d_grant_guid RAW(16);
967 l_grp_glob_fn BOOLEAN;
968 l_grp_glob_nofn BOOLEAN;
969 l_glob_fn BOOLEAN;
970 l_glob_nofn BOOLEAN;
971 l_grp_fn BOOLEAN;
972 l_grp_nofn BOOLEAN;
973 l_cursor_is_open BOOLEAN;
974 l_dummy NUMBER;
975 colon PLS_INTEGER;
976 -- abedajna
977 top varchar2(500);
978 mid1 varchar2(1000);
979 mid2 varchar2(1000);
980 mid3 varchar2(1000);
981 bottom varchar2(2000);
982 guid_subquery varchar2(32767);
983
984
985 /* This cursor determines if there are any grants to GLOBAL grantee, */
986 /* for a particular instance type */
987 CURSOR grant_types_global_c (cp_user_name varchar2,
988 cp_function_id NUMBER,
989 cp_object_id VARCHAR2,
990 cp_instance_type VARCHAR2)
991 IS
992 select 1 from
993 dual
994 where exists
995 (
996 SELECT /*+ INDEX(g, FND_GRANTS_N1) */ 1
997 FROM fnd_grants g
998 WHERE (g.grantee_type = 'GLOBAL')
999 AND g.object_id = cp_object_id
1000 AND ( (cp_function_id = -1)
1001 OR (g.menu_id in
1002 (select cmf.menu_id
1003 from fnd_compiled_menu_functions cmf
1004 where cmf.function_id = cp_function_id)))
1005 AND ( g.ctx_secgrp_id = -1
1006 OR g.ctx_secgrp_id =
1007 SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
1008 AND ( g.ctx_resp_id = -1
1009 OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
1010 AND ( g.ctx_resp_appl_id = -1
1011 OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
1012 AND ( g.ctx_org_id = -1
1013 OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
1014 AND g.start_date <= SYSDATE
1015 AND ( g.end_date IS NULL
1016 OR g.end_date >= SYSDATE )
1017 AND g.instance_type = cp_instance_type
1018 );
1019
1020
1021 /* This cursor determines if there are any grants to USER or GROUP */
1022 /* grantee, for a particular instance type */
1023 CURSOR grant_types_group_c (cp_user_name varchar2,
1024 cp_function_id NUMBER,
1025 cp_object_id VARCHAR2,
1026 cp_instance_type VARCHAR2)
1027 IS
1028 select 1 from
1029 dual
1030 where exists
1031 (
1032 SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */ 'X'
1033 FROM
1034 ( select /*+ NO_MERGE */ role_name
1035 from wf_user_roles wur,
1036 (
1037 select cp_user_name name from dual
1038 union all
1039 select incr1.name name
1040 from wf_local_roles incr1, fnd_user u1
1041 where 'HZ_PARTY' = incr1.orig_system
1042 and u1.user_name = cp_user_name
1043 and u1.person_party_id = incr1.orig_system_id
1044 and incr1.partition_id = 9 /* HZ_PARTY */
1045 ) incr2
1046 where wur.user_name = incr2.name
1047 ) u2,
1048 fnd_grants g
1049 WHERE rownum = 1
1050 AND g.grantee_key = u2.role_name
1051 and g.object_id = cp_object_id
1052 and ((cp_function_id = -1)
1053 or (g.menu_id in
1054 (select cmf.menu_id
1055 from fnd_compiled_menu_functions cmf
1056 where cmf.function_id = cp_function_id)))
1057 and ( g.ctx_secgrp_id = -1
1058 or g.ctx_secgrp_id =
1059 SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
1060 and ( g.ctx_resp_id = -1
1061 OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
1062 and ( g.ctx_resp_appl_id = -1
1063 OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
1064 and ( g.ctx_org_id = -1
1065 OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
1066 and g.start_date <= SYSDATE
1067 and ( g.end_date IS NULL
1068 OR g.end_date >= SYSDATE )
1069 and g.instance_type = cp_instance_type
1070 );
1071
1072
1073 /* Which instance sets are granted to specific function? */
1074 CURSOR isg_grp_glob_fn_c (cp_user_name varchar2,
1078 SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
1075 cp_function_id NUMBER,
1076 cp_object_id VARCHAR2)
1077 IS
1079 instance_sets.predicate, instance_sets.instance_set_id,
1080 g.grant_guid
1081 FROM
1082 ( select /*+ NO_MERGE */ 'GLOBAL' role_name from dual
1083 union all
1084 select role_name
1085 from wf_user_roles wur,
1086 (
1087 select cp_user_name name from dual
1088 union all
1089 select incr1.name name
1090 from wf_local_roles incr1, fnd_user u1
1091 where 'HZ_PARTY' = incr1.orig_system
1092 and u1.user_name = cp_user_name
1093 and u1.person_party_id = incr1.orig_system_id
1094 and incr1.partition_id = 9 /* HZ_PARTY */
1095 ) incr2
1096 where wur.user_name = incr2.name
1097 ) u2,
1098 fnd_grants g,
1099 fnd_object_instance_sets instance_sets
1100 WHERE g.grantee_key = u2.role_name
1101 AND g.instance_type = 'SET'
1102 AND g.object_id = cp_object_id
1103 AND (g.menu_id in
1104 (select cmf.menu_id
1105 from fnd_compiled_menu_functions cmf
1106 where cmf.function_id = cp_function_id))
1107 AND g.instance_set_id = instance_sets.instance_set_id
1108 AND ( g.ctx_secgrp_id = -1
1109 OR g.ctx_secgrp_id =
1110 SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
1111 AND ( g.ctx_resp_id = -1
1112 OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
1113 AND ( g.ctx_resp_appl_id = -1
1114 OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
1115 AND ( g.ctx_org_id = -1
1116 OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
1117 AND g.start_date <= SYSDATE
1118 AND ( g.end_date IS NULL
1119 OR g.end_date >= SYSDATE )
1120 ORDER BY instance_sets.predicate,
1121 instance_sets.instance_set_id desc;
1122
1123 /* Which instance sets are granted to specific function? */
1124 CURSOR isg_grp_fn_c (cp_user_name varchar2,
1125 cp_function_id NUMBER,
1126 cp_object_id VARCHAR2)
1127 IS
1128 SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
1129 instance_sets.predicate, instance_sets.instance_set_id,
1130 g.grant_guid
1131 FROM
1132 ( select /*+ NO_MERGE */ role_name
1133 from wf_user_roles wur,
1134 (
1135 select cp_user_name name from dual
1136 union all
1137 select incr1.name name
1138 from wf_local_roles incr1, fnd_user u1
1139 where 'HZ_PARTY' = incr1.orig_system
1140 and u1.user_name = cp_user_name
1141 and u1.person_party_id = incr1.orig_system_id
1142 and incr1.partition_id = 9 /* HZ_PARTY */
1143 ) incr2
1144 where wur.user_name = incr2.name
1145 ) u2,
1146 fnd_grants g,
1147 fnd_object_instance_sets instance_sets
1148 WHERE g.grantee_key = u2.role_name
1149 AND g.object_id = cp_object_id
1150 AND (g.menu_id in
1151 (select cmf.menu_id
1152 from fnd_compiled_menu_functions cmf
1153 where cmf.function_id = cp_function_id))
1154 AND g.instance_set_id = instance_sets.instance_set_id
1155 AND ( g.ctx_secgrp_id = -1
1156 OR g.ctx_secgrp_id =
1157 SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
1158 AND ( g.ctx_resp_id = -1
1159 OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
1160 AND ( g.ctx_resp_appl_id = -1
1161 OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
1162 AND ( g.ctx_org_id = -1
1163 OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
1164 AND g.start_date <= SYSDATE
1165 AND ( g.end_date IS NULL
1166 OR g.end_date >= SYSDATE )
1167 ORDER BY instance_sets.predicate,
1168 instance_sets.instance_set_id desc;
1169
1170
1171 /* Which instance sets are granted to specific function? */
1172 CURSOR isg_glob_fn_c (cp_user_name varchar2,
1173 cp_function_id NUMBER,
1174 cp_object_id VARCHAR2)
1175 IS
1176 SELECT instance_sets.predicate, instance_sets.instance_set_id,
1177 g.grant_guid
1178 FROM fnd_grants g,
1179 fnd_object_instance_sets instance_sets
1180 WHERE g.instance_type = 'SET'
1181 AND (g.grantee_type = 'GLOBAL')
1182 AND g.object_id = cp_object_id
1183 AND (g.menu_id in
1184 (select cmf.menu_id
1185 from fnd_compiled_menu_functions cmf
1186 where cmf.function_id = cp_function_id))
1187 AND g.instance_set_id = instance_sets.instance_set_id
1188 AND ( g.ctx_secgrp_id = -1
1189 OR g.ctx_secgrp_id =
1190 SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
1191 AND ( g.ctx_resp_id = -1
1195 AND ( g.ctx_org_id = -1
1192 OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
1193 AND ( g.ctx_resp_appl_id = -1
1194 OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
1196 OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
1197 AND g.start_date <= SYSDATE
1198 AND ( g.end_date IS NULL
1199 OR g.end_date >= SYSDATE )
1200 ORDER BY instance_sets.predicate,
1201 instance_sets.instance_set_id desc;
1202
1203
1204 l_object_id number := -2;
1205 l_function_id number := -2;
1206
1207 BEGIN
1208
1209 x_function_id := NULL;
1210 x_object_id := NULL;
1211 x_bind_order := NULL;
1212 x_predicate := NULL;
1213 x_return_status := 'T'; /* Assume Success */
1214
1215 -- check for call compatibility.
1216 if TRUNC(l_api_version) <> TRUNC(p_api_version) THEN
1217 x_return_status := 'U'; /* Unexpected Error */
1218 return;
1219 END IF;
1220
1221 /* default username if necessary. */
1222 if (p_user_name is NULL) then
1223 l_user_name := SYS_CONTEXT('FND','USER_NAME');
1224 else
1225 l_user_name := p_user_name;
1226 end if;
1227
1228
1229
1230
1231 /* Check one level cache to see if we have this value cached already*/
1232 if ( (g_gsp_function = p_function
1233 or (g_gsp_function is NULL and p_function is NULL))
1234 AND (g_gsp_object_name = p_object_name
1235 or (g_gsp_object_name is NULL and p_object_name is NULL))
1236 AND (g_gsp_grant_instance_type = p_grant_instance_type)
1237 AND (g_gsp_user_name = l_user_name
1238 or (g_gsp_user_name is NULL and l_user_name is NULL))
1239 AND (g_gsp_statement_type = p_statement_type)
1240 AND ( g_gsp_table_alias = p_table_alias
1241 or (g_gsp_table_alias is NULL and p_table_alias is NULL))
1242 AND (g_gsp_with_binds = p_with_binds)
1243 AND (g_gsp_context_user_id = SYS_CONTEXT('FND','USER_ID'))
1244 AND (g_gsp_context_resp_id = SYS_CONTEXT('FND','RESP_ID'))
1245 AND (g_gsp_context_secgrpid =
1246 SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
1247 AND (g_gsp_context_resp_appl_id =
1248 SYS_CONTEXT('FND','RESP_APPL_ID'))
1249 AND ( (g_gsp_context_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
1250 or ( g_gsp_context_org_id is NULL
1251 and SYS_CONTEXT('FND', 'ORG_ID') is NULL))) then
1252 x_predicate := g_gsp_predicate;
1253 x_return_status := g_gsp_return_status;
1254 x_object_id := g_gsp_object_id;
1255 x_function_id := g_gsp_function_id;
1256 x_bind_order := g_gsp_bind_order;
1257 return;
1258 end if;
1259
1260 -- Check to make sure we're not using unsupported statement_type
1261 if ( (p_statement_type <> 'VPD')
1262 AND (p_statement_type <> 'BASE' /* Deprecated, same as VPD */)
1263 AND (p_statement_type <> 'OTHER')
1264 AND (p_statement_type <> 'EXISTS')) then
1265 x_return_status := 'U'; /* Unexpected Error */
1266 return;
1267 end if;
1268
1269
1270 /* Make sure that the FND_COMPILED_MENU_FUNCTIONS table is compiled */
1271 if (FND_FUNCTION.G_ALREADY_FAST_COMPILED <> 'T') then
1272 FND_FUNCTION.FAST_COMPILE;
1273 end if;
1274
1275
1276 /* check if we need to call through to old routine */
1277 -- IF ( (p_grant_instance_type = 'FUNCLIST')
1278 -- OR (p_grant_instance_type = 'FUNCLIST_NOINST')
1279 -- OR (p_grant_instance_type = 'GRANTS_ONLY'))THEN
1280 -- /* If this is one of the modes that require the old-style */
1281 -- /* statement, just call the old code for that. */
1282 -- get_security_predicate_helper(
1283 -- p_function,
1284 -- p_object_name,
1285 -- p_grant_instance_type,
1286 -- p_user_name,
1287 -- p_statement_type,
1288 -- x_predicate,
1289 -- x_return_status,
1290 -- p_table_alias);
1291 -- return;
1292 -- end if;
1293
1294 -- Check to make sure a valid role is passed or defaulted for user_name
1295 if (check_user_role (l_user_name) = 'F') then
1296 -- If we got here then the grantee will never be found because
1297 -- it isn't even a role, so we know there won't be a matching grant.
1298 l_aggregate_predicate := '1=2';
1299 x_return_status := 'E'; /* Error condition */
1300 goto return_and_cache;
1301 end if;
1302
1303 /* Set up flags depending on which mode we are running in. */
1304 IF (p_grant_instance_type = C_TYPE_INSTANCE) THEN
1305 l_instance_set_flag:= FALSE;
1306 ELSIF (p_grant_instance_type = C_TYPE_SET) THEN
1307 l_instance_flag:= FALSE;
1308 ELSIF (p_grant_instance_type = 'UNIVERSAL') THEN
1309 null;
1310 END IF;
1311
1312
1313 -- Get the key columns from the user name
1314 -- We are not checking for NULL returns (meaning user not in wf_roles)
1315 -- because right now we allow checking of grants to users not in
1316 -- wf_roles.
1317 get_name_bind(l_user_name,
1318 l_user_name_bind);
1319
1320
1321 if (p_object_name is NULL) THEN
1322 x_return_status := 'U';
1323 return;
1324 END IF;
1325
1326 if(p_object_name = 'GLOBAL') then
1327 x_return_status := 'U';
1331 x_return_status := get_pk_information(p_object_name,
1328 return;
1329 else /* Normal case */
1330 /* Get the primary key lists and info for this object */
1332 l_db_pk1_column ,
1333 l_db_pk2_column ,
1334 l_db_pk3_column ,
1335 l_db_pk4_column ,
1336 l_db_pk5_column ,
1337 l_pk_column_names ,
1338 l_ik_clause,
1339 l_exact_clause,
1340 l_pk_orig_column_names,
1341 l_db_object_name,
1342 p_table_alias,
1343 'GNT');
1344 if (x_return_status <> 'T') then
1345 /* There will be a message on the msg dict stack. */
1346 return; /* We will return the x_return_status as out param */
1347 end if;
1348
1349 if (p_table_alias is not NULL) then
1350 l_table_alias := p_table_alias || '.';
1351 l_pk_orig_column_names := l_table_alias ||
1352 replace (l_pk_orig_column_names, ', ',
1353 ', '||l_table_alias);
1354 else
1355 l_table_alias := NULL;
1356 end if;
1357
1358 l_object_id :=get_object_id(p_object_name );
1359 if (l_object_id is NULL) THEN
1360 x_return_status := 'U';
1361 return;
1362 END IF;
1363 end if;
1364
1365 if(p_function is NULL) then
1366 l_function_id := -1;
1367 else
1368 l_function_id := get_function_id(p_function);
1369 if (l_function_id is NULL) THEN
1370 x_return_status := 'U';
1371 return;
1372 END IF;
1373 end if;
1374
1375 -- Performance note: we are doing up to six SQL statements in order
1376 -- to determine whether there exists all the possible combinations
1377 -- of grantee_type either GROUP (including USER) or GLOBAL
1378 -- and instance_type GLOBAL, INSTANCE, or SET.
1379 -- We had originally tried to check all these possibilities with
1380 -- a single statement, but it didn't perform adequately due to
1381 -- the ORs and sorting necessary, so this is the best solution.
1382 -- Perhaps if we get the WF folks to put GLOBAL (all) users into the
1383 -- a group in WF_USER_ROLES then we could go down to only 3 SQL
1384 -- or even one.
1385
1386 --
1387 -- Do not check for GLOBAL grants when the user is GUEST
1388 --
1389 if (l_user_name <> 'GUEST') then
1390 --
1391 -- check for 'GLOBAL' instance type
1392 --
1393 /* See if there are any grants with */
1394 /* grantee_type='GLOBAL' and instance_type = 'GLOBAL' that apply*/
1395 l_dummy := -1;
1396 open grant_types_global_c (l_user_name,
1397 l_function_id,
1398 l_object_id,
1399 'GLOBAL');
1400 fetch grant_types_global_c into l_dummy;
1401 IF(grant_types_global_c%NOTFOUND) THEN
1402 NULL;
1403 else
1404 if(l_dummy = 1) then
1405 l_global_instance_type := TRUE;
1406 close grant_types_global_c;
1407 goto global_inst_type;
1408 end if;
1409 end if;
1410 close grant_types_global_c;
1411 end if;
1412
1413 /* See if there are any grants with */
1414 /* grantee_type='GROUP' and instance_type = 'GLOBAL' that apply*/
1415 l_dummy := -1;
1416
1417
1418 open grant_types_group_c (l_user_name,
1419 l_function_id,
1420 l_object_id,
1421 'GLOBAL');
1422 fetch grant_types_group_c into l_dummy;
1423 IF(grant_types_group_c%NOTFOUND) THEN
1424 NULL;
1425 else
1426 if(l_dummy = 1) then
1427 l_global_instance_type := TRUE;
1428 close grant_types_group_c;
1429 goto global_inst_type;
1430 end if;
1431 end if;
1432 close grant_types_group_c;
1433
1434
1435 --
1436 -- check for 'SET' instance type
1437 --
1438 if l_instance_set_flag then
1439 /* See if there are any grants with */
1440 --
1441 -- Do not check for GLOBAL grants when the user is GUEST
1442 --
1443 if (l_user_name <> 'GUEST') then
1444 /* grantee_type='GLOBAL' and instance_type = 'SET' that apply*/
1445 l_dummy := -1;
1446 open grant_types_global_c (l_user_name,
1447 l_function_id,
1448 l_object_id,
1449 'SET');
1450 fetch grant_types_global_c into l_dummy;
1451 IF(grant_types_global_c%NOTFOUND) THEN
1452 NULL;
1453 else
1454 if(l_dummy = 1) then
1455 l_set_instance_type := TRUE;
1456 l_set_global_grantee_type := TRUE;
1457 end if;
1458 end if;
1459 close grant_types_global_c;
1460 end if;
1461
1462
1463 /* See if there are any grants with */
1464 /* grantee_type='GROUP' and instance_type = 'SET' that apply*/
1465
1469 open grant_types_group_c (l_user_name,
1466 l_dummy := -1;
1467
1468
1470 l_function_id,
1471 l_object_id,
1472 'SET');
1473 fetch grant_types_group_c into l_dummy;
1474 IF(grant_types_group_c%NOTFOUND) THEN
1475 NULL;
1476 else
1477 if(l_dummy = 1) then
1478 l_set_instance_type := TRUE;
1479 l_set_group_grantee_type := TRUE;
1480 end if;
1481 end if;
1482 close grant_types_group_c;
1483
1484 end if; /* l_instance_set_flag */
1485
1486 --
1487 -- check for 'INSTANCE' instance type
1488 --
1489 if l_instance_flag then
1490 /* See if there are any grants with */
1491 --
1492 -- Do not check for GLOBAL grants when the user is GUEST
1493 --
1494 if (l_user_name <> 'GUEST') then
1495
1496 /* grantee_type='GLOBAL' and instance_type = 'INSTANCE' that apply*/
1497 l_dummy := -1;
1498 open grant_types_global_c (l_user_name,
1499 l_function_id,
1500 l_object_id,
1501 'INSTANCE');
1502 fetch grant_types_global_c into l_dummy;
1503 IF(grant_types_global_c%NOTFOUND) THEN
1504 NULL;
1505 else
1506 if(l_dummy = 1) then
1507 l_inst_instance_type := TRUE;
1508 l_inst_global_grantee_type := TRUE;
1509 end if;
1510 end if;
1511 close grant_types_global_c;
1512 end if;
1513
1514 /* See if there are any grants with */
1515 /* grantee_type='GROUP' and instance_type = 'INSTANCE' that apply*/
1516 l_dummy := -1;
1517
1518
1519 open grant_types_group_c (l_user_name,
1520 l_function_id,
1521 l_object_id,
1522 'INSTANCE');
1523 fetch grant_types_group_c into l_dummy;
1524 IF(grant_types_group_c%NOTFOUND) THEN
1525 NULL;
1526 else
1527 if(l_dummy = 1) then
1528 l_inst_instance_type := TRUE;
1529 l_inst_group_grantee_type := TRUE;
1530 end if;
1531 end if;
1532 close grant_types_group_c;
1533
1534
1535 end if; /* l_instance_flag */
1536
1537 <<global_inst_type>>
1538
1539 /* If we have a global instance type grant, then all rows are */
1540 /* in scope, so just return 1=1 */
1541 if(l_global_instance_type = TRUE) then
1542 l_aggregate_predicate := '1=1';
1543 x_return_status := 'T';
1544 goto return_and_cache;
1545 end if;
1546
1547 /* If there are no instance sets and we aren't looking for */
1548 /* instances, then there won't be any rows returned by the */
1549 /* predicate so return (1=2) */
1550 if(l_global_instance_type = FALSE and
1551 l_inst_instance_type = FALSE and
1552 l_set_instance_type = FALSE) then
1553 l_aggregate_predicate := '1=2';
1554 x_return_status := 'T';
1555 goto return_and_cache;
1556 end if;
1557
1558 /* If we have an instance type grant, but no recognized grantee, */
1559 /* that is a data error, so signal that error */
1560 if(l_inst_instance_type = TRUE and
1561 l_inst_group_grantee_type = FALSE and
1562 l_inst_global_grantee_type = FALSE) then
1563 l_set_instance_type := TRUE;
1564 end if;
1565
1566 /* Build up the instance set part of the predicate */
1567 l_last_pred := '*NO_PRED*';
1568 if(l_set_instance_type = TRUE) then
1569
1570 l_last_instance_set_id := -11162202;
1571 l_need_to_close_pred := FALSE;
1572 l_refers_to_grants := FALSE;
1573
1574 l_grp_glob_fn := FALSE;
1575 l_grp_glob_nofn := FALSE;
1576 l_grp_fn := FALSE;
1577 l_grp_nofn := FALSE;
1578 l_glob_fn := FALSE;
1579 l_glob_nofn := FALSE;
1580
1581 /* Open one of six different cursors */
1582 if ( l_set_group_grantee_type
1583 AND l_set_global_grantee_type
1584 AND l_function_id <> -1) then
1585 OPEN isg_grp_glob_fn_c ( l_user_name,
1586 l_function_id,
1587 l_object_id);
1588 l_grp_glob_fn := TRUE;
1589
1590 elsif( l_set_group_grantee_type
1591 AND (NOT l_set_global_grantee_type)
1592 AND l_function_id <> -1) then
1593
1594 OPEN isg_grp_fn_c (l_user_name,
1595 l_function_id,
1596 l_object_id);
1597 l_grp_fn := TRUE;
1598
1599 elsif(NOT l_set_group_grantee_type
1600 AND l_set_global_grantee_type
1601 AND l_function_id <> -1) then
1602 OPEN isg_glob_fn_c (l_user_name,
1603 l_function_id,
1604 l_object_id);
1605 l_glob_fn := TRUE;
1606 else
1607 x_return_status := 'U';
1608 return;
1612 LOOP
1609 end if;
1610
1611 l_cursor_is_open := TRUE;
1613 if (l_grp_glob_fn) then
1614 FETCH isg_grp_glob_fn_c INTO d_predicate, d_instance_set_id,
1615 d_grant_guid;
1616 if (isg_grp_glob_fn_c%notfound) then
1617 close isg_grp_glob_fn_c;
1618 l_cursor_is_open := FALSE;
1619 exit; -- exit loop
1620 end if;
1621 elsif (l_grp_fn) then
1622 FETCH isg_grp_fn_c INTO d_predicate, d_instance_set_id,
1623 d_grant_guid;
1624 if (isg_grp_fn_c%notfound) then
1625 close isg_grp_fn_c;
1626 l_cursor_is_open := FALSE;
1627 exit; -- exit loop
1628 end if;
1629
1630 elsif (l_glob_fn) then
1631 FETCH isg_glob_fn_c INTO d_predicate, d_instance_set_id,
1632 d_grant_guid;
1633 if (isg_glob_fn_c%notfound) then
1634 close isg_glob_fn_c;
1635 l_cursor_is_open := FALSE;
1636 exit; -- exit loop
1637 end if;
1638 else
1639 x_return_status := 'U';
1640 return;
1641 end if;
1642
1643 /* If we are coming upon a new instance set */
1644 if (d_instance_set_id <>
1645 l_last_instance_set_id) then
1646 if (l_need_to_close_pred) then /* Close off the last pred */
1647 l_aggregate_predicate := substrb( l_aggregate_predicate ||
1648 ') AND '|| l_pred ||')', 1, c_pred_buf_size);
1649 l_need_to_close_pred := FALSE;
1650 l_last_was_hextoraw := FALSE;
1651 end if;
1652
1653 /* If we need to add an OR, do so. */
1654 if (l_last_pred <> '*NO_PRED*') then
1655 l_aggregate_predicate := substrb( l_aggregate_predicate ||
1656 ' OR ', 1, c_pred_buf_size);
1657 end if;
1658
1659 /* Upgrade and substitute predicate */
1660 l_pred := upgrade_predicate(
1661 d_predicate);
1662
1663 /* If this is the simple form of predicate that does not */
1664 /* refer to parameters in the grant table */
1665 if (instr(l_pred, C_GRANT_ALIAS_TOK) <> 0) then
1666 l_uses_params := TRUE;
1667 else
1668 l_uses_params := FALSE;
1669 end if;
1670
1671 l_pred := substitute_predicate(
1672 l_pred,
1673 p_table_alias);
1674
1675 /* If this is the simple form of predicate that does not */
1676 /* refer to parameters in the grant table */
1677 if ( NOT l_uses_params) then
1678 l_aggregate_predicate :=
1679 substrb( l_aggregate_predicate ||
1680 '('|| l_pred ||')', 1, c_pred_buf_size);
1681 l_need_to_close_pred := FALSE;
1682 l_refers_to_grants := FALSE;
1683 else /* Has references to grant table so we subselect */
1684 /* against the grants table */
1685 -- abedajna begin
1686
1687 top := ' SELECT g.grant_guid ' ||
1688 ' FROM fnd_grants g, fnd_compiled_menu_functions cmf ' ||
1689 ' WHERE g.instance_type = '||''''||'SET'||'''' ||
1690 ' and g.instance_set_id = ' || d_instance_set_id ||
1691 ' AND g.menu_id = cmf.menu_id AND cmf.function_id = '|| l_function_id;
1692
1693
1694
1695 mid1 := ' AND ((g.grantee_key in ' ||
1696 ' (select role_name ' ||
1697 ' from wf_user_roles ' ||
1698 ' where user_name in ' ||
1699 ' (select incrns.name ' ||
1700 ' from wf_local_roles src, ' ||
1701 ' wf_local_roles incrns ' ||
1702 ' where src.name = '||''''||l_user_name||'''' ||
1703 ' and src.parent_orig_system = incrns.parent_orig_system ' ||
1704 ' and src.parent_orig_system_id = incrns.parent_orig_system_id) ' ||
1705 ' )) ' ||
1706 ' OR (g.grantee_type = '||''''|| 'GLOBAL'|| '''' || ')) ';
1707
1708
1709 mid2 := ' AND (g.grantee_key in ' ||
1710 ' (select role_name ' ||
1711 ' from wf_user_roles ' ||
1712 ' where user_name in ' ||
1713 ' (select incrns.name ' ||
1714 ' from wf_local_roles src ' ||
1715 ' ,wf_local_roles incrns ' ||
1716 ' where src.name = '||''''||l_user_name||'''' ||
1717 ' and src.parent_orig_system = ' ||
1718 ' incrns.parent_orig_system ' ||
1719 ' and src.parent_orig_system_id = ' ||
1720 ' incrns.parent_orig_system_id) ' ||
1721 ' )) ';
1722
1723 mid3 := ' AND (g.grantee_type = '||''''|| 'GLOBAL' ||'''' || ') ';
1724
1725
1726 bottom := ' AND g.object_id = ' || l_object_id ||
1727 ' AND ( g.ctx_secgrp_id = -1 ' ||
1728 ' OR g.ctx_secgrp_id = ' ||
1729 ' SYS_CONTEXT('||''''||'FND'||''''||','||''''||'SECURITY_GROUP_ID'||''''||')) ' ||
1733 ' OR g.ctx_resp_appl_id = SYS_CONTEXT('||''''||'FND'||''''||','||''''||'RESP_APPL_ID'||''''||')) ' ||
1730 ' AND ( g.ctx_resp_id = -1 ' ||
1731 ' OR g.ctx_resp_id = SYS_CONTEXT('||''''||'FND'||''''||','||''''||'RESP_ID'||''''||')) ' ||
1732 ' AND ( g.ctx_resp_appl_id = -1 ' ||
1734 ' AND ( g.ctx_org_id = -1 ' ||
1735 ' OR g.ctx_org_id = SYS_CONTEXT('||''''||'FND'||''''||', '||''''||'ORG_ID'||''''||')) ' ||
1736 ' AND g.start_date <= SYSDATE ' ||
1737 ' AND ( g.end_date IS NULL ' ||
1738 ' OR g.end_date >= SYSDATE ) ';
1739
1740
1741 if (l_grp_glob_fn) then
1742 guid_subquery := ' ( '||top || mid1 || bottom;
1743 elsif (l_grp_fn) then
1744 guid_subquery := ' ( '||top || mid2 || bottom;
1745 elsif (l_glob_fn) then
1746 guid_subquery := ' ( '||top || mid3 || bottom;
1747 end if;
1748
1749
1750 -- abedajna end
1751
1752 l_aggregate_predicate :=
1753 substrb( l_aggregate_predicate ||
1754 ' exists (select null'||
1755 ' from fnd_grants gnt'||
1756 ' where gnt.grant_guid in ' || guid_subquery,
1757 1, c_pred_buf_size);
1758 l_need_to_close_pred := TRUE;
1759 l_refers_to_grants := TRUE;
1760 end if;
1761 end if;
1762
1763 l_last_instance_set_id := d_instance_set_id;
1764 l_last_pred := d_predicate;
1765
1766 /* Add this grant_guid to the predicate */
1767 -- if (l_refers_to_grants) then
1768 -- if (l_last_was_hextoraw) then /* Add a comma if necessary */
1769 -- l_aggregate_predicate :=
1770 -- substrb(l_aggregate_predicate ||
1771 -- ', ', 1, c_pred_buf_size);
1772 -- end if;
1773 -- l_aggregate_predicate :=
1774 -- substrb( l_aggregate_predicate ||
1775 -- 'hextoraw('''|| d_grant_guid
1776 -- ||''')', 1, c_pred_buf_size);
1777 -- l_last_was_hextoraw := TRUE;
1778 -- else
1779 -- l_last_was_hextoraw := FALSE;
1780 -- end if;
1781 END LOOP;
1782
1783 /* Close the cursor */
1784 if (l_cursor_is_open) then
1785 if (l_grp_glob_fn) then
1786 close isg_grp_glob_fn_c;
1787 elsif (l_grp_fn) then
1788 close isg_grp_fn_c;
1789 elsif (l_glob_fn) then
1790 close isg_glob_fn_c;
1791 else
1792 x_return_status := 'U';
1793 return;
1794 end if;
1795 end if;
1796
1797 if (l_need_to_close_pred) then /* Close off the last pred */
1798 l_aggregate_predicate := substrb( l_aggregate_predicate ||
1799 ') AND '|| l_pred ||')', 1, c_pred_buf_size);
1800 l_need_to_close_pred := FALSE;
1801 l_last_was_hextoraw := FALSE;
1802 end if;
1803
1804 end if;
1805
1806 /* If there were no predicates found */
1807 if (l_last_pred = '*NO_PRED*') then
1808 l_set_instance_type := FALSE;
1809 end if;
1810
1811
1812 /* ---------- Instance part */
1813 if (l_inst_instance_type) then
1814 l_instance_predicate :=
1815 l_instance_predicate ||
1816 ' exists (select null'||
1817 ' from fnd_grants gnt';
1818 if (p_with_binds = 'Y') then
1819 l_instance_predicate :=
1820 l_instance_predicate ||
1821 ' where (GNT.object_id = :OBJECT_ID_BIND'||
1822 ' AND (';
1823 x_object_id := l_object_id;
1824 if(x_bind_order is not NULL) then
1825 x_bind_order := x_bind_order || 'O';
1826 else
1827 x_bind_order := 'O';
1828 end if;
1829 else
1830 l_instance_predicate :=
1831 l_instance_predicate ||
1832 ' where (GNT.object_id = ' || l_object_id ||
1833 ' AND (';
1834 end if;
1835
1836 if (l_inst_group_grantee_type) then
1837
1838 l_instance_predicate :=
1839 l_instance_predicate ||
1840 ' ( GNT.grantee_key in ' ||
1841 ' (select role_name '||
1842 ' from wf_user_roles wur'||
1843 ' where wur.user_name in '||
1844 ' (select incrns.name '||
1845 ' from wf_local_roles src '||
1846 ' ,wf_local_roles incrns '||
1847 ' where src.name = ' ||
1848 l_user_name_bind ||
1849 ' and src.parent_orig_system '||
1850 ' = incrns.parent_orig_system '||
1851 ' and src.parent_orig_system_id '||
1852 ' = incrns.parent_orig_system_id)))';
1853 end if;
1854
1855 if (l_inst_global_grantee_type) then
1856 if (l_inst_group_grantee_type) then
1857 l_instance_predicate :=
1858 l_instance_predicate ||
1859 ' OR';
1860 end if;
1864 /* Close off the grantee part */
1861 l_instance_predicate := l_instance_predicate ||
1862 ' (GNT.grantee_type = ''GLOBAL'')';
1863 end if;
1865 l_instance_predicate := l_instance_predicate ||
1866 ' )';
1867 if (p_with_binds = 'Y') then /* If returning a stmnt w/ binds*/
1868 if (l_function_id <> -1) then
1869 l_instance_predicate := l_instance_predicate ||
1870 ' AND GNT.menu_id in'||
1871 ' (select cmf.menu_id'||
1872 ' from fnd_compiled_menu_functions cmf'||
1873 ' where cmf.function_id = :FUNCTION_ID_BIND )';
1874 x_function_id := l_function_id;
1875 if(x_bind_order is not NULL) then
1876 x_bind_order := x_bind_order || 'F';
1877 else
1878 x_bind_order := 'F';
1879 end if;
1880 end if;
1881 else
1882 if (l_function_id <> -1) then
1883 l_instance_predicate := l_instance_predicate ||
1884 ' AND GNT.menu_id in'||
1885 ' (select cmf.menu_id'||
1886 ' from fnd_compiled_menu_functions cmf'||
1887 ' where cmf.function_id = '||l_function_id||')';
1888 end if;
1889 end if;
1890 l_instance_predicate := l_instance_predicate ||
1891 ' AND( GNT.ctx_secgrp_id = -1'||
1892 ' OR GNT.ctx_secgrp_id = '||
1893 ' SYS_CONTEXT(''FND'',''SECURITY_GROUP_ID''))'||
1894 ' AND( GNT.ctx_resp_id = -1'||
1895 ' OR GNT.ctx_resp_id = '||
1896 ' SYS_CONTEXT(''FND'',''RESP_ID''))'||
1897 ' AND( GNT.ctx_resp_appl_id = -1'||
1898 ' OR GNT.ctx_resp_appl_id ='||
1899 ' SYS_CONTEXT(''FND'',''RESP_APPL_ID''))'||
1900 ' AND( GNT.ctx_org_id = -1'||
1901 ' OR GNT.ctx_org_id ='||
1902 ' SYS_CONTEXT(''FND'', ''ORG_ID''))'||
1903 ' AND GNT.start_date <= sysdate ' ||
1904 ' AND ( GNT.end_date IS NULL ' ||
1905 ' OR GNT.end_date >= sysdate ) ';
1906
1907 /* Add on the clause for INSTANCE_TYPE = 'INSTANCE' */
1908 l_instance_predicate := l_instance_predicate ||
1909 ' AND'|| l_ik_clause||'))';
1910 end if;
1911
1912 /* Add the instance predicate on to the end */
1913 if (l_set_instance_type and l_inst_instance_type) then
1914 l_aggregate_predicate := substrb(l_aggregate_predicate ||
1915 ' OR', 1, c_pred_buf_size);
1916 end if;
1917 if(l_inst_instance_type) then
1918 l_aggregate_predicate := substrb(l_aggregate_predicate ||
1919 l_instance_predicate, 1, c_pred_buf_size);
1920 end if;
1921
1922 /* If we have no predicate, then return 1=2. This is for robustness*/
1923 /* but probably isn't needed in practice. */
1924 if( l_aggregate_predicate is NULL
1925 or l_aggregate_predicate = '*NO_PRED*') then
1926 l_aggregate_predicate := '1=2';
1927 end if;
1928
1929 <<return_and_cache>>
1930
1931 /* Put parentheses around the statement in order to make it */
1932 /* amenable to ANDing with another statement */
1933 if(p_statement_type = 'EXISTS')then
1934 x_predicate :='ROWNUM=1 and ('||l_aggregate_predicate||')';
1935 else
1936 x_predicate :='('||l_aggregate_predicate||')';
1937 end if;
1938
1939 if ( (lengthb(l_aggregate_predicate) > c_vpd_buf_limit)
1940 AND ( (p_statement_type = 'BASE') /* deprecated, same as VPD*/
1941 or (p_statement_type = 'VPD')))then
1942 FND_MESSAGE.SET_NAME('FND', 'GENERIC-INTERNAL ERROR');
1943 FND_MESSAGE.SET_TOKEN('ROUTINE',
1944 'FND_DATA_SECURITY.GET_SECURITY_PREDICATE');
1945 FND_MESSAGE.SET_TOKEN('REASON',
1946 'The predicate was longer than the database VPD limit of '||
1947 to_char(c_vpd_buf_limit)||' bytes for the predicate. ');
1948 x_return_status := 'L'; /* Indicate Error */
1949 end if;
1950
1951 /* For VPD, null predicate is logically equivalent to and performs */
1952 /* similarly to (1=1) so return that. */
1953 if ( (x_predicate = '(1=1)')
1954 AND ( (p_statement_type = 'BASE') /* deprecated, same as VPD*/
1955 or (p_statement_type = 'VPD'))) then
1956 x_predicate := NULL;
1957 end if;
1958
1959
1960 /* Set params and results into 1-level cache for next time */
1961 g_gsp_function := p_function;
1962 g_gsp_object_name := p_object_name;
1963 g_gsp_grant_instance_type := p_grant_instance_type;
1964 g_gsp_user_name := l_user_name;
1965 g_gsp_statement_type := p_statement_type;
1966 g_gsp_table_alias := p_table_alias;
1967 g_gsp_with_binds := p_with_binds;
1968 g_gsp_context_user_id := SYS_CONTEXT('FND','USER_ID');
1969 g_gsp_context_resp_id := SYS_CONTEXT('FND','RESP_ID');
1970 g_gsp_context_secgrpid := SYS_CONTEXT('FND','SECURITY_GROUP_ID');
1971 g_gsp_context_resp_appl_id := SYS_CONTEXT('FND','RESP_APPL_ID');
1972 g_gsp_context_org_id := SYS_CONTEXT('FND', 'ORG_ID');
1973 g_gsp_predicate := x_predicate;
1974 g_gsp_return_status := x_return_status;
1975 g_gsp_object_id := x_object_id;
1976 g_gsp_function_id := x_function_id;
1977 g_gsp_bind_order := x_bind_order;
1978
1979 EXCEPTION
1980 WHEN OTHERS THEN
1981 x_return_status := 'U';
1982 return;
1983 END get_security_predicate_intrnl;
1984
1985
1986 -- abedajna: same as the one in fnd_data_security, except that it
1987 -- calls my own get_security_predicate_internal.
1988
1989 PROCEDURE get_security_predicate(
1990 p_api_version IN NUMBER,
1991 p_function IN VARCHAR2,
1992 p_object_name IN VARCHAR2,
1993 p_grant_instance_type IN VARCHAR2,/* SET, INSTANCE*/
1994 p_user_name IN VARCHAR2,
1995 /* stmnt_type: 'OTHER', 'VPD'=VPD, 'EXISTS'= for checking existence. */
1996 p_statement_type IN VARCHAR2,
1997 x_predicate out NOCOPY varchar2,
1998 x_return_status out NOCOPY varchar2,
1999 p_table_alias IN VARCHAR2 DEFAULT NULL
2000 ) IS
2001 l_api_name CONSTANT VARCHAR2(30) := 'GET_SECURITY_PREDICATE';
2002 l_api_version CONSTANT NUMBER := 1.0;
2003 x_function_id NUMBER;
2004 x_object_id NUMBER;
2005 x_bind_order varchar2(256);
2006 BEGIN
2007
2008 -- check for call compatibility.
2009 if TRUNC(l_api_version) <> TRUNC(p_api_version) THEN
2010 x_return_status := 'U'; /* Unexpected Error */
2011 return;
2012 END IF;
2013
2014 get_security_predicate_intrnl(
2015 p_api_version, p_function, p_object_name, p_grant_instance_type,
2016 p_user_name, p_statement_type, p_table_alias, 'N',
2017 x_predicate, x_return_status,
2018 x_function_id, x_object_id, x_bind_order);
2019
2020 END;
2021
2022 end AMW_SECURITY_PUB;