EBS - Retrieve EBS Application User and EBS Apps Schema paswords

There are times, when you will need to retrieve apps schema password or any EBS application user passwords.

To achieve this;
Supposing you are the admin and you have the apps password;

Connect to database with APPS user;

And create a package (get_pwd) with the following statement;
CREATE OR REPLACE PACKAGE get_pwd AS FUNCTION decrypt (KEY IN VARCHAR2,VALUE IN VARCHAR2)
RETURN VARCHAR2;
END get_pwd;
/

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

Then by using get_pwd fuction created above, passwords can be decyrpted and retrieved.

-- TO GET APPS PASSWORD

Retrieving Apps Password Terminal Screenshot

Query :

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 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))

-- TO GET PASSWORD OF A USER

EBS Application user passwords can also be decyrpted and retrieved.

Query:

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
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
fnd_user usertable
WHERE
usertable.user_name LIKE upper(:b1)