[Home] [Help]
PACKAGE BODY: APPS.FND_LDAP_UTIL
Source
1 package body fnd_ldap_util as
2 /* $Header: AFSCOLTB.pls 120.16.12010000.3 2008/08/11 17:34:22 rsantis ship $ */
3 --
4 -- Start of Package Globals
5
6 G_MODULE_SOURCE constant varchar2(80) := 'fnd.plsql.oid.fnd_ldap_util.';
7 G_TRACK_LDAP_STACK constant boolean:= false;
8
9 ldap_session_stack varchar2(4096):= null;
10
11 g_das_product_base varchar2(1000) := NULL;
12 g_das_base_url varchar2(1000) := NULL;
13 g_das_relative_base constant varchar2(100) := 'cn=OperationUrls,cn=DAS,cn=Products,cn=OracleContext';
14
15
16 g_common_ldap dbms_ldap.session;
17 g_common_counter pls_integer :=0;
18
19
20 -- End of Package Globals
21 --
22 -------------------------------------------------------------------------------
23 init boolean := false;
24 nickname varchar2(256) := null;
25 r_init boolean := false;
26 d_realm varchar2(4000) := null;
27 --
28 -------------------------------------------------------------------------------
29 function get_oid_session return dbms_ldap.session is
30
31 l_module_source varchar2(256);
32 l_retval pls_integer;
33 l_host varchar2(256);
34 l_port varchar2(256);
35 l_user varchar2(256);
36 l_pwd varchar2(256);
37 l_ldap_auth varchar2(256);
38 l_db_wlt_url varchar2(256);
39 l_db_wlt_pwd varchar2(256);
40 l_session dbms_ldap.session;
41
42 begin
43 l_module_source := G_MODULE_SOURCE || 'get_oid_session: ';
44 -- change it to FAILURE if open_ssl fails, else let the simple_bind_s
45 -- go through
46 l_retval := dbms_ldap.SUCCESS;
47
48 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
49 then
50 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
51 end if;
52
53 dbms_ldap.use_exception := TRUE;
54
55 l_host := fnd_preference.get(G_INTERNAL, G_LDAP_SYNCH, G_HOST);
56 l_port := fnd_preference.get(G_INTERNAL, G_LDAP_SYNCH, G_PORT);
57 l_user := fnd_preference.get(G_INTERNAL, G_LDAP_SYNCH, G_USERNAME);
58 l_pwd := fnd_preference.eget(G_INTERNAL, G_LDAP_SYNCH, G_EPWD, G_LDAP_PWD);
59 l_ldap_auth := fnd_preference.get(G_INTERNAL, G_LDAP_SYNCH, G_DBLDAPAUTHLEVEL);
60 l_db_wlt_url := fnd_preference.get(G_INTERNAL, G_LDAP_SYNCH, G_DBWALLETDIR);
61 l_db_wlt_pwd := fnd_preference.eget(G_INTERNAL, G_LDAP_SYNCH, G_DBWALLETPASS, G_LDAP_PWD);
62
63 l_session := DBMS_LDAP.init(l_host, l_port);
64
65 -- Elan, 04/27/2004, Not disclosing the password - gets saved to the database
66 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
67 then
68 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source
69 , 'l_host = ' || l_host || ', l_port = ' || l_port ||
70 ', l_ldap_auth = ' || l_ldap_auth || ', l_db_wlt_url = ' ||
71 l_db_wlt_url ||
72 ', l_user = ' || l_user || ', l_pwd = ****');
73 end if;
74
75 if ( l_ldap_auth > 0 )
76 then
77 l_retval := dbms_ldap.open_ssl
78 (l_session, 'file:'||l_db_wlt_url, l_db_wlt_pwd, l_ldap_auth);
79 end if;
80
81 --dbms_ldap.use_exception := false;
82 --retval := dbms_ldap.open_ssl(my_session, ' ', ' ', 1);
83
84 if (l_retval = dbms_ldap.SUCCESS) then
85 l_retval := dbms_ldap.simple_bind_s(l_session, l_user, l_pwd);
86 else
87 fnd_message.set_name ('FND', 'FND_SSO_SSL_ERROR');
88 raise_application_error(-20002, 'FND_SSO_SSL_ERROR');
89 end if;
90
91 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
92 then
93 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
94 end if;
95
96 return l_session;
97
98 exception
99 when dbms_ldap.invalid_session then
100 fnd_message.set_name ('FND', 'FND_SSO_INV_SESSION');
101 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
102 then
103 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
104 end if;
105 raise;
106 when dbms_ldap.invalid_ssl_wallet_loc then
107 fnd_message.set_name ('FND', 'FND_SSO_WALLET_LOC');
108 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
109 then
110 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
111 end if;
112 raise;
113 when dbms_ldap.invalid_ssl_wallet_passwd then
114 fnd_message.set_name ('FND', 'FND_SSO_WALLET_PWD');
115 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
116 then
117 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
118 end if;
119 raise;
120 when dbms_ldap.invalid_ssl_auth_mode then
121 fnd_message.set_name ('FND', 'FND_SSO_INV_AUTH_MODE');
122 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
123 then
124 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
125 end if;
126 raise;
127 when others then
128 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
129 then
130 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
131 end if;
132 raise;
133
134 end get_oid_session;
135 --
136 -------------------------------------------------------------------------------
137 function unbind(p_session in out nocopy dbms_ldap.session) return pls_integer
138 is
139 retval pls_integer;
140 l_module_source varchar2(256);
141 begin
142
143 l_module_source := G_MODULE_SOURCE || 'unbind: ';
144
145 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
146 then
147 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
148 end if;
149
150 retval := dbms_ldap.unbind_s(p_session);
151
152 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
153 then
154 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
155 end if;
156
157 return retval;
158
159 exception
160 when others then
161 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
162 then
163 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
164 end if;
165 raise;
166
167 end unbind;
168 --
169 -------------------------------------------------------------------------------
170 /* this procedure removes any " in the appName. Some deployments have this special character */
171 procedure removeExtraQuotes(p_app in out nocopy varchar2) is
172
173 quotesIndex pls_integer;
174 strLength pls_integer;
175 l_module_source varchar2(256);
176
177 begin
178
179 l_module_source := G_MODULE_SOURCE || 'removeExtraQuotes: ';
180
181 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
182 then
183 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
184 end if;
185
186 strLength := length(p_app);
187
188 while instr(p_app, '"') <> 0 loop
189 quotesIndex := instr(p_app, '"');
190 p_app := Substr(p_app, 0, quotesIndex-1) || Substr(p_app, quotesIndex+1, strLength);
191 end loop;
192
193 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
194 then
195 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
196 end if;
197
198 exception
199 when others then
200 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
201 then
202 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
203 end if;
204 raise;
205
206 end removeExtraQuotes;
207 ---------------------------------------------------------------
208 -- The username to connecto oid
209 function get_orclappname return varchar2 is
210
211 l_module_source varchar2(256);
212 orclAppName varchar2(256);
213
214 begin
215
216 l_module_source := G_MODULE_SOURCE || 'get_orclappname: ';
217
218 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
219 then
220 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
221 end if;
222
223 -- Bug 5001849 use FND API instead of directly doing a select against
224 -- fnd_user_preferences
225
226 orclAppName := fnd_preference.get(p_user_name => '#INTERNAL',
227 p_module_name => 'LDAP_SYNCH',
228 p_pref_name => 'USERNAME');
229
230 removeExtraQuotes(orclAppName);
231
232 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
233 then
234 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
235 end if;
236
237 return orclAppName;
238
239 exception
240 when others then
241 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
242 then
243 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
244 end if;
245 raise;
246
247 end get_orclappname;
248 --
249 -------------------------------------------------------------------------------
250 -- DELETED
251 -- function get_users_nodes return dbms_ldap.string_collection
252 --
253 -------------------------------------------------------------------------------
254 function get_dn_for_guid(p_orclguid in fnd_user.user_guid%type) return varchar2 is
255
256 l_module_source varchar2(256);
257 result pls_integer;
258 l_dn varchar2(1000);
259 l_base varchar2(1000);
260 l_message dbms_ldap.message := NULL;
261 l_entry dbms_ldap.message := NULL;
262 l_attrs dbms_ldap.string_collection;
263 l_ldap_session dbms_ldap.session;
264
265 begin
266
267 l_module_source := G_MODULE_SOURCE || 'get_dn_for_GUID: ';
268
269 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
270 then
271 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
272 end if;
273
274 l_base := '';
275 l_ldap_session := get_oid_session;
276
277 result := dbms_ldap.search_s(ld => l_ldap_session
278 , base => l_base
279 , scope => dbms_ldap.SCOPE_SUBTREE
280 , filter => 'orclguid='||p_orclguid
281 , attrs => l_attrs
282 , attronly => 0
283 , res => l_message);
284 l_entry := dbms_ldap.first_entry(l_ldap_session, l_message);
285
286 if (l_entry is null) then
287 l_dn := null;
288 else
289 l_dn := dbms_ldap.get_dn(l_ldap_session, l_entry);
290 end if;
291
292 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
293 then
294 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'GUID::'||p_orclguid||' DN::'||l_dn);
295 end if;
296
297 result := unbind(l_ldap_session);
298
299 if (l_dn is null) then
300 raise no_data_found;
301 end if;
302
303 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
304 then
305 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
306 end if;
307
308 return l_dn;
309
310 exception
311 when no_data_found then
312 fnd_message.set_name ('FND', 'FND_SSO_USER_NOT_FOUND');
313 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
314 then
315 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'No user found with the given GUID');
316 end if;
317 raise;
318 when others then
319 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
320 then
321 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
322 end if;
323 raise;
324
325 end get_dn_for_GUID;
326 --
327 -------------------------------------------------------------------------------
328 function get_dn_for_guid(p_orclguid in fnd_user.user_guid%type,
329 p_ldap_session in dbms_ldap.session) return varchar2 is
330
331 l_module_source varchar2(256);
332 result pls_integer;
333 l_dn varchar2(1000);
334 l_base varchar2(1000);
335 l_message dbms_ldap.message := NULL;
336 l_entry dbms_ldap.message := NULL;
337 l_attrs dbms_ldap.string_collection;
338
339 begin
340
341 l_module_source := G_MODULE_SOURCE || 'get_dn_for_GUID: ';
342
343 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
344 then
345 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
346 end if;
347
348 l_base := '';
349
350 result := dbms_ldap.search_s(ld => p_ldap_session
351 , base => l_base
352 , scope => dbms_ldap.SCOPE_SUBTREE
353 , filter => 'orclguid='||p_orclguid
354 , attrs => l_attrs
355 , attronly => 0
356 , res => l_message);
357 l_entry := dbms_ldap.first_entry(p_ldap_session, l_message);
358
359 if (l_entry is null) then
360 l_dn := null;
361 else
362 l_dn := dbms_ldap.get_dn(p_ldap_session, l_entry);
363 end if;
364
365 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
366 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'GUID::'||p_orclguid||' DN::'||l_dn);
367 end if;
368
369 if (l_dn is null) then
370 raise no_data_found;
371 end if;
372
373 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
374 then
375 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
376 end if;
377
378 return l_dn;
379
380 exception
381 when no_data_found then
382 fnd_message.set_name ('FND', 'FND_SSO_USER_NOT_FOUND');
383 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
384 then
385 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'No user found with the given GUID');
386 end if;
387 raise;
388 when others then
389 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
390 then
391 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
392 end if;
393 raise;
394
395 end get_dn_for_GUID;
396 --
397 -------------------------------------------------------------------------------
398 /*
399 ** Name : get_guid_for_dn
400 ** Type : Private (? , probably others will need this ,
401 ** is it a candidate for the API ?
402 ** Desc : Given and DN return its orclguid
403 ** if DN is not found, the raise "NO_DATA_FOUND'
404
405 ** Parameters :
406 ** aDN: the application DN, for example
407 ** orclApplicationCommonName=PROD1,cn=EBusiness,cn=Products,cn=OracleContext,dc=us,dc=oracle,dc=com
408 ** Returns :
409 ** Its orclguid
410 ** If it is NULL then the DN does not have a orcGuild attribute
411 ** Exceptions:
412 ** DATA_NOT_FOUND if search_s raise DBMS_LDAP.GENERAL_EXCEPTION
413 ** NOte that this DBMS_LDAP exception maybe risen by other reasons
414 **
415 */
416 function get_guid_for_dn(ldapSession in dbms_ldap.session,p_dn in varchar2) return varchar2
417 is
418
419 result pls_integer;
420 l_message dbms_ldap.message := null; -- the query result set
421 l_entry dbms_ldap.message := null; -- the entry
422 l_attrs dbms_ldap.string_collection; -- lookup attributes
423 l_guid varchar2(100); -- returning guid
424 err varchar2(1000);
425 l_module_source varchar2(256);
426 begin
427 l_module_source := G_MODULE_SOURCE || 'get_guid_for_dn:';
428
429 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
430 then
431 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
432 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'DN='||p_dn);
433 end if;
434
435 l_attrs(0) := 'orclGuid';
436 begin
437 result := dbms_ldap.search_s(ld => ldapSession,
438 base => p_dn,
439 scope => dbms_ldap.SCOPE_BASE,
440 filter => 'objectclass=*',
441 attrs => l_attrs,
442 attronly => 0,
443 res => l_message);
444 exception
445 when dbms_ldap.general_error then
446 -- asume that DN not found
450 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
447 -- is not accurate, but better that nothing
448 err := SQLERRM;
449 if (instr(err,'No such object')>1) then
451 then
452 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
453 ' DN not found : raise NO_DATA_FOUND');
454 end if;
455 raise NO_DATA_FOUND;
456 else
457 if (fnd_log.LEVEL_EXCEPTION>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
458 then
459 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, SQLERRM);
460 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, ' from dbms_ldap.search_s, dn='||p_dn);
461 end if;
462 raise;
463 end if;
464 end;
465 l_entry := dbms_ldap.first_entry(ldapSession, l_message);
466 l_attrs := dbms_ldap.get_values(ldapSession, l_entry, 'orclGuid');
467 begin
468 l_guid := l_attrs(0);
469 exception
470 when NO_DATA_FOUND then
471 -- this entry does not have orclguid
472 l_guid := null;
473 end;
474
475
476 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
477 then
478 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' END : guid='||l_guid);
479 end if;
480 return l_guid;
481
482 exception
483 when others then
484 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
485 then
486 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, ' for DN='||p_dn);
487 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
488 end if;
489 raise;
490 end get_guid_for_dn;
491 --
492 -------------------------------------------------------------------------------
493 function get_default_realm(username in out nocopy varchar2) return varchar2 is
494
495 l_module_source varchar2(256);
496 result pls_integer;
497 l_result varchar2(4000);
498 l_base varchar2(100);
499 l_message dbms_ldap.message := NULL;
500 l_entry dbms_ldap.message := NULL;
501 l_attrs dbms_ldap.string_collection;
502 l_ldap_session dbms_ldap.session;
503
504 begin
505
506 l_module_source := G_MODULE_SOURCE || 'get_default_realm ';
507
508 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
509 then
510 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
511 end if;
512 l_result := FND_OID_PLUG.getRealmDN(username);
513 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
514 then
515 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'realm: '||l_result);
516 end if;
517
518 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
519 then
520 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
521 end if;
522
523 return l_result;
524
525 exception
526 when others then
527 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
528 then
529 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
530 end if;
531 raise;
532
533 end get_default_realm;
534 --
535 -------------------------------------------------------------------------------
536 -- DELETED
537 -- function get_search_nodes return dbms_ldap.string_collection is
538
539 --
540 -------------------------------------------------------------------------------
541 function get_orclcommonnicknameattr(username in out nocopy varchar2) return varchar2 is
542
543 l_module_source varchar2(256);
544 result pls_integer;
545 l_base varchar2(1000);
546 l_message dbms_ldap.message := NULL;
547 l_entry dbms_ldap.message := NULL;
548 l_attrs dbms_ldap.string_collection;
549 l_ldap_session dbms_ldap.session;
550
551 begin
552
553 l_module_source := G_MODULE_SOURCE || 'get_orclcommonnicknameattr ';
554
555 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
556 then
557 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
558 end if;
559 l_base := fnd_oid_plug.getNickNameattr(username);
560 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
561 then
562 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END:'||username||' '||l_base);
563 end if;
564 return l_base;
565 exception
566 when others then
567 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
568 then
569 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
570 end if;
571 raise;
572 end get_orclcommonnicknameattr;
573 --
574 -------------------------------------------------------------------------------
575 procedure proxy_as_user(p_orclguid in fnd_user.user_guid%type, x_ldap_session out nocopy dbms_ldap.session) is
576
577 l_module_source varchar2(256);
578 l_retval pls_integer;
579 l_dn varchar2(512);
580
581 proxy_failed_exp exception;
582
583 PRAGMA EXCEPTION_INIT (proxy_failed_exp, -20002);
584
585 begin
586 l_module_source := G_MODULE_SOURCE || 'proxy_as_user: ';
587
591 end if;
588 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
589 then
590 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
592
593 x_ldap_session := fnd_ldap_util.get_oid_session;
594 l_dn := get_dn_for_guid(p_orclguid => p_orclguid, p_ldap_session => x_ldap_session);
595
596 dbms_ldap.use_exception := true;
597
598 l_retval := dbms_ldap.simple_bind_s(x_ldap_session, l_dn, null);
599
600 if (l_retval = dbms_ldap.SUCCESS) then
601 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
602 then
603 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Proxied Successfully for User DN:' ||
604 l_dn);
605 end if;
606 else
607 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
608 then
609 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Proxy Failed for User DN: ' ||
610 l_dn);
611 end if;
612 raise proxy_failed_exp;
613 end if;
614
615 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
616 then
617 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
618 end if;
619
620 --return l_session;
621
622 exception
623 when dbms_ldap.invalid_session then
624 fnd_message.set_name ('FND', 'FND_SSO_INV_SESSION');
625 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
626 then
627 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
628 end if;
629 raise;
630 when others then
631 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
632 then
633 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, sqlerrm);
634 end if;
635 raise;
636
637 end proxy_as_user;
638 --
639 -------------------------------------------------------------------------------
640 /*
641 ** Name : get_attribute
642 ** Type : Private
643 ** is it a candidate for the API ?
644 ** Desc : Given a DN and an attribut rename returns the value for that entry.
645 ** if DN is not found, the raise "NO_DATA_FOUND'
646
647 ** Parameters :
648 ** p_ldap_session: a valid connection
649 ** p_dn: DN
650 ** p_attr_name: Attribute name
651 **
652 **
653 ** Returns :
654 ** The value.
655 ** Returns NULL in the cases that attribute is not present in the entry, or the DN does not exists
656 ** Exceptions: NONE (? maybe change it to NODATAFOUND for the DN missing case)
657 **
658 */
659
660
661 FUNCTION get_attribute(p_ldap_session IN dbms_ldap.SESSION, p_dn IN VARCHAR2, p_attr_name IN VARCHAR2) RETURN VARCHAR2 IS l_module_source VARCHAR2(256);
662 l_attrs dbms_ldap.string_collection;
663 l_result VARCHAR2(1000);
664 result pls_integer;
665 l_entry dbms_ldap.message := NULL;
666 l_message dbms_ldap.message := NULL;
667 BEGIN
668 l_module_source := g_module_source || 'get_Attribute ';
669
670 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
671 fnd_log.string(fnd_log.level_statement, l_module_source, 'Begin ');
672 fnd_log.string(fnd_log.level_statement, l_module_source, ' dn:' || p_dn || ' attr:' || p_attr_name);
673 END IF;
674
675 l_attrs(0) := p_attr_name;
676 result := dbms_ldap.search_s(ld => p_ldap_session, base => p_dn,
677 scope => dbms_ldap.scope_base, filter => '(objectclass=*)',
678 attrs => l_attrs, attronly => 0, res => l_message);
679 l_entry := dbms_ldap.first_entry(p_ldap_session, l_message);
680 l_attrs := dbms_ldap.get_values(p_ldap_session, l_entry, p_attr_name);
681 BEGIN
682 l_result := l_attrs(0);
683 EXCEPTION WHEN NO_DATA_FOUND THEN
684 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
685 fnd_log.string(fnd_log.level_statement, l_module_source, 'attribute '||p_attr_name||' not present at '||p_dn);
686 END IF;
687 l_result := null; -- DN found, but does not contain the attribute
688 END;
689
690 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
691 fnd_log.string(fnd_log.level_statement, l_module_source, 'END-> ' || l_result);
692 END IF;
693 return l_result;
694
695 EXCEPTION
696 WHEN dbms_ldap.general_error THEN
697 BEGIN
698
699 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
700 fnd_log.string(fnd_log.level_statement, l_module_source, 'END-> not found '||sqlerrm);
701 END IF;
702 return null; -- DN NOT FOUND
703 END;
704 WHEN OTHERS THEN
705
706 IF(fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
707 fnd_log.string(fnd_log.level_error, l_module_source, sqlerrm);
708
709 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
710 fnd_log.string(fnd_log.level_statement, l_module_source, 'END-> RAISE');
711 END IF;
712
713 END IF;
714
715 RAISE;
716 END get_attribute;
717
718 --
719 -------------------------------------------------------------------------------
723 ** Desc : Returns the DAS url like 'http://xxx:123/oiddas/'
720 /*
721 ** Name : get_das_base_url
722 ** Type : Private
724 ** All the operations URL are relative to this.
725 ** Parameters :
726 ** p_ldap_session: a valid connection
727 ** p_realm_dn: The realm
728 **
729 **
730 ** Returns : and Url string
731 ** Exceptions: NONE (? maybe change it to NODATAFOUND for the DN missing case)
732 **
733 ** Note:
734 ** Although it may seems it support multiple realms it does not.
735 ** Since the value is cached, only the first value will be returned after that, even for other realms.
736 ** THIS NEEDS TO BE FIXED for multiple realm support
737 */
738
739
740 FUNCTION get_das_base_url(p_ldap_session IN dbms_ldap.SESSION, p_realm_dn IN VARCHAR2) RETURN VARCHAR2 IS
741
742 l_module_source VARCHAR2(256);
743 l_url VARCHAR2(2000);
744 l_attrs dbms_ldap.string_collection;
745 l_result VARCHAR2(1000);
746 l_try VARCHAR2(1000);
747
748 BEGIN
749 l_module_source := g_module_source || 'get_DAS_BASE_URL ';
750
751 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
752 fnd_log.string(fnd_log.level_statement, l_module_source, 'Begin ');
753 END IF;
754
755 IF(g_das_base_url is not NULL) THEN
756
757 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
758 fnd_log.string(fnd_log.level_statement, l_module_source, 'END (cached)-> ' || g_das_base_url);
759 END IF;
760
761 RETURN g_das_base_url;
762 END IF;
763 l_try := g_das_relative_base||','||p_realm_dn;
764 l_result := get_attribute(p_ldap_session,l_try, 'orcldasurlbase' );
765 if (l_result is not null)
766 THEN
767 g_das_product_base := l_try;
768 g_das_base_url := l_result;
769 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
770 fnd_log.string(fnd_log.level_statement, l_module_source, ' DAS_BASE_DN ' || g_das_product_base);
771 fnd_log.string(fnd_log.level_statement, l_module_source, ' DAS_URL-> ' || g_das_base_url);
772 fnd_log.string(fnd_log.level_statement, l_module_source, 'END -> ' || g_das_base_url);
773 END IF;
774
775 return g_das_base_url;
776 END IF;
777 l_try := g_das_relative_base;
778 l_result := get_attribute(p_ldap_session,l_try, 'orcldasurlbase' );
779 if (l_result is not null)
780 THEN
781 g_das_product_base :=l_try;
782 g_das_base_url := l_result;
783 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
784 fnd_log.string(fnd_log.level_statement, l_module_source, ' DAS_BASE_DN ' || g_das_product_base);
785 fnd_log.string(fnd_log.level_statement, l_module_source, ' DAS_URL-> ' || g_das_base_url);
786 fnd_log.string(fnd_log.level_statement, l_module_source, 'END -> ' || g_das_base_url);
787 END IF;
788 return g_das_base_url;
789 END IF;
790 /*
791 * Incorrect settings or something
792 */
793 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level)
794 THEN
795 fnd_log.string(fnd_log.level_statement, l_module_source, 'END-> FAILED ' );
796 END IF;
797 G_DAS_PRODUCT_BASE:=NULL;
798 G_DAS_BASE_URL := NULL;
799 return NULL;
800
801 EXCEPTION WHEN others THEN
802
803 IF(fnd_log.level_error >= fnd_log.g_current_runtime_level)
804 THEN
805 fnd_log.string(fnd_log.level_error, l_module_source, sqlerrm);
806 END IF;
807
808 fnd_message.set_name('FND', 'get_DAS_BASE_URL'); RETURN NULL;
809 END get_das_base_url;
810 --
811 -------------------------------------------------------------------------------
812 --
813 -------------------------------------------------------------------------------
814 /*
815 ** Name : get_das_operationurl
816 ** Type : Private (FND Internal)
817 ** Desc : Return URL for operation (For exmaple 'Password Change' at DAS.
818
819 ** Parameters :
820 ** p_realm: The realm , NULL for request default value.
821 ** p_operation: operation (see package schema for examples )
822 ** Returns :
823 ** A URL string
824 ** Exceptions: NONE
825 **
826 ** Note: This function can be tested from SQL*Plus , for example
827 ** select fnd_ldap_util.get_das_operationurl(null,'Password Change') from dual;
828 **
829 ** http://rslnz.us.oracle.com:7777/oiddas/ui/oracle/ldap/das/mypage/AppChgPwdMyPage
830 */
831
832 FUNCTION get_das_operationurl(p_realm IN VARCHAR2, p_operation IN VARCHAR2) RETURN VARCHAR2 IS
833
834
835 l_result VARCHAR2(4000) := NULL;
836 l_base VARCHAR2(4000) := NULL;
837 l_ldap_session dbms_ldap.SESSION;
838 l_module_source varchar2(2000);
839 result pls_integer;
840 BEGIN
841 l_module_source := g_module_source || 'get_das_operationurl ';
842
843 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
844 fnd_log.string(fnd_log.level_statement, l_module_source, 'Begin ');
848 l_ldap_session := fnd_ldap_util.get_oid_session;
845 fnd_log.string(fnd_log.level_statement, l_module_source, ' realm:' || p_realm || ' op:"' || p_operation||'"');
846 END IF;
847
849 IF (p_realm is NOT null)
850 THEN
851
852 l_result := get_attribute(l_ldap_session, 'cn='|| p_operation||','|| g_das_relative_base||','||p_realm, 'orcldasurl' );
853 l_base := get_das_base_url(l_ldap_session,p_realm);
854 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
855
856 fnd_log.string(fnd_log.level_statement, l_module_source, ' op_url:' || l_result);
857 fnd_log.string(fnd_log.level_statement, l_module_source, ' base:' || l_base);
858
859 END IF;
860 END IF;
861 -- For no realm returned the default data (which is not the same
862 -- as data for the default realm )
863 if (p_realm is NULL or l_result is null)
864 THEN
865 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
866 fnd_log.string(fnd_log.level_statement, l_module_source, ' get default Urls');
867 END IF;
868
869 l_result := get_attribute(l_ldap_session, 'cn='||p_operation||','||g_das_relative_base, 'orcldasurl' );
870 l_base := get_das_base_url(l_ldap_session,null);
871 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
872 fnd_log.string(fnd_log.level_statement, l_module_source, ' op_url:' || l_result);
873 fnd_log.string(fnd_log.level_statement, l_module_source, ' base:' || l_base);
874 END IF;
875 END IF;
876
877 result := fnd_ldap_util.unbind(l_ldap_session);
878 if (l_result is null or l_base is null)
879 then
880 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
881 fnd_log.string(fnd_log.level_statement, l_module_source, 'END->NULLl:');
882 END IF;
883 return NULL;
884 end if;
885 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
886 fnd_log.string(fnd_log.level_statement, l_module_source, 'END-> ' || l_base||l_result);
887 END IF;
888 return l_base||l_result;
889
890 EXCEPTION WHEN OTHERS THEN
891 IF(fnd_log.level_error >= fnd_log.g_current_runtime_level)
892 THEN
893 fnd_log.string(fnd_log.level_error, l_module_source, sqlerrm);
894 END IF;
895
896 fnd_message.set_name('FND', 'get_DAS_BASE_URL'); RETURN NULL;
897 return null;
898 END get_das_operationurl;
899
900 ----------------------------------------------------------------------------
901 --
902 function get_user_create_base(username in out nocopy varchar2) return varchar2
903 is
904 BEGIN
905 username := FND_OID_PLUG.getUserRepository(username);
906 END get_user_create_base;
907
908 function get_users_search_base(username in out nocopy varchar2) return varchar2
909 is
910 BEGIN
911 username := FND_OID_PLUG.getSearchBase(username);
912 END get_users_search_base;
913
914 -------------------------------------------------------------------------------------------------
915 ---
916 procedure add_attribute_M(x_ldap in out nocopy dbms_ldap.session, dn in varchar2, name in varchar2, value in varchar2 )
917 IS
918 modArray dbms_ldap.mod_array;
919 vals dbms_ldap.string_collection;
920 ret pls_integer;
921 l_module_source varchar2(256):= G_MODULE_SOURCE || 'add_attribute_M: ';
922
923 BEGIN
924 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
925 then
926 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
927 end if;
928 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
929 then
930 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'dn='||dn);
931 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'attribute='||name||' value='||value);
932 end if;
933
934 modArray := dbms_ldap.create_mod_array(num => 1);
935 vals(0) := value;
936 dbms_ldap.populate_mod_array(modptr=>modArray,mod_op=>dbms_ldap.mod_add,mod_type=>name,modval=>vals);
937 ret := dbms_ldap.modify_s(ld=>x_ldap,entrydn=>dn,modptr=>modArray);
938 dbms_ldap.free_mod_array(modptr => modArray);
939 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
940 then
941 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END');
942 end if;
943
944 exception when others then
945 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
946 then
947 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, sqlerrm);
948 end if;
949 raise;
950
951 END add_attribute_M;
952
953
954 function c_get_oid_session(flag in out nocopy pls_integer) return dbms_ldap.session
955 is
956 l_module_source varchar2(256):= G_MODULE_SOURCE || 'c_get_oid_session: ';
957 BEGIN
958 /*
959 * flag=-99 just to print the stack on the log
960 */
961 IF (flag=-99) THEN
962 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
963 then
964 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'DUMP ldap status for FND_LDAP_UTIL='||g_common_counter);
965 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'g_common_counter='||g_common_counter);
969 end if;
966 IF (G_TRACK_LDAP_STACK) THEN
967 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ldap_session_stack);
968 END IF;
970 return null;
971 END IF;
972
973
974
975 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
976 then
977 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
978 end if;
979 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
980 then
981 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'initialy g_common_counter='||g_common_counter);
982 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'initialy flag='||flag);
983 end if;
984
985 if (g_common_counter=0) then
986 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
987 then
988 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'creating a new connection');
989 IF (G_TRACK_LDAP_STACK) THEN
990 ldap_session_stack := dbms_utility.FORMAT_CALL_STACK;
991 END IF;
992 end if;
993 g_common_ldap := get_oid_session;
994 end if;
995 flag := g_common_counter;
996 g_common_counter := g_common_counter + 1;
997
998 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
999 then
1000 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'finally g_common_counter='||g_common_counter);
1001 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'finally flag='||flag);
1002 end if;
1003 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1004 then
1005 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END');
1006 end if;
1007 return g_common_ldap;
1008
1009 exception when others then
1010 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1011 then
1012 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, sqlerrm);
1013 end if;
1014 raise;
1015 END c_get_oid_session;
1016 procedure c_unbind(ldap in out nocopy dbms_ldap.session , flag in out nocopy pls_integer)
1017 is
1018 l_module_source varchar2(256):= G_MODULE_SOURCE || 'c_unbind: ';
1019 ret pls_integer;
1020 begin
1021 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1022 then
1023 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1024 end if;
1025
1026 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1027 then
1028 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' incomming g_common_counter='||g_common_counter);
1029 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' incomming flag='||flag);
1030 end if;
1031
1032 if (flag=0)then
1033 ret := unbind(g_common_ldap);
1034 g_common_counter := 0;
1035 elsif (g_common_counter>1) then
1036 g_common_counter := g_common_counter - 1;
1037 -- we don't wont to reach 0, because we will lose control.
1038 else
1039 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1040 then
1041 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, 'g_common_counter reached invalid value='||g_common_counter);
1042 end if;
1043
1044 end if;
1045 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1046 then
1047 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' final g_common_counter='||g_common_counter);
1048 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' final flag='||flag);
1049 end if;
1050 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1051 then
1052 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END');
1053 end if;
1054 exception when others then
1055 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1056 then
1057 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, sqlerrm);
1058 end if;
1059 raise;
1060 end c_unbind;
1061 end fnd_ldap_util;
1062