add

About Me

My photo
Oracle Apps - Techno Functional consultant

Friday, August 26

How to get Login Passwords for application & Data Base instances


Most often, oracle apps developers have to work in different DB instances for development as well as for testing purposes. In such situations we need to get access to different oracle application instances and data base (dev/test/crp etc.,) instances. So we have to request & follow a lengthy approval process to get those login details, some times you may end up in loosing your delivery schedules.


There is a simple way by which you can get the logins/passwords of your DB & Application with out chasing the DBAs.
Oracle follows an encryption algorithm to encrypt user passwords. Most references to the encryption algorithm point to either the PL/SQL package APPS.FND_WEB_SEC or the Java class "oracle.apps.fnd.security.WebSessionManagerProc".

For decryption and encryption, the following calls are made:
APPS.FND_WEB_SEC >
oracle.apps.fnd.security.WebSessionManagerProc>
oracle.apps.fnd.security.AolSecurity>
oracle.apps.fnd.security.AolSecurityPrivate

The actual encryption and decryption routines are in the "oracle.apps.fnd.security.AolSecurityPrivate" Java class. This Java class is stored both in the database as a Java Stored Procedure and in the operating system directory $COMMON_TOP/java.



Create a package specification: get_pwd
-- Package Specification
CREATE OR REPLACE PACKAGE get_pwd AS
FUNCTION decrypt (KEY IN VARCHAR2,VALUE IN VARCHAR2)RETURN VARCHAR2;
END get_pwd;


Create the package body: get_pwd

-- Package Body 
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;


Call the package function as shown below:
/** Run this on toad, Get the DB apps password */
SELECT (SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD'))
FROM DUAL)), usertable.encrypted_foundation_password)
FROM DUAL) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD')
,1
, INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/')
- 1
)
FROM DUAL))

Call the package function as shown below:
/** Run this on toad, Get the application usernames and passwords */
SELECT usertable.user_name
, (SELECT get_pwd.decrypt (UPPER ((SELECT (SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD'))
FROM DUAL)), usertable.encrypted_foundation_password)
FROM DUAL) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE
UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD')
,1
, INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/')
- 1
)
FROM DUAL))))
,usertable.encrypted_user_password)
FROM DUAL) AS encrypted_user_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE UPPER ('username') -- Here username is application login such as 'OPERATIONS'

5 comments:

Soumya Prakash said...

The query is now working. Please review the same and kindly update it with comments.

More often the DBA password is not appearing.

Thanks.

Jithendra Kumar B said...

Hi Prakash,

Thanks you for leaving a comment...

Please confirm that if you have compiled the above mentioned package ('GET_PWD') in apps schema?

Also you need to run the Query in apps schema but not 'read'.

I have checked this and working fine in both 11i and R12 insataces.. Can you please try again and let mw know your feedback.

Soumya Prakash said...

Sir,

I am able to compile the package get_pwd.

But unfortunately the query does not work. Neither the DBA one or the instance one.

Could you please send me your saved query to my mail id.

soumya.parhi@gmail.com

Thank you !!

Warmest Regards,
Soumya Prakash

Jithendra Kumar B said...

Prakash, Sent you an email with the required info...

feel free to buzz me for any help....:)

Jitin

Anonymous said...

Hi Jithendar,

Thank you very much for sharing wonderful information. It works well in 11i, but in R12, it is not working. Would you please tel me the reason?

Syed