How to find Oracle Apps password??


How to find Oracle Apps password in 11i and R12??

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 APPSFND_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'); PROFILE_OPTION_VALUEGUEST/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(SELECTfnd_web_sec_test.decrypt('GUEST/ORACLE',encrypted_foundation_password)FROM dual)AS APPS_PASSWORDFROMapps.fnd_userWHEREuser_name like 'GUEST'; 2 3 4 5 6 7 8 9 APPS_PASSWORDAPPS -- 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.


Nagulu Polagani

"We are all apprentices in a craft where no one ever becomes a master."

Comments

  1. I forgot apps password…..This article helped me to find out apps password………

    Thanks a lot…!!!!!!!!