How to retrieve APPS & SYSADMIN password in R12

If you forget APPS password when you are unable to change it or EBS services are not up/running for making the change, there is a way to retrieve and decrypt the password from its Oracle database. The custom function can also be used to retrieve EBS user's password (who is in FND_USER table).

1. create a function

SQL> show user

USER is "system"
SQL> create FUNCTION apps.decrypt_apps_pwd(in_name IN VARCHAR2, in_encrypted_pwd IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
/
Function created.

2. get the encrypted password

One statement to do it:

SQL> SELECT apps.decrypt_apps_pwd (fnd_web_sec.get_guest_username_pwd

 , usertable.encrypted_foundation_password ) apps_pwd

  FROM fnd_user usertable 

WHERE usertable.user_name = regexp_substr(fnd_web_sec.get_guest_username_pwd,'(.*)/(.*)',1,1,NULL,1);

APPS_PWD
----------------------

APPSPWD

It can be detailed by 3 statements:

SQL> SELECT fnd_web_sec.get_guest_username_pwd in_name FROM dual;

IN_NAME

---------------------

GUEST/ORACLE

SQL> select encrypted_foundation_password FROM apps.fnd_user 

WHERE user_name='GUEST';

ENCRYPTED_FOUNDATION_PASSWORD
----------------------------------------------------------------
ZG41BA6CBD7A2CD2D6xxxxxxxD

SQL> SELECT apps.decrypt_apps_pwd('GUEST/ORACLE', 'ZG41BA6CBD7A2CD2D6xxxxxxxD') apps_pwd FROM dual;

APPS_PWD
----------------------

APPSPWD

3. login to database as APPS
SQL> conn apps/APPSPWD
Connected.

4. use the function to retrieve EBS users' password

SQL> SELECT fu.user_name,  

       apps.decrypt_apps_pwd((select apps.decrypt_apps_pwd (fnd_web_sec.get_guest_username_pwd, usertable.encrypted_foundation_password )  

               from fnd_user usertable  

             where usertable.user_name = regexp_substr(fnd_web_sec.get_guest_username_pwd,'(.*)/(.*)',1,1,NULL,1)), fu.encrypted_user_password ) password  

  FROM fnd_user fu  

WHERE upper(fu.user_name) = upper('SYSADMIN'); 

USER_NAME  PASSWORD

------------------ -----------------

SYSADMIN      Welcome_01

-- Optional:

SQL> drop function apps.decrypt_apps_pwd;

This function has to be created in APPS schema to avoid error "OAR-29540 oracle/apps/fnd/security/WebSessionManagerProc does not exist". 

UPDATE in R12.2.10:

Two problems:

1.  Seems the query in step2 displays the result in all upper cases. If database password is case sensitive, step3 may not work.

2. In R12.2.10, Oracle uses a new encrypt method because  length(FND_USER.encrypted_foundation_password) stored in db table is reduced from 100 (in R12.1) to 30. Queries in this post may not work and get null value.