版本：tested on r12.0.6/r12.1.1
注意：请根据具体环境调整guest用户密码，，默认情况都是oracle。 guest用户的密码可以在$context_file文件中找到：
view $context_file
51
52 guest
53 oracle
54
用法：编译package，调用函数即可。
create or replace package apps.xxdba_fnd_security
authid current_user
as
-- guest login can be retrieved from profile guest_user_pwd(guest user password) or $context_file
function get_user_pass (p_fnd_user in varchar2
,p_guest_login in varchar2 default 'guest/oracle')
return varchar2;
function get_apps_pass (p_guest_login in varchar2 default 'guest/oracle')
return varchar2;
function encrypt (key in varchar2
,value in varchar2)
return varchar2;
function decrypt (key in varchar2
,value in varchar2)
return varchar2;
end xxdba_fnd_security;
/
create or replace package body apps.xxdba_fnd_security
as
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 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_apps_pass (p_guest_login in varchar2 default 'guest/oracle')
return varchar2
is
l_apps_encrypted_pass varchar2 (200);
l_apps_decrypted_pass varchar2 (200);
begin
-- get apps encrypted pass
select encrypted_foundation_password
into l_apps_encrypted_pass
from apps.fnd_user
where user_name = 'guest';
--decrypt apps pass
select decrypt (p_guest_login
,l_apps_encrypted_pass)
into l_apps_decrypted_pass
from dual;
return l_apps_decrypted_pass;
end get_apps_pass;
function get_user_pass (p_fnd_user in varchar2
,p_guest_login in varchar2 default 'guest/oracle')
return varchar2
is
l_user_encrypted_pass varchar2 (200);
l_user_decrypted_pass varchar2 (200);
begin
-- get fnd user encrypted pass
begin
select encrypted_user_password
into l_user_encrypted_pass
from fnd_user
where user_name = p_fnd_user;
exception
when no_data_found
then
return 'user ' || p_fnd_user || ' is not exist in fnd_user table';
end;
--decrypt user pass
select decrypt (get_apps_pass (p_guest_login)
,l_user_encrypted_pass)
into l_user_decrypted_pass
from dual;
return l_user_decrypted_pass;
end get_user_pass;
end xxdba_fnd_security;
/
/*
select xxdba_fnd_security.get_user_pass('sysadmin') from dual;
select xxdba_fnd_security.get_apps_pass from dual;
drop package apps.xxdba_fnd_security;
*/