Forgot your password?    
+ Reply to Thread
Results 1 to 2 of 2

Thread: How To Find / Decrypt the Apps Password Oracle Applications 11i / R12

  1. #1
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    New Delhi, India
    Posts
    427

    How To Find / Decrypt the Apps Password Oracle Applications 11i / R12

    Method to find out the lost Apps password.

    Requirements:
    Need have system password for the database.

    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/<pwd> @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_objectswhere 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_valueswhere profile_option_id = (select profile_option_id from apps.fnd_profile_optionswhere 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';


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

    -- This is the APPS Password

    SQL> drop synonym FND_WEB_SEC_TEST;

    Synonym dropped.

    sqlplus apps/APPS @AFSCJAVS.pls

    Package created.

    Commit complete.

    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 useraccount passwords and the FND_ORACLE_USERID table stores internal Oracle Applications database account passwords,Both tables use thesame 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_SECpackage in Apps.The DECRYPT function in the FND_WEB_SEC package isa 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

  2. #2
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    Delhi, India, India
    Posts
    128
    nice trick...
    Regards,
    -Amit

+ Reply to Thread

Similar Threads

  1. Find Master Node in Oracle RAC
    By Hemant in forum RAC Concepts and Administration
    Replies: 0
    Last Post: 11-15-2012, 06:12 PM
  2. How to reset Weblogic Administrator Password
    By Hemant in forum Oracle Fusion Middleware 11G - OIM , OAM , ODS , Weblogic , Webgate ..
    Replies: 0
    Last Post: 06-07-2012, 03:56 PM
  3. How to run ssh without password
    By devesh_ocp in forum RAC Concepts and Administration
    Replies: 0
    Last Post: 04-24-2012, 04:20 PM
  4. Find Versions of Different Oracle Apps Components
    By Amit in forum SCRIPTS : Users,Profiles,Applications,Responsibilities,Forms,JDBC,Patch,Versions,Products
    Replies: 0
    Last Post: 11-01-2011, 03:30 PM
  5. Query to find who and when update an Oracle Application Profile Options.
    By Amit in forum SCRIPTS : Users,Profiles,Applications,Responsibilities,Forms,JDBC,Patch,Versions,Products
    Replies: 0
    Last Post: 11-01-2011, 10:48 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics