1 package body fnd_ldap_user as
2 /* $Header: AFSCOLUB.pls 120.71 2012/04/12 20:13:54 ctilley ship $ */
3 --
4 -------------------------------------------------------------------------------
5 -- Start of Package Globals
6
7 G_CREATE constant pls_integer := 1;
8 G_UPDATE constant pls_integer := 2;
9 G_MODULE_SOURCE constant varchar2(80) := 'fnd.plsql.oid.fnd_ldap_user.';
10 G_OID_USER_EXISTS constant pls_integer := 2;
11
12 -- End of Package Globals
13 --
14 -------------------------------------------------------------------------------
15
16 function delete_user_nodes(p_ldap_session in dbms_ldap.session, p_orclguid in fnd_user.user_guid%type) return pls_integer;
17 function delete_user_subscription(p_ldap_session in dbms_ldap.session, guid raw) return pls_integer;
18 function delete_uniquemember(p_ldap_session in dbms_ldap.session, p_orclguid in fnd_user.user_guid%type) return pls_integer;
19 procedure ProcessLoadedLpadUserRecord (p_ldap_user IN OUT nocopy fnd_ldap_user.ldap_user_type ,realmDN in varchar2 ,dn_z in varchar2 );
20 --function CanSync ( p_user_id in pls_integer, p_user_name in varchar2 ) return boolean;
21 function get_user_guid(p_ldap_session in dbms_ldap.session, p_user_name in varchar2, dn out nocopy varchar2) return raw ;
22 function isValueOf( u ldap_user_type, fld in varchar2, val in varchar2 ) return boolean;
23 function get_user_guid( p_user_name in varchar2) return raw ;
24 function CanUpdate( attr in varchar2 , user_name in varchar2 , realm in varchar2,x_user_creation in boolean default FALSE) return boolean;
25 function CanPopulate( attr in varchar2 , user_name in varchar2 , realm in varchar2) return boolean;
26 -- Bug 9271995 : internal signature
27 procedure update_user(p_user_guid in raw,
28 p_user_name in varchar2,
29 p_password in varchar2 default null,
30 p_start_date in date default null,
31 p_end_date in date default null,
32 p_description in varchar2 default null,
33 p_email_address in varchar2 default null,
34 p_fax in varchar2 default null,
35 p_expire_password in pls_integer,
36 x_password out nocopy varchar2,
37 x_result out nocopy pls_integer,
38 x_user_creation in boolean default FALSE ) ;
39
40 --
41 -- Type to hold preferences
42 TYPE update_record IS record (
43 att varchar2(200),
44 op varchar2(10),
45 val varchar2(4000)
46 );
47
48 TYPE update_list IS table OF update_record INDEX BY pls_integer;
49
50 PROCEDURE ProcessUpdateRec(ldap in dbms_ldap.session, dn in varchar2, upd in update_list);
51
52 --
53 -- LOCAL EXCEPTIONS
54 CANNOT_CREATE_EXCEPTION EXCEPTION;
55
56 duplicate_dn_EXCEPTION EXCEPTION;
57 duplicate_username_EXCEPTION EXCEPTION;
58 link_create_failed_EXCEPTION EXCEPTION;
59
60
61 cache_user_name varchar2(200) := null;
62 cache_nna varchar2(200) := null;
63 cache_default_nna varchar2(200) := null;
64
65 --
66 -------------------------------------------------------------------------------
67 --- REMOVED
68 -- function add_uniquemember(p_ldap_user in fnd_ldap_util.ldap_user_type) return pls_integer is
69 -- translate_ldap_error: Internal
70 -- Will attempt to translate the sqlerrms from an dbms_ldap operation into a FND message
71
72 -------------------------------------------------------------------------------
73 function translate_ldap_error( errm in varchar2) return varchar2
74 is
75 begin
76
77 if (instr(errm,':9000')>0 ) then return 'FND_SSO_PASSWORD_EXPIRED'; end if;
78 if (instr(errm,':9001')>0 ) then return 'FND_SSO_LOCKED'; end if;
79 if (instr(errm,':9002')>0 ) then return 'FND_SSO_PASSWORD_EXPIRED'; end if;
80 if (instr(errm,':9003')>0 ) then return 'FND_SSO_PASSWORD_POLICY_ERR'; end if;
81 if (instr(errm,':9004')>0 ) then return 'FND_SSO_PASSWORD_POLICY_ERR'; end if;
82 if (instr(errm,':9005')>0 ) then return 'FND_SSO_PASSWORD_POLICY_ERR'; end if;
83 if (instr(errm,':9006')>0 ) then return 'FND_SSO_PASSWORD_POLICY_ERR'; end if;
84 if (instr(errm,':9007')>0 ) then return 'FND_SSO_PASSWORD_POLICY_ERR'; end if;
85 if (instr(errm,':9008')>0 ) then return 'FND_SSO_UNEXP_ERROR'; end if;
86 if (instr(errm,':9009')>0 ) then return 'FND_SSO_UNEXP_ERROR'; end if;
87 if (instr(errm,':9010')>0 ) then return 'FND_SSO_UNEXP_ERROR'; end if;
88 if (instr(errm,':9011')>0 ) then return 'FND_SSO_CL_IP_LOCK'; end if;
89 if (instr(errm,':9050')>0 ) then return 'FND_SSO_USER_DISABLED'; end if;
90 if (instr(errm,':9051')>0 ) then return 'FND_SSO_LOCKED'; end if;
91 if (instr(errm,':9052')>0 ) then return 'FND_SSO_USER_DISABLED'; end if;
92 if (instr(errm,':9053')>0 ) then return 'FND_SSO_USER_DISABLED'; end if;
93 return 'FND_SSO_UNEXP_ERROR';
94
95
96 end translate_ldap_error;
97
98 --
99 -------------------------------------------------------------------------------
100 PROCEDURE delete_user(ldapSession in dbms_ldap.session, p_user_guid in fnd_user.user_guid%type ,
101 x_result out nocopy pls_integer,
102 p_forced in boolean default false) is
103
104
105 l_module_source varchar2(256) := G_MODULE_SOURCE || 'delete_user: ';
106 l_orclappname varchar2(256);
107 l_user_name varchar2(256);
108 subsNode varchar2(1000);
109 --ldapSession dbms_ldap.session;
110 l_message dbms_ldap.message := null;
111 l_entry dbms_ldap.message := null;
112 l_attrs dbms_ldap.string_collection;
113 l_attrs_vals dbms_ldap.string_collection;
114 l_isenabled varchar2(100);
115 l_creatorname varchar2(1000);
116 searchNodes dbms_ldap.string_collection;
117 l_filter varchar2(256);-- := 'cn=' || p_user_name; Commented out by scheruku to use orclguid instead
118 l_base varchar2(1000);
119 l_guid raw(256);
120 l_fnd_op pls_integer;
121 l_oid_op pls_integer;
122 sso_registration_failure exception;
123 -- l_apps_user_key_type fnd_oid_util.apps_user_key_type;
124 l_orclguid fnd_user.user_guid%type;
125 begin
126
127 -- initializing
128 l_module_source := G_MODULE_SOURCE || 'delete_user: ';
129 x_result := fnd_ldap_util.G_SUCCESS;
130
131 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
132 then
133 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
134 end if;
135
136 --scheruku :: Added logic to get orclguid from fnd_user
137 -- l_apps_user_key_type := fnd_oid_util.get_fnd_user(p_user_name => p_user_name);
138 -- l_orclguid := l_apps_user_key_type.user_guid;
139 l_orclguid := p_user_guid;
140
141 if(l_orclguid IS NULL)
142 then
143 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
144 then
145 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
146 'NULL guid in FND_USER');
147 end if;
148 x_result := fnd_ldap_util.G_FAILURE;
149 else
150 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
151 then
152 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
153 'FND_USER GUID::'||l_orclguid);
154 end if;
155 l_filter := 'orclguid='||l_orclguid;
156 --ldapSession := fnd_ldap_util.get_oid_session;
157 --searchNodes := fnd_ldap_util.get_search_nodes;
158 l_base := ''; -- don't need any more for guid search
159 l_attrs(0) := 'orclisenabled';
160 l_attrs(1) := 'creatorsname';
161 l_attrs(2) := 'orclguid';
162
163 -- search and delete the user only if the creator is the current apps instance and if the user is disabled.
164
165 --for i in 0..searchNodes.count-1 loop
166 --l_base := searchNodes(i);
167 x_result := dbms_ldap.search_s(ld => ldapSession, base => l_base,
168 scope => dbms_ldap.SCOPE_SUBTREE, filter => l_filter, attrs => l_attrs, attronly => 0, res => l_message);
169 if (x_result is not NULL) then
170
171 l_entry := dbms_ldap.first_entry(ldapSession, l_message);
172 if l_entry is not null then
173
174 -- get the first entry
175 l_entry := dbms_ldap.first_entry(ldapSession, l_message);
176
177 l_attrs_vals := dbms_ldap.get_values(ldapSession, l_entry, 'creatorsname');
178 l_creatorname := l_attrs_vals(0);
179 l_attrs_vals := dbms_ldap.get_values(ldapSession, l_entry, 'orclisenabled');
180 if l_attrs_vals is not NULL and l_attrs_vals.count > 0 then
181 l_isenabled := l_attrs_vals(0);
182 end if;
183 l_attrs_vals := dbms_ldap.get_values(ldapSession, l_entry, 'orclguid');
184 l_guid := l_attrs_vals(0);
185
186 if (p_forced OR (upper(l_creatorname) = upper(fnd_ldap_util.get_orclappname)
187 and l_isenabled is not NULL
188 and (upper(l_isenabled) = 'INACTIVE' or upper(l_isenabled) = 'DISABLED')
189 ) ) then
190 x_result := delete_user_subscription(ldapSession, l_guid);
191 -- x_result := delete_uniquemember(ldapSession, p_user_name);
192 -- x_result := delete_user_nodes(ldapSession, p_user_name);
193
194 --scheruku: Calling the APIS which use the GUID instead
195 x_result := delete_uniquemember(ldapSession, l_orclguid);
196 x_result := delete_user_nodes(ldapSession, l_orclguid);
197
198
199 --end if;
200 --end if;
201 --end if;
202 --end loop;
203 --x_result := fnd_ldap_util.unbind(ldapSession);
204
205 ELSE
206
207 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
208 then
209 if (upper(l_creatorname) = upper(fnd_ldap_util.get_orclappname)) THEN
210 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'NOT DELETED BECAUSE was created by '||l_creatorname);
211 END IF;
212
213 if NOT (l_isenabled is not NULL and (upper(l_isenabled) = 'INACTIVE' or upper(l_isenabled) = 'DISABLED'))
214 THEN
215 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'NOT DELETED BECAUSE is still enabled '||l_isenabled);
216 END IF;
217 end if;
218 end if;
219 end if;
220 end if;
221
222 if (x_result = dbms_ldap.SUCCESS) then
223 x_result := fnd_ldap_util.G_SUCCESS;
224 end if;
225 end if;-- fnd_user guid null check if block ends here
226
227 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
228 then
229 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End');
230 end if;
231
232 exception
233 when others then
234 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
235 then
236 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
237 end if;
238 x_result := fnd_ldap_util.G_FAILURE;
239
240 end delete_user;
241
242 procedure delete_user(p_user_guid in fnd_user.user_guid%type ,
243 x_result out nocopy pls_integer,
244 p_forced in boolean ) is
245
246 ldapSession dbms_ldap.session;
247 dummy pls_integer;
248 BEGIN
249 ldapSession := fnd_ldap_util.c_get_oid_session(dummy);
250 delete_user(ldapSession,p_user_guid,x_result,p_forced);
251 fnd_ldap_util.c_unbind(ldapSession,dummy);
252 end delete_user;
253 procedure delete_user(p_user_guid in fnd_user.user_guid%type ,
254 x_result out nocopy pls_integer ) is
255
256 ldapSession dbms_ldap.session;
257 dummy pls_integer;
258 BEGIN
259
260 delete_user(p_user_guid,x_result,false);
261
262 end delete_user;
263
264
265 --
266
267 -------------------------------------------------------------------------------
268 --** INTERNAL SIGNATURE
269 -- The external siganture can only call change_password for updates
270 -- Only from this package we can call change_password for creation phase
271 --
272 procedure change_password(p_user_guid in raw,
273 p_user_name in varchar2,
274 p_new_pwd in varchar2,
275 p_expire_password in pls_integer,
276 x_password out nocopy varchar2,
277 x_result out nocopy pls_integer,
278 p_user_creation in boolean default FALSE ) is
279 no_such_user_exp exception;
280 PRAGMA EXCEPTION_INIT (no_such_user_exp, -20001);
281 l_module_source varchar2(256):= G_MODULE_SOURCE || 'change_password: ';
282
283 BEGIN
284 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
285 then
286 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin - call update user');
287 end if;
288
289 update_user(p_user_guid =>p_user_guid,
290 p_user_name=>p_user_name,
291 p_password => p_new_pwd,
292 p_expire_password =>p_expire_password,
293 x_password=>x_password,
294 x_result => x_result,
295 x_user_creation=>p_user_creation);
296
297 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
298 then
299 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
300 end if;
301
302 exception
303 when no_such_user_exp then
304 fnd_message.set_name ('FND', 'FND_SSO_USER_NOT_FOUND');
305 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
306 then
307 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
308 end if;
309 x_result := fnd_ldap_util.G_FAILURE;
310 when others then
311 fnd_message.set_name ('FND', 'FND_SSO_UNEXP_ERROR');
312 if (fnd_log.LEVEL_EXCEPTION>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
313 then
314 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
315 end if;
316 --x_result := fnd_ldap_util.G_FAILURE;
317 -- bug 4573677
318 raise;
319
320 end change_password;
321 procedure change_password(p_user_guid in raw,
322 p_user_name in varchar2,
323 p_new_pwd in varchar2,
324 p_expire_password in pls_integer,
325 x_password out nocopy varchar2,
326 x_result out nocopy pls_integer ) is
327 BEGIN
328 change_password(p_user_guid,p_user_name,p_new_pwd,p_expire_password,x_password,x_result,FALSE);
329 END change_password;
330 --
331 -------------------------------------------------------------------------------
332 function user_exists_by_guid( guid in raw ) return pls_integer
333 is
334 dn varchar2(2000);
335 result pls_integer;
336
337 begin
338
342 else
339 dn := fnd_ldap_util.get_dn_for_guid(guid);
340 if (dn is not null) then
341 result := FND_LDAP_UTIL.G_SUCCESS;
343 result := FND_LDAP_UTIL.G_FAILURE;
344 end if;
345 return result;
346
347 EXCEPTION WHEN OTHERS THEN
348 IF (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
349 fnd_log.string(fnd_log.LEVEL_EXCEPTION, G_MODULE_SOURCE || 'user_exists_by_guid:', sqlerrm);
350 END IF;
351 raise;
352
353 end user_exists_by_guid;
354
355 -------------------------------------------------------------------------------
356 procedure change_user_name(p_user_guid in raw,
357 p_old_user_name in varchar2,
358 p_new_user_name in varchar2,
359 x_result out nocopy pls_integer) is
360 l_module_source VARCHAR2(256);
361 l_user_id fnd_user.user_id%type;
362 l_to_synch BOOLEAN;
363 ldap dbms_ldap.session;
364 flag pls_integer;
365 user_rec FND_LDAP_USER.ldap_user_type;
366 invalid_new_user_exp EXCEPTION;
367 no_such_user_exp EXCEPTION;
368 dn VARCHAR2(4000);
369 val varchar2(4000);
370 nna varchar2(200);
371 handle pls_integer;
372 upd update_list;
373 target dbms_ldap.string_collection;
374 fld VARCHAR2(200);
375 i pls_integer;
376 ma dbms_ldap.mod_array;
377 found boolean;
378 PRAGMA EXCEPTION_INIT (no_such_user_exp, -20001);
379 x_fnd pls_integer;
380 x_oid pls_integer;
381
382 begin
383 l_module_source := G_MODULE_SOURCE || 'change_user_name: ';
384 IF (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
385 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
386 END IF;
387 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
388 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'guid:'||p_user_guid||' old='||p_old_user_name||' new='||p_new_user_name);
389 END IF;
390 -- Check the obivious: No change (ignore case)
391 IF (upper(p_old_user_name) =upper(p_new_user_name))THEN
392 IF (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
393 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END-> SAME NAMES, no changes!');
394 END IF;
395 x_result:=fnd_ldap_util.G_SUCCESS;
396 RETURN;
397 END IF;
398 -- look for the user_id.
399 -- this procedure asumes that name was already changed on FND_USER (not commit maybe)
400 BEGIN
401 SELECT user_id
402 INTO l_user_id
403 FROM FND_USER
404 WHERE user_guid=p_user_guid
405 AND user_name =p_old_user_name;
406 EXCEPTION
407 WHEN NO_DATA_FOUND THEN
408 IF (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
409 fnd_log.string(fnd_log.LEVEL_UNEXPECTED , l_module_source, 'Cannot locate user_name[new]='||p_new_user_name||' guid='|| p_user_guid||':'||sqlerrm);
410 END IF;
411 x_result:=fnd_ldap_util.G_FAILURE;
412 RETURN;
413 END;
414 /** to do - what if there are multiple linked users ? **/
415 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
416 then
417 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'USER id:: '||l_user_id);
418 end if;
419 l_to_synch := CanSync(l_user_id,p_old_user_name);
420 IF (l_to_synch) THEN
421 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
422 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'SYNC user '||p_new_user_name);
423 END IF;
424 ldap := fnd_ldap_util.c_get_oid_session(flag);
425 IF FND_LDAP_UTIL.loadLdapRecord( ldap, user_rec.user_data, dn , p_user_guid, fnd_ldap_util.G_GUID_KEY) THEN
426 user_rec.dn :=dn;
427 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
428 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Updating dn:'||dn);
429 END IF;
430 ProcessLoadedLpadUserRecord(user_rec,NULL,dn);
431 nna := user_rec.NickName_ATT_NAME;
432
433 FND_SSO_REGISTRATION.is_operation_allowed (
434 p_direction => FND_LDAP_WRAPPER.G_EBIZ_TO_OID,
435 p_entity => FND_LDAP_WRAPPER.G_IDENTITY,
436 p_operation => FND_LDAP_WRAPPER.G_MODIFY,
437 p_attribute => nna,
438 x_fnd_user => x_fnd,
439 x_oid => x_oid,
440 p_user_name => user_rec.user_name,
441 p_realm_dn => user_rec.realmDN);
442
443 if (x_oid = FND_LDAP_WRAPPER.G_SUCCESS ) THEN
444 i := user_rec.user_data(nna).first;
445 found := false;
446 target.delete;
447 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
448 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Verifiying that Nickname atrribute does contain username in '||nna);
449 END IF;
450
451 while i is not null loop
452 if (user_rec.user_data(nna)(i)=p_old_user_name) THEN
453 found := true;
454 target(target.count) := p_new_user_name;
455 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
456 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'It does');
457 END IF;
458
459 ELSE
460 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
461 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'may need to keep '||user_rec.user_data(nna)(i));
462 END IF;
463
464 target(target.count) := user_rec.user_data(nna)(i);
465 END IF;
466 i:= user_rec.user_data(nna).next(i);
467 end loop;
468 IF found THEN
472 ma := dbms_ldap.create_mod_array(num=> 1);
469 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
470 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Updating LDAP ');
471 END IF;
473 dbms_ldap.populate_mod_array(modptr => ma,
474 mod_op => DBMS_LDAP.MOD_REPLACE,
475 mod_type => nna,
476 modval => target);
477 x_result:= dbms_ldap.modify_s(ldap,user_rec.dn, ma);
478 if (x_result = dbms_ldap.SUCCESS) then
479 x_result := fnd_ldap_util.G_SUCCESS;
480 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
481 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Updating succedd ');
482 END IF;
483 end if;
484 dbms_ldap.free_mod_array(modptr => ma);
485 ELSE
486 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
487 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Cannot update nickname attribute');
488 END IF;
489 END IF;
490 ELSE
491
492 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
493 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'NickName att does not contain username, no changes ');
494 END IF;
495 END IF;
496 ELSE
497 IF (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
498 fnd_log.string(fnd_log.LEVEL_UNEXPECTED , l_module_source, 'Cannot locate user_name[new]='||p_new_user_name||' guid='|| p_user_guid||':'||sqlerrm);
499 END IF;
500 raise no_such_user_exp;
501 END IF;
502 fnd_ldap_util.c_unbind(ldap,flag);
503 ELSE
504 x_result := fnd_ldap_util.G_SUCCESS;
505 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
506 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'User is a local user or Synch profile is disabled.');
507 END IF;
508 END IF;
509
510
511
512 IF (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
513 IF (x_result = fnd_ldap_util.G_SUCCESS ) THEN
514 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End-> fnd_ldap_util.G_SUCCESS ');
515 ELSE
516 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End-> fnd_ldap_util.G_FAIL ');
517 END IF;
518 END IF;
519
520
521
522 exception
523 when invalid_new_user_exp then
524 fnd_ldap_util.c_unbind(ldap,flag);
525 fnd_message.set_name ('FND', 'FND_SSO_INVALID_NEW_USER_NAME');
526 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
527 then
528 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
529 end if;
530 x_result := fnd_ldap_util.G_FAILURE;
531 when no_such_user_exp then
532 fnd_ldap_util.c_unbind(ldap,flag);
533 fnd_message.set_name ('FND', 'FND_SSO_USER_NOT_FOUND');
534 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
535 then
536 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
537 end if;
538 x_result := fnd_ldap_util.G_FAILURE;
539 when others then
540 fnd_message.set_name ('FND', 'FND_SSO_UNEXP_ERROR');
541 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
542 then
543 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
544 end if;
545 x_result := fnd_ldap_util.G_FAILURE;
546 end change_user_name;
547 --
548 -------------------------------------------------------------------------------
549 --
550 -- 1) Fills an usertype fnd_ldap_util.ldap_user_type and call create_user(fnd_ldap_util.ldap_user_type)
551 -- if it returns failure put a FND_SSO_USER_EXIST on the errors tack
552 --
553 -- 2) Retreive its guid
554 -- 3) Loook at the APPS_SSO_LOCAL_LOGIN ( user=-1 level, which may or may not be site)
555 -- If its SSO set the FND_USER password to external
556 -- Any EXCEPTION will be logged and passed up
557
558
559 FUNCTION create_ldap_user (
560 p_ldap_session IN dbms_ldap.session,
561 p_ldap_user IN OUT nocopy ldap_user_type)
562 RETURN pls_integer
563 IS
564 l_module_source VARCHAR2(256);
565 retval pls_integer;
566 ldap_result pls_integer;
567 modArray dbms_ldap.mod_array;
568 atName VARCHAR2(4000);
569 atVal VARCHAR2(4000);
570 handler pls_integer;
571 myid INTEGER;
572 l_dn VARCHAR2(4000);
573 list1 dbms_ldap.string_collection;
574 n pls_integer;
575 i pls_integer;
576 some_data boolean := false;
577 BEGIN
578 l_module_source := G_MODULE_SOURCE || 'create_ldap_user: ';
579 retval := fnd_ldap_util.G_FAILURE;
580
581 IF (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
582 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
583
584 -- LOG THE ATTEMPTED CHANGES
585 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
586 BEGIN
587 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'username:'||p_ldap_user.user_name||' DN :'||l_dn);
588 myid:= sys_context('USERENV', 'SESSIONID');
589 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Attempt to create LDAP user ['||p_ldap_user.user_name||'] ['||myid||']');
590 IF firstValue(p_ldap_user, atname, atval, handler) THEN
591 WHILE (atName IS NOT NULL)
592 LOOP
593 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, '['||myid||'] '||atName||':'||atVal);
594 IF (NOT NextValue(p_ldap_user,atName,atVal,handler) )THEN
598 END LOOP;
595 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, '['||myid||'] END ');
596 atName:=NULL;
597 END IF;
599 END IF;
600 EXCEPTION WHEN OTHERS THEN
601 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Error during log operation '||sqlerrm);
602 END;
603 END IF;
604 END IF;
605
606 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
607 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Populate modarray : count='|| p_ldap_user.user_data.count);
608 END IF;
609
610 -- Now we need to figure out the DN and the realm
611 FND_OID_PLUG.completeforcreate(p_ldap_session,p_ldap_user);
612
613
614 FND_OID_PLUG.fixupLDAPUser(p_ldap_user,FND_OID_PLUG.G_CREATE_USER);
615
616 modArray := dbms_ldap.create_mod_array(num=> p_ldap_user.user_data.count);
617 atName := p_ldap_user.user_data.first ;
618
619 WHILE atName IS NOT NULL
620 LOOP
621 -- Login current data
622 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
623 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' adding '|| atName);
624 FOR i IN p_ldap_user.user_data(atName).first .. p_ldap_user.user_data(atName).last
625 LOOP
626 IF (p_ldap_user.user_data(atName).exists(i) ) THEN
627 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' '||p_ldap_user.user_data(atName)(i) );
628 ELSE
629 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' missing element '||i );
630 END IF;
631 END LOOP;
632 END IF;
633
634 list1.delete();
635 n:=0;
636 i:=p_ldap_user.user_data(atName).first;
637
638 if lower(atName)='objectclass' or
639 CanPopulate(atName,p_ldap_user.user_name,p_ldap_user.realmDN) THEN
640 LOOP
641 list1(n):= p_ldap_user.user_data(atName)(i);
642 n := n+1;
643 i := p_ldap_user.user_data(atName).next(i);
644 EXIT WHEN i IS NULL;
645 END LOOP;
646 dbms_ldap.populate_mod_array(modptr => modArray, mod_op => DBMS_LDAP.MOD_ADD, mod_type => atName, modval => list1);
647 some_data := true;
648 ELSE
649 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
650 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Populate modarray : count='|| p_ldap_user.user_data.count);
651 END IF;
652 END IF;
653 atName := p_ldap_user.user_data.next(atName);
654 END LOOP;
655
656 if (some_data) THEN
657 ldap_result := dbms_ldap.add_s(ld => p_ldap_session, entrydn => p_ldap_user.dn , modptr =>modArray);
658 ELSE
659 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
660 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'add_s no data to load');
661 END IF;
662 END IF;
663 dbms_ldap.free_mod_array(modArray);
664 IF ldap_result = dbms_ldap.SUCCESS THEN
665
666 retval := fnd_ldap_util.G_SUCCESS;
667 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
668 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'created user:' || p_ldap_user.user_name);
669 END IF;
670
671 -- get the guid
672 p_ldap_user.user_guid := FND_LDAP_UTIL.get_guid_for_dn(p_ldap_session,p_ldap_user.dn );
673 IF (p_ldap_user.user_guid IS NULL) THEN
674 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
675 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Unoticed falure to create created DN [' || p_ldap_user.dn||']');
676 END IF;
677 retval:= fnd_ldap_util.G_FAILURE;
678 ELSE
679 retval := fnd_ldap_util.G_SUCCESS;
680 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
681 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'new user:' || p_ldap_user.user_name || ' dn:' || p_ldap_user.dn );
682 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'new user:' || p_ldap_user.user_name || ' guid:' || p_ldap_user.user_guid );
683 END IF;
684 END IF;
685 ELSE
686 retval := fnd_ldap_util.G_FAILURE;
687 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
688 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Falied to create ['||p_ldap_user.dn||'] user:'||p_ldap_user.user_name);
689 END IF;
690
691 END IF;
692 IF (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
693 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END->'||retval);
694 END IF;
695 RETURN retval;
696
697
698 exception
699 when others then
700 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
701 then
702 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
703 end if;
704 raise;
705
706 END create_ldap_user;
707 --
708 -------------------------------------------------------------------------------
709 /*
710 get_ACCOUNT_dn: returns where to store subscription information at OiD.
711 Now is simple, in the future may change for multiples realms.
712 */
713 function get_ACCOUNT_dn(p_guid in raw) return varchar2 is
714 begin
715 return 'cn=ACCOUNTS,cn=subscription_data,cn=subscriptions,' || fnd_ldap_util.get_orclappname;
716 end get_ACCOUNT_dn;
717 --
718 -------------------------------------------------------------------------------
719 function create_user_subscription(ldapSession in dbms_ldap.session, p_user_dn in varchar2 , p_guid in raw)
720 return pls_integer is
721
725 --userDN varchar2(4000):= p_user_dn;
722 l_module_source varchar2(256);
723 subsNode varchar2(4000);
724 acctNode varchar2(4000);
726 result pls_integer;
727 retval pls_integer;
728 --ldapSession dbms_ldap.session;
729 modArray dbms_ldap.mod_array;
730 modmultivalues dbms_ldap.string_collection;
731 i number;
732 flag pls_integer;
733 err varchar2(1000); --bug 8618800
734 begin
735 l_module_source := G_MODULE_SOURCE || 'create_user_subscription: ';
736 -- set default value to failure. change to success when user created successfully
737 retval := fnd_ldap_util.G_FAILURE;
738 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
739 then
740 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin guid='||p_guid);
741 end if;
742
743 -- ldapSession := fnd_ldap_util.c_get_oid_session(flag);
744
745 -- userDN := fnd_ldap_util.get_dn_for_guid(p_guid);
746
747 acctNode := get_ACCOUNT_dn(p_guid);
748 -- num_attributes := process_attributes(p_ldap_user, x_atts => l_atts,
749 -- x_att_values => l_att_values);
750
751 modArray := dbms_ldap.create_mod_array(num => 2);
752
753 modmultivalues(0) := 'orclServiceSubscriptionDetail';
754 dbms_ldap.populate_mod_array(modptr => modArray, mod_op => dbms_ldap.mod_add, mod_type => 'objectclass', modval => modmultivalues);
755
756 modmultivalues(0) := p_user_dn;
757 dbms_ldap.populate_mod_array(modptr => modArray, mod_op => dbms_ldap.mod_add, mod_type => 'seeAlso', modval => modmultivalues);
758
759 subsNode := 'orclOwnerGUID=' || p_guid|| ',' || acctNode;
760 retval := dbms_ldap.add_s(ld => ldapSession, entrydn => subsNode, modptr => modArray);
761
762 if (retval = dbms_ldap.SUCCESS) then
763 --retval := add_uniquemember(p_ldap_user);
764 fnd_ldap_util.add_attribute_M(ldapSession,acctNode,'uniqueMember',p_user_dn);
765 retval:= fnd_ldap_util.G_SUCCESS;
766 else
767 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
768 then
769 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Failed! retval='||retval||' subsNode:'||subsNode);
770 end if;
771 end if;
772
773 dbms_ldap.free_mod_array(modptr => modArray);
774 --fnd_ldap_util.c_unbind(ldapSession,flag);
775
776 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
777 then
778 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
779 end if;
780
781 return retval;
782
783 exception
784 when others then
785 err := sqlerrm; --bug 8618800
786
787 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
788 then
789 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
790 end if;
791 -- Bug 8618800 if already exists continue
792 if (instr(err,'Already exists. Object already exists') > 0) then
793 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
794 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'User already subscribed');
795 end if;
796 retval := fnd_ldap_util.G_SUCCESS;
797 return retval;
798 else
799 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
800 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Failed! retval='||retval||' subsNode:'||subsNode);
801 end if;
802 raise;
803 end if;
804
805
806 end create_user_subscription;
807 --
808 -------------------------------------------------------------------------------
809 procedure decode_dates(p_user_name in varchar2, p_start_date in date, p_end_date in date, x_orclisEnabled out nocopy varchar2, x_user_id out nocopy fnd_user.user_id%type) is
810
811 -- bug 12925276 : to_date(null) is returned instead of null to indicate that current return type is date and hence make decode() function correctly.
812 -- Also see base bug 13654885 and bug 1124610
813 cursor fnd_dates is
814 select user_id, decode(p_start_date, fnd_user_pkg.null_date, to_date(null),
815 null, start_date,
816 p_start_date) l_start_date,
817 decode(p_end_date, fnd_user_pkg.null_date, to_date(null),
818 null, end_date,
819 p_end_date) l_end_date
820 from fnd_user
821 where user_name = p_user_name;
822
823 l_rec fnd_dates%rowtype;
824 l_found boolean;
825 l_user_id fnd_user.user_id%type;
826 l_start_date date;
827 l_end_date date;
828 l_module_source varchar2(256);
829 no_such_user_exp exception;
830
831 begin
832 l_module_source := G_MODULE_SOURCE || 'decode_dates: ';
833 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
834 then
835 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
836 end if;
837
838 open fnd_dates;
839 fetch fnd_dates into l_rec;
840 l_found := fnd_dates%found;
841 close fnd_dates;
842
843 if (not l_found)
844 then
845 raise no_such_user_exp;
846 end if;
847
848
849 -- Fetching the user_id also in order to fetch user level profiles after call to this procedure.
850 x_user_id := l_rec.user_id;
851
852 if ((l_rec.l_start_date is not null and l_rec.l_start_date > sysdate)
853 or
854 (l_rec.l_end_date is not null and l_rec.l_end_date <= sysdate))
855 then
856 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
857 then
858 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'User is disabled');
859 end if;
860 x_orclisEnabled := fnd_oid_util.G_DISABLED;
861
862 else
866 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
863 x_orclisEnabled := fnd_oid_util.G_ENABLED;
864 end if;
865
867 then
868 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
869 end if;
870
871 exception
872 when others then
873 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
874 then
875 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
876 end if;
877 raise_application_error(-20001, 'FND_SSO_USER_NOT_FOUND');
878
879 end decode_dates;
880 --
881 -------------------------------------------------------------------------------
882 --
883 --Added by scheruku for Nickname changes
884 -------------------------------------------------------------------------------
885 function delete_user_nodes(p_ldap_session in dbms_ldap.session,
886 p_orclguid in fnd_user.user_guid%type) return pls_integer is
887
888 l_module_source varchar2(256);
889 usersNode varchar2(1000);
890 usersNodes dbms_ldap.string_collection;
891 l_result pls_integer;
892
893 begin
894 l_module_source := G_MODULE_SOURCE || 'delete_user_nodes: ';
895 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
896 then
897 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
898 end if;
899
900
901
902 usersNode := fnd_ldap_util.get_dn_for_guid(p_orclguid => p_orclguid);
903
904 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
905 then
906 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'DN for user with GUID::'
907 ||p_orclguid||' DN::'||usersNode);
908 end if;
909
910 l_result := dbms_ldap.delete_s(ld => p_ldap_session, entrydn => usersNode);
911
912 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
913 then
914 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
915 end if;
916 return l_result;
917
918 EXCEPTION WHEN OTHERS THEN
919 IF (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
920 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
921 END IF;
922 raise;
923
924 end delete_user_nodes;
925 --
926 ------------------------------------------------------------------------------
927 function delete_user_subscription(p_ldap_session in dbms_ldap.session, guid in raw)
928 return pls_integer is
929
930 l_module_source varchar2(256);
931 subsNode varchar2(1000);
932 l_user_guid raw(256) := guid;
933 l_attrs dbms_ldap.string_collection;
934 l_message dbms_ldap.message := null;
935 l_result pls_integer;
936 l_entry dbms_ldap.message := null;
937
938 begin
939 l_module_source := G_MODULE_SOURCE || 'delete_user_subscription ';
940 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
941 then
942 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
943 end if;
944
945 -- delete subcriptions with orclOwnerGUID
946 subsNode := 'cn=ACCOUNTS,cn=subscription_data,cn=subscriptions,' || fnd_ldap_util.get_orclappname;
947 l_result := dbms_ldap.search_s(ld => p_ldap_session, base => subsNode,
948 scope => dbms_ldap.SCOPE_SUBTREE, filter => 'orclOwnerGUID=' || l_user_guid, attrs => l_attrs, attronly => 0, res => l_message);
949 if (l_result is not NULL) then
950 l_entry := dbms_ldap.first_entry(p_ldap_session, l_message);
951 if l_entry is not null then
952 l_result := dbms_ldap.delete_s(ld => p_ldap_session, entrydn => 'orclOwnerGUID=' || l_user_guid||','||subsNode);
953 end if;
954 end if;
955
956 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
957 then
958 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
959 end if;
960 return l_result;
961
962 EXCEPTION WHEN OTHERS THEN
963 IF (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
964 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
965 END IF;
966 raise;
967
968 end delete_user_subscription;
969 --
970 -------------------------------------------------------------------------------
971 function delete_uniquemember(p_ldap_session in dbms_ldap.session,
972 p_orclguid in fnd_user.user_guid%type) return pls_integer is
973
974 l_module_source varchar2(256);
975 subsNode varchar2(1000);
976 usersNode varchar2(1000);
977 usersNodes dbms_ldap.string_collection;
978 result pls_integer;
979 retval pls_integer;
980 modArray dbms_ldap.mod_array;
981 modmultivalues dbms_ldap.string_collection;
982 i number;
983
984 begin
985 l_module_source := G_MODULE_SOURCE || 'delete_uniquemember: ';
986 -- set default value to failure. change to success when added successfully
987 retval := fnd_ldap_util.G_FAILURE;
988 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
989 then
990 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
991 end if;
992
993 subsNode := 'cn=ACCOUNTS,cn=subscription_data,cn=subscriptions,' || fnd_ldap_util.get_orclappname;
994
995 modArray := dbms_ldap.create_mod_array(num => 1);
996
997 modmultivalues(0) := fnd_ldap_util.get_dn_for_guid(p_orclguid);
998
999 dbms_ldap.populate_mod_array(modptr => modArray, mod_op => dbms_ldap.mod_delete, mod_type => 'uniquemember', modval => modmultivalues);
1000
1001 retval := dbms_ldap.modify_s(ld => p_ldap_Session, entrydn => subsNode, modptr => modArray);
1002
1003
1004 if (retval = dbms_ldap.SUCCESS) then
1005 retval := fnd_ldap_util.G_SUCCESS;
1006 end if;
1007
1008 dbms_ldap.free_mod_array(modptr => modArray);
1009
1013 end if;
1010 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1011 then
1012 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1014
1015 return retval;
1016
1017 exception
1018 when others then
1019 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1020 then
1021 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
1022 end if;
1023 raise;
1024
1025 end delete_uniquemember;
1026 --
1027 -------------------------------------------------------------------------------
1028 procedure disable_user(p_user_guid in raw,
1029 p_user_name in varchar2,
1030 x_result out nocopy pls_integer) is
1031
1032 usertype fnd_ldap_util.ldap_user_type;
1033 l_module_source varchar2(256);
1034 no_such_user_exp exception;
1035 l_user_id fnd_user.user_id%type;
1036 l_local_login varchar2(30);
1037 l_allow_sync varchar2(1);
1038 l_profile_defined boolean;
1039 l_to_synch boolean;
1040 x_password pls_integer;
1041
1042 PRAGMA EXCEPTION_INIT (no_such_user_exp, -20001);
1043
1044 begin
1045 l_module_source := G_MODULE_SOURCE || 'disable_user: ';
1046
1047 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1048 then
1049 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1050 end if;
1051 -- there is no need to do something more specific
1052 -- Note that this only update sOID with DISABLE
1053 update_user(p_user_guid =>p_user_guid,p_user_name =>p_user_name,
1054 p_end_date =>sysdate-100,
1055 p_expire_password => 0,x_password => x_password,x_result => x_result , x_user_creation=>FALSE);
1056
1057 if x_result <> fnd_ldap_util.G_SUCCESS then
1058 raise no_such_user_exp;
1059 end if;
1060 exception
1061 when no_such_user_exp then
1062 fnd_message.set_name ('FND', 'FND_SSO_USER_NOT_FOUND');
1063 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1064 then
1065 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
1066 end if;
1067 x_result := fnd_ldap_util.G_FAILURE;
1068 when others then
1069 fnd_message.set_name ('FND', 'FND_SSO_UNEXP_ERROR');
1070 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1071 then
1072 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
1073 end if;
1074 x_result := fnd_ldap_util.G_FAILURE;
1075
1076 end disable_user;
1077 --
1078 --
1079 -- Return the first GUID found for this username
1080 -- also return in n the number of entryes found
1081 --
1082 --
1083
1084 FUNCTION get_user_guid_and_count
1085 (
1086 p_user_name IN VARCHAR2,
1087 n OUT nocopy pls_integer)
1088 RETURN VARCHAR2
1089 IS
1090 l_module_source VARCHAR2(256);
1091 orclguid VARCHAR2(1000);
1092 BEGIN
1093 l_module_source := G_MODULE_SOURCE || 'get_user_guid_and_count: ';
1094 IF (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
1095 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin p_username='||p_user_name);
1096 END IF;
1097 orclguid := get_user_guid(p_user_name);
1098 IF orclguid IS NULL THEN
1099 n :=0;
1100 ELSE
1101 n:=1;
1102 END IF;
1103 IF (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
1104 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, ' END orclguid='||orclguid||' n='||n);
1105 END IF;
1106 RETURN orclguid;
1107 EXCEPTION
1108 WHEN OTHERS THEN
1109 IF (fnd_log.LEVEL_EXCEPTION>= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
1110 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'EXCEPTION:'||sqlerrm);
1111 END IF;
1112 raise;
1113 END get_user_guid_and_count;
1114
1115 -----------------------------------------
1116 --
1117 -------------------------------------------------------------------------------
1118 function get_user_guid(p_ldap_session in dbms_ldap.session, p_user_name in varchar2, dn out nocopy varchar2)
1119 return raw is
1120
1121 l_module_source varchar2(256);
1122 result pls_integer;
1123 l_user_guid raw(256);
1124 l_message dbms_ldap.message := null;
1125 l_entry dbms_ldap.message := null;
1126 l_attrs dbms_ldap.string_collection;
1127 searchBase varchar2(1000);
1128 searchFilter varchar2(1000);
1129 orclguid varchar2(1000);
1130 ldapSession dbms_ldap.session;
1131 dummy dbms_ldap.session;
1132
1133 realmList dbms_ldap.string_collection;
1134 ridx pls_integer;
1135 sbase dbms_ldap.string_collection;
1136 begin
1137 l_module_source := G_MODULE_SOURCE || 'get_user_guid: ';
1138 -- retval := fnd_ldap_util.G_FAILURE;
1139 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1140 then
1141 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1142 end if;
1143
1144 ldapSession := p_ldap_session;
1145 l_attrs(0) := 'orclguid';
1146 realmList := fnd_oid_plug.getRealmList();
1147 for r in realmList.first .. realmList.last loop
1148 if (orclguid is null ) THEN
1149 ridx := fnd_sso_registration.find_realm_index(realmList(r));
1150 sbase := fnd_sso_registration.getrealmsearchbaselist(ridx);
1151 searchFilter := fnd_sso_registration.get_realm_attribute(ridx,'orclcommonnicknameattribute')
1152 ||'='||p_user_name ;
1153
1154 for s in sbase.first .. sbase.last loop
1155 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1156 then
1157 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'realm:'||r||' base:'||sbase(s)||' filter:'||searchFilter);
1158 end if;
1162 filter => searchFilter,
1159 result := dbms_ldap.search_s(ld => ldapSession,
1160 base => sbase(s),
1161 scope => dbms_ldap.SCOPE_SUBTREE,
1163 attrs => l_attrs, attronly => 0,
1164 res => l_message);
1165 l_entry := dbms_ldap.first_entry(ldapSession, l_message);
1166 if (l_entry is not null) then
1167 l_attrs := dbms_ldap.get_values(ldapSession, l_entry, 'orclguid');
1168 dn := dbms_ldap.get_dn(ldapSession,l_entry);
1169 orclguid := l_attrs(0);
1170 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1171 then
1172 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'FOUND under base='||sbase(s)||
1173 ' dn:'|| dn ||' guid='||orclguid);
1174 end if;
1175 END IF;
1176 end loop;
1177 END IF;
1178 end loop;
1179
1180
1181 l_user_guid := orclguid;
1182
1183 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1184 then
1185 if (l_user_guid is not null) then
1186 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'GUID found = ' || l_user_guid);
1187 ELSE
1188 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'User '||p_user_name||' not found');
1189 END IF;
1190 end if;
1191
1192 --result := fnd_ldap_util.unbind(ldapSession);
1193
1194 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1195 then
1196 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1197 end if;
1198 return l_user_guid;
1199
1200 exception
1201 when others then
1202 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1203 then
1204 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
1205 -- print stack just for 7306960
1206 --result:= -99;
1207 --dummy := fnd_ldap_util.c_get_oid_session(result);
1208 end if;
1209 raise;
1210
1211 end get_user_guid;
1212
1213 function get_user_guid(p_ldap_session in dbms_ldap.session, p_user_name in varchar2)
1214 return raw is
1215 dn varchar2(4000);
1216 begin
1217 return get_user_guid(p_ldap_session,p_user_name,dn);
1218 end;
1219
1220 function get_user_guid( p_user_name in varchar2)
1221 return raw is
1222 l_ldap dbms_ldap.session;
1223 ret fnd_user.user_guid%type;
1224 dummy pls_integer;
1225 BEGIN
1226 l_ldap := fnd_ldap_util.c_get_oid_session(dummy);
1227 ret := get_user_guid(l_ldap,p_user_name);
1228 fnd_ldap_util.C_unbind(l_ldap,dummy);
1229 return ret;
1230 EXCEPTION
1231 WHEN OTHERS THEN
1232 fnd_ldap_util.c_unbind(l_ldap,dummy);
1233 IF (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
1234 fnd_log.string(fnd_log.LEVEL_EXCEPTION, G_MODULE_SOURCE || 'get_user_guid: ', sqlerrm);
1235 END IF;
1236 raise;
1237 END get_user_guid;
1238 --
1239 ----------------------------------------
1240
1241
1242
1243 --
1244 -------------------------------------------------------------------------------
1245
1246 --
1247 -------------------------------------------------------------------------------
1248 procedure link_user(p_user_name in varchar2,
1249 x_user_guid out nocopy raw,
1250 x_password out nocopy varchar2,
1251 x_result out nocopy pls_integer) is
1252
1253 l_module_source varchar2(256);
1254 l_user_exists pls_integer;
1255 l_result pls_integer;
1256 l_orclguid fnd_user.user_guid%type;
1257 l_local_login varchar2(100);
1258 l_profile_defined boolean;
1259 l_nickname varchar2(256);
1260
1261 begin
1262 l_module_source := G_MODULE_SOURCE || 'link_user: ';
1263 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1264 then
1265 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1266 end if;
1267
1268
1269 l_orclguid := get_user_guid(p_user_name);
1270 -- only proceed if user exists
1271
1272 if (l_orclguid is not null ) then
1273
1274
1275 fnd_oid_util.add_user_to_OID_sub_list(p_orclguid => l_orclguid, x_result => l_result);
1276
1277 x_result := l_result;
1278
1279 if (l_result = fnd_ldap_util.G_SUCCESS) then
1280 x_user_guid := l_orclguid;
1281
1282 fnd_profile.get_specific(
1283 name_z => 'APPS_SSO_LOCAL_LOGIN',
1284 user_id_z => -1,
1285 val_z => l_local_login,
1286 defined_z => l_profile_defined);
1287
1288 if (l_local_login = 'SSO') then
1289 x_password := fnd_web_sec.EXTERNAL_PWD;
1290 end if;
1291
1292 end if;
1293
1294 -- user does not exist in OID
1295 else
1296 fnd_message.set_name('FND', 'FND_SSO_USER_NOT_FOUND');
1297 x_result := fnd_ldap_util.G_FAILURE;
1298 end if;
1299
1300 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1301 then
1302 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1303 end if;
1304
1305 exception
1306 when others then
1307 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1308 then
1309 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
1310 end if;
1311 raise;
1312
1313 end link_user;
1314 --
1315 -------------------------------------------------------------------------------
1316 function process_attributes(p_ldap_user in fnd_ldap_util.ldap_user_type,
1317 p_operation_type in pls_integer default G_CREATE,
1321
1318 x_atts out nocopy dbms_ldap.string_collection,
1319 x_att_values out nocopy dbms_ldap.string_collection)
1320 return number is
1322 l_module_source varchar2(256);
1323 num_attributes number;
1324
1325 begin
1326 l_module_source := G_MODULE_SOURCE || 'process_attributes: ';
1327 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1328 then
1329 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1330 end if;
1331
1332 num_attributes := 0;
1333
1334 if (p_ldap_user.sn is not null) then
1335 x_atts(num_attributes) := 'sn';
1336 x_att_values(num_attributes) := p_ldap_user.sn;
1337 num_attributes := num_attributes + 1;
1338 end if;
1339 if (p_ldap_user.cn is not null) then
1340 x_atts(num_attributes) := 'cn';
1341 x_att_values(num_attributes) := p_ldap_user.cn;
1342 num_attributes := num_attributes + 1;
1343 end if;
1344 if (p_ldap_user.uid is not null) then
1345 x_atts(num_attributes) := 'uid';
1346 x_att_values(num_attributes) := p_ldap_user.uid;
1347 num_attributes := num_attributes + 1;
1348 end if;
1349 if (p_ldap_user.userPassword is not null) then
1350 x_atts(num_attributes) := 'userPassword';
1351 x_att_values(num_attributes) := p_ldap_user.userPassword;
1352 num_attributes := num_attributes + 1;
1353 end if;
1354 if (p_ldap_user.telephoneNumber is not null) then
1355 x_atts(num_attributes) := 'telephoneNumber';
1356 x_att_values(num_attributes) := p_ldap_user.telephoneNumber;
1357 num_attributes := num_attributes + 1;
1358 end if;
1359 if (p_ldap_user.street is not null) then
1360 x_atts(num_attributes) := 'street';
1361 x_att_values(num_attributes) := p_ldap_user.street;
1362 num_attributes := num_attributes + 1;
1363 end if;
1364 if (p_ldap_user.postalCode is not null) then
1365 x_atts(num_attributes) := 'postalCode';
1366 x_att_values(num_attributes) := p_ldap_user.postalCode;
1367 num_attributes := num_attributes + 1;
1368 end if;
1369 if (p_ldap_user.physicalDeliveryOfficeName is not null) then
1370 x_atts(num_attributes) := 'physicalDeliveryOfficeName';
1371 x_att_values(num_attributes) := p_ldap_user.physicalDeliveryOfficeName;
1372 num_attributes := num_attributes + 1;
1373 end if;
1374 if (p_ldap_user.st is not null) then
1375 x_atts(num_attributes) := 'st';
1376 x_att_values(num_attributes) := p_ldap_user.st;
1377 num_attributes := num_attributes + 1;
1378 end if;
1379 if (p_ldap_user.l is not null) then
1380 x_atts(num_attributes) := 'l';
1381 x_att_values(num_attributes) := p_ldap_user.l;
1382 num_attributes := num_attributes + 1;
1383 end if;
1384 if (p_ldap_user.displayName is not null) then
1385 x_atts(num_attributes) := 'displayName';
1386 x_att_values(num_attributes) := p_ldap_user.displayName;
1387 num_attributes := num_attributes + 1;
1388 end if;
1389 if (p_ldap_user.givenName is not null) then
1390 x_atts(num_attributes) := 'givenName';
1391 x_att_values(num_attributes) := p_ldap_user.givenName;
1392 num_attributes := num_attributes + 1;
1393 end if;
1394 if (p_ldap_user.homePhone is not null) then
1395 x_atts(num_attributes) := 'homePhone';
1396 x_att_values(num_attributes) := p_ldap_user.homePhone;
1397 num_attributes := num_attributes + 1;
1398 end if;
1399 if (p_ldap_user.mail is not null) then
1400 x_atts(num_attributes) := 'mail';
1401 x_att_values(num_attributes) := p_ldap_user.mail;
1402 num_attributes := num_attributes + 1;
1403 end if;
1404 if (p_ldap_user.c is not null) then
1405 x_atts(num_attributes) := 'c';
1406 x_att_values(num_attributes) := p_ldap_user.c;
1407 num_attributes := num_attributes + 1;
1408 end if;
1409 if (p_ldap_user.facsimileTelephoneNumber is not null) then
1410 x_atts(num_attributes) := 'facsimileTelephoneNumber';
1411 x_att_values(num_attributes) := p_ldap_user.facsimileTelephoneNumber;
1412 num_attributes := num_attributes + 1;
1413 end if;
1414 if (p_ldap_user.description is not null) then
1415 x_atts(num_attributes) := 'description';
1416 x_att_values(num_attributes) := p_ldap_user.description;
1417 num_attributes := num_attributes + 1;
1418 end if;
1419 if (p_ldap_user.orclisEnabled is not null) then
1420 x_atts(num_attributes) := 'orclisEnabled';
1421 x_att_values(num_attributes) := p_ldap_user.orclisEnabled;
1422 num_attributes := num_attributes + 1;
1423 end if;
1424 if (p_ldap_user.orclActiveStartDate is not null) then
1425 x_atts(num_attributes) := 'orclActiveStartDate';
1426 x_att_values(num_attributes) := p_ldap_user.orclActiveStartDate;
1427 num_attributes := num_attributes + 1;
1428 end if;
1429 if (p_ldap_user.orclActiveEndDate is not null) then
1430 x_atts(num_attributes) := 'orclActiveEndDate';
1431 x_att_values(num_attributes) := p_ldap_user.orclActiveEndDate;
1432 num_attributes := num_attributes + 1;
1433 end if;
1434
1435 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1436 then
1437 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1438 end if;
1439
1440 return num_attributes;
1441
1442 exception
1443 when others then
1444 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1445 then
1446 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
1447 end if;
1448 raise;
1449
1450 end process_attributes;
1451 --
1452 -------------------------------------------------------------------------------
1453 procedure unlink_user(p_user_guid in fnd_user.user_guid%type,
1454 p_user_name in varchar2,
1455 x_result out nocopy pls_integer) is
1459 from fnd_user
1456
1457 cursor linked_users is
1458 select user_name
1460 where user_guid = p_user_guid
1461 and user_name <> p_user_name;
1462
1463 l_rec linked_users%rowtype;
1464 l_found boolean;
1465 l_module_source varchar2(256);
1466 l_local_login varchar2(100);
1467 l_profile_defined boolean;
1468 l_ldap_session dbms_ldap.session :=null;
1469 l_user_exists pls_integer;
1470 dn varchar2(2000);
1471 dummy pls_integer;
1472
1473 begin
1474 l_module_source := G_MODULE_SOURCE || 'unlink_user: ';
1475 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1476 then
1477 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1478 end if;
1479
1480 open linked_users;
1481 fetch linked_users into l_rec;
1482 l_found := linked_users%found;
1483 close linked_users;
1484
1485 -- no other user linked
1486 if (not l_found)
1487 then
1488
1489 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1490 then
1491 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'No other FND users linked to this OID User');
1492 end if;
1493 l_user_exists := user_exists_by_guid( p_user_guid);
1494
1495 if (l_user_exists = fnd_ldap_util.G_SUCCESS) then
1496
1497 l_ldap_session := fnd_ldap_util.c_get_oid_session(dummy);
1498
1499 x_result := delete_user_subscription(l_ldap_session, p_user_guid);
1500 x_result := delete_uniquemember(l_ldap_session, p_user_guid);
1501
1502 -- user does not exist in OID
1503 else
1504 fnd_message.set_name('FND', 'FND_SSO_USER_NOT_FOUND');
1505 x_result := fnd_ldap_util.G_FAILURE;
1506 end if;
1507
1508 -- other users linked
1509 else
1510
1511 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1512 then
1513 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Other FND users linked to this OID User');
1514 end if;
1515
1516 x_result := fnd_ldap_util.G_FAILURE;
1517 fnd_message.set_name ('FND', 'FND_SSO_USER_MULT_LINKED');
1518
1519 end if;
1520 if ( l_ldap_session is not null) then
1521 fnd_ldap_util.c_unbind(l_ldap_session,dummy);
1522 end if ;
1523 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1524 then
1525 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1526 end if;
1527
1528 exception
1529 when others then
1530 fnd_ldap_util.c_unbind(l_ldap_session,dummy);
1531 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1532 then
1533 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
1534 end if;
1535 raise;
1536
1537 end unlink_user;
1538 --
1539 -------------------------------------------------------------------------------
1540
1541 -- INTERNAL SIGNATURE
1542 -- x_user_creation can only be true when is called from this package
1543 -- calling public signature only permits x_user_creation=false
1544 --
1545 procedure update_user(p_user_guid in raw,
1546 p_user_name in varchar2,
1547 p_password in varchar2 default null,
1548 p_start_date in date default null,
1549 p_end_date in date default null,
1550 p_description in varchar2 default null,
1551 p_email_address in varchar2 default null,
1552 p_fax in varchar2 default null,
1553 p_expire_password in pls_integer,
1554 x_password out nocopy varchar2,
1555 x_result out nocopy pls_integer,
1556 x_user_creation in boolean default FALSE ) is
1557
1558
1559 l_orclisEnabled varchar2(256);
1560 -- usertype fnd_ldap_util.ldap_user_type;
1561 ldap_user fnd_ldap_user.ldap_user_type;
1562 l_module_source varchar2(256);
1563 no_such_user_exp exception;
1564 l_nickname varchar2(256);
1565
1566 l_user_id fnd_user.user_id%type;
1567 l_local_login varchar2(30);
1568 l_allow_sync varchar2(1);
1569 l_profile_defined boolean;
1570 l_to_synch boolean;
1571
1572 l_guid FND_USER.user_guid%type:= p_user_guid;
1573 ldap dbms_ldap.session;
1574 flag pls_integer;
1575 dn varchar2(4000);
1576 realm varchar2(4000);
1577 upd update_list;
1578 i pls_integer;
1579 x_name_change pls_integer;
1580 ldap_result pls_integer;
1581 l_use_proxy pls_integer := 0;
1582 PRAGMA EXCEPTION_INIT (no_such_user_exp, -20001);
1583
1584 begin
1585
1586 l_module_source := G_MODULE_SOURCE || 'update_user[proc]: ';
1587
1588 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1589 then
1590 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
1591 end if;
1592
1593
1594 -- figure out the user_id
1595 BEGIN
1596 select user_id into l_user_id from fnd_user
1597 where user_name=p_user_name and user_guid=p_user_guid;
1598 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1599 then
1600 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'user_id:'||l_user_id);
1601 end if;
1602
1603 l_to_synch := CanSync(l_user_id,p_user_name);
1604
1605 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1606 then
1607 if (l_to_synch) then
1608 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' Can synch');
1609 else
1610 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' NOT synch username:'
1611 ||p_user_name||' userid:'||l_user_id||' userGuid:'||p_user_guid);
1612 end if;
1616 -- THIS IS UNEXPECTED !!
1613 END IF;
1614 EXCEPTION WHEN NO_DATA_FOUND THEN
1615 l_to_synch:= true;
1617 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1618 then
1619 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Sinc, No ebzlinked user found:'||p_user_name||' guid:'||p_user_guid);
1620 end if;
1621 END;
1622
1623
1624 if (l_to_synch) then
1625 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1626 then
1627 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' synch');
1628 end if;
1629
1630 l_use_proxy := 0;
1631
1632 IF (p_password is not null) THEN
1633
1634 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1635 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'Password is not null');
1636 end if;
1637
1638 if (p_expire_password is not null and p_expire_password <> fnd_ldap_util.G_TRUE) THEN
1639
1640 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1641 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'So far password will not be expired - proxy as user');
1642 end if;
1643
1644 l_use_proxy :=2;
1645 -- Bug 9271995
1646 -- During user_creation if password is not in IDENTITY_ADD
1647 -- expiration will be forced disregarding what was requested
1648 IF (x_user_creation AND not canPopulate('userpassword',ldap_user.user_name, ldap_user.realmDN) )THEN
1649 -- always expire the password
1650 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1651 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Forcing password expiration - not in IDENTITY_ADD');
1652 end if;
1653 l_use_proxy :=1;
1654 END IF;
1655 else
1656 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1657 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'Password should be expired - p_expire_password: '||to_char(p_expire_password));
1658 end if;
1659
1660 l_use_proxy :=1;
1661 end if;
1662 ELSE
1663 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
1664 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'Password is null');
1665 end if;
1666
1667 l_use_proxy := 1;
1668 END IF;
1669 if ( l_use_proxy=2) then
1670 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1671 then
1672 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Getting a proxied connection to avoid password forced change: NEW LDAP connection required');
1673 end if;
1674 fnd_ldap_util.proxy_as_user(p_orclguid => p_user_guid,x_ldap_session => ldap);
1675 else
1676 ldap := FND_LDAP_UTIL.c_get_oid_session(flag);
1677 l_use_proxy :=1;
1678 end if;
1679
1680 l_guid := p_user_guid;
1681 IF FND_LDAP_UTIL.loadLdapRecord( ldap , ldap_user.user_data,dn,l_guid,FND_LDAP_UTIL.G_GUID_KEY)
1682 THEN
1683 ldap_user.dn :=dn;
1684 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
1685 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Updating dn:'||dn);
1686 END IF;
1687 ProcessLoadedLpadUserRecord(ldap_user,NULL,dn);
1688
1689 --Bug 13329571
1690 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
1691 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Calling FIXUP:'||dn);
1692 END IF;
1693 FND_OID_PLUG.fixupLDAPUser(ldap_user,FND_OID_PLUG.G_UPDATE_USER);
1694
1695 -- Bug 9271995
1696 -- x_user_creation=TRUE we always update the password
1697 -- x_user_creation=FALSE we check IDENTITY_MODIFY provisioning profile
1698 --
1699 IF (p_password is not null and
1700 (
1701 x_user_creation -- we set the password anyway, although it will be expired
1702 OR canUpdate('userpassword',ldap_user.user_name, ldap_user.realmDN,x_user_creation)
1703 )
1704 ) THEN
1705 i:= upd.count;
1706 upd(i).att := 'userpassword';
1707 upd(i).val := p_password;
1708 upd(i).op := DBMS_LDAP.MOD_REPLACE;
1709 END IF;
1710 IF (p_description is not null and NOT isValueOf(ldap_user,'description',p_description)
1711 and canUpdate('description',ldap_user.user_name, ldap_user.realmDN,x_user_creation) ) THEN
1712 i:= upd.count;
1713 upd(i).att := 'description';
1714 upd(i).val := p_description;
1715 upd(i).op := DBMS_LDAP.MOD_REPLACE;
1716 END IF;
1717
1718 IF (p_email_address is not null and NOT isValueOf(ldap_user,'mail',p_email_address)
1719 and canUpdate('mail',ldap_user.user_name, ldap_user.realmDN,x_user_creation) ) THEN
1720 i:= upd.count;
1721 upd(i).att := 'mail';
1722 upd(i).val := p_email_address;
1723 upd(i).op := DBMS_LDAP.MOD_REPLACE;
1724 END IF;
1725 IF (p_fax is not null and NOT isValueOf(ldap_user,'facsimileTelephoneNumber',p_fax)
1726 and canUpdate('facsimileTelephoneNumber',ldap_user.user_name, ldap_user.realmDN,x_user_creation)
1727 ) THEN
1728 i:= upd.count;
1732 END IF;
1729 upd(i).att := 'facsimileTelephoneNumber';
1730 upd(i).val := p_fax;
1731 upd(i).op := DBMS_LDAP.MOD_REPLACE;
1733 decode_dates(p_user_name, p_start_date, p_end_date,
1734 x_orclisEnabled => l_orclisEnabled,
1735 x_user_id => l_user_id);
1736 IF (l_orclIsEnabled is not null and NOT isValueOf(ldap_user,'orclIsEnabled',l_orclIsEnabled)
1737 and canUpdate('orclisenabled',ldap_user.user_name, ldap_user.realmDN)
1738 and l_orclIsEnabled = fnd_oid_util.G_ENABLED) THEN
1739 i:= upd.count;
1740 upd(i).att := 'orclIsEnabled';
1741 upd(i).val := l_orclIsEnabled;
1742 upd(i).op := DBMS_LDAP.MOD_REPLACE;
1743 END IF;
1744 if (upd.count>0) THEN
1745 ProcessUpdateRec(ldap,ldap_user.dn,upd);
1746 END IF;
1747 x_result := fnd_ldap_util.G_SUCCESS;
1748 /*
1749 CANNOT CHANGE USERNAME
1750 Unless the old is known, and that is not possible because already was changed on FND_USER...
1751 IF (x_result=FND_LDAP_UTIl.G_SUCCESS AND p_user_name is not null and isValueOf(ldap_user,ldap_user.nickname_att_name, p_user_name) ) THEN
1752 change_user_name(p_user_guid,ldap_user.user_name,p_user_name,x_name_change);
1753 x_result :=x_name_change;
1754 END IF;
1755 */
1756 ELSE
1757 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1758 then
1759 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'USER '||ldap_user.user_name||' has an invalid guid:'||p_user_guid);
1760 end if;
1761 raise no_such_user_exp;
1762 END IF;
1763 if ( l_use_proxy=2 ) then
1764 ldap_result := fnd_ldap_util.unbind(ldap);
1765 elsif l_use_proxy=1 then
1766 FND_LDAP_UTIL.c_unbind(ldap,flag);
1767 end if;
1768 l_use_proxy:=0;
1769
1770 -- ldap:=null;
1771
1772 else
1773 x_result := fnd_ldap_util.G_SUCCESS;
1774 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1775 then
1776 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
1777 'User is a local user or synch is disabled for this user.');
1778 end if;
1779 end if;
1780
1781 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1782 then
1783 if ( x_result = fnd_ldap_util.G_SUCCESS) THEN
1784 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End->SUCCESS');
1785 ELSE
1786 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End->FAIL');
1787 END IF;
1788 end if;
1789
1790 if x_result <> fnd_ldap_util.G_SUCCESS then
1791 raise no_such_user_exp;
1792 else
1793 fnd_profile.get_specific(
1794 name_z => 'APPS_SSO_LOCAL_LOGIN',
1795 user_id_z => l_user_id,
1796 val_z => l_local_login,
1797 defined_z => l_profile_defined);
1798
1799 if (l_local_login = 'SSO') then
1800 x_password := fnd_web_sec.EXTERNAL_PWD;
1801 end if;
1802 end if;
1803
1804 exception
1805 when no_such_user_exp then
1806 if ( l_use_proxy=2 ) then
1807 ldap_result := fnd_ldap_util.unbind(ldap);
1808 elsif l_use_proxy=1 then
1809 FND_LDAP_UTIL.c_unbind(ldap,flag);
1810 end if;
1811 l_use_proxy:=0;
1812 fnd_message.set_name ('FND', 'FND_SSO_USER_NOT_FOUND');
1813 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1814 then
1815 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
1816 end if;
1817 x_result := fnd_ldap_util.G_FAILURE;
1818 when others then
1819 if ( l_use_proxy=2 ) then
1820 ldap_result := fnd_ldap_util.unbind(ldap);
1821 elsif l_use_proxy=1 then
1822 FND_LDAP_UTIL.c_unbind(ldap,flag);
1823 end if;
1824 l_use_proxy:=0;
1825
1826 fnd_message.set_name ('FND', 'FND_SSO_UNEXP_ERROR');
1827 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1828 then
1829 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
1830 end if;
1831 -- x_result := fnd_ldap_util.G_FAILURE;
1832 raise;
1833
1834 end update_user;
1835
1836 --
1837 -----------------------------------
1838 -- PUBLIC SIGNATURE
1839 --
1840 procedure update_user(p_user_guid in raw,
1841 p_user_name in varchar2,
1842 p_password in varchar2 default null,
1843 p_start_date in date default null,
1844 p_end_date in date default null,
1845 p_description in varchar2 default null,
1846 p_email_address in varchar2 default null,
1847 p_fax in varchar2 default null,
1848 p_expire_password in pls_integer,
1849 x_password out nocopy varchar2,
1850 x_result out nocopy pls_integer ) is
1851 BEGIN
1852 update_user(p_user_guid,p_user_name,p_password,p_start_date,p_end_date,p_description,p_email_address,p_fax,p_expire_password,x_password,x_result,FALSE);
1853 END update_user;
1854 --
1855 -------------------------------------------------------------------------------
1856 PROCEDURE ConverToNew(n in out nocopy fnd_ldap_user.ldap_user_type,
1857 o in out nocopy fnd_ldap_util.ldap_user_type )
1858 IS
1859 BEGIN
1860
1861 o.object_name := n.user_name;
1862
1863
1864
1868 o.userPassword :=getAttribute(n,'userPassword');
1865 o.uid :=getAttribute(n,'uid');
1866 o.sn :=getAttribute(n,'sn');
1867 o.cn :=getAttribute(n,'cn');
1869 o.telephoneNumber :=getAttribute(n,'telephoneNumber');
1870 o.street :=getAttribute(n,'street');
1871 o.postalCode :=getAttribute(n,'postalCode');
1872 o.physicalDeliveryOfficeName :=getAttribute(n,'physicalDeliveryOfficeName');
1873 o.st :=getAttribute(n,'st');
1874 o.l :=getAttribute(n,'l');
1875 o.displayName :=getAttribute(n,'displayName');
1876 o.givenName :=getAttribute(n,'givenName');
1877 o.homePhone :=getAttribute(n,'homePhone');
1878 o.mail :=getAttribute(n,'mail');
1879 o.c :=getAttribute(n,'c');
1880 o.facsimileTelephoneNumber :=getAttribute(n,'facsimileTelephoneNumber');
1881 o.description :=getAttribute(n,'description');
1882 o.orclisEnabled :=getAttribute(n,'orclisEnabled');
1883 o.orclActiveStartDate :=getAttribute(n,'orclActiveStartDate');
1884 o.orclActiveEndDate :=getAttribute(n,'orclActiveEndDate');
1885 o.orclGUID :=n.user_guid;
1886
1887 if o.uid is null then o.uid:=n.user_name; END IF;
1888 if o.sn is null then o.sn:=n.user_name; END IF;
1889 if o.cn is null then o.cn:=n.user_name; END IF;
1890
1891 END ConverToNew;
1892
1893 -------------------------------------------------------------------------------
1894 function update_user_nodes(p_ldap_session in dbms_ldap.session, p_mod_array in dbms_ldap.mod_array, p_orclguid in raw)
1895 return pls_integer is
1896
1897 l_module_source varchar2(256);
1898 usersNode varchar2(1000);
1899 retval pls_integer;
1900 l_message varchar2(200);
1901
1902 begin
1903 l_module_source := G_MODULE_SOURCE || 'update_user_nodes: ';
1904 -- set default value to failure. change to success when user created successfully
1905 retval := fnd_ldap_util.G_FAILURE;
1906 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1907 then
1908 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1909 end if;
1910
1911 usersNode := fnd_ldap_util.get_dn_for_guid(p_orclguid, p_ldap_session);
1912 -- dbms_ldap.use_exception := true;
1913
1914 retval := dbms_ldap.modify_s(ld => p_ldap_session, entrydn => usersNode, modptr => p_mod_array);
1915
1916 if (retval = dbms_ldap.SUCCESS) then
1917 retval := fnd_ldap_util.G_SUCCESS;
1918 end if;
1919
1920 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1921 then
1922 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
1923 end if;
1924 return retval;
1925
1926 exception
1927 -- bug 4573677
1928 when dbms_ldap.general_error then
1929 l_message := translate_ldap_error(sqlerrm);
1930 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1931 then
1932 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'error '||l_message||':'||sqlerrm);
1933 end if;
1934 fnd_message.set_name('FND',l_message);
1935 if (l_message='FND_SSO_PASSWORD_POLICY_ERR')
1936 then
1937 fnd_message.set_token('SQLMSG',sqlerrm);
1938 elsif (l_message='FND_SSO_UNEXP_ERROR') then
1939 fnd_message.set_token('SQLMSG',sqlerrm);
1940 end if;
1941 return fnd_ldap_util.G_FAILURE;
1942 when others then
1943 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1944 then
1945 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
1946 end if;
1947 raise;
1948
1949 end update_user_nodes;
1950
1951 function user_exists(p_user_name in varchar2)
1952 return pls_integer is
1953 ldap dbms_ldap.session;
1954 ret pls_integer;
1955 ret2 pls_integer;
1956 flag pls_integer;
1957 begin
1958 ldap := fnd_ldap_util.c_get_oid_session(flag);
1959 ret := user_exists(ldap,p_user_name);
1960 fnd_ldap_util.c_unbind(ldap,flag);
1961 return ret;
1962 EXCEPTION
1963 WHEN OTHERS THEN
1964 fnd_ldap_util.c_unbind(ldap,flag);
1965 IF (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
1966 fnd_log.string(fnd_log.LEVEL_EXCEPTION, G_MODULE_SOURCE || 'user_exists: ', sqlerrm);
1967 END IF;
1968 raise;
1969 end user_exists;
1970 --
1971 -------------------------------------------------------------------------------
1972 function user_exists(ldap in dbms_ldap.session,p_user_name in varchar2)
1973 return pls_integer is
1974
1975 l_module_source varchar2(256);
1976 --result pls_integer;
1977 retval pls_integer;
1978 --l_nickname varchar2(256);
1979
1980
1981 guid raw(16);
1982
1983 begin
1984 l_module_source := G_MODULE_SOURCE || 'user_exists: ';
1985 retval := fnd_ldap_util.G_FAILURE;
1986 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
1987 then
1988 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
1989 end if;
1990
1991 guid := get_user_guid(p_user_name);
1992 if (guid is not null ) then
1993 retval := fnd_ldap_util.G_SUCCESS;
1994 else
1995 retval :=fnd_ldap_util.G_FAILURE;
1996 end if;
1997
1998
1999 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2000 then
2001 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'retval=' || retval);
2002 end if;
2003
2004 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2005 then
2006 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
2007 end if;
2008
2009 return retval;
2010
2011 exception
2012 when others then
2013 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2014 then
2015 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
2016 end if;
2017 raise;
2021 -------------------------------------------------------------------------------
2018
2019 end user_exists;
2020 --
2022 -- REMOVED
2023 -- function user_exists_with_filter(p_attr_name in varchar2, p_attr_value in varchar2) return pls_integer is
2024
2025 --
2026 -------------------------------------------------------------------------------
2027 /**
2028 * FUNCTION comparePassword: Internal
2029 * Returns true if the password is the same as the stored at OiD for the given DN
2030 * If Not, or any exceptions occurs, returns false
2031 * It can be used repeteadly since this comparision does not count as failed attempts.
2032 * Parameters:
2033 * ldapSession: OiD connection to use
2034 * user_dn: user DN
2035 * p_password: password
2036 **/
2037
2038 function comparePassword(ldapSession in dbms_ldap.session, user_dn in varchar2 , p_password in varchar2) return boolean is
2039 l_result pls_integer;
2040 result boolean;
2041 l_module_source varchar2(256);
2042 begin
2043 l_module_source := G_MODULE_SOURCE || 'comparePassword: ';
2044 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2045 then
2046 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'BEGIN DN:'||user_dn);
2047 end if;
2048 l_result := dbms_ldap.compare_s(ld => ldapSession, dn => user_dn, attr => 'userpassword', value => p_password);
2049 result := l_result= dbms_ldap.COMPARE_TRUE;
2050 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2051 then
2052 if (result) then
2053 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END: Yes');
2054 else
2055 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END: NO');
2056
2057 end if;
2058 end if;
2059
2060 return result;
2061 exception when others then
2062 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2063 then
2064 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'Exception: '||sqlcode||' - '||sqlerrm);
2065 end if;
2066 return false;
2067
2068 end comparePassword;
2069 --
2070 -------------------------------------------------------------------------------
2071 function validate_login(p_user_name in varchar2, p_password in varchar2) return pls_integer is
2072
2073 l_module_source varchar2(256);
2074 l_host varchar2(256);
2075 l_port varchar2(256);
2076
2077 result pls_integer;
2078 retval pls_integer;
2079 l_user_guid raw(256);
2080 l_user_name fnd_user.user_name%type;
2081 l_enabled boolean;
2082 l_ldap_attr_list ldap_attr_list;
2083 ldapSession dbms_ldap.session;
2084 l_retval pls_integer;
2085 user_dn varchar2(4000);
2086 l_ldap_auth varchar2(256);
2087 l_db_wlt_url varchar2(256);
2088 l_db_wlt_pwd varchar2(256);
2089 l_message varchar2(2000);
2090
2091 begin
2092 l_module_source := G_MODULE_SOURCE || 'validate_login: ';
2093
2094 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2095 then
2096 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
2097 end if;
2098
2099
2100 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2101 then
2102 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Username: '||p_user_name);
2103 end if;
2104
2105 if (p_user_name is null or p_password is null ) then
2106 fnd_message.set_name('FND','FND_SSO_USER_PASSWD_EMPTY');
2107 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2108 then
2109 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, 'END: refusing to validate empty username and/or password');
2110 end if;
2111 return fnd_ldap_util.G_FAILURE;
2112 end if;
2113
2114 -- Find the DN of the linked guid
2115 begin
2116 select user_guid into l_user_guid from fnd_user where user_name=p_user_name;
2117 if (l_user_guid is null ) then
2118 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2119 then
2120 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, 'END: Null guid in FND_USER for: '||p_user_name);
2121 end if;
2122 fnd_message.set_name('FND','FND_SSO_NOT_LINKED');
2123 return fnd_ldap_util.G_FAILURE;
2124 end if;
2125 exception when no_data_found then
2126 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2127 then
2128 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END: user not found');
2129 end if;
2130 fnd_message.set_name('FND','FND_SSO_LOGIN_FAILED'); -- do no disclusre the real causeL
2131 return fnd_ldap_util.G_FAILURE;
2132 when others then
2133 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2134 then
2135 fnd_log.string(fnd_log.LEVEL_UNEXPECTED ,l_module_source, 'END with exception: '||sqlcode||'-'||sqlerrm);
2136 end if;
2137 fnd_message.set_name('FND','FND-9914'); -- unexpected error
2138 return fnd_ldap_util.G_FAILURE;
2139 end;
2140
2141 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2142 then
2143 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'GUID:'||l_user_guid);
2144 end if;
2145
2146
2147 -- Obtain the user DN using the GUID
2148 begin
2149 user_dn := fnd_Ldap_util.get_dn_for_guid(l_user_guid); -- may raise no data found for invalid guids
2150 exception when no_data_found then
2151 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2152 then
2153 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, 'Guid['||l_user_guid||'] for '||p_user_name||' is not a valid guid');
2154 end if;
2158
2155 fnd_message.set_name('FND','FND_SSO_USER_NOT_FOUND'); -- Carefull, this is INVALID GUID message, wrong acronym though
2156 return fnd_ldap_util.G_FAILURE;
2157 end;
2159 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2160 then
2161 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'DN:'||user_dn);
2162 end if;
2163
2164
2165
2166 l_host := fnd_preference.get(FND_LDAP_UTIL.G_INTERNAL, FND_LDAP_UTIL.G_LDAP_SYNCH, FND_LDAP_UTIL.G_HOST);
2167 l_port := fnd_preference.get(FND_LDAP_UTIL.G_INTERNAL, FND_LDAP_UTIL.G_LDAP_SYNCH, FND_LDAP_UTIL.G_PORT);
2168
2169 if (l_host is null or l_port is null) then
2170 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2171 then
2172 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, 'Invalid OiD Setup: host:'||l_host||' port:'||l_port);
2173 end if;
2174
2175 fnd_message.set_name('FND','FND-9903'); -- OID setup is incomplete
2176 return fnd_ldap_util.G_FAILURE;
2177 end if;
2178
2179 l_ldap_auth := fnd_preference.get(FND_LDAP_UTIL.G_INTERNAL, FND_LDAP_UTIL.G_LDAP_SYNCH, FND_LDAP_UTIL.G_DBLDAPAUTHLEVEL);
2180
2181 if (l_ldap_auth>0) then
2182 l_db_wlt_url := fnd_preference.get(FND_LDAP_UTIL.G_INTERNAL, FND_LDAP_UTIL.G_LDAP_SYNCH, FND_LDAP_UTIL.G_DBWALLETDIR);
2183 l_db_wlt_pwd := fnd_preference.eget(FND_LDAP_UTIL.G_INTERNAL, FND_LDAP_UTIL.G_LDAP_SYNCH, FND_LDAP_UTIL.G_DBWALLETPASS, FND_LDAP_UTIL.G_LDAP_PWD);
2184 if (l_db_wlt_url is null or l_db_wlt_pwd is null) then
2185 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2186 then
2187 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, 'Invalid Wallet Setup: authLEvel:'
2188 ||l_ldap_auth||' url:'||l_db_wlt_url||' pwd:'||l_db_wlt_url);
2189 end if;
2190
2191 fnd_message.set_name('FND','FND-9903'); -- OID setup is incomplete
2192 return fnd_ldap_util.G_FAILURE;
2193 end if;
2194 else
2195 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2196 then
2197 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'WARNING: NON-SSL connection to OiD, check that the Net is secure');
2198 end if;
2199 end if;
2200
2201 dbms_ldap.use_exception := TRUE;
2202
2203 begin
2204 begin
2205 ldapSession := DBMS_LDAP.init(l_host, l_port);
2206 exception when dbms_ldap.init_failed then
2207 if (fnd_log.LEVEL_UNEXPECTED>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2208 then
2209 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, 'Cannot contact OID (init failed) at '||l_host||':'||l_port||':'||sqlcode||'-'||sqlerrm);
2210 end if;
2211 fnd_message.set_name('FND','FND_SSO_SYSTEM_NOT_AVAIL');
2212 return fnd_ldap_util.G_FAILURE;
2213 when others then
2214 raise;
2215 end;
2216
2217 if (l_ldap_auth>0) then
2218
2219 begin
2220 l_retval := dbms_ldap.open_ssl(ldapSession, 'file:'||l_db_wlt_url, l_db_wlt_pwd, l_ldap_auth);
2221 exception when others then
2222 if (fnd_log.LEVEL_UNEXPECTED>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2223 then
2224 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source,' Cannot establish SSL channel to OiD: '||sqlcode||'-'||sqlerrm);
2225 end if;
2226
2227 fnd_message.set_name('FND','FND_SSO_INV_AUTH_MODE'); -- Invalid SSL authcode... it is enouggh description
2228 return fnd_ldap_util.G_FAILURE;
2229 end;
2230
2231 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2232 then
2233 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Excellent!! Using SSL to contact OiD');
2234 end if;
2235 end if;
2236
2237
2238 l_retval := dbms_ldap.simple_bind_s(ldapSession, user_dn , p_password);
2239
2240 -- we do analyze in extense the possible DBMS_LDAP exceptions to return accurate messages
2241
2242
2243 exception
2244 when dbms_ldap.general_error then
2245 -- here comes the explanation
2246 l_message := sqlerrm;
2247 -- first we check if the password is real,
2248
2249 if (instr(l_message,':9000:')>0 )then
2250 fnd_message.set_name('FND','FND_SSO_PASSWORD_EXPIRED'); --Your account is locked
2251 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2252 then
2253 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'OiD account password expired ');
2254 end if;
2255 elsif (instr(l_message,':9001:')>0 )then
2256 fnd_message.set_name('FND','FND_SSO_LOCKED'); --Your account is locked
2257 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2258 then
2259 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'OiD account locked');
2260 end if;
2261
2262 else
2263 if (comparePassword(ldapSession, user_dn , p_password) )then
2264 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2265 then
2266 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'OiD password match but ..');
2267 end if;
2268 if (instr(l_message,':9050:')>0) then
2269 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2270 then
2271 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'OiD account is disabled');
2272 end if;
2273 fnd_message.set_name('FND','FND_SSO_USER_DISABLED'); --Your account is disabled
2274 elsif (instr(l_message,':9053:')>0) then
2278 end if;
2275 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2276 then
2277 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'OiD account is not active: today is out of [start,end] dates ');
2279 fnd_message.set_name('FND','FND_SSO_NOT_ACTIVE'); --Your account not active. Either past end_date or future start_date
2280 else --unknown reason
2281 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2282 then
2283 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'cannot bind because:'||l_message);
2284 end if;
2285 -- maybe is not the reason, but it is enough for return , I guess
2286 fnd_message.set_name('FND','FND_APPL_LOGIN_FAILED'); -- invalid username password
2287 end if;
2288
2289 else
2290 if (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2291 then
2292 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'OiD password did not match');
2293 end if;
2294 -- maybe is not the reason, but it is enough for return , I guess
2295 fnd_message.set_name('FND','FND_APPL_LOGIN_FAILED'); -- invalid username password
2296 end if;
2297 end if;
2298 if (fnd_log.LEVEL_PROCEDURE>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2299 then
2300 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END: bind error: '||l_message);
2301 end if;
2302 return fnd_ldap_util.G_FAILURE;
2303 when others then
2304 if (fnd_log.LEVEL_UNEXPECTED>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2305 then
2306 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, 'END: unexpected'||l_message);
2307 end if;
2308 return fnd_ldap_util.G_FAILURE;
2309 end;
2310
2311
2312 l_retval:= dbms_ldap.unbind_s(ldapSession);
2313 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2314 then
2315 fnd_log.string(fnd_log.LEVEL_PROCEDURE , l_module_source, 'END: Valid Username/password');
2316 end if;
2317 return fnd_ldap_util.G_SUCCESS;
2318
2319 exception when others then
2320 if (fnd_log.LEVEL_UNEXPECTED>= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2321 then
2322 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, 'END: unexpected '||sqlcode||' - '||sqlerrm);
2323 end if;
2324 fnd_message.set_name('FND','FND-9914'); -- unexpected error
2325 return fnd_ldap_util.G_FAILURE;
2326 end validate_login;
2327
2328
2329 function get_user_name_from_data( dn in varchar2, data in FND_LDAP_UTIL.ldap_record_values )
2330 return varchar2 is
2331 l_realm_idx pls_integer;
2332 nna varchar2(2000);
2333 ret varchar2(4000);
2334 BEGIN
2335 ret := null;
2336 l_realm_idx := FND_SSO_REGISTRATION.getUserRealmIndex(dn);
2337 if (l_realm_idx >=0 ) THEN
2338 nna:=fnd_sso_registration.get_realm_attribute(l_realm_idx, 'orclcommonnameattribute');
2339 if (data.exists(nna)) THEN
2340 ret := data(nna)(0);
2341 end if;
2342 END IF;
2343 return ret;
2344 END get_user_name_from_data;
2345
2346 function get_username_from_guid(p_guid in fnd_user.user_guid%type)
2347 return varchar2
2348 is
2349 ldapSession dbms_ldap.session;
2350 flag pls_integer;
2351 ret varchar2(4000);
2352 l_dn varchar2(4000);
2353 l_user_data FND_LDAP_UTIL.ldap_record_type;
2354 l_realm_idx pls_integer;
2355 nna varchar2(2000);
2356
2357 BEGIN
2358 ldapSession := fnd_ldap_util.c_get_oid_session(flag);
2359
2360 l_dn := FND_LDAP_UTIL.get_dn_for_guid(p_guid);
2361 l_realm_idx := FND_SSO_REGISTRATION.getUserRealmIndex(l_dn);
2362 ret:= null;
2363 if (l_realm_idx >=0 ) THEN
2364 nna:=fnd_sso_registration.get_realm_attribute(l_realm_idx, 'orclcommonnicknameattribute');
2365 if ( FND_LDAP_UTIL.LoadLdapRecord(ldapsession, l_user_data ,p_guid,FND_LDAP_UTIL.G_GUID_KEY)) THEN
2366 if (l_user_data.data.exists(nna)) THEN
2367 ret := l_user_data.data(nna)(0);
2368 end if;
2369 end if;
2370 END IF;
2371
2372 fnd_ldap_util.c_unbind(ldapSession,flag);
2373 return ret;
2374
2375 EXCEPTION WHEN OTHERS THEN
2376 fnd_ldap_util.c_unbind(ldapSession,flag);
2377 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2378 then
2379 fnd_log.string(fnd_log.LEVEL_EXCEPTION, G_MODULE_SOURCE||'.get_username_from_guid: ', sqlerrm);
2380 end if;
2381 raise;
2382
2383 END;
2384
2385 --
2386 -- Search either by user_name or dn
2387 -- Fills the record with relevant information
2388 ---
2389 --- FUTURE: it is possible to cache , seems that the user is looked up several times in some flows.
2390
2391 FUNCTION SearchUser (ldap in out nocopy dbms_ldap.session ,
2392 p_ldap_user IN OUT nocopy fnd_ldap_user.ldap_user_type ,
2393 username_z in varchar2 default null,
2394 dn_z in varchar2 default null) return boolean
2395 IS
2396
2397 guid varchar2(4000);
2398 realmDN varchar2(4000);
2399 dn varchar2(4000);
2400 l_module_source varchar2(256);
2401 BEGIN
2402 l_module_source := G_MODULE_SOURCE || 'SearchUser: ';
2403
2404 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2405 then
2406 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin');
2407 end if;
2408
2409 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2410 then
2414
2411 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' isername:'||username_z||' dn:'||dn_z);
2412 end if;
2413
2415 if (dn_z is null and username_z is null) THEN
2416 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2417 then
2418 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END-> false ,Must suply either dn or username ');
2419 end if;
2420
2421 return false;
2422 END IF;
2423
2424 IF (dn_z is not null) THEN
2425
2426 iF (username_z is not null) THEN
2427 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2428 then
2429 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END-> false , choose dn or username, do not use both ');
2430 end if;
2431 raise TOO_MANY_ROWS ;
2432 END IF;
2433
2434
2435 realmDN := FND_OID_PLUG.get_realm_from_user_dn(ldap,dn_z);
2436
2437 if (realmDN is not null ) THEN
2438 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2439 then
2440 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' Realm->:'||realmDN);
2441 end if;
2442 IF FND_LDAP_UTIL.loadldaprecord(ldap,p_ldap_user.user_data,p_ldap_user.dn,dn_z,FND_LDAP_UTIL.G_DN_KEY) THEN
2443 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2444 then
2445 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' Complete the record ' );
2446 end if;
2447 ProcessLoadedLpadUserRecord(p_ldap_user,realmDN,dn_z);
2448
2449 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2450 then
2451 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END-> true ');
2452 end if;
2453 return true; -- loaded from dn_z
2454 ELSE
2455 return false;
2456 END IF;
2457 ELSE
2458 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2459 then
2460 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, ' END-> Dn does not belong to any realm');
2461 end if;
2462 return false; -- no a valid user dn
2463 END IF;
2464 ELSE
2465 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2466 then
2467 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' lookup by username');
2468 end if;
2469 guid := get_user_guid(ldap,username_z,dn);
2470 if (guid is not null) THEN
2471 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2472 then
2473 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, ' Found guid:'||guid);
2474 end if;
2475 IF FND_LDAP_UTIL.loadldaprecord(ldap,p_ldap_user.user_data,p_ldap_user.dn,dn,FND_LDAP_UTIL.G_DN_KEY) THEN
2476 ProcessLoadedLpadUserRecord(p_ldap_user,realmDN,dn);
2477 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2478 then
2479 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, ' END-> FOUND');
2480 end if;
2481
2482 return true; -- loaded from username search
2483 ELSE
2484 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2485 then
2486 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, ' END-> FAIL');
2487 end if;
2488
2489 return false;
2490 END IF;
2491 else
2492 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2493 then
2494 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, ' END-> NOT FOUND');
2495 end if;
2496
2497 return null;
2498 END IF;
2499 END IF;
2500
2501
2502 EXCEPTION WHEN OTHERS THEN
2503 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2504 then
2505 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source , sqlerrm);
2506 end if;
2507 raise;
2508
2509
2510 END SearchUser;
2511
2512 FUNCTION getEmptyLU return ldap_user_type
2513 IS
2514 ret ldap_user_type;
2515 BEGIN
2516 ret.user_name :=null;
2517 return ret;
2518 END getEmptyLU;
2519
2520
2521 --
2522 -- Wrapper to suply an ldapSession
2523 ---
2524 FUNCTION SearchUser ( username_z in varchar2,
2525 p_ldap_user IN OUT nocopy fnd_ldap_user.ldap_user_type) return boolean
2526 IS
2527 ret boolean;
2528 ldapSession dbms_ldap.session;
2529 flag pls_integer;
2530 l_module varchar2(200) := G_MODULE_SOURCE||'.SearchUser[public]';
2531 BEGIN
2532 ldapSession := fnd_ldap_util.c_get_oid_session(flag);
2533 ret:= false;
2534 ret := SearchUser(ldapSession, p_ldap_user , username_z);
2535
2536 fnd_ldap_util.c_unbind(ldapSession,flag);
2537 return ret;
2538
2539 EXCEPTION WHEN OTHERS THEN
2540 fnd_ldap_util.c_unbind(ldapSession,flag);
2541 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2542 then
2543 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module, sqlerrm);
2544 end if;
2545 raise;
2546 END SearchUser;
2547
2548 FUNCTION new_Ldap_user( user_name in FND_USER.USER_NAME%TYPE) return ldap_user_type
2549 is
2550 ret ldap_user_type := getEmptyLU();
2551 BEGIN
2552 ret.user_name := user_name;
2553 select user_id,user_name,user_guid into ret.user_id,ret.user_name, ret.user_guid
2554 from FND_USER WHERE
2555 FND_USER.USER_NAME= ret.user_name;
2556 return ret;
2557 EXCEPTION WHEN NO_DATA_FOUND then
2558 ret.user_id:=null;
2562
2559 ret.user_guid:=null;
2560 return ret;
2561 END new_Ldap_user; -- user_name
2563 PROCEDURE TrimPermited(
2564 p_entity pls_integer,
2565 p_operation pls_integer,
2566 l_user IN OUT nocopy fnd_ldap_user.ldap_user_type)
2567 IS
2568 attr varchar2(200);
2569 l_attr varchar2(200);
2570 x_oid pls_integer;
2571 x_fnd pls_integer;
2572 l_module_source varchar2(200) := G_MODULE_SOURCE||'.TrimPermited';
2573
2574 BEGIN
2575 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2576 then
2577 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'Begin:'|| p_entity||' '|| p_operation);
2578 end if;
2579 attr := l_user.user_data.first;
2580 WHILE attr is not null LOOP
2581 l_attr := attr;
2582 if (p_operation = FND_LDAP_WRAPPER.G_ADD and attr='userpassword') THEN
2583 null;
2584 --BUG 9271995:" do not trim password during creation
2585 ELSE
2586 FND_SSO_REGISTRATION.is_operation_allowed(FND_LDAP_WRAPPER.G_EBIZ_TO_OID,
2587 p_entity,p_operation,
2588 l_attr,x_fnd,x_oid,l_user.user_name,l_user.realmDN);
2589 if (x_oid <> FND_LDAP_WRAPPER.G_SUCCESS) THEN
2590 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)then
2591 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Discard '||attr);
2592 end if;
2593 l_user.user_data.delete(attr);
2594 END IF;
2595 END IF;
2596 attr := l_user.user_data.next(attr);
2597 END LOOP;
2598 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2599 then
2600 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END');
2601 end if;
2602
2603 END TrimPermited;
2604
2605 FUNCTION pvt_create_user
2606 (p_ldap_user IN OUT nocopy fnd_ldap_user.ldap_user_type)
2607 RETURN pls_integer
2608 IS
2609 l_module_source VARCHAR2(256);
2610 retval pls_integer;
2611 result pls_integer;
2612 ldapSession dbms_ldap.session;
2613 flag pls_integer;
2614 l_userDN VARCHAR2(4000);
2615 l_user_guid VARCHAR2(4000);
2616 l_oid_username VARCHAR2(4000);
2617 l_counter INTEGER;
2618 l_guid VARCHAR2(100);
2619 l_link VARCHAR2(10);
2620 l_profile_defined BOOLEAN;
2621 l_user_exists BOOLEAN;
2622 l_dn_exists BOOLEAN;
2623 l_username_exists BOOLEAN;
2624 l_rollback_ldap BOOLEAN;
2625 l_uname varchar2(4000);
2626 l_dn varchar2(4000);
2627 v varchar2(4000);
2628 l_multi_sso varchar2(10) := 'Y';
2629 l_user_linked varchar2(1) := 'N';
2630 l_session_flag boolean := false;
2631 BEGIN
2632 l_module_source := G_MODULE_SOURCE || 'pvt_create_user: ';
2633 -- set default value to failure. change to success when user created successfully
2634 retval := fnd_ldap_util.G_FAILURE;
2635 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2636 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
2637 END IF;
2638 ldapSession := fnd_ldap_util.c_get_oid_session(flag);
2639
2640 l_session_flag := true; /* fix for bug 8271359 */
2641
2642 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
2643 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'l_session_flag = true ' );
2644 end if;
2645
2646 l_uname := p_ldap_user.user_name;
2647 IF SearchUser(ldapSession,p_ldap_user, username_z => l_uname) THEN
2648 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2649 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'User exists , checkin APPS_SSO_LINK_SAME_NAMES');
2650 END IF;
2651
2652 -- Bug 8618800
2653 -- Link same names should only apply if the LDAP user is not already linked to an EBS user on this instance
2654 -- and APPS_SSO_ALLOW_MULTIPLE_ACCOUNTS is Disabled. Get Site level only.
2655 fnd_profile.get_specific(name_z => 'APPS_SSO_ALLOW_MULTIPLE_ACCOUNTS',
2656 USER_ID_Z => -1,
2657 RESPONSIBILITY_ID_Z => -1,
2658 APPLICATION_ID_Z => -1,
2659 ORG_ID_Z => -1,
2660 val_z => l_multi_sso,
2661 defined_z => l_profile_defined);
2662
2663 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2664 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Checking APPS_SSO_ALLOW_MULTIPLE_ACCOUNTS '||l_multi_sso);
2665 END IF;
2666
2667 FND_SSO_REGISTRATION.get_user_or_site_profile(
2668 profile_name=>'APPS_SSO_LINK_SAME_NAMES' ,
2669 user_name_z => p_ldap_user.user_name ,
2670 val_z =>l_link,
2671 defined_z => l_profile_defined );
2672
2673 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2674 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Checking APPS_SSO_LINK_SAME_NAMES '||l_link);
2675 END IF;
2676
2677 -- Get guid of LDAP User.
2678 l_user_guid := get_user_guid(ldapSession,l_uname,l_dn);
2679
2680 if (l_user_guid is not null) then
2681 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2682 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'User guid found...check if already linked to an EBS user');
2683 END IF;
2684
2685 begin
2686 select 'Y' into l_user_linked from fnd_user
2687 where user_guid = l_user_guid
2688 and rownum = 1;
2689
2690 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2691 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Is this OID user already linked? '||l_user_linked);
2692 END IF;
2693
2697 END IF;
2694 exception when no_data_found then
2695 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2696 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'This OID is not linked ');
2698 null;
2699 end;
2700 end if;
2701
2702 IF (l_multi_sso = 'N' and l_user_linked = 'Y') then
2703 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2704 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source,
2705 'STOP - Allow Multiple accounts is disabled and this LDAP user is already linked to an EBS user(s)');
2706 end if;
2707 raise link_create_failed_EXCEPTION;
2708 END IF;
2709
2710 IF (l_link = 'Y') THEN
2711 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2712 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'User exists but APPS_SSO_LINK_SAME_NAMES is Enabled, adding user to subscription list');
2713 end if;
2714
2715 retval := create_user_subscription(ldapSession, p_ldap_user.dn , p_ldap_user.user_guid);
2716
2717 IF (retval <> fnd_ldap_util.G_SUCCESS) THEN
2718 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2719 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source,
2720 'Failed to create subscription for create_user("'||p_ldap_user.user_name
2721 ||'"), user existed and (APPS_SSO_LINK_SAME_NAMES=Enabled)');
2722 end if;
2723 raise link_create_failed_EXCEPTION;
2724
2725 ELSE
2726 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2727 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Subscription created at OiD');
2728 end if;
2729
2730 -- Bug 8661715 Potential ldap leak
2731 if (l_session_flag = true) then
2732 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
2733 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'LDAP SESSION closing ' );
2734 end if;
2735 fnd_ldap_util.c_unbind(ldapSession,flag);
2736 l_session_flag := false;
2737 end if;
2738 -- Bug 8618800 - User already exists and Link Same Names is enabled - simply link the users
2739 -- return retval;
2740
2741 -- Bug 13692093: Return new status since the LDAP user already exists the password should not be updated.
2742 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2743 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'OID User already exists - return G_OID_USER_EXISTS');
2744 END IF;
2745
2746 return G_OID_USER_EXISTS;
2747
2748 END IF;
2749 ELSE -- AUTOLINK DISABLED
2750 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2751 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'FAILED: User exists [username='||p_ldap_user.user_name||']');
2752 END IF;
2753 raise duplicate_username_EXCEPTION;
2754 END IF;
2755 ELSE
2756 FND_OID_PLUG.completeForCreate(ldapSession, p_ldap_user);
2757 l_oid_username := p_ldap_user.user_name;
2758 l_dn := p_ldap_user.dn ;
2759 IF SearchUser(ldapSession,p_ldap_user,dn_z => l_dn ) THEN
2760 IF (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2761 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'DN collsion, trying to create "'||l_oid_username||'" on dn:' || p_ldap_user.dn );
2762 END IF;
2763 raise duplicate_dn_EXCEPTION;
2764 END IF;
2765 END IF;
2766
2767 --Time to verify is operation allowed for given attributes
2768 TrimPermited(fnd_ldap_wrapper.G_IDENTITY,fnd_ldap_wrapper.G_ADD,p_ldap_user);
2769
2770 BEGIN
2771 v := p_ldap_user.user_data(p_ldap_user.NickName_ATT_NAME)(0);
2772 EXCEPTION WHEN OTHERS THEN
2773 v := NULL;
2774 END;
2775 if (v is null) THEN
2776 IF (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2777 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'NicknameAtrtribute not preosente in the record , Cannot create. Check configuration (prov Profiles)');
2778 END IF;
2779 raise CANNOT_CREATE_EXCEPTION;
2780 END IF;
2781
2782
2783 IF NOT ( attributePresent(p_ldap_user,'sn') AND attributePresent(p_ldap_user,'cn')) THEN
2784 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2785 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Not all attrirbutes are present ' || ' cn='||getAttribute(p_ldap_user,'cn') || ' sn='||getAttribute(p_ldap_user,'sn') );
2786 END IF;
2787 IF (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2788 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END -> failed');
2789 END IF;
2790 -- Bug 8661715 Potential ldap leak
2791 if (l_session_flag = true) then
2792 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
2793 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'LDAP SESSION closing ' );
2794 end if;
2795 l_session_flag := false;
2796 fnd_ldap_util.c_unbind(ldapSession,flag);
2797 end if;
2798 RETURN fnd_ldap_util.G_FAILURE;
2799 END IF;
2800
2801 setAttribute(p_ldap_user,'objectClass','top',true);
2802 setAttribute(p_ldap_user,'objectClass','inetorgperson',false);
2803 setAttribute(p_ldap_user,'objectClass','orcluserv2',false);
2804
2805 retval := create_ldap_user(ldapSession, p_ldap_user);
2806
2807 IF (retval <> fnd_ldap_util.G_SUCCESS) THEN
2808 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2809 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, 'User creation failed');
2810 end if;
2811 ELSE
2812 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2816 IF (retval <> fnd_ldap_util.G_SUCCESS) THEN
2813 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'LDAP user created, now creating susbscriptions');
2814 END IF;
2815 retval := create_user_subscription(ldapSession, p_ldap_user.dn, p_ldap_user.user_guid);
2817 IF (fnd_log.LEVEL_UNEXPECTED>= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2818 fnd_log.string(fnd_log.LEVEL_UNEXPECTED ,l_module_source, 'Subscription creation failed for a new user, removing user');
2819 END IF;
2820 delete_user(ldapSession, p_ldap_user.user_guid,result);
2821 IF (result <>fnd_ldap_util.G_SUCCESS) THEN
2822 if (fnd_log.LEVEL_UNEXPECTED >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2823 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, l_module_source, ' unable to remove user ');
2824 end if;
2825 END IF;
2826 raise link_create_failed_EXCEPTION;
2827 ELSIF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2828 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Subscription creation succeeded');
2829 END IF;
2830
2831 fnd_ldap_util.c_unbind(ldapSession,flag);
2832 l_session_flag := false;
2833
2834 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
2835 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'l_session_flag : = false ' );
2836 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'LDAP SESSION CLOSED NORMALLY : ' );
2837 end if;
2838
2839 IF (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2840 IF (retval = fnd_ldap_util.G_SUCCESS) THEN
2841 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End ->fnd_ldap_util.G_SUCCESS');
2842 ELSE
2843 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'End ->fnd_ldap_util.G_FAILURE');
2844 END IF ;
2845 END IF;
2846 END IF;
2847 RETURN retval;
2848 EXCEPTION
2849
2850 WHEN CANNOT_CREATE_EXCEPTION THEN
2851 if l_session_flag = true then
2852 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
2853 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'LDAP SESSION closing in CANNOT CREATE EXCEPTION BLOCK - START ' );
2854 end if;
2855 fnd_ldap_util.c_unbind(ldapSession,flag);
2856
2857 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
2858 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'LDAP SESSION closed in CANNOT CREATE EXCEPTION BLOCK - END ');
2859 end if;
2860 end if;
2861
2862 IF (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2863 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'Error creating ldap user "' ||p_ldap_user.user_name||'" ' ||' Incorrect configuration' );
2864 END IF;
2865 fnd_message.set_name ('FND', 'FND-9903');
2866 RETURN fnd_ldap_util.G_FAILURE;
2867
2868 WHEN duplicate_dn_EXCEPTION THEN
2869 if l_session_flag = true then
2870 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
2871 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'LDAP SESSION closing in Duplicate DN EXCEPTION BLOCK - START ' );
2872 end if;
2873 fnd_ldap_util.c_unbind(ldapSession,flag);
2874
2875 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
2876 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'LDAP SESSION closed in Duplicate DN EXCEPTION BLOCK - END ');
2877 end if;
2878 end if;
2879
2880 IF (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2881 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'Error creating ldap user "' ||p_ldap_user.user_name||'" ' ||' DN already exists [DN:'||p_ldap_user.dn ||']' );
2882 END IF;
2883 fnd_message.set_name ('FND', 'FND_SSO_USER_EXISTS');
2884 RETURN fnd_ldap_util.G_FAILURE;
2885 WHEN duplicate_username_EXCEPTION THEN
2886 if l_session_flag = true then
2887 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
2888 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'LDAP SESSION closing in Duplicate Username EXCEPTION BLOCK - START ' );
2889 end if;
2890 fnd_ldap_util.c_unbind(ldapSession,flag);
2891
2892 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
2893 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'LDAP SESSION closed in Duplicate username EXCEPTION BLOCK - END ');
2894 end if;
2895 end if;
2896
2897 IF (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2898 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'Error creating ldap user "' ||p_ldap_user.user_name||'" ' ||' username already exists [guid:'||p_ldap_user.user_guid||']' );
2899 END IF;
2900 fnd_message.set_name ('FND', 'FND_SSO_USER_EXISTS');
2901 RETURN fnd_ldap_util.G_FAILURE;
2902 WHEN link_create_failed_EXCEPTION THEN
2903 if l_session_flag = true then
2904 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
2905 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'LDAP SESSION closing in Link create failed EXCEPTION BLOCK - START ' );
2906 end if;
2907 fnd_ldap_util.c_unbind(ldapSession,flag);
2908
2909 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
2910 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'LDAP SESSION closed in Link create failed EXCEPTION BLOCK - END ');
2911 end if;
2912 end if;
2913
2914 IF (fnd_log.LEVEL_EXCEPTION>= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2915 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, 'Error creating sunscriptions for "'||p_ldap_user.user_name||'" guid:'||p_ldap_user.user_guid );
2916 END IF;
2917 fnd_message.set_name('FND','FND_SSO_LINK_USER_FAILED');
2918 RETURN fnd_ldap_util.G_FAILURE;
2919 WHEN OTHERS THEN
2920 if l_session_flag = true then
2921 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
2922 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'LDAP SESSION closing in WHEN OTHERS EXCEPTION BLOCK - START ' );
2926 if (fnd_log.LEVEL_ERROR >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
2923 end if;
2924 fnd_ldap_util.c_unbind(ldapSession,flag);
2925
2927 fnd_log.string(fnd_log.LEVEL_ERROR, l_module_source, 'LDAP SESSION closed in WHEN OTHERS EXCEPTION BLOCK - END ');
2928 end if;
2929 end if;
2930
2931 IF (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2932 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
2933 END IF;
2934 raise;
2935 END pvt_create_user;
2936
2937 PROCEDURE create_user
2938 (
2939 p_realm in out nocopy varchar2,
2940 p_user_name IN VARCHAR2,
2941 p_password IN VARCHAR2,
2942 p_start_date IN DATE DEFAULT sysdate,
2943 p_end_date IN DATE DEFAULT NULL,
2944 p_description IN VARCHAR2 DEFAULT NULL,
2945 p_email_address IN VARCHAR2 DEFAULT NULL,
2946 p_fax IN VARCHAR2 DEFAULT NULL,
2947 p_expire_password IN pls_integer,
2948 x_user_guid OUT nocopy raw,
2949 x_password OUT nocopy VARCHAR2,
2950 x_result OUT nocopy pls_integer)
2951 IS
2952
2953 l_usr fnd_ldap_user.ldap_user_type;
2954 l_module_source VARCHAR2(256) := G_MODULE_SOURCE || 'create_user: ';
2955 l_start_date VARCHAR2(256);
2956 l_end_date VARCHAR2(656);
2957 l_local_login VARCHAR2(100);
2958 l_profile_defined BOOLEAN;
2959 l_nickname VARCHAR2(256);
2960 l_password VARCHAR2(256);
2961 l_enabled VARCHAR2(30);
2962 l_du_result pls_integer;
2963 l_cp_result pls_integer;
2964 user_name fnd_user.user_name%type;
2965 l_disabled_usr boolean;
2966 l_oid_user_exists varchar2(1) := 'N';
2967
2968
2969
2970 BEGIN
2971 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
2972 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
2973 END IF;
2974 if (p_realm is null) THEN
2975 p_realm := FND_OID_PLUG.get_realm_dn(p_user_name=>p_user_name);
2976 END IF;
2977
2978 l_usr := new_Ldap_user(p_user_name);
2979
2980 l_usr.realmDN := p_realm;
2981
2982 l_enabled := fnd_oid_util.G_ENABLED;
2983 IF ((p_start_date IS NOT NULL AND p_start_date > sysdate) OR (p_end_date IS NOT NULL AND p_end_date <= sysdate)) THEN
2984 --usertype.orclisEnabled := fnd_oid_util.G_DISABLED;
2985 setAttribute(l_usr,'orclisEnabled',fnd_oid_util.G_DISABLED,true);
2986 l_disabled_usr := true;
2987 ELSE
2988 -- usertype.orclisEnabled := fnd_oid_util.G_ENABLED;
2989 setAttribute(l_usr,'orclisEnabled',fnd_oid_util.G_ENABLED,true);
2990 l_disabled_usr := false;
2991 END IF;
2992
2993 /* Bug 9271995: Always create the user with the password
2994 By default it will remain expired
2995 IF (p_expire_password = fnd_ldap_util.G_TRUE) THEN
2996 l_password := p_password;
2997 ELSE
2998 l_password := NULL;
2999 END IF;
3000 */
3001 l_password := p_password;
3002 /* If self service user and pending user, create the user as enabled user first and then change
3003 the flag to disabled after the password has been updated by proxying as that user. This is because
3004 we cannot proxy as a disabled user. */
3005 -- Bug 9398572.
3006 -- IF ( (l_password IS NULL) AND l_disabled_usr ) THEN
3007 IF ( (p_expire_password = fnd_ldap_util.G_FALSE) AND (l_disabled_usr) ) THEN
3008 l_enabled := fnd_oid_util.G_DISABLED;
3009 --usertype.orclisEnabled := fnd_oid_util.G_ENABLED;
3010 setAttribute(l_usr,'orclisEnabled',fnd_oid_util.G_ENABLED,true);
3011 END IF;
3012 -- first create user. If self service, then pass in a null password
3013 user_name := p_user_name;
3014 --l_nickname := fnd_ldap_util.get_orclcommonnicknameattr(user_name);
3015 l_nickname := FND_SSO_REGISTRATION.get_realm_attribute(p_realm,'orclCommonNickNameAttribute');
3016 -- usertype.uid := p_user_name;
3017 -- usertype.sn := p_user_name;
3018 -- usertype.cn := p_user_name;
3019 -- usertype.userPassword := l_password;
3020 -- usertype.description := p_description;
3021 setAttribute(l_usr,l_nickname,p_user_name,true);
3022 setAttribute(l_usr,'uid',p_user_name,true);
3023 setAttribute(l_usr,'sn',p_user_name,true);
3024 setAttribute(l_usr,'cn',p_user_name,true);
3025 setAttribute(l_usr,'description',p_description,true);
3026
3027 -- Passing a null password fails - previously this check was done in
3028 -- process_attributes
3029 /* Bug 13472484 and 9498047 - commenting out adding the password attribute
3030 * when creating the user. We will create the user with no password initially
3031 * and set the password later in the flow by calling change_password. This
3032 * will perform a check as to whether we should change the pwd as the user or
3033 * admin so that expiration occurs. This is to resolve issues with pwd
3034 * history. Setting this to NULL or a dummy pwd may cause issues with password
3035 * policies.
3036 */
3037 -- IF (l_password IS NOT NULL) then
3038 -- setAttribute(l_usr,'userPassword',l_password,true);
3039 -- END IF;
3040
3041 IF (upper(l_nickname) = fnd_ldap_util.G_MAIL) THEN
3042 --usertype.mail := p_user_name;
3043 setAttribute(l_usr,'mail',p_user_name,true);
3044 ELSE
3045 --usertype.mail := p_email_address;
3046 setAttribute(l_usr,'mail',p_email_address,true);
3047 END IF;
3048 IF (upper(l_nickname) = fnd_ldap_util.G_FACSIMILETELEPHONENUMBER) THEN
3049 --usertype.facsimileTelephoneNumber := p_user_name;
3050 setAttribute(l_usr,'facsimileTelephoneNumber',p_user_name,true);
3051 ELSE
3052 --usertype.facsimileTelephoneNumber := p_fax;
3053 setAttribute(l_usr,'facsimileTelephoneNumber',p_fax,true);
3054 END IF;
3055
3059
3056 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3057 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Done setting attributes for user creation...now create the user in OID');
3058 END IF;
3060
3061 x_result := pvt_create_user(l_usr);
3062
3063 if (x_result = G_OID_USER_EXISTS) then
3064 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) then
3065 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'OID user already exists...set flag and status. Password related task should be bypassed');
3066 end if;
3067 l_oid_user_exists := 'Y';
3068 x_result := fnd_ldap_util.G_SUCCESS;
3069 end if;
3070
3071
3072 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3073 if (x_result = fnd_ldap_util.G_SUCCESS) then
3074 fnd_log.string(fnd_log.LEVEL_STATEMENT,l_module_source,'Successfully created LDAP user');
3075 else
3076 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'Failed to create LDAP user');
3077 end if;
3078 END IF;
3079
3080 -- NOTE: This code seems to be a NOOP and may be removed
3081 IF (p_expire_password = fnd_ldap_util.G_TRUE) THEN
3082 l_password := p_password;
3083 ELSE
3084 l_password := NULL;
3085 END IF;
3086
3087 -- Bug 13692093: Added check for LINK_EXISTING which is a successful linking of a new FND and existing LDAP user
3088 IF (x_result = fnd_ldap_util.G_SUCCESS) THEN
3089 -- x_user_guid := get_user_guid(p_user_name);
3090 x_user_guid := l_usr.user_guid;
3091 fnd_profile.get_specific( name_z => 'APPS_SSO_LOCAL_LOGIN', user_id_z => -1, val_z => l_local_login, defined_z => l_profile_defined);
3092 IF (l_local_login = 'SSO') THEN
3093 x_password := fnd_web_sec.EXTERNAL_PWD;
3094 END IF;
3095 -- if p_expire_password = false then update the user password (and password only)
3096 IF ( (x_result = fnd_ldap_util.G_SUCCESS and l_oid_user_exists = 'N') )THEN
3097 begin
3098 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3099 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'User has been created - now set password');
3100 END IF;
3101
3102 change_password(x_user_guid, p_user_name, p_password, p_expire_password, x_password, l_cp_result,TRUE);
3103
3104 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3105 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'User password has been set');
3106 END IF;
3107
3108 exception when others then
3109 delete_user(x_user_guid, x_result,true);
3110 raise;
3111 end;
3112 IF (l_enabled = fnd_oid_util.G_DISABLED) THEN
3113 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3114 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'Setting user to disabled.');
3115 end if;
3116 disable_user(x_user_guid, p_user_name, l_du_result);
3117 END IF;
3118 IF ( (l_cp_result = fnd_ldap_util.G_FAILURE ) OR (l_du_result = fnd_ldap_util.G_FAILURE) ) THEN
3119 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3120 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'Error occurred - delete user');
3121 end if;
3122
3123 delete_user(x_user_guid, x_result);
3124 ELSE
3125 x_result := fnd_ldap_util.G_SUCCESS;
3126 END IF;
3127 END IF;
3128 ELSE
3129 fnd_message.set_name ('FND', 'FND_SSO_USER_EXISTS');
3130 END IF;
3131 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3132 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'End');
3133 END IF;
3134 EXCEPTION
3135 WHEN OTHERS THEN
3136 IF (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3137 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
3138 END IF;
3139 raise;
3140 END create_user;
3141
3142
3143
3144 FUNCTION getNickNameAttr( username_z in varchar2) return varchar2
3145 IS
3146 realm varchar2(4000);
3147 user_rec ldap_user_type;
3148 idx pls_integer;
3149 l_module_source varchar2(256);
3150 BEGIN
3151 l_module_source:= G_MODULE_SOURCE ||'getNickNameAttr';
3152 IF (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3153 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, sqlerrm);
3154 END IF;
3155
3156 if (username_z is null) THEN
3157 IF (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3158 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'No user given: use default');
3159 END IF;
3160 if (cache_default_nna is null) THEN
3161 cache_default_nna:= fnd_sso_registration.get_realm_attribute(
3162 FND_SSO_REGISTRATION.getdefaultrealm,'orclCommonNickNameAttribute');
3163 IF (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3164 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Caching:'||cache_default_nna);
3165 END IF;
3166 END IF;
3167 IF (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3168 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END->'||cache_default_nna||' From cache');
3169 END IF;
3170 return cache_default_nna;
3171 END IF;
3172
3173
3174 if (cache_user_name is not null) THEN
3175 if (cache_user_name = username_z) THEN
3176 IF (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3177 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Asking again for '||cache_user_name||'?');
3178 END IF;
3179 IF (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3180 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END->'||cache_default_nna||' USER From cache');
3181 END IF;
3185 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'different user, not cached');
3182 return cache_nna;
3183 ELSE
3184 IF (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3186 END IF;
3187 cache_user_name:= null;
3188 END IF;
3189 END IF;
3190 -- ok, no options but search
3191
3192 IF (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3193 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Need to locate the user at LDAP');
3194 END IF;
3195 IF (SearchUser(username_z=>username_z,p_ldap_user=>user_rec)) THEN
3196
3197 IF (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3198 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'User FOUND');
3199 END IF;
3200 cache_user_name := username_z;
3201 idx := FND_SSO_REGISTRATION.getuserrealmindex(user_rec.dn);
3202 cache_nna := Fnd_sso_registration.get_realm_attribute(
3203 idx ,'orclCommonNickNameAttribute');
3204 IF (fnd_log.LEVEL_PROCEDURE>= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3205 fnd_log.string(fnd_log.LEVEL_PROCEDURE ,l_module_source, 'END->'||cache_nna);
3206 END IF;
3207 return cache_nna;
3208 ELSE
3209 IF (fnd_log.LEVEL_STATEMENT>= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3210 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'User NOT FOUND, using default');
3211 END IF;
3212 IF (fnd_log.LEVEL_PROCEDURE>= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3213 fnd_log.string(fnd_log.LEVEL_PROCEDURE ,l_module_source, 'END->'||cache_default_nna);
3214 END IF;
3215
3216 return cache_default_nna; -- do not cache it, maybe it is about to change
3217 END IF;
3218 EXCEPTION
3219 WHEN OTHERS THEN
3220 IF (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3221 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source, sqlerrm);
3222 END IF;
3223 raise;
3224
3225 END getNickNameAttr;
3226
3227
3228 --
3229 --
3230 -------------------------------------------------------------------------------
3231 /*
3232
3233
3234 API FOR LDAP_RECORD HANDLING
3235
3236 */
3237
3238
3239 FUNCTION locateIdx ( col in out nocopy DBMS_LDAP.STRING_COLLECTION , val in varchar ) return pls_integer
3240 is
3241 i pls_integer;
3242 BEGIN
3243 if (col is null or val is null ) then
3244 return null;
3245 end if;
3246 i:= col.first;
3247 while i is not null loop
3248 if (VAL = col(i) ) then
3249 return i;
3250 END IF;
3251 i := col.next(i);
3252 END LOOP;
3253 return null;
3254 END locateIdx;
3255
3256 PROCEDURE setAttribute( usr in out nocopy ldap_user_type,
3257 attName in varchar2,
3258 attVal in varchar2,
3259 replaceIt in boolean default false )
3260 IS
3261 old_idx pls_integer;
3262 lista DBMS_LDAP.STRING_COLLECTION ;
3263 l_name varchar2(4000) := lower(attName);
3264 BEGIN
3265 if (replaceIt) then
3266 usr.user_data.delete(l_name);
3267 END IF;
3268 if (NOT usr.user_data.exists(l_name) ) then
3269 usr.user_data(l_name)(0) := attVal;
3270 ELSE
3271 -- the attribute exists
3272 old_idx := locateIdx(usr.user_data(l_name),attVal);
3273
3274 if (old_idx is null) THEN
3275 -- the value is not duplicated
3276 usr.user_data(l_name)(usr.user_data(l_name).count+1):= attVal;
3277 end if;
3278 END IF;
3279 END setAttribute;
3280
3281 PROCEDURE deleteAttribute( usr in out nocopy ldap_user_type,
3282 attName in varchar2,
3283 attVal in varchar2 )
3284 IS
3285 i pls_integer ;
3286 l_name varchar2(4000) := lower(attName);
3287
3288 BEGIN
3289 i := locateIdx( usr.user_data(l_name), attVal);
3290 while i is not null LOOP
3291 usr.user_data(attName).delete(i);
3292 -- make sure we don't left duplicates
3293 i := locateIdx( usr.user_data(l_name), attVal);
3294 end LOOP;
3295 END deleteAttribute;
3296
3297
3298 PROCEDURE deleteAttribute( usr in out nocopy ldap_user_type,
3299 attName in varchar2)
3300 is
3301 l_name varchar2(4000) := lower(attName);
3302
3303 BEGIN
3304 if (usr.user_data(l_name) is not null ) THEN
3305 usr.user_data(l_name).delete;
3306 usr.user_data.delete(l_name);
3307 END IF;
3308 END deleteAttribute;
3309
3310 FUNCTION getAttribute( usr in out nocopy ldap_user_type,
3311 attName in varchar2,
3312 attValIdx in pls_integer default 0 ) return varchar2
3313 is
3314 BEGIN
3315 return usr.user_data(lower(attName))(attValIdx);
3316 EXCEPTION WHEN OTHERS THEN
3317 return null;
3318 END getAttribute;
3319
3320
3321
3322
3323
3324 FUNCTION firstValue(usr in out nocopy ldap_user_type,
3325 attName in out nocopy varchar2,
3326 attValue in out nocopy varchar2,
3327 handle in out nocopy pls_integer ) return boolean -- false when record is empty
3328 IS
3329 BEGIN
3330 attName := null;
3331 attValue := null;
3332 handle := null;
3333 attName := usr.user_data.first;
3334 if (attName is not null) THEN
3335 handle := usr.user_data(attName).first;
3336 ELSE
3337 handle := -1;
3338 return false;
3339 END IF ;
3340 -- skip empty lists
3341 WHILE handle is null LOOP
3342 attName := usr.user_data.next(attName);
3343 if (attName is not null) THEN
3344 handle := usr.user_data(attName).first;
3345 ELSE
3346 handle := -1;
3347 return false;
3348 END IF;
3352 END IF;
3349 END LOOP;
3350 if (handle is not null) THEN
3351 attValue := usr.user_data(attName)(handle);
3353 return handle is not null;
3354 EXCEPTION when others then
3355 handle:= -1;
3356 return false;
3357 END firstValue;
3358
3359 FUNCTION nextValue(usr in out nocopy ldap_user_type,
3360 attName in out nocopy varchar2,
3361 attValue in out nocopy varchar2,
3362 handle in out nocopy pls_integer ) return boolean -- true if returned fields contains data
3363 is
3364 BEGIN
3365 if (handle = -1 or handle is null ) THEN
3366 attName := null;
3367 attValue :=null;
3368 handle := -1;
3369 return false;
3370 end if;
3371 handle := usr.user_data(attName).next(handle);
3372 WHILE handle is null LOOP
3373 attName := usr.user_data.next(attName);
3374 if (attName is null) then
3375 handle := -1;
3376 attValue := null;
3377 return false;
3378 END IF;
3379 handle := usr.user_data(attName).first;
3380 END LOOP;
3381 if (handle is not null) THEN
3382 attValue := usr.user_data(attName)(handle);
3383 return true;
3384 else
3385 return false;
3386 end if;
3387
3388 END nextValue;
3389
3390 FUNCTION attributePresent( usr in out nocopy ldap_user_type,
3391 attName in varchar2) return boolean
3392 is
3393 l_name varchar2(4000):= lower(attName);
3394 BEGIN
3395 return usr.user_data.exists(l_name) and usr.user_data(l_name).exists(0);
3396
3397 END attributePresent;
3398
3399 function CanSync ( p_user_id in pls_integer, p_user_name in varchar2 ) return boolean
3400 is
3401 l_local_login varchar2(30);
3402 l_profile_defined boolean;
3403 l_to_synch boolean := false;
3404 l_allow_sync varchar2(1);
3405 l_user_id FND_USER.user_ID%TYPE := p_user_id;
3406 l_user_name FND_USER.user_name%TYPE := p_user_name;
3407 l_module_source varchar2(200) := G_MODULE_SOURCE || 'CanSync:['||p_user_id||']';
3408 BEGIN
3409 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)then
3410 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,'BEGIN '||p_user_name||' userid:'||p_user_id);
3411 end if;
3412 if (l_user_id is null and l_user_name is null) THEN
3413 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)then
3414 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,'END->False, try it manually ');
3415 end if;
3416 return false;
3417 ELSIF (l_user_id is null) THEN
3418 BEGIN
3419 select user_id into l_user_id from FND_USER where user_name =l_user_name;
3420 EXCEPTION WHEN OTHERS THEN
3421 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)then
3422 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,'END->False, user not found ');
3423 end if;
3424 return false;
3425 END;
3426 ELSIF (l_user_name is null) THEN
3427 BEGIN
3428 select user_name into l_user_name from FND_USER where user_id =l_user_id;
3429 EXCEPTION WHEN OTHERS THEN
3430 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)then
3431 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,'END->False, user not found ');
3432 end if;
3433 return false;
3434 END;
3435
3436 ELSE
3437 null;
3438 END IF;
3439 fnd_profile.get_specific(
3440 name_z => 'APPS_SSO_LOCAL_LOGIN',
3441 user_id_z => l_user_id,
3442 val_z => l_local_login,
3443 defined_z => l_profile_defined);
3444
3445 if (not l_profile_defined or l_local_login = fnd_oid_util.G_LOCAL)
3446 then
3447 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
3448 then
3449 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
3450 'value of APPS_SSO_LOCAL_LOGIN:: '|| l_local_login);
3451 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
3452 'Local user dont sych '|| l_user_name);
3453 end if;
3454 l_to_synch := FALSE;
3455 else
3456 fnd_profile.get_specific(name_z => 'APPS_SSO_LDAP_SYNC',
3457 user_id_z => l_user_id,
3458 val_z => l_allow_sync,
3459 defined_z => l_profile_defined);
3460
3461 if (not l_profile_defined or l_allow_sync = fnd_oid_util.G_N)
3462 then
3463 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
3464 then
3465 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
3466 'value of APPS_SSO_LDAP_SYNC '|| l_allow_sync);
3467 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,
3468 'Synch profile is disabled for user ...dont sych '|| l_user_name);
3469 end if;
3470 l_to_synch := FALSE;
3471 else
3472 l_to_synch := TRUE;
3473 end if;
3474 end if;
3475 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)then
3476 if (l_to_synch) THEN
3477 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,'END->True' );
3478 ELSE
3479 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source,'END->False' );
3480 END IF;
3481 end if;
3482
3483 return l_to_synch;
3484
3485
3486 EXCEPTION WHEN OTHERS THEN
3487 IF (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3488 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source , sqlerrm);
3489 END IF;
3490 raise;
3491
3492 END CanSync;
3493
3497 i pls_integer;
3494 -------------------------------------------------------------------------------
3495 PROCEDURE ProcessUpdateRec(ldap in dbms_ldap.session, dn in varchar2, upd in update_list)
3496 IS
3498 ma dbms_ldap.mod_array := null;
3499 l dbms_ldap.string_collection;
3500 m varchar2(100);
3501 l_module_source varchar2(400);
3502 BEGIN
3503 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
3504 then
3505 l_module_source := G_MODULE_SOURCE || 'ProcessUpdateRec: ';
3506 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
3507 end if;
3508
3509 ma := dbms_ldap.create_mod_array(num=> upd.count);
3510 i:= upd.first;
3511 while i is not null LOOP
3512 l.delete;
3513 l(0) := upd(i).val;
3514
3515
3516 dbms_ldap.populate_mod_array(modptr => ma,
3517 mod_op =>upd(i).op,
3518 mod_type => upd(i).att,
3519 modval => l);
3520 i:=upd.next(i);
3521 END LOOP;
3522 i := dbms_ldap.modify_s(ldap,dn,ma);
3523
3524 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
3525 then
3526 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'END');
3527 end if;
3528
3529
3530 EXCEPTION WHEN OTHERS THEN
3531 if (ma is not null) then
3532 dbms_ldap.free_Mod_array(ma);
3533 END IF;
3534 IF (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3535 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module_source , sqlerrm);
3536 END IF;
3537 raise;
3538 END ProcessUpdateRec;
3539
3540 FUNCTION isValueOf( u ldap_user_type, fld in varchar2, val in varchar2 ) return boolean
3541 IS
3542 i pls_integer;
3543 n varchar2(200) := lower(fld);
3544 l dbms_ldap.string_collection;
3545 BEGIN
3546 if val is null THEN
3547 return false;
3548 END IF;
3549 IF u.user_data.exists(n) THEN
3550 L:= u.user_data(n);
3551 i:= l.first;
3552 while i is not null loop
3553 if (l(i) = val) THEN
3554 return true;
3555 END IF;
3556 i:= l.next(i);
3557 end loop;
3558 END IF;
3559 return false;
3560 END isValueOf;
3561
3562 PROCEDURE ProcessLoadedLpadUserRecord (p_ldap_user IN OUT nocopy fnd_ldap_user.ldap_user_type ,
3563 realmDN in varchar2 ,
3564 dn_z in varchar2 )
3565 IS
3566 realm pls_integer;
3567 exp1 dbms_ldap.string_collection;
3568 i pls_integer;
3569 l_module varchar2(4000):= G_MODULE_SOURCE || 'ProcessLoadedLpadUserRecord: ';
3570 shortest varchar2(4000);
3571 l_v varchar2(4000);
3572 BEGIN
3573
3574 IF (fnd_log.LEVEL_PROCEDURE>= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3575 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module, 'BEGIN');
3576 END IF;
3577 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3578 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module, 'in realm:'||realmDN||' dn:'||dn_z);
3579 END IF;
3580 if ( p_ldap_user.user_data.exists('orclguid') and p_ldap_user.user_data('orclguid').count>0 ) THEN
3581 p_ldap_user.user_guid := p_ldap_user.user_data('orclguid')(0);
3582 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3583 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module, 'guid(from record):'||p_ldap_user.user_guid );
3584 END IF;
3585 else
3586 p_ldap_user.user_guid:=null;
3587 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3588 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module, 'NULL Guid (?)');
3589 END IF;
3590
3591 END IF;
3592
3593 p_ldap_user.dn := dn_z; -- no validation
3594
3595 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3596 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module, 'dn(parameter):'||dn_z);
3597 END IF;
3598
3599 realm := FND_SSO_REGISTRATION.getUserRealmIndex(p_ldap_user.dn);
3600 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3601 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module, 'realmIdx(from dn)'||realm);
3602 END IF;
3603
3604 p_ldap_user.NickName_ATT_NAME := lower(FND_SSO_REGISTRATION.get_realm_attribute(realm,'orclcommonnicknameattribute'));
3605 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3606 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module, 'NickNameAttribute(realm)'||p_ldap_user.NickName_ATT_NAME );
3607 END IF;
3608
3609 p_ldap_user.realmDN := FND_SSO_REGISTRATION.find_realm(realm);
3610 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3611 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module, 'realmDN(resolving):'|| p_ldap_user.realmDN );
3612 END IF;
3613
3614 exp1 := dbms_ldap.explode_dn(lower(dn_z),0);
3615 i := instr(exp1(0),'=');
3616 p_ldap_user.RDN_ATT_NAME:= substr(exp1(0),0,i-1) ;
3617 p_ldap_user.RDN_VALUE := substr(exp1(0),i+1) ;
3618 p_ldap_user.parent_DN := '';
3619 for i in 1 .. exp1.last -- skip the first
3620 LOOP
3621 p_ldap_user.parent_DN := p_ldap_user.parent_DN || ',' || exp1(i);
3622 END LOOP;
3623 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3624 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module, 'parentDN(from parameter dn)'||p_ldap_user.parent_DN);
3625 END IF;
3626 -- The username calculation:: Can by tricky
3627 -- case 0: No value
3628 IF ( p_ldap_user.user_data(p_ldap_user.NickName_ATT_NAME).count=0) THEN
3629 p_ldap_user.user_name := null;
3630 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3631 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module, 'nna has no value: username=NULL');
3635 p_ldap_user.user_name := p_ldap_user.user_data(p_ldap_user.NickName_ATT_NAME)(0);
3632 END IF;
3633 -- case 1: only one value in the nickanme attribute
3634 ELSIF ( p_ldap_user.user_data(p_ldap_user.NickName_ATT_NAME).count=1) THEN
3636 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3637 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module, 'user_name(unique nna in record):'||p_ldap_user.user_name);
3638 END IF;
3639 ELSE
3640 -- case 2: several values, let's lookup on FND_USER to see if there is a match
3641 i := p_ldap_user.user_data(p_ldap_user.NickName_ATT_NAME).first;
3642 p_ldap_user.user_id:= null;
3643 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3644 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module, ' several nna , lookinf for a best match');
3645 END IF;
3646 shortest := null;
3647 p_ldap_user.user_name :=null;
3648 while i is not null loop
3649 l_v:=p_ldap_user.user_data(p_ldap_user.NickName_ATT_NAME)(i);
3650
3651
3652 BEGIN
3653 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3654 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module, ' testing nna:'||l_v);
3655 END IF;
3656
3657 select user_id into p_ldap_user.user_id from fnd_user where
3658 user_name=l_v and user_guid=p_ldap_user.user_guid;
3659
3660 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3661 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module, ' there it is user_uid:'||p_ldap_user.user_id);
3662 END IF;
3663 if (p_ldap_user.user_name is null) THEN
3664 p_ldap_user.user_name := l_v;
3665 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3666 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module, ' tyring with '||l_v);
3667 END IF;
3668 ELSIF (length(p_ldap_user.user_name)>length(l_v)) THEN
3669 p_ldap_user.user_name:= l_v;
3670 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3671 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module, ' Better with shorter : '||l_v);
3672 END IF;
3673 END IF;
3674
3675 i:= p_ldap_user.user_data(p_ldap_user.NickName_ATT_NAME).next(i);
3676
3677 --- multilink will be a disaster if a user with serveral nna matches several Ebz users
3678 EXCEPTION WHEN NO_DATA_FOUND THEN
3679 i:= p_ldap_user.user_data(p_ldap_user.NickName_ATT_NAME).next(i);
3680
3681 END;
3682 end loop;
3683 if (p_ldap_user.user_name is null) THEN
3684 IF (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3685 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module, ' bad luck, using the first one then '||p_ldap_user.user_data(p_ldap_user.NickName_ATT_NAME)(0));
3686 END IF;
3687
3688 p_ldap_user.user_name := p_ldap_user.user_data(p_ldap_user.NickName_ATT_NAME)(0);
3689 END IF;
3690 END IF;
3691
3692 EXCEPTION WHEN OTHERS THEN
3693 IF (fnd_log.LEVEL_EXCEPTION >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
3694 fnd_log.string(fnd_log.LEVEL_EXCEPTION, l_module , sqlerrm);
3695 END IF;
3696 raise;
3697
3698 END ProcessLoadedLpadUserRecord;
3699
3700
3701 function CanPopulate( attr in varchar2 , user_name in varchar2 , realm in varchar2) return boolean
3702 IS
3703 x_fnd pls_integer;
3704 x_oid pls_integer;
3705 vAttr varchar2(200) := attr;
3706 BEGIN
3707
3708 FND_SSO_REGISTRATION.is_operation_allowed (
3709 p_direction => FND_LDAP_WRAPPER.G_EBIZ_TO_OID,
3710 p_entity => FND_LDAP_WRAPPER.G_IDENTITY,
3711 p_operation => FND_LDAP_WRAPPER.G_ADD,
3712 p_attribute => vAttr,
3713 x_fnd_user => x_fnd,
3714 x_oid => x_oid,
3715 p_user_name => user_name,
3716 p_realm_dn => realm);
3717 return x_oid=FND_LDAP_WRAPPER.G_SUCCESS;
3718
3719 END CanPopulate;
3720
3721 function CanUpdate( attr in varchar2 , user_name in varchar2 , realm in varchar2, x_user_creation in boolean default FALSE ) return boolean
3722 IS
3723 x_fnd pls_integer;
3724 x_oid pls_integer;
3725 vAttr varchar2(200) := attr;
3726 BEGIN
3727 IF (x_user_creation) THEN
3728 return CanPopulate(attr,user_name,realm);
3729 ELSE
3730 FND_SSO_REGISTRATION.is_operation_allowed (
3731 p_direction => FND_LDAP_WRAPPER.G_EBIZ_TO_OID,
3732 p_entity => FND_LDAP_WRAPPER.G_IDENTITY,
3733 p_operation => FND_LDAP_WRAPPER.G_MODIFY,
3734 p_attribute => vAttr,
3735 x_fnd_user => x_fnd,
3736 x_oid => x_oid,
3737 p_user_name => user_name,
3738 p_realm_dn => realm);
3739 return x_oid=FND_LDAP_WRAPPER.G_SUCCESS;
3740 END IF;
3741
3742 END CanUpdate;
3743
3744
3745 end fnd_ldap_user;
[Home] [Help]