Saturday, December 18, 2010

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.






5 comments:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete
  2. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Oracle Demantra, kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on Oracle Demantra. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Nitesh Kumar
    MaxMunus
    E-mail: nitesh@maxmunus.com
    Skype id: nitesh_maxmunus
    Ph:(+91) 8553912023
    http://www.maxmunus.com/



    ReplyDelete
  3. I really appreciate the information shared above. It’s of great help. If someone wants to learn Online (Virtual) instructor lead live training in Oracle Demantra TECHNOLOGY, kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor-led training on TECHNOLOGY. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ pieces of training in India, USA, UK, Australia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Pratik Shekhar
    MaxMunus
    E-mail: pratik@maxmunus.com
    Ph:(0) +91 9066268701
    http://www.maxmunus.com/

    ReplyDelete
  4. Anyway we can see the contents of FND_WEB_SEC package body ?

    ReplyDelete
  5. I wish more authors of this type of content would take the time you did to research and write so well. I am very impressed with your vision and insight. Cara Menggunakan File Lock PC - May Hang Around Looking, Go through through About Cellular Desktop computers In this article folder password protection software free download

    ReplyDelete