Thursday 18 April 2013

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

No comments:

Post a Comment