Apps password not working

 Someone changed apps password using 'alter user' which is not a good practice.

Now during a clone, password change is not working using FNDCPASS.


1) SEC_CASE_SENSITIVE_LOGON parameter is already set to FALSE

2) select fnd_web_sec.validate_login('GUEST','ORACLE') from dual;

FND_WEB_SEC.VALIDATE_LOGIN('GUEST','ORACLE')
--------------------------------------------------------------------------------
Y
3) There is no error on FNDCPASS but password change is not successful.



So, this is what we did as a fix,


Changed apps password to default apps/apps -- using 'alter user'.

Updated password in FND_ORACLE_USERID to apps using encrypted value.

and then re-tried FNDCPASS, this time it was successful.


EBSPROD> create table FND_ORACLE_USERID_BAK as (select * from FND_ORACLE_USERID);

Table created.

EBSPROD> create table FND_USER_BAK as (select * from FND_USER);

Table created.


SQL> ALTER USER apps IDENTIFIED BY apps;

User altered.

SQL> ALTER USER applsys IDENTIFIED BY apps;

User altered.

Below is the value for encrypted apps password --


SQL> update apps.FND_ORACLE_USERID set ENCRYPTED_ORACLE_PASSWORD = 'ZGA34EA20B5C4C9726CC95AA9D49EA4DBA8EDB705CB7673E645EED570D5447161491D78D444554655B87486EF537ED9843C8' where ORACLE_USERNAME in ('APPS', 'APPLSYS');

2 rows updated.

SQL> commit;

Commit complete.

#######################

col user_name for a30

select USER_NAME from APPS.fnd_user order by 1;

CREATE FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2)

RETURN VARCHAR2

     AS

LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';

/

######################

SYSADMIN

=========

SELECT APPS.decrypt_pin_func('GUEST/ORACLE',(SELECT ENCRYPTED_FOUNDATION_PASSWORD from APPS.fnd_user where USER_NAME='GUEST')) from dual;

SELECT ENCRYPTED_USER_PASSWORD FROM APPS.FND_USER where USER_NAME='SYSADMIN';

SELECT APPS.decrypt_pin_func('&appspwd','&sysadminkey') from dual;