查询EBS用户密码sql语句

最新推荐文章于 2023-12-26 10:01:50 发布

原创最新推荐文章于 2023-12-26 10:01:50 发布 · 1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#EBS #查询sql

PLSQL 专栏收录该内容

23 篇文章

订阅专栏

本文介绍了一个Oracle EBS环境中用于检索用户密码的PL/SQL包实现。该包提供了获取特定用户（如EBS-YC）及APPS用户加密后的密码，并通过解密函数返回明文密码的功能。此方案依赖于Oracle提供的加密和解密函数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;