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

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

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.