Skip to content

Decrypt FND_USER password in 11i/R12

For Apps DBA sometimes it is necessary to decrypt the EBS Application user’s password. Below package can be used to decrypt the FND_USER password. You will need password of “apps” database userid to be able to decrypt the application user’s password.

Step 1: Create Package Specfication

SQL>  create or replace 
PACKAGE test_package12
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END test_package12;
/
Step 2: Create Package Body
SQL> create or replace 
PACKAGE BODY test_package12
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 test_package12;
/
Step 3: Decrypt the Password
SQL> SELECT USERTABLE.USER_NAME
,(SELECT test_package12.decrypt (UPPER ('&apps_password') 
,usertable.encrypted_user_password)
FROM DUAL) AS encrypted_user_password
FROM FND_USER USERTABLE
WHERE usertable.user_name LIKE UPPER ('&USER_NAME_TO_DECRYPT');
Step 4: Drop Package
SQL> drop package test_package12;

[Post Views: 953]

Brijesh Gogia

I’m an experienced Oracle Applications DBA with more than a decade of full-time DBA experience. I have gained a wide knowledge of the Oracle software stack and have worked on several big projects for multi-national companies. I enjoy working with the leading-edge technology and have passion for database performance and stability. Thankfully my work allows me time for researching new technologies (and to write about them).
  
You can connect with me on LinkedIn.

2 Comments

  1. singhsk singhsk

    This doesn’t work in R12.2.. does it?

    • Brijesh Gogia Brijesh Gogia

      Works with R12.2 as we have tried in one of our R12.2 instance.

      Enter value for apps_password: myappspassword
      old 2: ,(SELECT test_package12.decrypt (UPPER (‘&apps_password’)
      new 2: ,(SELECT test_package12.decrypt (UPPER (‘myappspassword’)
      Enter value for user_name: SYSADMIN
      old 6: WHERE usertable.user_name LIKE UPPER (‘&USER_NAME’)
      new 6: WHERE usertable.user_name LIKE UPPER (‘SYSADMIN’)

      USER_NAME
      ——————————————————————————–
      ENCRYPTED_USER_PASSWORD
      ——————————————————————————–
      SYSADMIN
      mysysadminpassword

      SQL> select release_name from apps.fnd_product_groups;

      RELEASE_NAME
      ————————————————–
      12.2.4

Leave a Reply

Required fields are marked *