Wednesday, August 5, 2015

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

No comments:

Post a Comment