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
This package will decrypt the password for you but you still need to write the query to recover the password. Here is the query.
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.
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
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,
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 is DEV03 (we got this in the previous query as well). Now let’s change the first query accordingly. It will look like this.
Let’s execute the query
We get the password for the user once more but with a shorter query.
Please note that this article is for educational purposes only.
There is an option to recover the password
Compile the following package in APPS schema
01 | CREATE OR REPLACE PACKAGE get_pwd |
02 | AS |
03 | FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2) |
04 | RETURN VARCHAR2; |
05 | END get_pwd; |
06 | / |
07 |
08 | CREATE OR REPLACE PACKAGE BODY get_pwd |
09 | AS |
10 | FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2) |
11 | RETURN VARCHAR2 |
12 | AS |
13 | LANGUAGE JAVA |
14 | NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String'; |
15 | END get_pwd; |
01 | SELECT usr.user_name, |
02 | get_pwd.decrypt ((SELECT (SELECT get_pwd.decrypt (fnd_web_sec.get_guest_username_pwd, |
03 | encrypted_foundation_password) |
04 | FROM DUAL) AS apps_password |
05 | FROM fnd_user |
06 | WHERE user_name = |
07 | (SELECT SUBSTR (fnd_web_sec.get_guest_username_pwd, |
08 | 1, |
09 | INSTR (fnd_web_sec.get_guest_username_pwd, '/') - 1 |
10 | ) |
11 | FROM DUAL)), |
12 | usr.encrypted_user_password) PASSWORD |
13 | FROM fnd_user usr |
14 | WHERE usr.user_name = '&USER_NAME'; |
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 | DECLARE |
2 | ret VARCHAR2 (200); |
3 | BEGIN |
4 | ret := fnd_web_sec.change_password ('10003', 'W3lc0me1'); |
5 | END; |
Execute the following query
01 | SELECT usr.user_name, |
02 | get_pwd.decrypt ((SELECT (SELECT get_pwd.decrypt (fnd_web_sec.get_guest_username_pwd, |
03 | encrypted_foundation_password) |
04 | FROM DUAL) AS apps_password |
05 | FROM fnd_user |
06 | WHERE user_name = |
07 | (SELECT SUBSTR (fnd_web_sec.get_guest_username_pwd, |
08 | 1, |
09 | INSTR (fnd_web_sec.get_guest_username_pwd, '/') - 1 |
10 | ) |
11 | FROM DUAL)), |
12 | usr.encrypted_user_password) PASSWORD |
13 | FROM fnd_user usr |
14 | WHERE usr.user_name = '10003'; |
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 | -- Get APPS database password |
2 | SELECT (SELECT get_pwd.decrypt (fnd_web_sec.get_guest_username_pwd, encrypted_foundation_password) |
3 | FROM DUAL) AS apps_password |
4 | FROM fnd_user |
5 | WHERE user_name = (SELECT SUBSTR (fnd_web_sec.get_guest_username_pwd, |
6 | 1, |
7 | INSTR (fnd_web_sec.get_guest_username_pwd, '/') - 1 |
8 | ) |
9 | FROM DUAL) |
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 is DEV03 (we got this in the previous query as well). Now let’s change the first query accordingly. It will look like this.
1 | SELECT usertable.user_name, (SELECT get_pwd.decrypt (UPPER ('DEV03'), usertable.encrypted_user_password) |
2 | FROM DUAL) AS encrypted_user_password |
3 | FROM fnd_user usertable |
4 | WHERE usertable.user_name LIKE UPPER ('10003') |
Please note that this article is for educational purposes only.
0 comments