Oracle EBS-use PLSQL to get the apps password and the user password in EBS

Version: Tested on R12.0.6/R12.1.1

Note: Adjust the GUEST user password according to the specific environment. The default password is Oracle. The password of the GUEST user can be found in the $ CONTEXT_FILE file:

View $ CONTEXT_FILE

51 <oa_user type = "GUEST">
52 <username oa_var = "s_guest_user"> GUEST </username>
53 <password oa_var = "s_guest_pass"> ORACLE </password>
54 </oa_user>

Usage: Compile the package and call the function.

Create or replace package apps. xxdba_fnd_security
AUTHID CURRENT_USER
AS
-- Guest login can be retrieved from profile GUEST_USER_PWD (Guest User Password) or $ CONTEXT_FILE
FUNCTION get_user_pass (p_fnd_user IN VARCHAR2
, P_guest_login IN VARCHAR2 DEFAULT 'guest/ORACLE ')
RETURN VARCHAR2;
FUNCTION get_pai_pass (p_guest_login IN VARCHAR2 DEFAULT 'guest/ORACLE ')
RETURN VARCHAR2;
FUNCTION encrypt (key IN VARCHAR2
, Value in VARCHAR2)
RETURN VARCHAR2;
FUNCTION decrypt (key IN VARCHAR2
, Value in VARCHAR2)
RETURN VARCHAR2;
END xxdba_fnd_security;
/

Create or replace package body apps. xxdba_fnd_security
AS
FUNCTION encrypt (
Key IN VARCHAR2
, Value in VARCHAR2)
RETURN VARCHAR2
AS
LANGUAGE JAVA
NAME 'oracle. apps. fnd. security. WebSessionManagerProc. encrypt (java. lang. String, java. lang. String) return java. lang. string ';
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 ';

FUNCTION get_pai_pass (p_guest_login IN VARCHAR2 DEFAULT 'guest/ORACLE ')
RETURN VARCHAR2
IS
Rochelle _ encrypted_pass VARCHAR2 (200 );
L_pai_decrypted_pass VARCHAR2 (200 );
BEGIN
-- Get apps encrypted pass
SELECT encrypted_foundation_password
INTO l_assist_encrypted_pass
FROM apps. fnd_user
WHERE user_name = 'guest ';

-- Decrypt apps pass
SELECT decrypt (p_guest_login
, Rochelle into _encrypted_pass)
INTO l_assist_decrypted_pass
From dual;

RETURN l_assist_decrypted_pass;
END get_pai_pass;

FUNCTION get_user_pass (p_fnd_user IN VARCHAR2
, P_guest_login IN VARCHAR2 DEFAULT 'guest/ORACLE ')
RETURN VARCHAR2
IS
Rochelle user_encrypted_pass VARCHAR2 (200 );
Rochelle user_decrypted_pass VARCHAR2 (200 );
BEGIN
-- Get fnd user encrypted pass
BEGIN
SELECT encrypted_user_password
INTO l_user_encrypted_pass
FROM fnd_user
WHERE user_name = p_fnd_user;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN 'user' | p_fnd_user | 'is not exist in FND_USER table ';
END;

-- Decrypt user pass
SELECT decrypt (get_pai_pass (p_guest_login)
, L_user_encrypted_pass)
INTO l_user_decrypted_pass
From dual;

RETURN l_user_decrypted_pass;
END get_user_pass;

END xxdba_fnd_security;
/

/*
Select xxdba_fnd_security.get_user_pass ('sysadmin') from dual;

Select xxdba_fnd_security.get_pass _pass from dual;

Drop package apps. xxdba_fnd_security;
*/