Script to get Application user passwords !!! don't misuse it :)

Connect to APPS user and create following package to get Application user password.

Package Specification:
------------------------------------------------------------------------------------------------------------


CREATE OR REPLACE PACKAGE get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;
/


Package Body:
------------------------------------------------------------------------------------------------------------

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;
/

Query:
------------------------------------------------------------------------------------------------------------

SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM 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)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
WHERE usr.user_name = '&Username';

###############################################################################

How to get APPS Password, that's very interesting...

Step #1 Connect as system or sys User in the database.

[oracle@inblrdrdbadm01 ~]$ sqlplus system

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 25 21:24:58 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

Step#2:  Create Function to decrypt the encrypted password

SQL> set linesize 200 long 300
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';
  2  /

Function created.

Step#3: Query for password

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

ENCRYPTED_FOUNDATION_PASSWORD
----------------------------------------------------------------------------------------------------
ZG040B6D2CDF90B3493544F3BC2EFD960DD2F5C5D230E6F2C14D59E59C9F258726756BD7B9227552996F63F8795EAFE62F45

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

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

APPS.DECRYPT_PIN_FUNC('GUEST/ORACLE','ZG040B6D2CDF90B3493544F3BC2EFD960DD2F5C5D230E6F2C14D59E59C9F258726756BD7B9227552996F63F8795EAFE62F45')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EBSDEVCLONE

Step#5: Test apps password

SQL> conn apps/ebsdevclone
Connected.
SQL> show user
USER is "APPS"
SQL>

I hope, this could be important to get APPS / Any Application User password in-case of loss.

Cheeeers !!!

How to End-Date/Delete an Applications User ???

Applications users cannot be deleted since records belonging to users are kept for security and monitoring purposes. In addition, the user is recorded in Who columns, so deleting would cause foreign table data integrity problems. Instead, set "End Date" for that user.

Also same for the responsibility. We cannot delete a responsibility. We can only end-date a responsibility.

So Users and responsibilities cannot be deleted, instead you should end-date it.
For menus, functions, custom applications, it is to delete them -- Navigate to the form, query the record, and click on the delete button (or Edit > Delete).

There is no API or scripts to delete user accounts in FND_USER. Deletion of an an e-business suite user account is not possible and not supported. The supported methods to disable an Applications User are:
1. To end-date the user account from the Define Users forms or using the following API :

• FND_USER_PKG.UPDATEUSER you may pass an end date to the parameter X_END_DATE of the procedure FND_USER_PKG.UPDATEUSER.
  This will disable the user account.

The example below shows how to end-date custom user PIERRE:

fnd_user_pkg.updateuser(x_user_name=>'PIERRE',x_owner=>'CUST',x_end_date=>SYSDATE);

x_owner can be either SEED or CUST:

• To end date seeded users (i.e. sysadmin) x_owner= SEED
• To end date custom users x_owner = CUST

2. To use FND_USER_PKG.DISABLEUSER API:

• Pass the username as parameter. This will disable the user.
  

Note : After disabling a user, you can enable it by using the package FND_USER_PKG.ENABLEUSER