How to find out Apps password when we dont know the old password.

How to find out Apps password when we dont know the old password.


The below methog helps us to find out when we lost the Apps password, Please note you need have system password to perform the below actions.The below method is not suggested for Production or Critical systems.




Login to Middle Tier as ap user.
-------------------------------


$cd $FND_TOP/patch/115/sql


$cp AFSCJAVS.pls AFSCJAVS.pls.orig


Add following lines to the AFSCJAVS.pls file




CREATE OR REPLACE PACKAGE apps.fnd_web_sec AS




/*--------------------------------------------------------------*/


function decrypt(key in varchar2, value in varchar2)
return varchar2;


/*--------------------------------------------------------------*/




$sqlplus system/ @AFSCJAVS.pls


Package created.




Commit complete.




as system excute the following to find out if anything marked as invalid.


SQL> show user
USER is "SYSTEM"


SQL>col OBJECT_NAME for a30;
set line 200;
select object_name,object_type,status,owner from dba_objects where object_name='FND_WEB_SEC';


SQL>col OBJECT_NAME for a30;
set line 200;
select object_name,object_type,status,owner from dba_objects where object_name='FND_WEB_SEC';


OBJECT_NAME OBJECT_TYPE STATUS OWNER
------------------------------ ------------------ ------- ------------------------------
FND_WEB_SEC PACKAGE VALID APPS
FND_WEB_SEC PACKAGE BODY INVALID APPS


SQL> alter PACKAGE apps.FND_WEB_SEC compile body;


Package body altered.




If there are any invalids compile them.


Obtain Guest user ID and password.

as system excute the following.


SQL> show user
USER is "SYSTEM"


SQL> select profile_option_value from apps.fnd_profile_option_values where
profile_option_id = (select profile_option_id from apps.fnd_profile_options where
profile_option_name='GUEST_USER_PWD');
2 3
PROFILE_OPTION_VALUE
----------------------------------------------------------------------------------------------------
GUEST/ORACLE


SQL> show user
USER is "SYSTEM"


SQL>create synonym FND_WEB_SEC_TEST for apps.FND_WEB_SEC;


Synonym created.


SQL> show user
USER is "SYSTEM"
SQL> create synonym FND_WEB_SEC_TEST for apps.FND_WEB_SEC;


Synonym created.


-- Now Obtain Apps password using the below sql :)


SQL> SELECT(
SELECT
fnd_web_sec_test.decrypt('GUEST/ORACLE',encrypted_foundation_password)
FROM dual
)AS APPS_PASSWORD
FROM
apps.fnd_user
WHERE
user_name like 'GUEST'; 2 3 4 5 6 7 8 9


APPS_PASSWORD
----------------------------------------------------------------------------------------------------
APPS


-- WOW we got the Apps password now


SQL> drop synonym FND_WEB_SEC_TEST;


Synonym dropped.




-bash-3.2$ sqlplus apps/ @AFSCJAVS.pls




Package created.




Commit complete.






Please find the below explanation for the above steps:
---------------------------------------------------------------


A)
We know in Oracle Applications passwords are stored in FND_USER and FND_ORACLE_USERID tables,The FND_USER table stores application user account passwords and
the FND_ORACLE_USERID table stores internal Oracle Applications database account passwords,Both tables use the same encryption algorithm to protect the passwords.




The FND_USER table contains all the application accounts.There are two password columns in this table: ENCRYPTED_FOUNDATION_PASSWORD and ENCRYPTED_USER_PASSWORD.


====================================================
Column Value Encryption Key
===================================================
ENCRYPTED_FOUNDATION_PASSWORD APPS username/password
ENCRYPTED_USER_PASSWORD user APPS password




The 2 columns as...


a)If we know the username/password we can get the Apps Password (ENCRYPTED_FOUNDATION_PASSWORD)
b)If we know the Apps password we can get any users password (ENCRYPTED_USER_PASSWORD)




B)


To decrypt and verify user passwords Oracle Apps uses FND_WEB_SEC package in Apps.The DECRYPT function in the FND_WEB_SEC package is a local function and cannot be called from outside the package.


So to make the function available outside the package,we have altered the AFSCJAVS.pls above.