The below method 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’;
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’);
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.
— 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
Oracle APPS DBA Scenario's 