Simple Steps to Find Password for an Application User in Oracle Applications

STEP 1. CREATE GET_PWD PACKAGE SPECIFICATION, AS SHOWN BELOW.

CREATE OR REPLACE PACKAGE get_pwd

AS

FUNCTION decrypt(

KEY IN VARCHAR2 ,

VALUE IN VARCHAR2 )

RETURN VARCHAR2;

END get_pwd;

/



STEP 2. CREATE GET_PWD PACKAGE BODY, AS SHOWN BELOW.

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;

/



Step 3. Query TO get password FOR apps user.

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

)

)



STEP 4. QUERY TO GET PASSWORD FOR ANY APPLICATION USER.

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