查询EBS用户密码sql语句

1. CREATE OR REPLACE PACKAGE cux_fnd_web_sec IS
2. FUNCTION get_user_pass(p_fnd_user IN VARCHAR2 ,
3. p_guest_login IN VARCHAR2 DEFAULT 'GUEST/ORACLE')
4. RETURN VARCHAR2 ;
6. FUNCTION get_apps_pass(p_guest_login IN VARCHAR2 DEFAULT 'GUEST/ORACLE')
7. RETURN VARCHAR2 ;
9. --加密函数
10. FUNCTION encrypt(key IN VARCHAR2 , VALUE IN VARCHAR2 ) RETURN VARCHAR2;
12. --解密函数
13. FUNCTION decrypt(key IN VARCHAR2 , VALUE IN VARCHAR2 ) RETURN VARCHAR2;
15. END cux_fnd_web_sec;

1. CREATE OR REPLACE PACKAGE BODY cux_fnd_web_sec IS
2. FUNCTION encrypt( key IN VARCHAR2 , VALUE IN VARCHAR2 ) RETURN VARCHAR2 AS
3. LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.encrypt(java.lang.String,java.lang.String) return java.lang.String' ;
5. FUNCTION decrypt(key IN VARCHAR2 , VALUE IN VARCHAR2 ) RETURN VARCHAR2 AS
6. LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String' ;
8. FUNCTION get_apps_pass(p_guest_login IN VARCHAR2 DEFAULT 'GUEST/ORACLE')
9. RETURN VARCHAR2 IS
10. l_apps_encrypted_pass VARCHAR2(200 );
11. l_apps_decrypted_pass VARCHAR2(200 );
12. BEGIN
13. -- get apps encrypted pass
14. SELECT encrypted_foundation_password
15. INTO l_apps_encrypted_pass
16. FROM apps.fnd_user
17. WHERE user_name = 'GUEST' ;
19. --decrypt apps pass
20. SELECT decrypt(p_guest_login, l_apps_encrypted_pass)
21. INTO l_apps_decrypted_pass
22. FROM dual;
24. RETURN l_apps_decrypted_pass;
25. END get_apps_pass;
27. FUNCTION get_user_pass(p_fnd_user IN VARCHAR2 ,
28. p_guest_login IN VARCHAR2 DEFAULT 'GUEST/ORACLE')
29. RETURN VARCHAR2 IS
30. l_user_encrypted_pass VARCHAR2(200 );
31. l_user_decrypted_pass VARCHAR2(200 );
32. BEGIN
33. -- get fnd user encrypted pass
34. BEGIN
35. SELECT encrypted_user_password
36. INTO l_user_encrypted_pass
37. FROM fnd_user
38. WHERE user_name = p_fnd_user;
39. EXCEPTION
40. WHEN no_data_found THEN
41. RETURN 'User ' || p_fnd_user || ' is not exist in FND_USER table';
42. END;
44. --decrypt user pass
45. SELECT decrypt(get_apps_pass(p_guest_login), l_user_encrypted_pass)
46. INTO l_user_decrypted_pass
47. FROM dual;
49. RETURN l_user_decrypted_pass;
50. END get_user_pass;
52. 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;

1. CREATE OR REPLACE PACKAGE cux_fnd_web_sec IS
2. FUNCTION get_user_pass(p_fnd_user IN VARCHAR2 ,
3. p_guest_login IN VARCHAR2 DEFAULT 'GUEST/ORACLE')
4. RETURN VARCHAR2 ;
6. FUNCTION get_apps_pass(p_guest_login IN VARCHAR2 DEFAULT 'GUEST/ORACLE')
7. RETURN VARCHAR2 ;
9. --加密函数
10. FUNCTION encrypt(key IN VARCHAR2 , VALUE IN VARCHAR2 ) RETURN VARCHAR2;
12. --解密函数
13. FUNCTION decrypt(key IN VARCHAR2 , VALUE IN VARCHAR2 ) RETURN VARCHAR2;
15. END cux_fnd_web_sec;

1. CREATE OR REPLACE PACKAGE BODY cux_fnd_web_sec IS
2. FUNCTION encrypt( key IN VARCHAR2 , VALUE IN VARCHAR2 ) RETURN VARCHAR2 AS
3. LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.encrypt(java.lang.String,java.lang.String) return java.lang.String' ;
5. FUNCTION decrypt(key IN VARCHAR2 , VALUE IN VARCHAR2 ) RETURN VARCHAR2 AS
6. LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String' ;
8. FUNCTION get_apps_pass(p_guest_login IN VARCHAR2 DEFAULT 'GUEST/ORACLE')
9. RETURN VARCHAR2 IS
10. l_apps_encrypted_pass VARCHAR2(200 );
11. l_apps_decrypted_pass VARCHAR2(200 );
12. BEGIN
13. -- get apps encrypted pass
14. SELECT encrypted_foundation_password
15. INTO l_apps_encrypted_pass
16. FROM apps.fnd_user
17. WHERE user_name = 'GUEST' ;
19. --decrypt apps pass
20. SELECT decrypt(p_guest_login, l_apps_encrypted_pass)
21. INTO l_apps_decrypted_pass
22. FROM dual;
24. RETURN l_apps_decrypted_pass;
25. END get_apps_pass;
27. FUNCTION get_user_pass(p_fnd_user IN VARCHAR2 ,
28. p_guest_login IN VARCHAR2 DEFAULT 'GUEST/ORACLE')
29. RETURN VARCHAR2 IS
30. l_user_encrypted_pass VARCHAR2(200 );
31. l_user_decrypted_pass VARCHAR2(200 );
32. BEGIN
33. -- get fnd user encrypted pass
34. BEGIN
35. SELECT encrypted_user_password
36. INTO l_user_encrypted_pass
37. FROM fnd_user
38. WHERE user_name = p_fnd_user;
39. EXCEPTION
40. WHEN no_data_found THEN
41. RETURN 'User ' || p_fnd_user || ' is not exist in FND_USER table';
42. END;
44. --decrypt user pass
45. SELECT decrypt(get_apps_pass(p_guest_login), l_user_encrypted_pass)
46. INTO l_user_decrypted_pass
47. FROM dual;
49. RETURN l_user_decrypted_pass;
50. END get_user_pass;
52. 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;