Oracle Apps and FND Password Decryption

This one I just found in some old emails. There are many blogs available on this topic, I just tried to compile it in a simpler way. Anyways it's really handy if you have lost apps, sysadmin or any FND user password and would like to retrieve it without resetting. 

These SQLs work in Oracle E-Business Suite R12 environments. 

 Log in as APPS  on any E-Business Suite instance:

SQL> SELECT USER, NAME FROM V$DATABASE;

USER                           NAME

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

APPS                           DBA1

Only thing you need to do is to create a function (you can drop it later).

SQL> CREATE OR REPLACE FUNCTION NAVDECRYPT (KEY IN VARCHAR2, VALUE IN VARCHAR2) RETURN VARCHAR2

  2  AS LANGUAGE JAVA

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

  4  /

Function created.

That's it. You are now ready to read passwords. 


How to get apps Password ?

SQL> (SELECT (SELECT NAVDECRYPT(FND_WEB_SEC.GET_GUEST_USERNAME_PWD,A.ENCRYPTED_FOUNDATION_PASSWORD) FROM DUAL) FROM FND_USER A WHERE A.USER_NAME='GUEST');

(SELECTNAVDECRYPT(FND_WEB_SEC.GET_GUEST_USERNAME_PWD,A.ENCRYPTED_FOUNDATION_PASS

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

APPS

SQL>

How to get FND user password ?

SQL> SELECT (SELECT NAVDECRYPT((SELECT (SELECT NAVDECRYPT(FND_WEB_SEC.GET_GUEST_USERNAME_PWD,A.ENCRYPTED_FOUNDATION_PASSWORD) FROM DUAL) FROM FND_USER A WHERE A.USER_NAME='GUEST'),B.ENCRYPTED_USER_PASSWORD) FROM DUAL) FROM FND_USER B WHERE B.USER_NAME='&USER_NAME';

Enter value for user_name: SYSADMIN

old   1: A.USER_NAME='GUEST'),B.ENCRYPTED_USER_PASSWORD) FROM DUAL) FROM FND_USER B WHERE B.USER_NAME='&USER_NAME'

new   1: A.USER_NAME='GUEST'),B.ENCRYPTED_USER_PASSWORD) FROM DUAL) FROM FND_USER B WHERE B.USER_NAME='SYSADMIN'

(SELECTNAVDECRYPT((SELECT(SELECTNAVDECRYPT(FND_WEB_SEC.GET_GUEST_USERNAME_PWD,A.

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

sysadmin

SQL>

And finally drop the function when the job is done :)  

For Release 11i replace  FND_WEB_SEC.GET_GUEST_USERNAME_PWD by FND_PROFILE.VALUE ('GUEST_USER_PWD').

And oh yes, how would you login to apps schema if you don't have the password ? The sql will give you apps password only when you're in as apps.  

Well there is a way, Login to database sqlplus  '/as sysdba' and run

SQL> alter session set current_schema=apps; 

before creating function.


Hope you enjoy it J

Regards,

N@v