Sunday, March 18, 2018

How to get Oracle Application User Password from database - oracle.apps.fnd.security.WebSessionManagerProc.decrypt













This is purely for knowledge purpose and may be useful in test instances. 

Step 1: Compile the below package in APPS schema

-- APPS SCHEMA
DROP PACKAGE get_pwd;

CREATE OR REPLACE PACKAGE get_pwd
AUTHID CURRENT_USER
IS
  FUNCTION decrypt(key   IN VARCHAR2,
                   value IN VARCHAR2
                                 ) return VARCHAR2;
end get_pwd;
/

CREATE OR REPLACE PACKAGE BODY get_pwd
AS
  FUNCTION decrypt_java(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';

  FUNCTION decrypt(key   IN VARCHAR2,
                   value IN VARCHAR2
                                 ) return VARCHAR2
  AS
  BEGIN
    IF user = 'APPSVIEWER' -- Schema from where password should not be shown
        THEN
          RETURN NULL;
        ELSE
      RETURN (decrypt_java(KEY,VALUE));
        END IF;
  END;
END get_pwd;

Step 2: Below query will help you to get password

SELECT usr.user_name,usr.user_id,usr.description,
    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 IN ('SYSADMIN');

6 Responses to “How to get Oracle Application User Password from database - oracle.apps.fnd.security.WebSessionManagerProc.decrypt”

Anonymous said...
May 22, 2020 at 4:19 AM

Its a informative one!!


Rana Nadeem said...
May 28, 2020 at 11:11 PM

Very useful thanks a lot for sharing this query


Cherry said...
July 20, 2021 at 4:51 AM

It is giving password with null.


Preet said...
September 18, 2021 at 7:11 AM

It is incredible. Getting password is a piece of cake now.


Anonymous said...
November 5, 2022 at 8:42 AM

Excellent Thanks a lot for the Query


Guacamole Recipes said...
April 29, 2023 at 11:33 PM

Lovely blog you haave


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.