To find forgotten Apps and Sysadmin password in R12

To find forgotten Apps Password

1. Connect sqlplus via sysdba

sqlplus / as sysdba

2. Create a function as follows

create or replace FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,in_chr_encrypt_pin IN VARCHAR2) RETURN VARCHAR2
AS
LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
/

3. Get the Encrypted password for GUEST user

select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST';

4. Get the apps password using encrypted password return in step 3

SELECT apps.decrypt_pin_func('GUEST/ORACLE','<ENCRYPTED_FOUNDATION_PASSWORD>') from dual;

To find forgotten Sysadmin Password

1. Connect sqlplus via sysdba

sqlplus / as sysdba

2. Create a package an package body as follows

CREATE OR REPLACE PACKAGE XXARTO_GET_PWD AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2;
END XXARTO_GET_PWD;

CREATE OR REPLACE PACKAGE BODY XXARTO_GET_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 XXARTO_GET_PWD;

4. Get the password for sysadmin using below script

SELECT Usr.User_Name,
Usr.Description,
XXARTO_GET_PWD.Decrypt (
(SELECT (SELECT XXARTO_GET_PWD.Decrypt (
Fnd_Web_Sec.Get_Guest_Username_Pwd,
Usertable.Encrypted_Foundation_Password)
FROM DUAL)
AS Apps_Password
FROM applsys.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 applsys.Fnd_User Usr
WHERE Usr.User_Name = 'SYSADMIN';