Get the Password of a User In EBS (11i & R12)

=======================================================--Package To Be Created=======================================================

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

--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;
/

====================================================

Find apps password in 11i

====================================================

SELECT (SELECT GET_PWD.Decrypt (
                  UPPER( (SELECT UPPER (Fnd_Profile.VALUE ('Guest_User_Pwd'))
                            FROM DUAL)),
                  Usertable.Encrypted_Foundation_Password)
          FROM DUAL)
          AS Apps_Password
  FROM applsys.Fnd_User Usertable
 WHERE Usertable.User_Name LIKE
          UPPER( (SELECT SUBSTR (
                            Fnd_Profile.VALUE ('Guest_User_Pwd'),
                            1,
                            INSTR (Fnd_Profile.VALUE ('Guest_User_Pwd'), '/')
                            - 1)
                    FROM DUAL));
 
 

=======================================================Find apps password in R12=======================================================
SELECT (SELECT GET_PWD.Decrypt (
                  Fnd_Web_Sec.Get_Guest_Username_Pwd,
                  Usertable.Encrypted_Foundation_Password)
          FROM DUAL)
          AS Apps_Password
  FROM applsys.Fnd_User Usertable
 WHERE Usertable.User_Name LIKE
          (SELECT SUBSTR (
                     Fnd_Web_Sec.Get_Guest_Username_Pwd,
                     1,
                     INSTR (Fnd_Web_Sec.Get_Guest_Username_Pwd, '/') - 1)
             FROM DUAL);

=============================================================
Find application user password (for exaample SYSADMIN) in 11i=============================================================

SELECT Usertable.User_Name,
       (SELECT GET_PWD.Decrypt (
                  UPPER( (SELECT (SELECT GET_PWD.Decrypt (
                                            UPPER( (SELECT UPPER(Fnd_Profile.VALUE('Guest_User_Pwd'))
                                                      FROM DUAL)),
                                            Usertable.Encrypted_Foundation_Password)
                                    FROM DUAL)
                                    AS Apps_Password
                            FROM applsys.Fnd_User Usertable
                           WHERE Usertable.User_Name LIKE
                                    UPPER( (SELECT SUBSTR (
                                                      Fnd_Profile.VALUE (
                                                         'Guest_User_Pwd'),
                                                      1,
                                                      INSTR (
                                                         Fnd_Profile.VALUE (
                                                            'Guest_User_Pwd'),
                                                         '/')
                                                      - 1)
                                              FROM DUAL)))),
                  Usertable.Encrypted_User_Password)
          FROM DUAL)
          AS Encrypted_User_Password
  FROM Applsys.Fnd_User Usertable
 WHERE Usertable.User_Name LIKE UPPER ('&Username');
 
 

===================================================================Find application user password (for exaample SYSADMIN) in R12====================================================================
SELECT Usr.User_Name,
       Usr.Description,
       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 applsys.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 applsys.Fnd_User Usr
 WHERE Usr.User_Name = '&User_Name';

--Query to execute
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';