7)How to Change User Password

Users log in to Oracle Applications using a client web browser. From the Oracle Applications Login page, users access the E-Business Suite Home Page(AppsLocalLogin.jsp) which provides a single point of access to HTML-based applications, forms-based applications, and Business Intelligence applications.

Here we will discuss how can we change the user password with the help of oracle provided API.

Basic Requirement

Our basic requirement is to change the user password programatically and also force the user to change the password(which was changed through API) on first login.

Assumption

Following assumptions are hold true for the solution approach.

1) The password is maintained at FND_USER level (not portal single-signon).

Solution Approach

The password can be changed with the help of following Oracle APIs FND_USER_PKG.CHANGE_PASSWORD

But the main problem we faced after resetting the password with the help of the above mentioned API is that, it doesn't prompt for change password on first login.But when we change the password for the same user from FND_USER form (Navigation:- System Administrator >> Security : User >> Define) it prompt for change password on first login.

Now the question comes why oracle is not asking for change password when we change it through API.

The answer can be found if we scan the records in FND_USER table. Now consider there are two rows one is updated with the API(password updated with API) and the 2nd row is update with the help of Form. If we compare the column value, then we can easily able to notice that The "PASSWORD_DATE" column value is different.

There is a sysdate (System Date) in one row which was updated through API and there is a null value which was updated via Form.

The PASSWORD_DATE column holds the date the current password was set.If the date present in the column it is interpreted as "User has changed the old password with the new password".

Now to avoid the above problem we have to use the following API instead of using above mentioned API.

API Name:- FND_USER_PKG.UPDATE_USER

We need to send null value** to the X_PASSWORD_DATE parameter.

** ==> Here Null value doesn't mean the NULL. The null value must be same as the variable null_date defined in FND_USER_PKG.

Null_Date := to_date('2','J');

Here we have used dbms_random.string to generate random alpha-numeric number as password.

dbms_random.string

dbms_random.string(opt,len);

opt:- specifies that the returned string may contain

'u','U' : upper case alpha characters only

'l','L' : lower case alpha characters only

'a','A' : alpha characters only (mixed case)

'x','X' : any alpha-numeric characters (upper)

'p','P' : any printable characters

len:- Length of the random string

Code

DECLARE

l_unenc_pwd VARCHAR2(1000);

CURSOR c1 IS

SELECT *

FROM fnd_user fu

WHERE fu.user_name='001C.ARNAUD@GMAIL.COM';

BEGIN

FOR I IN c1 LOOP

l_unenc_pwd:=dbms_random.string('x',6);

fnd_user_pkg.UpdateUser(x_user_name=>'001C.ARNAUD@GMAIL.COM'

,x_owner=>'SEED'

,x_unencrypted_password=>l_unenc_pwd

,x_password_date=>to_date('2', 'J')

,x_user_guid=>I.user_GUID

);

COMMIT;

END LOOP;

END;

Note:- 1) Earlier Oracle Applications, user passwords were treated as case insensitive. Now, Oracle Applications user passwords can optionally be treated as case sensitive, depending on the mode you choose. Case-sensitivity in passwords is controlled by the profile option Password Case Option.

2) How do implement the forgot password functionality?

Answer:- To implement the functionality set the following profile option at site level

Profile Option Name :- Local Login Mask

The values should be as mentioned below

USERNAME_HINT = 01

PASSWORD_HINT = 02

CANCEL_BUTTON = 04

FORGOT_PASSWORD_URL = 08

REGISTER_URL = 16

LANGUAGE_IMAGES = 32

SARBANES_OXLEY_TEXT = 64

To implement the language image and the forgot password URL, the profile option value should be 32 + 08 = 40

Disclaimer:- This is a knowledge sharing site. This topic talks about a custom solution. Oracle Corporation may not provide you a support for any data corruption or any other problem in your custom code/problem arises because of the custom code. The author is not responsible for any kind of system/data problem appears because of usages of this code.Reader/implementer must do it on his/her own risk/responsibility.

References

1) https://metalink.oracle.com