- CREATE OR REPLACE PACKAGE cux_fnd_web_sec IS
- 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 cux_fnd_web_sec;
- CREATE OR REPLACE PACKAGE BODY cux_fnd_web_sec IS
- 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 cux_fnd_web_sec;
--先执行上面的代码------
--获取EBS用户密码
SELECT cux_fnd_web_sec.get_user_pass('EBS-YC' ) FROM dual;
--获取APPS用户的密码
SELECT cux_fnd_web_sec.get_apps_pass FROM dual;
- CREATE OR REPLACE PACKAGE cux_fnd_web_sec IS
- 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 cux_fnd_web_sec;
- CREATE OR REPLACE PACKAGE BODY cux_fnd_web_sec IS
- 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 cux_fnd_web_sec;
--先执行上面的代码------
--获取EBS用户密码
SELECT cux_fnd_web_sec.get_user_pass('EBS-YC' ) FROM dual;
--获取APPS用户的密码
SELECT cux_fnd_web_sec.get_apps_pass FROM dual;