欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Oracle EBS

程序员文章站 2022-06-14 12:27:12
...

版本:Tested on R12.0.6/R12.1.1,注意:请根据具体环境调整GUEST用户密码,默认情况都是ORACLE。 GUEST用户的密码可以在$CONTEX

版本: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;
*/

Oracle EBS