Monday, April 07, 2014

Find out APPS/SYSADMIN password in Oracle Apps R12 if you lost or forgotten

Step to Find APPS PASSWORD :

Step #1

sqlplus> sqlplus system/system_password
sqlplus / as sysdba

Step#2:  Create Function to decrypt the encrypted password

SQL> create FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,in_chr_encrypted_pin 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 created.

Step#3: Query for password

set linesize 200 long 300

SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST';

ENCRYPTED_FOUNDATION_PASSWORD
--------------------------------------------------------------------------------
ZG379C5C883FF56235BE975D075A6FD2E7A749279B7BFCE331530B443E86F41A1966E3E4B1864519DB88BC07D58666AD837B

Step#4:  Get the Apps password using encrypted guest password

SQL> SELECT apps.decrypt_pin_func('GUEST/ORACLE','ZG379C5C883FF56235BE975D075A6FD2E7A749279B7BFCE331530B443E86F41A1966E3E4B1864519DB88BC07D58666AD837B') from dual;

APPS.DECRYPT_PIN_FUNC('GUEST/ORACLE','ZG379C5C883FF56235BE975D075A6FD2E7A749279B
--------------------------------------------------------------------------------
APPS123

Step#5: Test apps password

SQL> conn apps/APPS123;
Connected.

Step to Find SYSADMIN PASSWORD :

Step# 1:

SQL> conn apps/APPS123;
Connected.

Step#2:

SQL> CREATE OR REPLACE PACKAGE get_pwd AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2;
END get_pwd;
Package created.

Step# 3:

SQL>
 CREATE OR REPLACE PACKAGE BODY get_pwd  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'; END get_pwd;
 /
Package body created.

Step# 4:

SQL> SELECT usr.user_name,
       get_pwd.decrypt
  2    3            ((SELECT (SELECT get_pwd.decrypt
  4                                (fnd_web_sec.get_guest_username_pwd,
  5                                 usertable.encrypted_foundation_password
  6                                )
  7                        FROM DUAL) AS apps_password
  8                FROM fnd_user usertable
  9               WHERE usertable.user_name =
 10                        (SELECT SUBSTR
 11                                    (fnd_web_sec.get_guest_username_pwd,
 12                                     1,
 13                                       INSTR
 14                                            (fnd_web_sec.get_guest_username_p
 15                                             '/'
 16                                            )
 17                                     - 1
 18                                    )
 19                           FROM DUAL)),
 20             usr.encrypted_user_password) PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name = '&USER_NAME';
Enter value for user_name: SYSADMIN
old  23:  WHERE usr.user_name = '&USER_NAME'
new  23:  WHERE usr.user_name = 'SYSADMIN'


USER_NAME                                     PASSWORD
------------------------                        -----------------
SYSADMIN                                        SYSADMIN123

Step#5 :  Bellow sql will help you to validate SYSADMIN Password

SQL> select fnd_web_sec.validate_login('SYSADMIN','SYSADMIN123) from dual;

FND_WEB_SEC.VALIDATE_LOGIN('SYSADMIN','SYSADMIN123')
--------------------------------------------------------------------------------
Y

Oracle EBS integration with Oracle IDCS for SSO

Oracle EBS integration with Oracle IDCS for SSO Oracle EBS SSO? Why is it so important? Oracle E-Business Suite is a widely used application...