Finding password of a User in Oracle Apps(11i\R12)?

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';