How to get apps password and application user password in R12/11i - With Clear fundas

Step -1 

Create get_pwd package specification as below.

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


Step -2 

Create get_pwd package body as below.

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;


Step-1 and step-2 are common in both R12 and 11i
For executing get_pwd package u need access on

'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';

class file.Generally apps user will have this access , so you need to create the above package in APPS package.

Now For 11i

In 11i oracle has defined one seeded profile option GUEST_USER_PWD (Guest User Password)
This profile option holds the value 'GUEST/ORACLE' in almost all the 11i instances.

===========================================

ALTER SESSION SET current_schema = apps;

===========================================

For both step-3 and step-4 execute the above or log in as apps 

Step-3 

Query to get apps password

SELECT (SELECT get_pwd.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)
                );


Step-4 

Query to get application user password


SELECT usertable.user_name,
       (SELECT get_pwd.decrypt
                  (UPPER
                      ((SELECT (SELECT get_pwd.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 'SYSADMIN';


Now in R12

In R12 oracle has removed GUEST_USER_PWD (Guest User Password) Profile option.

So the above queries will not work.

oracle has provided new seeded Procedure fnd_web_sec.get_guest_username_pwd which will help us to find out user password.

===========================================

ALTER SESSION SET current_schema = apps;

===========================================

For both step-3 and step-4 execute the above or log in as apps 

Step-3 

Query to get apps password in R12.

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 LIKE
          (SELECT SUBSTR (fnd_web_sec.get_guest_username_pwd,
                          1,
                          INSTR (fnd_web_sec.get_guest_username_pwd, '/') - 1
                         )
             FROM DUAL);


Step-4 

Query to get application user password in R12.


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 LIKE 'SYSADMIN';

==============================================================

Now if you have gone through the step-3 and step-4 queries for both 11i and R12.
we can simplified the queries as below :)
The below will work in both 11i and R12.

Step-3 

Query to get apps password

In the below query you can replace 'GUEST/ORACLE' with any application username and password.

Let say in the below example :

- user name : GUEST
- password   : ORACLE

SELECT (SELECT get_pwd.decrypt
                           ('GUEST/ORACLE',
                            fu.encrypted_foundation_password
                           )
          FROM DUAL) AS apps_password
  FROM fnd_user fu
 WHERE fu.user_name LIKE 'GUEST';

Let say in the below example :

- user name : TEST_USER_NAME
- password   : TEST_USER_pwd

Note : note that password should be case sensitive. 


SELECT (SELECT get_pwd.decrypt
                           ('TEST_USER_NAME/TEST_USER_pwd',
                            fu.encrypted_foundation_password
                           )
          FROM DUAL) AS apps_password
 FROM fnd_user fu

WHERE fu.user_name LIKE 'TEST_USER_NAME';


Step -4

Query to get application password.

TEST_APPS_PWD - is the apps password

TEST_USER_NAME  - is the application user for whom you want to know the password.


SELECT fu.user_name,
       (SELECT get_pwd.decrypt
                       ('TEST_APPS_PWD',
                        fu.encrypted_user_password
                       )
          FROM DUAL) AS encrypted_user_password
  FROM fnd_user fu

 WHERE fu.user_name LIKE 'TEST_USER_NAME';