[Home] [Help]
PACKAGE BODY: APPS.EDW_SEC_PKG
Source
1 package body edw_sec_pkg as
2 /* $Header: EDWSPKGB.pls 120.2 2005/09/20 00:07:19 amitgupt noship $*/
3
4 -- This procedure sets the EDW context attributes
5
6 PROCEDURE set_context IS
7
8 -- PRAGMA AUTONOMOUS_TRANSACTION;
9
10 cursor dim_access_cursor is
11 select distinct dim_id from edw_sec_dim_access
12 where resp_id = ses_resp_id;
13
14 current_dim_id edw_sec_dim_info_t.dim_id%TYPE;
15 current_level_id edw_sec_lvl_info_t.level_id%TYPE;
16
17 cursor access_value_cursor is
18 select distinct level_id
19 from edw_sec_dim_access
20 where resp_id = ses_resp_id
21 and dim_id = current_dim_id;
22
23 x_db_version varchar2(30);
24 x_compatible varchar2(30);
25 version_number number;
26 max_context_length number;
27
28 dim_access_rec dim_access_cursor%ROWTYPE;
29 access_value_rec access_value_cursor%ROWTYPE;
30
31 x_context_value varchar2(4000) := NULL;
32 temp_context_value varchar2(4000) := NULL;
33 x_context_name edw_sec_dim_info_t.context_name%TYPE := NULL;
34 x_table_name edw_sec_dim_info_t.table_name%TYPE;
35 x_lowest_level_col_name edw_sec_dim_info_t.lowest_level_col_name%TYPE;
36 x_star_user_col_name edw_sec_lvl_info_t.star_level_name_col_name%TYPE;
37 x_access_value number;
38
39 x_object_name varchar2(30) := 'EDW_SEC_PKG.SET_CONTEXT';
40 x_object_type varchar2(30) := 'Security Procedure';
41
42 v_Errorcode number;
43 v_ErrorText varchar2(200);
44
45 LARGE_CONTEXT_LENGTH EXCEPTION;
46
47 x_default_sec varchar2(30) := NULL;
48
49 x_message varchar2(2000);
50
51 BEGIN
52
53 --Reset context values
54 dbms_session.set_context('edw_context', 'resp_context',null);
55 dbms_session.set_context('edw_context', 'error_context',null);
56 dbms_session.set_context('edw_context', 'db_version_context',null);
57
58
59 --Get responsibility_id
60
61 ses_resp_id := FND_PROFILE.VALUE('RESP_ID');
62
63 --Get database version
64
65 DBMS_UTILITY.DB_VERSION(x_db_version, x_compatible);
66 --code change for bug 4498820
67 -- logic changed to take care of 10 G databases
68 --version_number := to_number(substr(x_db_version, 0, 1)||substr(x_db_version, 3, 1)||substr(x_db_version, 5, 1));
69 version_number := replace(substr(x_db_version,1,instr(x_db_version,'.',1,2)+1),'.');
70 dbms_session.set_context('edw_context', 'db_version_context', to_char(version_number));
71
72
73 -- Set maximum allowable context length
74
75 If version_number < 816 then
76 max_context_length := 256;
77 else
78 max_context_length := 4000;
79 end if;
80
81 -- Process the dimensions for which security is defined for current responsibility
82
83 FOR dim_access_rec IN dim_access_cursor LOOP
84
85 select context_name, table_name, lowest_level_col_name
86 into x_context_name, x_table_name, x_lowest_level_col_name
87 from edw_sec_dim_info_t
88 where dim_id = dim_access_rec.dim_id;
89
90 current_dim_id := dim_access_rec.dim_id;
91
92 x_context_value := ' select ' ||
93 x_lowest_level_col_name ||
94 ' from '||
95 x_table_name ||
96 ' where ';
97
98
99
100
101 -- Following 'FOR' loop takes care of multiple access_values for a dimension
102
103 FOR access_value_rec IN access_value_cursor LOOP
104
105 select star_level_name_col_name
106 into x_star_user_col_name
107 from edw_sec_lvl_info_t
108 where dim_id = current_dim_id
109 and level_id = access_value_rec.level_id;
110
111
112 current_level_id := access_value_rec.level_id;
113
114 If temp_context_value is null then
115 temp_context_value := x_star_user_col_name ||
116 ' in ( select access_value from edw_sec_dim_access where resp_id = ' || ses_resp_id || ' and dim_id = ' || current_dim_id || ' and level_id = ' || current_level_id ||' )';
117
118 else
119 temp_context_value := temp_context_value ||
120 ' OR ' ||
121 x_star_user_col_name ||
122 ' in ( select access_value from edw_sec_dim_access where resp_id = ' || ses_resp_id || ' and dim_id = ' || current_dim_id || ' and level_id = ' || current_level_id ||' )';
123 end if;
124
125 END LOOP;
126
127 x_context_value := x_context_value || temp_context_value;
128
129 temp_context_value := NULL;
130
131 x_context_name := '' || x_context_name || '';
132
133
134 -- Check the context length
135
136 If length(x_context_value) > max_context_length then
137 RAISE LARGE_CONTEXT_LENGTH;
138 end if;
139
140
141 -- Set the context attribute for the dimension
142
143 dbms_session.set_context('edw_context', x_context_name, x_context_value);
144
145 END LOOP;
146
147 dbms_session.set_context('edw_context', 'resp_context', ses_resp_id);
148
149 commit;
150
151
152 --Get edw_default_security_profile
153
154 x_default_sec := FND_PROFILE.VALUE('EDW_DEFAULT_SECURITY');
155 x_default_sec := UPPER(x_default_sec);
156
157 dbms_session.set_context('edw_context', 'DEF_SEC_ENABLE', x_default_sec);
158
159 IF x_default_sec = 'Y' THEN /* Need to implement default security */
160
161 edw_sec_pkg.link_aol_user;
162 edw_sec_pkg.set_default_context;
163
164 END IF;
165
166
167 EXCEPTION
168
169 WHEN NO_DATA_FOUND THEN NULL;
170
171 WHEN LARGE_CONTEXT_LENGTH THEN
172
173 -- Log error message into edw_error_log table
174
175 x_message := 'Context length exceeds maximum allowable limit.
176 Please try by reducing the number of dimensions or levels on which security is defined.';
177 edw_sec_util.log_error(x_object_name, x_object_type, ses_resp_id, null, x_message);
178
179
180 -- Set the error_context to make security function aware of the error occured in initialization function
181
182 dbms_session.set_context('edw_context', 'error_context', 'TRUE');
183 commit;
184
185 WHEN OTHERS THEN
186 v_ErrorCode := SQLCODE;
187 v_ErrorText := SUBSTR(SQLERRM, 1, 200);
188
189 -- Set the error_context to make security function aware of the error occured in initialization function
190 -- This is moved before logging error so that if error logging fails, still we set the error_context
191
192 dbms_session.set_context('edw_context', 'error_context', 'TRUE');
193
194
195 -- Log error message into edw_error_log table
196
197 x_message := 'Oracle error occured.
198 Errorcode is : ' || v_ErrorCode || ' and Errortext is : ' || v_ErrorText ;
199
200 edw_sec_util.log_error(x_object_name, x_object_type, ses_resp_id, null, x_message);
201
202 commit;
203
204
205
206 END set_context;
207
208
209
210
211 PROCEDURE set_default_context IS
212
213 -- This procedure populates two context attributes.
214 -- 1. DEFAULT_PERSON - This is set with proper subquery
215 -- 2. DEF_DIM_ID - This is populated with dim_id of Person dimension
216
217 -- PRAGMA AUTONOMOUS_TRANSACTION;
218
219 x_db_version varchar2(30);
220 version_number number;
221 max_context_length number;
222
223 x_context_value varchar2(4000) := NULL;
224 x_context_name varchar2(30); -- edw_sec_dim_info_t.context_name%TYPE := NULL;
225
226 x_object_name varchar2(40) := 'EDW_SEC_PKG.SET_DEFAULT_CONTEXT';
227 x_object_type varchar2(30) := 'Security Procedure';
228
229 v_Errorcode number;
230 v_ErrorText varchar2(200);
231
232 LARGE_CONTEXT_LENGTH EXCEPTION;
233
234 error_flag varchar2(30) := NULL;
235 x_def_dim_id edw_sec_dim_info_t.dim_id%TYPE;
236
237 LINK_AOL_USER_ERROR EXCEPTION;
238
239 x_message varchar2(2000);
240
241
242 BEGIN
243
244
245 -- ASSUMPTION : link_aol_user populates two context attributes : def_access_column and def_access_id
246 -- 1. def_access_column - populated with pk_key column name for the highest level at which person exists
247 -- For example if person exists at level 3 then def_access_column = S03_SPRVSR_LVL1_PK_KEY and so on.
248 -- 2. def_access_id - populated with pk_key at that level i.e. the value of S03_SPRVSR_LVL1_PK_KEY column
249
250
251 x_context_name := 'DEFAULT_PERSON';
252 x_context_name := '' || x_context_name || '';
253
254
255 --Reset context values
256 dbms_session.set_context('edw_context', x_context_name ,null);
257
258
259 -- Check for error flag to make sure successfull execution of link_aol_user
260 error_flag := SYS_CONTEXT( 'edw_context', 'error_context');
261
262 If error_flag = 'TRUE' then
263 RAISE LINK_AOL_USER_ERROR;
264 end if;
265
266
267 -- Get database version
268 -- ASSUMPTION: It is already captured in advance in set_context (i.e. set_default_context is called after that..)
269
270 version_number := to_number(SYS_CONTEXT( 'edw_context', 'db_version_context'));
271
272
273 -- Set maximum allowable context length
274
275 If version_number < 816 then
276 max_context_length := 256;
277 else
278 max_context_length := 4000;
279 end if;
280
281
282 x_context_value := 'select ASGN_ASSIGNMENT_PK_KEY from EDW_HR_PERSON_M where '
283 || SYS_CONTEXT('edw_context','def_access_column')
284 || ' = '
285 || SYS_CONTEXT('edw_context','def_access_id');
286
287
288 -- Check the context length
289
290 If length(x_context_value) > max_context_length then
291 RAISE LARGE_CONTEXT_LENGTH;
292 end if;
293
294
295 -- Set the context attribute for the dimension
296
297 dbms_session.set_context('edw_context', x_context_name, x_context_value);
298
299
300 << last >>
301
302 commit;
303
304 -- Set attribute def_dim_id
305 select dim_id into x_def_dim_id from edw_sec_dim_info_t
306 where dim_name = 'EDW_HR_PERSON_M';
307 dbms_session.set_context('edw_context', 'DEF_DIM_ID', to_char(x_def_dim_id));
308
309
310 EXCEPTION
311
312 WHEN LINK_AOL_USER_ERROR THEN
313
314 -- Do nothing as error flag is already set by link_aol_user
315 NULL;
316
317 WHEN LARGE_CONTEXT_LENGTH THEN
318
319 -- Log error message into edw_error_log table
320
321 x_message := 'Default Context length exceeds maximum allowable limit.';
322
323 edw_sec_util.log_error(x_object_name, x_object_type, ses_resp_id, null, x_message);
324
325
326 -- Set the error_context to make security function aware of the error occured in initialization function
327
328 dbms_session.set_context('edw_context', 'error_context', 'TRUE');
329 commit;
330
331 WHEN OTHERS THEN
332 v_ErrorCode := SQLCODE;
333 v_ErrorText := SUBSTR(SQLERRM, 1, 200);
334
335 -- Set the error_context to make security function aware of the error occured in initialization function
336 -- This is moved before logging error so that if error logging fails, still we set the error_context
337
338 dbms_session.set_context('edw_context', 'error_context', 'TRUE');
339
340
341 -- Log error message into edw_error_log table
342
343 x_message := 'Oracle error occured.
344 Errorcode is : ' || v_ErrorCode || ' and Errortext is : ' || v_ErrorText ;
345
346 edw_sec_util.log_error(x_object_name, x_object_type, ses_resp_id, null, x_message);
347
348 commit;
349
350 END set_default_context;
351
352
353
354
355 PROCEDURE Link_aol_user IS
356
357 -- PRAGMA AUTONOMOUS_TRANSACTION;
358
359 x_object_name varchar2(40) := 'EDW_SEC_PKG.LINK_AOL_USER';
360 x_object_type varchar2(30) := 'Security Procedure';
361
362 v_Errorcode number;
363 v_ErrorText varchar2(200);
364
365 x_user_count number;
366 x_user_name fnd_user.user_name%type;
367 x_db_link_name edw_source_instances.warehouse_to_instance_link%type;
368 x_person_id number;
369 x_instance_code edw_system_parameters.instance_code%type;
370 x_pk_fixed_string varchar2(30) := '-EMPLOYEE-PERS';
371 x_pk edw_hr_person_m.asgn_assignment_pk%type;
372 x_pk_key edw_hr_person_m.asgn_assignment_pk_key%type;
373 x_name varchar2(2000); -- edw_hr_person_m.s01_name%type;
374 x_asgn_name edw_hr_person_m.asgn_name%type;
375 x_number number;
376 x_char varchar2(30);
377 x_double_char varchar2(30);
378 x_access_id edw_hr_person_m.asgn_assignment_pk_key%type;
379 x_access_column varchar2(30);
380 sql_stmt varchar2(4000);
381
382 USER_NOT_EXIST EXCEPTION;
383 USER_NOT_LINKED EXCEPTION;
384
385 x_current_person_id number;
386
387 TYPE PersonIdTable IS TABLE OF NUMBER;
388
389 x_table PersonIdTable := PersonIdTable(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15);
390 i number;
391
392 x_message varchar2(2000);
393
394
395 BEGIN
396
397
398 -- The API link_aol_user populates two context attributes : def_access_column and def_access_id
399 -- 1. def_access_column - populated with pk_key column name for the highest level at which person exists
400 -- For example if person exists at level 3 then def_access_column = S03_SPRVSR_LVL1_PK_KEY and so on.
401 -- 2. def_access_id - populated with pk_key at that level i.e. the value of S03_SPRVSR_LVL1_PK_KEY column
402
403
404 --Reset context values
405 dbms_session.set_context('edw_context', 'def_access_column',null);
406 dbms_session.set_context('edw_context', 'def_access_id',null);
407
408
409 -- Capture apps user and get person_id by going across db_link to fnd_user on master source instance.
410
411 -- Find application user
412
413 x_user_name := FND_PROFILE.VALUE('USERNAME');
414
415
416 -- Find master source instance
417
418 select instance_code into x_instance_code from edw_system_parameters;
419
420
421 -- Find database link to master source instance
422
423 select warehouse_to_instance_link into x_db_link_name from edw_source_instances
424 where instance_code = x_instance_code;
425
426
427 -- Check if application user exists on source instance
428
429
430 sql_stmt := 'select count(*) from fnd_user@' || x_db_link_name ||
431 ' where user_name = ''' ||x_user_name || '''';
432 EXECUTE IMMEDIATE sql_stmt INTO x_user_count;
433
434 /*
435 sql_stmt := 'select count(*) from fnd_user' ||
436 ' where user_name = ''' ||x_user_name || '''';
437 EXECUTE IMMEDIATE sql_stmt INTO x_user_count;
438 */
439
440
441 IF x_user_count = 0 THEN /* Application user doesn't exists on source. */
442 RAISE USER_NOT_EXIST;
446 -- Get person_id by refering to FND_USER on master source instance
443 END IF;
444
445
447
448 sql_stmt := 'select nvl(employee_id, -1) from fnd_user@' || x_db_link_name ||
449 ' where user_name = ''' ||x_user_name || '''';
450 EXECUTE IMMEDIATE sql_stmt INTO x_person_id;
451
452 /*
453 sql_stmt := 'select nvl(employee_id, -1) from fnd_user' ||
454 ' where user_name = ''' ||x_user_name || '''';
455 EXECUTE IMMEDIATE sql_stmt INTO x_person_id;
456 */
457
458
459
460 IF x_person_id = -1 THEN /* Application user not linked with any employee. */
461 RAISE USER_NOT_LINKED;
462 END IF;
463
464 x_pk := x_person_id || '-' || x_instance_code || x_pk_fixed_string;
465
466 select asgn_assignment_pk_key into x_pk_key
467 from edw_hr_person_m
468 where asgn_assignment_pk = x_pk;
469
470
471
472 -----------------------------------------------------------
473 --
474 -- Start of new code based on Person_id
475 --
476 ------------------------------------------------------------
477
478 -- Using dynamic SQL to make code independent of person dimension changes
479
480 sql_stmt := ' select
481 s01_person_id,
482 s02_person_id,
483 s03_person_id,
484 s04_person_id,
485 s05_person_id,
486 s06_person_id,
487 s07_person_id,
488 s08_person_id,
489 s09_person_id,
490 s10_person_id,
491 s11_person_id,
492 s12_person_id,
493 s13_person_id,
494 s14_person_id,
495 s15_person_id
496 from
497 edw_hr_person_m
498 where
499 asgn_assignment_pk_key = '|| x_pk_key;
500
501
502 EXECUTE IMMEDIATE sql_stmt INTO
503 x_table(1),
504 x_table(2),
505 x_table(3),
506 x_table(4),
507 x_table(5),
508 x_table(6),
509 x_table(7),
510 x_table(8),
511 x_table(9),
512 x_table(10),
513 x_table(11),
514 x_table(12),
515 x_table(13),
516 x_table(14),
517 x_table(15);
518
519
520 -- Check if Supervisor is null or Person at last level
521
522
523 IF ((x_table(1) IS NULL) OR (x_person_id <> x_table(1))) THEN
524
525 x_access_column := 'ASGN_ASSIGNMENT_PK_KEY';
526 x_access_id := x_pk_key;
527
528 dbms_session.set_context('edw_context', 'def_access_column',x_access_column);
529 dbms_session.set_context('edw_context', 'def_access_id',to_char(x_access_id));
530
531 goto last;
532
533 END IF;
534
535
536 -- Find the level
537
538 i := 2;
539
540 WHILE (x_person_id = x_table(i)) LOOP
541 IF i=15 THEN /* Person at highest level */
542 i:=16;
543 EXIT;
544 END IF;
545 i := i+1;
546 END LOOP;
547
548 i := i-1;
549 x_char := i;
550
551
552 ------------------------------------------------------------------
553 --
554 -- End of new code
555 --
556 ----------------------------------------------------------------
557
558 -- Transformation logic to form PK_KEY columns name
559 -- They are named as : S01_SPRVSR_LVL1_PK_KEY, S02_SPRVSR_LVL2_PK_KEY.....S15_SPRVSR_LVL15_PK_KEY
560
561 IF length(x_char) = 1 THEN
562 x_double_char := '0'||x_char;
563 ELSE
564 x_double_char := x_char;
565 END IF;
566
567 x_access_column := 'S' || x_double_char || '_SPRVSR_LVL' || x_char || '_PK_KEY';
568
569
570 -- Need to use dynamic SQL as x_access_column not known in advance
571
572 sql_stmt := 'select ' || x_access_column || ' from edw_hr_person_m where asgn_assignment_pk_key = ' || x_pk_key;
573
574 EXECUTE IMMEDIATE sql_stmt INTO x_access_id;
575
576
577
578 dbms_session.set_context('edw_context', 'def_access_column',x_access_column);
579 dbms_session.set_context('edw_context', 'def_access_id',to_char(x_access_id));
580
581
582 << last >>
583
584 commit;
585
586
587 EXCEPTION
588
589 WHEN USER_NOT_EXIST THEN
590
591 -- Log error message into edw_error_log table
592
593 x_message := 'Application user '|| x_user_name || ' does not exist on master source instance.';
594
595 edw_sec_util.log_error(x_object_name, x_object_type, ses_resp_id, null, x_message);
596
597 -- Set the error_context to make security function aware of the error occured in initialization function
598
599 dbms_session.set_context('edw_context', 'error_context', 'TRUE');
600 commit;
601
602
603 WHEN USER_NOT_LINKED THEN
604
605 -- Log error message into edw_error_log table
606
607 x_message := 'Application user '|| x_user_name || ' is not linked with any employee.';
608
609 edw_sec_util.log_error(x_object_name, x_object_type, ses_resp_id, null, x_message);
610
611 -- Set the error_context to make security function aware of the error occured in initialization function
612
613 dbms_session.set_context('edw_context', 'error_context', 'TRUE');
614 commit;
615
616
617 WHEN OTHERS THEN
621 -- Set the error_context to make security function aware of the error occured in initialization function
618 v_ErrorCode := SQLCODE;
619 v_ErrorText := SUBSTR(SQLERRM, 1, 200);
620
622 -- This is moved before logging error so that if error logging fails, still we set the error_context
623
624 dbms_session.set_context('edw_context', 'error_context', 'TRUE');
625
626
627 -- Log error message into edw_error_log table
628
629 x_message := 'Oracle error occured.
630 Errorcode is : ' || v_ErrorCode || ' and Errortext is : ' || v_ErrorText ;
631
632 edw_sec_util.log_error(x_object_name, x_object_type, ses_resp_id, null, x_message);
633
634
635 commit;
636
637 END link_aol_user;
638
639 ---------------------------------------------------------
640 --Added for bug 4577390
641 --This will set the global names to false.
642 --this code was in BIS_INIT.INITALIZE and has been
643 --moved here. This function will be called from set_context
644 ---------------------------------------------------------
645 PROCEDURE set_global_names_for_source IS
646 sorc_records number := 0;
647 v_Errorcode number;
648 v_ErrorText varchar2(200);
649 x_step varchar2(50);
650 sql_stmt varchar2(1000);
651 BEGIN
652 -- Check if source
653 select count(*) into sorc_records from dba_tables
654 where owner = EDW_OWB_COLLECTION_UTIL.get_db_user('BIS')
655 and table_name = 'EDW_LOCAL_INSTANCE';
656
657 IF sorc_records > 0 THEN
658 sql_stmt := 'select count(*) from edw_local_instance';
659 EXECUTE IMMEDIATE sql_stmt into sorc_records;
660 END IF;
661
662 x_step := 'set_global_names_false';
663
664 IF sorc_records > 0 THEN
665 -- Set Global_names to false
666 sql_stmt := ' ALTER SESSION SET global_names = false';
667 EXECUTE IMMEDIATE sql_stmt;
668 END IF;
669 EXCEPTION
670 WHEN OTHERS THEN
671 v_ErrorCode := SQLCODE;
672 v_ErrorText := SUBSTR(SQLERRM, 1, 200);
673
674 -- Log error message into edw_error_log table
675
676 insert into edw_error_log
677 (object_name, object_type, resp_id, message,
678 last_update_date, last_updated_by, creation_date, created_by, last_update_login)
679 values
680 ('EDW_SEC_PKG.set_global_names_for_source', 'EDW Security Package', NULL,
681 'Oracle error occured in EDW_SEC_PKG.set_global_names_for_source procedure at step : '|| x_step
682 || '. Errorcode is : ' || v_ErrorCode || ' and Errortext is : ' || v_ErrorText,
683 sysdate, 0, sysdate, 0, 0);
684 commit;
685 END set_global_names_for_source;
686
687 -- This is a generic EDW security function which generates predicates based on context attributes
688
689 function dim_sec
690 (obj_schema varchar2,
691 obj_name varchar2)
692 return varchar2
693 is
694
695 PRAGMA AUTONOMOUS_TRANSACTION;
696
697 d_predicate varchar2(4000) := null;
698 x_fact_id number;
699 x_fk_col_name edw_sec_fact_info_t.fk_col_name%TYPE;
700 x_context_name edw_sec_dim_info_t.context_name%TYPE;
701
702
703 cursor sec_cursor is select distinct t1.dim_id
704 from edw_sec_dim_access t1, edw_sec_fact_info_t t2
705 where resp_id = ses_resp_id
706 and t1.dim_id = t2.dim_id
707 and t2.fact_id = x_fact_id;
708
709 sec_rec sec_cursor%ROWTYPE;
710
711 ---------------------------------------------------------------
712
713 x_def_dim_id edw_sec_dim_access.dim_id%TYPE;
714
715 cursor sec_def_cursor is select distinct t1.dim_id
716 from edw_sec_dim_access t1, edw_sec_fact_info_t t2
717 where resp_id = ses_resp_id
718 and t1.dim_id = t2.dim_id
719 and t2.fact_id = x_fact_id
720 and t1.dim_id <> x_def_dim_id;
721
722 sec_def_rec sec_def_cursor%ROWTYPE;
723
724 x_item_set_name varchar2(30) := 'EDW_DEF_SEC_PERM';
725 x_number number :=0;
726 x_default_sec varchar2(30);
727 ---------------------------------------------------------------------
728 current_dim_id edw_sec_fact_info_t.dim_id%TYPE;
729
730
731
732 -- Cursor changed to eliminate disabled references - 3/6/2002
733
734 cursor fk_cursor is select fk_col_name
735 from edw_sec_fact_info_t
736 where fact_id = x_fact_id
737 and dim_id = current_dim_id
738 and fk_col_name not in
739 ( select fk_col_name
740 from edw_sec_ref_info_t
741 where resp_id = ses_resp_id
742 and fact_id = x_fact_id
743 and dim_id = current_dim_id);
744
745
746
747 fk_rec fk_cursor%ROWTYPE;
748
749 error_flag varchar2(30) := NULL;
750 version_number number;
751 max_predicate_length number;
752 temp_predicate varchar2(4000);
753
754 x_object_name varchar2(30) := 'EDW_SEC_PKG.DIM_SEC';
755 x_object_type varchar2(30) := 'Security Procedure';
756
757 v_Errorcode number;
758 v_ErrorText varchar2(200);
759
760
764
761 LARGE_PREDICATE_LENGTH EXCEPTION;
762 INIT_ERROR EXCEPTION;
763 DEF_DIM_ID_IS_NULL EXCEPTION;
765 x_message varchar2(2000);
766
767
768 BEGIN
769 --added for bug 4577390, need to global names to false
770 edw_sec_pkg.set_global_names_for_source;
771
772 --- Set edw context Added for bug 4577390
773 edw_sec_pkg.set_context;
774
775 --Get responsibility_id
776
777 --ses_resp_id := FND_PROFILE.VALUE('RESP_ID');
778 ses_resp_id := SYS_CONTEXT( 'edw_context', 'resp_context');
779
780 select distinct fact_id into x_fact_id
781 from edw_sec_fact_info_t
782 where fact_name = obj_name;
783
784
785 -- Check for error flag to make sure successfull execution of initialization function
786
787 error_flag := SYS_CONTEXT( 'edw_context', 'error_context');
788
789 If error_flag = 'TRUE' then
790 RAISE INIT_ERROR;
791 end if;
792
793 -- Get database version and set maximum predicate length
794
795 version_number := to_number(SYS_CONTEXT( 'edw_context', 'db_version_context'));
796
797 If version_number < 817 then
798 max_predicate_length := 2000;
799 else
800 max_predicate_length := 4000;
801 end if;
802
803
804
805 -- Check if default Security is enabled
806
807 x_default_sec := SYS_CONTEXT( 'edw_context', 'DEF_SEC_ENABLE');
808
809 If x_default_sec = 'Y' then /* Default Security is enabled */
810
811 -- Check if default security needs to be implemented for this fact
812
813 select count(*) into x_number
814 from edw_sec_itemset_info_t
815 where fact_id = x_fact_id;
816
817 If x_number > 0 then /* Default security needs to be implemented for this fact */
818
819 -- Find out the dimension on which default security needs to be implemented
820 -- ASSUMPTION : The context attribute DEF_DIM_ID is already populated by set_default_context
821
822 -- Make sure def_dim_id is not null
823
824 If SYS_CONTEXT('edw_context', 'DEF_DIM_ID') is null THEN
825 RAISE DEF_DIM_ID_IS_NULL;
826 End if;
827
828 x_def_dim_id := SYS_CONTEXT('edw_context', 'DEF_DIM_ID');
829
830
831 -- Process the dimensions which are applicable to fact and for which security is defined for current responsibility
832 -- Except the dimension on which default security needs to be implemented (that will be taken care of by default_sec)
833
834 FOR sec_def_rec in sec_def_cursor LOOP
835 current_dim_id := sec_def_rec.dim_id;
836
837 -- Following 'FOR' loop takes care of multiple references to same dimension by a fact cube
838
839 FOR fk_rec in fk_cursor LOOP
840 x_fk_col_name := fk_rec.fk_col_name;
841
842 select context_name into x_context_name
843 from edw_sec_dim_info_t
844 where dim_id = sec_def_rec.dim_id;
845
846 x_context_name := '' || x_context_name || '';
847
848 -- This is to prevent erroring out if context not initialized.
849
850 If SYS_CONTEXT('edw_context', x_context_name) IS NULL then
851 goto last2;
852 end if;
853 --
854 -- This is to take care of pl/sql bug with sys_context
855 select SYS_CONTEXT('edw_context', x_context_name, 4000) into temp_predicate from dual;
856
857 If d_predicate is null then
858 d_predicate := x_fk_col_name ||
859 ' in ('||
860 temp_predicate ||
861 ')';
862 else
863 d_predicate := d_predicate ||
864 ' and ' ||
865 x_fk_col_name ||
866 ' in (' ||
867 temp_predicate ||
868 ')';
869 end if;
870
871 x_context_name := NULL;
872
873 END LOOP;
874
875 << last2 >>
876 null;
877
878 END LOOP;
879
880 end if;
881
882 goto final;
883
884 end if;
885
886 -- Following will get executed if default security not enabled
887
888 -- Process the dimensions which are applicable to fact and for which security is defined for current responsibility
889
890 FOR sec_rec in sec_cursor LOOP
891 current_dim_id := sec_rec.dim_id;
892
893 -- Following 'FOR' loop takes care of multiple references to same dimension by a fact cube
894
895 FOR fk_rec in fk_cursor LOOP
896 x_fk_col_name := fk_rec.fk_col_name;
897
898 select context_name into x_context_name
899 from edw_sec_dim_info_t
900 where dim_id = sec_rec.dim_id;
901
902 x_context_name := '' || x_context_name || '';
903
904 -- This is to prevent erroring out if context not initialized.
905
906 If SYS_CONTEXT('edw_context', x_context_name) IS NULL then
907 goto last;
908 end if;
909 --
910 -- This is to take care of pl/sql bug with sys_context
911 select SYS_CONTEXT('edw_context', x_context_name, 4000) into temp_predicate from dual;
912
913 If d_predicate is null then
914 d_predicate := x_fk_col_name ||
915 ' in ('||
916 temp_predicate ||
917 ')';
918 else
919 d_predicate := d_predicate ||
923 temp_predicate ||
920 ' and ' ||
921 x_fk_col_name ||
922 ' in (' ||
924 ')';
925 end if;
926
927 x_context_name := NULL;
928
929 END LOOP;
930
931 << last >>
932 null;
933
934 END LOOP;
935
936
937 << final >>
938
939 -- Check the predicate length
940
941 If length(d_predicate) > max_predicate_length then
942 RAISE LARGE_PREDICATE_LENGTH;
943 end if;
944
945 commit;
946
947 return d_predicate;
948
949 EXCEPTION
950
951 WHEN NO_DATA_FOUND THEN
952 d_predicate := NULL;
953 commit;
954 return d_predicate;
955
956 WHEN INIT_ERROR THEN
957
958 -- Log error message into edw_error_log table
959
960 x_message := 'Error in initialization function.';
961
962 edw_sec_util.log_error(x_object_name, x_object_type, ses_resp_id, null, x_message);
963
964
965 -- Make security function fail
966 RAISE;
967
968 WHEN DEF_DIM_ID_IS_NULL THEN
969
970 -- Log error message into edw_error_log table
971
972 x_message := 'Def_dim_id is null.';
973
974 edw_sec_util.log_error(x_object_name, x_object_type, ses_resp_id, null, x_message);
975
976 -- Make security function fail
977 RAISE;
978
979 WHEN LARGE_PREDICATE_LENGTH THEN
980
981 -- Log error message into edw_error_log table
982
983 x_message := 'Predicate length exceeds maximum allowable limit.
984 Please try by reducing the number of dimensions or levels on which security is defined.';
985
986 edw_sec_util.log_error(x_object_name, x_object_type, ses_resp_id, null, x_message);
987
988
989 -- Make security function fail
990 RAISE;
991
992 WHEN OTHERS THEN
993 v_ErrorCode := SQLCODE;
994 v_ErrorText := SUBSTR(SQLERRM, 1, 200);
995
996 -- Log error message into edw_sec_error table
997
998 x_message := 'Oracle error occured.
999 Errorcode is : ' || v_ErrorCode || ' and Errortext is : ' || v_ErrorText ;
1000
1001 edw_sec_util.log_error(x_object_name, x_object_type, ses_resp_id, null, x_message);
1002
1003
1004 -- Make security function fail
1005 RAISE;
1006
1007 END dim_sec;
1008
1009
1010
1011
1012 function Default_sec
1013 (obj_schema varchar2,
1014 obj_name varchar2)
1015 return varchar2
1016 is
1017
1018 PRAGMA AUTONOMOUS_TRANSACTION;
1019
1020 d_predicate varchar2(4000) := null;
1021 x_fk_col_name varchar2(30); -- edw_sec_fact_info_t.fk_col_name%TYPE;
1022 x_context_name varchar2(30); --edw_sec_dim_info_t.context_name%TYPE;
1023
1024
1025 error_flag varchar2(30) := NULL;
1026 version_number number;
1027 max_predicate_length number;
1028 temp_predicate varchar2(4000);
1029
1030 x_object_name varchar2(30) := 'EDW_SEC_PKG.DEFAULT_SEC';
1031 x_object_type varchar2(30) := 'Security Procedure';
1032
1033 v_Errorcode number;
1034 v_ErrorText varchar2(200);
1035
1036 x_fact_id number;
1037 x_item_set_name varchar2(30) := 'EDW_DEF_SEC_PERM';
1038
1039 LARGE_PREDICATE_LENGTH EXCEPTION;
1040 INIT_ERROR EXCEPTION;
1041
1042 x_message varchar2(2000);
1043
1044
1045 BEGIN
1046 --added for bug 4577390, need to global names to false
1047 edw_sec_pkg.set_global_names_for_source;
1048
1049 --- Set edw context Added for bug 4577390
1050 edw_sec_pkg.set_context;
1051
1052 -- Check for error flag to make sure successfull execution of initialization function
1053
1054 error_flag := SYS_CONTEXT( 'edw_context', 'error_context');
1055
1056 If error_flag = 'TRUE' then
1057 RAISE INIT_ERROR;
1058 end if;
1059
1060
1061 -- Check if default Security is enabled
1062
1063 If SYS_CONTEXT('edw_context', 'DEF_SEC_ENABLED') <> 'Y' then
1064 d_predicate := NULL;
1065 goto last;
1066 end if;
1067
1068
1069 --If default security context is not initialized then return NULL predicate.
1070 -- This should not occur in any condition
1071 -- We can make security function fail in this case if desired.
1072
1073 -- ASSUMPTION : Context name is DEFAULT_PERSON
1074
1075 x_context_name := 'DEFAULT_PERSON';
1076
1077 x_context_name := '' || x_context_name || '';
1078
1079
1080 If SYS_CONTEXT('edw_context', x_context_name) IS NULL then
1081 d_predicate := NULL;
1082 goto last;
1083 end if;
1084
1085
1086 -- Find the fact against which security function is being executed
1087
1088 select distinct fact_id into x_fact_id
1089 from edw_sec_fact_info_t
1090 where fact_name = obj_name;
1091
1092
1096
1093 -- Get database version and set maximum predicate length
1094
1095 version_number := to_number(SYS_CONTEXT( 'edw_context', 'db_version_context'));
1097 If version_number < 817 then
1098 max_predicate_length := 2000;
1099 else
1100 max_predicate_length := 4000;
1101 end if;
1102
1103
1104 -- Get FK column name in fact table which needs to be secured by default security
1105
1106 select fk_col_name into x_fk_col_name
1107 from edw_sec_itemset_info_t
1108 where itemset_name = x_item_set_name
1109 and fact_id = x_fact_id;
1110
1111 -- ASSUMPTION : Context name is DEFAULT_PERSON
1112
1113 x_context_name := 'DEFAULT_PERSON';
1114
1115 x_context_name := '' || x_context_name || '';
1116
1117 --If default security context is not initialized then return NULL predicate.
1118 -- This should not occur in any condition
1119 -- We can make security function fail in this case if desired.
1120
1121 If SYS_CONTEXT('edw_context', x_context_name) IS NULL then
1122 d_predicate := NULL;
1123 goto last;
1124 end if;
1125 --
1126 -- This is to take care of pl/sql bug with sys_context
1127 select SYS_CONTEXT('edw_context', x_context_name, 4000) into temp_predicate from dual;
1128
1129 -- ASSUMPTION : Only one reference (person_fk_key) needs to be secured.
1130
1131 -- If d_predicate is null then
1132
1133 d_predicate := x_fk_col_name ||
1134 ' in ('||
1135 temp_predicate ||
1136 ')';
1137
1138 << last >>
1139 null;
1140
1141
1142 -- Check the predicate length
1143
1144 If length(d_predicate) > max_predicate_length then
1145 RAISE LARGE_PREDICATE_LENGTH;
1146 end if;
1147
1148
1149 commit;
1150 return d_predicate;
1151
1152
1153 EXCEPTION
1154
1155 WHEN INIT_ERROR THEN
1156
1157 -- Log error message into edw_error_log table
1158
1159 x_message := 'Error in initialization function';
1160
1161 edw_sec_util.log_error(x_object_name, x_object_type, ses_resp_id, null, x_message);
1162
1163 -- Make security function fail
1164 RAISE;
1165
1166 WHEN LARGE_PREDICATE_LENGTH THEN
1167
1168 -- Log error message into edw_error_log table
1169
1170 x_message := 'Predicate length exceeds maximum allowable limit.';
1171
1172 edw_sec_util.log_error(x_object_name, x_object_type, ses_resp_id, null, x_message);
1173
1174 -- Make security function fail
1175 RAISE;
1176
1177 WHEN OTHERS THEN
1178 v_ErrorCode := SQLCODE;
1179 v_ErrorText := SUBSTR(SQLERRM, 1, 200);
1180
1181 -- Log error message into edw_sec_error table
1182
1183 x_message := 'Oracle error occured.
1184 Errorcode is : ' || v_ErrorCode || ' and Errortext is : ' || v_ErrorText ;
1185
1186 edw_sec_util.log_error(x_object_name, x_object_type, ses_resp_id, null, x_message);
1187
1188 -- Make security function fail
1189 RAISE;
1190
1191 END default_sec;
1192
1193
1194
1195
1196 END edw_sec_pkg;