Oracle password recovery method

Sometimes we might have forgotten a password for an Oracle Apps user. We could simply reset the password but we might not want to do so as someone else might be working with this user. Then it would simply be useful to ask him/her for the password, you would say. True, it would be easy. But what happens if the person is on leave or in a different time zone?

There is an option to recover the password

Compile the following package in APPS schema

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

CREATE OR REPLACE PACKAGE get_pwd AS    FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)       RETURN VARCHAR2; END get_pwd; / 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;

This package will decrypt the password for you but you still need to write the query to recover the password. Here is the query.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SELECT usr.user_name,        get_pwd.decrypt ((SELECT (SELECT get_pwd.decrypt (fnd_web_sec.get_guest_username_pwd,                                                          encrypted_foundation_password)                                    FROM DUAL) AS apps_password                            FROM fnd_user                           WHERE 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 = '&USER_NAME';

Execute the query to recover the password.

  

Test the method

We shall reset the password for an Oracle user, 10003, to W3lc0me1. We shall use the API from another article to do so.

1 2 3 4 5

DECLARE    ret   VARCHAR2 (200); BEGIN    ret := fnd_web_sec.change_password ('10003', 'W3lc0me1'); END;

 Now we shall try to login to Oracle with this username and new password.

Login will be successful. Now let us try to recover the password for user 10003.

Execute the following query

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SELECT usr.user_name,        get_pwd.decrypt ((SELECT (SELECT get_pwd.decrypt (fnd_web_sec.get_guest_username_pwd,                                                          encrypted_foundation_password)                                    FROM DUAL) AS apps_password                            FROM fnd_user                           WHERE 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 = '10003';

It will look like this,

Now the password for this Oracle user is recovered but note that the case of the password is UPPER. The logic for this query is to pass the APPS schema password as the key to decrypt the Oracle user’s password. A large portion of the previous query is to recover the APPS database password.

I can run that portion as an individual query to recover the APPS database password also, like this,

1 2 3 4 5 6 7 8 9

-- Get APPS database password SELECT (SELECT get_pwd.decrypt (fnd_web_sec.get_guest_username_pwd, encrypted_foundation_password)           FROM DUAL) AS apps_password   FROM fnd_user  WHERE user_name = (SELECT SUBSTR (fnd_web_sec.get_guest_username_pwd,                                    1,                                    INSTR (fnd_web_sec.get_guest_username_pwd, '/') - 1                                   )                       FROM DUAL)

Again the password is recovered, but in UPPER case.

Note:
The recovered password is always in UPPER case

This means that if we directly pass the APPS database password to the first query, we will get the same result. The APPS password isDEV03 (we got this in the previous query as well). Now let’s change the first query accordingly. It will look like this.

1 2 3 4

SELECT usertable.user_name, (SELECT get_pwd.decrypt (UPPER ('DEV03'), usertable.encrypted_user_password)                                FROM DUAL) AS encrypted_user_password   FROM fnd_user usertable  WHERE usertable.user_name LIKE UPPER ('10003')

Let’s execute the query

We get the password for the user once more but with a shorter query.

======================   **********************  ========================

/* Formatted on 4/18/2013 2:26:11 PM (QP5 v5.114.809.3010) */
SELECT   usr.user_name,
         get_pwd.decrypt (
            (SELECT   (SELECT   get_pwd.decrypt (
                                   fnd_web_sec.get_guest_username_pwd,
                                   encrypted_foundation_password
                                )
                         FROM   DUAL)
                         AS apps_password
               FROM   fnd_user
              WHERE   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-047';



/* Formatted on 4/18/2013 2:26:03 PM (QP5 v5.114.809.3010) */
SELECT   (SELECT   get_pwd.decrypt (fnd_web_sec.get_guest_username_pwd,
                                    encrypted_foundation_password)
            FROM   DUAL)
            AS apps_password
  FROM   fnd_user
 WHERE   user_name =
            (SELECT   SUBSTR (
                         fnd_web_sec.get_guest_username_pwd,
                         1,
                         INSTR (fnd_web_sec.get_guest_username_pwd, '/') - 1
                      )
               FROM   DUAL)
                        


/* Formatted on 4/18/2013 2:25:42 PM (QP5 v5.114.809.3010) */
SELECT   usr.user_name,
         get_pwd.decrypt (
            (SELECT   (SELECT   get_pwd.decrypt (
                                   fnd_web_sec.get_guest_username_pwd,
                                   encrypted_foundation_password
                                )
                         FROM   DUAL)
                         AS apps_password
               FROM   fnd_user
              WHERE   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-047';