FINDING USER PASSWORDS IN ORACLE APPLICATIONS - 11i & R12
Step 1. Create a package as below
Create or replace package xxa_get_user_pwd
As Function decrypt ( key in varchar2 ,value in
varchar2 ) RETURN VARCHAR2;
END xxa_get_user_pwd;
/
CREATE OR REPLACE PACKAGE BODY xxa_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 xxa_get_user_pwd;
/
Step 2. Query to get password for apps user
Select (Select XXA_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 */
/-------------------------------------------------------------------------------------------------/
ALTER SESSION SET current_schema = apps;
Select (Select XXA_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
XX_Get_User_Pwd.Decrypt (Upper ((Select
(Select
XXA_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 THE PASSWORD FOR THE FRONTEND USER LOGIN */
/----------------------------------------------------------------------------------------------------------------/
ALTER SESSION SET current_schema = apps;
Select Usr.User_Name, Usr.Description,
XXA_Get_User_Pwd.Decrypt((Select (Select XXA_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';
