[Home] [Help]
PACKAGE BODY: APPS.XLA_SECURITY_PKG
Source
1 PACKAGE BODY xla_security_pkg AS
2 -- $Header: xlacmsec.pkb 120.24 2006/08/11 17:53:46 wychan ship $
3 /*===========================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +============================================================================+
8 | FILENAME |
9 | xlacmsec.pkb |
10 | |
11 | PACKAGE NAME |
12 | xla_security_pkg |
13 | |
14 | DESCRIPTION |
15 | XLA security package that contains code related to implementation of |
16 | 'Transaction Security' on accounting events. |
17 | |
18 | HISTORY |
19 | 08-Feb-01 G. Gu Created |
20 | 10-Mar-01 P. Labrevois Reviewed |
21 | 08-Apr-02 S. Singhania Removed the set_security_context API with the|
22 | change in security approach and added APIs |
23 | 'set_context' and 'set_product_security'. |
24 | Changed the approach based on oracle 9i. |
25 | 15-Nov-02 S. Singhania Reworked on the package to make it a working |
26 | package. |
27 | 27-Nov-02 S. Singhania Made changes to 'set_subledger_security'. |
28 | Added 'install_security' procedure |
29 | 11-Feb-03 S. Singhania Removed 'install_security' and |
30 | 'xla_security_policy' from this package. |
31 | 19-Aug-03 S. Singhania Renamed the profile option code for |
32 | 'SLA: Use Transaction Security' |
33 | 02-Oct-03 S. Singhania Implemented change in implementation arch. |
34 | (see bug # 3173884) |
35 | 28-Feb-04 S. Singhania Bug 3416534. Added local trace procedure and |
36 | added FND_LOG messages. |
37 | 23-Mar-04 S. Singhania Added a parameter p_module to the TRACE calls|
38 | and the procedure. |
39 +===========================================================================*/
40
41 --=============================================================================
42 -- *********** Declarations **********
43 --=============================================================================
44 TYPE t_array_policy_name IS TABLE OF VARCHAR2(80);
45
46 --=============================================================================
47 -- *********** Local Trace Routine **********
48 --=============================================================================
49 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
50 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
51 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
52 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
53 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
54 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
55
56 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
57 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_security_pkg';
58
59 g_log_level NUMBER;
60 g_log_enabled BOOLEAN;
61
62 PROCEDURE trace
63 (p_msg IN VARCHAR2
64 ,p_level IN NUMBER
65 ,p_module IN VARCHAR2 DEFAULT C_DEFAULT_MODULE) IS
66 BEGIN
67 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
68 fnd_log.message(p_level, p_module);
69 ELSIF p_level >= g_log_level THEN
70 fnd_log.string(p_level, p_module, p_msg);
71 END IF;
72
73 EXCEPTION
74 WHEN xla_exceptions_pkg.application_exception THEN
75 RAISE;
76 WHEN OTHERS THEN
77 xla_exceptions_pkg.raise_message
78 (p_location => 'xla_security_pkg.trace');
79 END trace;
80
81 --=============================================================================
82 -- *********** public procedures and functions **********
83 --=============================================================================
84 --=============================================================================
85 --
86 --
87 --
88 --
89 --
90 --
91 --
92 --
93 --
94 --
95 -- Following are the routines on which "single event/entity" public APIs
96 -- are based.
97 --
98 -- 1. set_security_context
99 -- 2. set_subledger_security
100 --
101 --
102 --
103 --
104 --
105 --
106 --
107 --
108 --
109 --
110 --
111 --
112 --=============================================================================
113 --=============================================================================
114 --
115 -- Changes maded as described in bug # 3173884
116 --
117 -- IF the user responsibility is GL THEN
118 -- IF SLA: Use Transaction Security = "Yes" THEN
119 -- IF p_application.SECURITY_FUNCTION_NAME is NOT NULL THEN
120 -- Transaction security is enabled based on the <appl_short_name> group
121 -- END IF
122 -- ELSE
123 -- Transaction security is enabled based on the XLA group
124 -- END IF
125 -- ELSE
126 -- IF p_application.SECURITY_FUNCTION_NAME is NOT NULL THEN
127 -- Transaction security is enabled based on the <appl_short_name> group
128 -- ELSE
129 -- Transaction security is enabled based on the XLA group
130 -- END IF
131 -- END IF
132 --
133 -- bug 5438150: Added parameter p_always_do_mo_init_flag to allow mo_init to be
134 -- called regardless of the is_mo_init_done. This is used when calling from OAF
135 -- page when is_mo_init_done incorrectly return 'Y' (due to cache/session-reuse)
136 -- even mo_init was not called from the current session.
137 --
138 --=============================================================================
139 PROCEDURE set_security_context
140 (p_application_id IN NUMBER) IS
141 BEGIN
142 set_security_context
143 (p_application_id => p_application_id
144 ,p_always_do_mo_init_flag => 'N');
145 END;
146
147 PROCEDURE set_security_context
148 (p_application_id IN NUMBER
149 ,p_always_do_mo_init_flag IN VARCHAR2) IS
150 l_appl_short_name VARCHAR2(30);
151 l_appl_name VARCHAR2(240);
152 l_security_group VARCHAR2(30);
153 l_policy_function VARCHAR2(80);
154
155 l_resp_appl_short_name VARCHAR2(30);
156 l_use_trx_security VARCHAR2(30);
157 l_log_module VARCHAR2(240);
158 l_access_ctrl_enabled varchar2(1);
159 l_mo_initialized PLS_INTEGER;
160
161 BEGIN
162 IF g_log_enabled THEN
163 l_log_module := C_DEFAULT_MODULE||'.set_security_context';
164 END IF;
165 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
166 trace
167 (p_msg => 'BEGIN of procedure SET_SECURITY_CONTEXT'
168 ,p_level => C_LEVEL_PROCEDURE
169 ,p_module => l_log_module);
170 trace
171 (p_msg => 'p_application_id = '||p_application_id
172 ,p_level => C_LEVEL_PROCEDURE
173 ,p_module => l_log_module);
174 END IF;
175 ----------------------------------------------------------------------------
176 -- Check to see if application is a defined subledger
177 ----------------------------------------------------------------------------
178
179
180 -- Bypass the security if it's SLA.
181
182 IF p_application_id <> 602 THEN
183 BEGIN
184 SELECT fap.application_short_name
185 ,xsv.security_function_name
186 INTO l_appl_short_name
187 ,l_policy_function
188 FROM xla_subledgers_fvl xsv
189 ,fnd_application fap
190 WHERE xsv.application_id = p_application_id
191 AND fap.application_id = xsv.application_id;
192 EXCEPTION
193 WHEN NO_DATA_FOUND THEN
194 xla_exceptions_pkg.raise_message
195 (p_appli_s_name => 'XLA'
196 ,p_msg_name => 'XLA_COMMON_ERROR'
197 ,p_token_1 => 'ERROR'
198 ,p_value_1 => 'Invalid Subledger Applcation. Application_ID = ' || p_application_id
199 );
200
201 END;
202 END IF;
203
204 -- Bug 4628909
205 -- Check if an application is MO enbaled.
206 BEGIN
207 SELECT nvl(mpi.status, 'N')
208 INTO l_access_ctrl_enabled
209 FROM fnd_mo_product_init mpi
210 WHERE mpi.application_short_name = l_appl_short_name;
211 EXCEPTION
212 WHEN NO_DATA_FOUND THEN
213 l_access_ctrl_enabled := 'N';
214 WHEN OTHERS THEN
215 l_access_ctrl_enabled := 'N';
216 END;
217
218 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
219 trace
220 (p_msg => 'l_access_ctrl_enabled = ' || l_access_ctrl_enabled
221 ,p_level => C_LEVEL_STATEMENT
222 ,p_module => l_log_module);
223 trace
224 (p_msg => 'MO_GLOBAL.is_mo_init_done = ' || MO_GLOBAL.is_mo_init_done
225 ,p_level => C_LEVEL_STATEMENT
226 ,p_module => l_log_module);
227 END IF;
228
229 -- Call MO_Global if an application is MO enabled and MO is not initialized.
230 IF nvl(l_access_ctrl_enabled,'N') = 'Y' AND
231 (p_always_do_mo_init_flag = 'Y' OR MO_GLOBAL.is_mo_init_done = 'N')
232 THEN
233 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
234 trace
235 (p_msg => 'calling MO_GLOBAL.init '
236 ,p_level => C_LEVEL_STATEMENT
237 ,p_module => l_log_module);
238 END IF;
239
240 mo_global.init(l_appl_short_name);
241
242 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
243 trace
244 (p_msg => 'after calling MO_GLOBAL.init '
245 ,p_level => C_LEVEL_STATEMENT
246 ,p_module => l_log_module);
247 END IF;
248
249 END IF;
250
251
252 l_resp_appl_short_name := fnd_profile.value('RESP_APPL_SHORT_NAME');
253 l_use_trx_security := fnd_profile.value('XLA_USE_TRANSACTION_SECURITY');
254
255 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
256 trace
257 (p_msg => 'l_appl_short_name = '||l_appl_short_name
258 ,p_level => C_LEVEL_STATEMENT
259 ,p_module => l_log_module);
260 trace
261 (p_msg => 'l_policy_function = '||l_policy_function
262 ,p_level => C_LEVEL_STATEMENT
263 ,p_module => l_log_module);
264 trace
265 (p_msg => 'l_resp_appl_short_name = '||l_resp_appl_short_name
266 ,p_level => C_LEVEL_STATEMENT
267 ,p_module => l_log_module);
268 trace
269 (p_msg => 'l_use_trx_security = '||l_use_trx_security
270 ,p_level => C_LEVEL_STATEMENT
271 ,p_module => l_log_module);
272 END IF;
273
274 ----------------------------------------------------------------------------
275 -- If the calling responsibility is a GL responsibility then the transaction
276 -- security for the application is enabled only if the profile option
277 -- 'XLA_USE_TRANSACTION_SECURITY' is set to 'Yes'. If this value is 'No',
278 -- the standard 'XLA' security group is enabled for that session.
279 -- But if the calling responsibility belongs to any other application,
280 -- transaction security is enabled for the application (passed in as the
281 -- parameter).
282 ----------------------------------------------------------------------------
283 IF l_resp_appl_short_name = 'SQLGL' THEN
284 IF l_use_trx_security = 'Y' THEN
285 IF l_policy_function IS NOT NULL THEN
286 l_security_group := l_appl_short_name;
287 END IF;
288 ELSE
289 l_security_group := 'XLA';
290 END IF;
291 ELSE
292 IF l_policy_function IS NOT NULL THEN
293 l_security_group := l_appl_short_name;
294 ELSE
295 l_security_group := 'XLA';
296 END IF;
297 END IF;
298
299 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
300 trace
301 (p_msg => 'l_security_group = '||l_security_group
302 ,p_level => C_LEVEL_STATEMENT
303 ,p_module => l_log_module);
304 END IF;
305
306 ----------------------------------------------------------------------------
307 -- Call the package that is responsible for setting the application contexts
308 ----------------------------------------------------------------------------
309 xla_context_pkg.set_security_context
310 (p_security_group => l_security_group);
311
312 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
313 trace
314 (p_msg => 'END of procedure SET_SECURITY_CONTEXT'
315 ,p_level => C_LEVEL_PROCEDURE
316 ,p_module => l_log_module);
317 END IF;
318
319 EXCEPTION
320 WHEN xla_exceptions_pkg.application_exception THEN
321 RAISE;
322 WHEN NO_DATA_FOUND THEN
323 ----------------------------------------------------------------------------
324 -- Get more information about the application to make the error message
325 -- more clear to the user.
326 ----------------------------------------------------------------------------
327 BEGIN
328 SELECT fav.application_name
329 INTO l_appl_name
330 FROM fnd_application_vl fav
331 WHERE fav.application_id = p_application_id;
332
333 xla_exceptions_pkg.raise_message
334 (p_appli_s_name => 'XLA'
335 ,p_msg_name => 'XLA_COMMON_ERROR'
336 ,p_token_1 => 'LOCATION'
337 ,p_value_1 => 'xla_security_pkgset_security_context'
338 ,p_token_2 => 'APPLICATION_NAME'
339 ,p_value_2 => l_appl_name);
340 EXCEPTION
341 WHEN NO_DATA_FOUND THEN
342 xla_exceptions_pkg.raise_message
343 (p_appli_s_name => 'XLA'
344 ,p_msg_name => 'XLA_COMMON_ERROR'
345 ,p_token_1 => 'LOCATION'
346 ,p_value_1 => 'xla_security_pkgset_security_context'
347 ,p_token_2 => 'APPLICATION_ID'
348 ,p_value_2 => TO_CHAR(p_application_id));
349 END;
350 WHEN OTHERS THEN
351 xla_exceptions_pkg.raise_message
352 (p_location => 'xla_security_pkgset_security_context');
353 END set_security_context;
354
355
356 --=============================================================================
357 --
358 -- Changes maded as described in bug # 3173884
359 --
360 -- IF xla_subledgers.SECURITY_FUNCTION_NAME is null
361 -- THEN
362 -- do nothing
363 -- ELSE
364 -- a policy group <appl_short_name> is created
365 -- and SECURITY_FUNCTION_NAME security policy is attached to this group
366 -- END IF.
367 --
368 --=============================================================================
369 PROCEDURE set_subledger_security
370 (p_application_id IN NUMBER
371 ,p_security_function_name IN VARCHAR2) IS
372 l_appl_short_name VARCHAR2(30);
373 l_object_owner VARCHAR2(30);
374 l_group_count NUMBER;
375 l_object_name VARCHAR2(30):='XLA_TRANSACTION_ENTITIES';
376 l_array_policy_name t_array_policy_name;
377 l_log_module VARCHAR2(240);
378 BEGIN
379 IF g_log_enabled THEN
380 l_log_module := C_DEFAULT_MODULE||'.set_subledger_security';
381 END IF;
382 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
383 trace
384 (p_msg => 'BEGIN of procedure SET_SUBLEDGER_SECURITY'
385 ,p_level => C_LEVEL_PROCEDURE
386 ,p_module => l_log_module);
387 trace
388 (p_msg => 'p_application_id = '||p_application_id
389 ,p_level => C_LEVEL_PROCEDURE
390 ,p_module => l_log_module);
391 trace
392 (p_msg => 'p_security_function_name = '||p_security_function_name
393 ,p_level => C_LEVEL_PROCEDURE
394 ,p_module => l_log_module);
395 END IF;
396
397 SELECT fap.application_short_name, USER
398 INTO l_appl_short_name, l_object_owner
399 FROM fnd_application fap
400 WHERE fap.application_id = p_application_id;
401
402 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
403 trace
404 (p_msg => 'l_appl_short_name = '||l_appl_short_name
405 ,p_level => C_LEVEL_STATEMENT
406 ,p_module => l_log_module);
407 END IF;
408
409 SELECT COUNT(policy_group)
410 INTO l_group_count
411 FROM dba_policy_groups
412 WHERE object_owner = l_object_owner
413 AND object_name = l_object_name
414 AND policy_group = l_appl_short_name;
415
416 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
417 trace
418 (p_msg => 'l_group_count = '||l_group_count
419 ,p_level => C_LEVEL_STATEMENT
420 ,p_module => l_log_module);
421 END IF;
422
423 IF p_security_function_name IS NULL THEN
424 IF l_group_count = 0 THEN
425 IF (C_LEVEL_EVENT >= g_log_level) THEN
426 trace
427 (p_msg => 'Doing nothing'
428 ,p_level => C_LEVEL_EVENT
429 ,p_module => l_log_module);
430 END IF;
431 -- Do Nothing
432 NULL;
433 ELSE
434 SELECT policy_name BULK COLLECT
435 INTO l_array_policy_name
436 FROM dba_policies
437 WHERE object_owner = l_object_owner
438 AND object_name = l_object_name
439 AND policy_group = l_appl_short_name;
440
441 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
442 trace
443 (p_msg => 'Number of policies found = '||l_array_policy_name.COUNT
444 ,p_level => C_LEVEL_STATEMENT
445 ,p_module => l_log_module);
446 END IF;
447
448 FOR i IN 1 .. l_array_policy_name.COUNT LOOP
449 IF (C_LEVEL_EVENT >= g_log_level) THEN
450 trace
451 (p_msg => 'Dropping policy = '||l_array_policy_name(i)
452 ,p_level => C_LEVEL_EVENT
453 ,p_module => l_log_module);
454 END IF;
455
456 dbms_rls.drop_grouped_policy
457 (object_schema => l_object_owner
458 ,object_name => l_object_name
459 ,policy_group => l_appl_short_name
460 ,policy_name => l_array_policy_name(i));
461 END LOOP;
462
463 IF (C_LEVEL_EVENT >= g_log_level) THEN
464 trace
465 (p_msg => 'Dropping policy group = '||l_appl_short_name
466 ,p_level => C_LEVEL_EVENT
467 ,p_module => l_log_module);
468 END IF;
469
470 dbms_rls.delete_policy_group
471 (object_schema => l_object_owner
472 ,object_name => l_object_name
473 ,policy_group => l_appl_short_name);
474 END IF;
475 ELSE
476 IF l_group_count = 0 THEN
477 IF (C_LEVEL_EVENT >= g_log_level) THEN
478 trace
479 (p_msg => 'Creating policy group = '||l_appl_short_name
480 ,p_level => C_LEVEL_EVENT
481 ,p_module => l_log_module);
482 END IF;
483
484 dbms_rls.create_policy_group
485 (object_schema => l_object_owner
486 ,object_name => l_object_name
487 ,policy_group => l_appl_short_name);
488 ELSE
489 SELECT policy_name BULK COLLECT
490 INTO l_array_policy_name
491 FROM dba_policies
492 WHERE object_owner = l_object_owner
493 AND object_name = l_object_name
494 AND policy_group = l_appl_short_name;
495
496 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
497 trace
498 (p_msg => 'Number of policies found = '||l_array_policy_name.COUNT
499 ,p_level => C_LEVEL_STATEMENT
500 ,p_module => l_log_module);
501 END IF;
502
503 FOR i IN 1 .. l_array_policy_name.COUNT LOOP
504 IF (C_LEVEL_EVENT >= g_log_level) THEN
505 trace
506 (p_msg => 'Dropping policy = '||l_array_policy_name(i)
507 ,p_level => C_LEVEL_EVENT
508 ,p_module => l_log_module);
509 END IF;
510
511 dbms_rls.drop_grouped_policy
512 (object_schema => l_object_owner
513 ,object_name => 'XLA_TRANSACTION_ENTITIES'
514 ,policy_group => l_appl_short_name
515 ,policy_name => l_array_policy_name(i));
516 END LOOP;
517 END IF;
518
519 IF (C_LEVEL_EVENT >= g_log_level) THEN
520 trace
521 (p_msg => 'Creating policy = '||l_appl_short_name||'_SECURITY_POLICY'
522 ,p_level => C_LEVEL_EVENT
523 ,p_module => l_log_module);
524 END IF;
525
526 dbms_rls.add_grouped_policy
527 (object_schema => l_object_owner
528 ,object_name => 'XLA_TRANSACTION_ENTITIES'
529 ,policy_group => l_appl_short_name
530 ,policy_name => l_appl_short_name||'_SECURITY_POLICY'
531 ,policy_type => dbms_rls.SHARED_CONTEXT_SENSITIVE
532 ,function_schema => l_object_owner
533 ,policy_function => p_security_function_name
534 ,statement_types => 'UPDATE, SELECT, DELETE'
535 ,update_check => FALSE
536 ,enable => TRUE);
537 END IF;
538
539 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
540 trace
541 (p_msg => 'END of procedure SET_SUBLEDGER_SECURITY'
542 ,p_level => C_LEVEL_PROCEDURE
543 ,p_module => l_log_module);
544 END IF;
545
546 EXCEPTION
547 WHEN xla_exceptions_pkg.application_exception THEN
548 RAISE;
549 WHEN NO_DATA_FOUND THEN
550 xla_exceptions_pkg.raise_message
551 (p_appli_s_name => 'XLA'
552 ,p_msg_name => 'XLA_COMMON_ERROR'
553 ,p_token_1 => 'LOCATION'
554 ,p_value_1 => 'xla_security_pkgset_subledger_security'
555 ,p_token_2 => 'APPLICATION_ID'
556 ,p_value_2 => TO_CHAR(p_application_id));
557 WHEN OTHERS THEN
558 xla_exceptions_pkg.raise_message
559 (p_location => 'xla_security_pkgset_subledger_security');
560 END set_subledger_security;
561
562
563 --=============================================================================
564 -- *********** Initialization routine **********
565 --=============================================================================
566
567 --=============================================================================
568 --
569 --
570 --
571 --
572 --
573 --
574 --
575 --
576 --
577 --
578 -- Following code is executed when the package body is referenced for the first
579 -- time
580 --
581 --
582 --
583 --
584 --
585 --
586 --
587 --
588 --
589 --
590 --
591 --
592 --=============================================================================
593
594 BEGIN
595 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
596 g_log_enabled := fnd_log.test
597 (log_level => g_log_level
598 ,module => C_DEFAULT_MODULE);
599
600 IF NOT g_log_enabled THEN
601 g_log_level := C_LEVEL_LOG_DISABLED;
602 END IF;
603
604 END xla_security_pkg;