How to Find password of a User in Oracle Apps(11i\R12)?

This can be done in 3 steps. 

Step 1. Create below package

CREATE OR REPLACE PACKAGE xx_get_user_pwd

AS FUNCTION decrypt ( KEY IN VARCHAR2 ,VALUE IN VARCHAR2 ) RETURN VARCHAR2;

END xx_get_user_pwd;
/

CREATE OR REPLACE PACKAGE BODY xx_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 xx_get_user_pwd;
/

Step 2. Query to get password for apps user

Select (Select XX_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 XX_ 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 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,

       XX_Get_User_Pwd.Decrypt((Select (Select 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';

More information about Password security in Oracle

•   Oracle application user passwords can be setup as Case sensitive this profile option ‘Signon Password Case’, based on this Oracle will accept case sensitive password for front-end application users.

    

  

•  ‘Signon Password Case’ Profile can be setup at site level or Individual user level and this profile is available in 11i\R12 irrespective of Data base version

   

• Back-end data base passwords can be case sensitive from DB version 11g (it can be disabled) and in 10g below version data base can’t store case sensitive passwords for DB back-end users.