Subject: RESETTING APPLICATIONS PASSWORDS Type: PROBLEM Creation Date: 08-MAY-1995 Please note: this article has not been verified for technical accuracy. Problem Description: ==================== How to reset applications passwords? FOR USE WITH ORACLE APPLICATIONS 10.6 (WITH OUT PATCH 336384 APPLIED) AND BELOW. ***************************** RESETTING APPLICATIONS PASSWORDS ******************************** Passwords in Applications are encrypted both on the database level, AND at the Applications level. The Applications passwords are encrypted using the APPLSYS password as a seed; therefore, if the APPLSYS password changes, all the encryptions will change. Note: By APPLSYS, we refer specifically to the actual owner of your foundation tables, i.e. the value of the FNDNAM environment variable or logical. Below are three select statements which will help you reset the passwords in your database. Specifically, you will be returning your APPLSYS password to FND, and the SYSADMIN user's password to SYSADMIN. Following these steps will cause ALL Applications users except for SYSADMIN to become INVALID until they are re-registered using the Register Oracle ID form. Therefore, you should only follow these steps in case of desperation, and when all other means of obtaining passwords have been exhausted. NOTE: You should make a backup copy of both fnd_user and fnd_oracle_userid before you make any changes to these tables. To backup a table use the following syntax: SQL> create table fnd_user_backup as (select * from fnd_user); The first select statement shows the encrypted ORACLE password for APPLSYS. Update this row in your fnd_oracle_userid table with this value. The second select statement shows the encrypted foundation password in the fnd_user table (which is based on the APPLSYS password), and the encrypted user password for the SYSADMIN user. Update the necessary rows in your fnd_user table to reflect these values. Finally, the third statement gives the ORACLE password for the APPLSYSPUB account (specifically, the username specified by the GWYUID environment variable or logical). It is rarely necessary to change this, but in case it is, update your fnd_oracle_userid table as you did for the first statement. SQL> select encrypted_oracle_password from fnd_oracle_userid 2 where oracle_username='APPLSYS'; ENCRYPTED_ORACLE_PASSWORD ---------------------------------------------------------------- 9A700283DD7552A58D8DCCB3652CCA37044FDC7C8AAC60004301FAA481A662BD SQL> select encrypted_foundation_password,encrypted_user_password 2 from fnd_user where user_name='SYSADMIN'; ENCRYPTED_FOUNDATION_PASSWORD ----------------------------------------------------------------- 9C608E480F4C1E5012D34265F7F3DFDD6779CF66449A9ED9EE6AC8BA978BC1C5 ENCRYPTED_USER_PASSWORD ----------------------------------------------------------------- 8F6715C299381BEB8D8DCCB3652CCA37044FDC7C8AAC60004301FAA481A662BD SQL> select encrypted_oracle_password from fnd_oracle_userid 2 where oracle_username='APPLSYSPUB'; ENCRYPTED_ORACLE_PASSWORD ----------------------------------------------------------------- 8C6B0483DD7552A58D8DCCB3652CCA37044FDC7C8AAC60004301FAA481A662BD ****************************************** CHANGING ORACLE PASSWORDS FOR APPLICATIONS ****************************************** Changing the ORACLE passwords for the applications must be performed following a specific sequence of steps or you may lock you and your users out of the applications completely because of the password encryption schemes. A different sequence of steps needs to be followed when changing the APPLSYS id's password than the steps needed to change the other user?s passwords. The public ORACLE id (APPLSYSPUB) should not have its password changed since this account has only read-only privileges on a few tables and views for signing on to Applications. Besides, the password for this account can be seen from the OS in the environment variable or logical GWYUID, so it would be pointless to change it. What follows is the steps to change the passwords for ORACLE ids other than the APPLSYS password. The concurrent manager should be up and running and all Applications users should be signed off. Step 1: * Sign-on to the Applications and choose the System Administrator Responsibility. * Navigate to the Register Oracle ID form (\Navigate Security ORACLE). * Query up all the records and place the cursor on the password field of the ORACLE ID you would like to change (Do NOT change the APPLSYS password at this point!). * Type in the new password and then screen save. Upon commit, the password is reencrypted, however, this does not change the password at the RDBMS level. Step 2: * Logout out of Applications, or in another session, log into SQL*Plus as SYSTEM, and change the password in the database for the ORACLE ID selected in Step 1: ALTER USER username IDENTIFIED BY password Step 3: * Login back into the Applications and choose the System Administrator responsibility again. * Navigate to the Register Oracle Ids form. For the ORACLE ID you just changed, tab over to the Register column and change the value from NO to YES. This will submit a concurrent request (FNDSCGSO) to redo the grants and synonyms on the AOL tables for the ORACLE ID reflecting the new password. Step 4: * After the concurrent request completes successfully, the password change is complete. * At this point, you can go back to step 1 to change any other ORACLE password you wish to change outside of the APPLSYS password. ***************************** CHANGING THE APPLSYS PASSWORD ***************************** Here are the steps for changing the AOL ORACLE userid?s password. Be sure to shutdown the concurrent manager, and have all users sign off of the system before proceeding. Step1: * In the System Administrator responsibility, navigate to the Register Oracle Ids form (\Navigate Security ORACLE). * Query up the AOL id. Type in the new password and screen save. This will reencrypt all the passwords, since the encryption scheme is based upon the AOL id's password. Step 2: * Logout of the application, or in another window, log into SQL*Plus as SYSTEM. Change the APPLSYS password: ALTER USER applsys IDENTIFIED BY password Step 3: * Restart the concurrent manager using the startmgr script as the OS user who owns the Applications code. Note that you may need to edit the startmgr script to reflect the password change if you do not specify it on the command line when running the script. The password change should now be complete, and the applications users can signon. Unlike changing the other passwords, you do not need to go back to the Register ORACLE Ids formand change register to Yes. **NOTE: It has been experienced by some customers that when they changed the AOL ORACLE Id?s password, that all the application user passwords become invalid so users were no longer able to signon, even the SYSADMIN password. If this happens, you will need to reset the APPLSYS password, the SYSADMIN password, and then follow the steps above a second time to initiate the change. **NOTE 2: You may need to recompile invalid objects. You can do this by calling in to support to get the "recompile.sql" script sent to you from Robert Sechrest's Home Web page or by compiling the objects manually. ************************************************ This has not been tested for 10.7 yet. The APPLSYS password has changed to APPS from FND and the encryption dependencies may have changed as well. New APPLSYS/APPS encrypted_oracle_password is: B9538850E83205FA537028E51FF45607EF1B69A699E085DF5D72D88AAB5E2DBA APPS encrypted_oracle_password is: B9538850E83205FA537028E51FF45607EF1B69A699E085DF5D72D88AAB5E2DBA (The APPLSYS & APPS passwords should always be the same). New SYSADMIN/SYSADMIN passwords are: ENCRYPTED_FOUNDATION_PASSWORD: 9B7E9A1B0F4C1E5012D34265F7F3DFDD6779CF66449A9ED9EE6AC8BA978BC1C5 ENCRYPTED_USER_PASSWORD: AB5A8B42AC7F4CB4537028E51FF45607EF1B69A699E085DF5D72D88AAB5E2DBA New APPLSYSPUB/PUB password is: ENCRYPTED_ORACLE_PASSWORD A8569A03E83205FA537028E51FF45607EF1B69A699E085DF5D72D88AAB5E2DBA