In three steps we can find any user name’s password in oracle apps
Step 1. Create below package
CREATE OR REPLACE PACKAGE get_user_pwd
AS FUNCTION decrypt ( KEY IN VARCHAR2
,VALUE IN VARCHAR2 ) RETURN VARCHAR2;
END get_user_pwd;
/
CREATE OR REPLACE PACKAGE BODY get_user_pwd
/
CREATE OR REPLACE PACKAGE BODY 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 get_user_pwd;
/
/
Step 2. Query to get password for apps user
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));
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 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 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');
/***************************************************************************/
/* QUERY TO GET THE PASSWORD FOR THE
FRONTEND USER LOGIN */
/***************************************************************************/
ALTER SESSION SET current_schema = apps;
Select Usr.User_Name, Usr.Description,
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.
Thanks for this. It seems to work nicely for Step 2 i.e. for getting APPS password. However, it doesn't seem to work for Step 3 i.e. for getting an application user's password. It returns NULL for password (it returns user name and description OK). We are on 12.1.1 version with 11G database, does the SQL query need any change to get it working for us?
ReplyDelete