Find Oracle APPS User Password From Backend
In three steps we can find any user name’s password in oracle apps
Step 1. Create below package
This package is used to decrypt the user's password.
CREATE OR REPLACE PACKAGE get_user_pwd
AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2;
END get_user_pwd;
/
CREATE OR REPLACE PACKAGE BODY get_user_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_user_pwd;
/
Step 2. Query to get password for apps user
SELECT (SELECT get_user_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)
);
above query works only in 11i since Oracle removed ‘Guest User Password’ profile option from Release 12 so please use below query for R12
/***********************************************************************************/
/* QUERY TO GET APPS USER PASSWORD FOR BACK END IN R12 */
/***********************************************************************************/
ALTER SESSION SET current_schema = apps;
SELECT (SELECT get_user_pwd.decrypt
(fnd_web_sec.get_guest_username_pwd,
usertable.encrypted_foundation_password
)
FROM DUAL) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE
(SELECT SUBSTR (fnd_web_sec.get_guest_username_pwd,
1,
INSTR (fnd_web_sec.get_guest_username_pwd, '/') - 1
)
FROM DUAL);
Step 3. Query to get password for application user
SELECT usertable.user_name,
(SELECT get_user_pwd.decrypt
(UPPER
((SELECT (SELECT get_user_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');
above query works only in 11i since Oracle removed ‘Guest User Password’ profile option from Release 12 so please use below query for R12
/***********************************************************************************/
/* QUERY TO GET APPS USER PASSWORD FOR BACK END IN R12 */
/***********************************************************************************/
ALTER SESSION SET current_schema = apps;
SELECT usr.user_name, usr.description,
get_user_pwd.decrypt
((SELECT (SELECT get_user_pwd.decrypt
(fnd_web_sec.get_guest_username_pwd,
usertable.encrypted_foundation_password
)
FROM DUAL) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name =
(SELECT SUBSTR
(fnd_web_sec.get_guest_username_pwd,
1,
INSTR
(fnd_web_sec.get_guest_username_pwd,
'/'
)
- 1
)
FROM DUAL)),
usr.encrypted_user_password
) PASSWORD
FROM fnd_user usr
WHERE usr.user_name = '&User_Name';
