FNDCPASS erroring out with APP-FND-02704: Unable to alter user XXC to change password

Issue Description

When running FNDCPASS to change password of Product Schema we got the below error in the FNDCPASS log.

bash $> FNDCPASS apps/LPG 0 Y system/CNG ORACLE XXC K3r

Log filename : L7339701.log

Report filename : O7339701.out

bash $> cat L7339701.log

+---------------------------------------------------------------------------+

Application Object Library: Version : 12.0.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

module:

+---------------------------------------------------------------------------+

Current system time is 09-JUL-2011 04:58:10

+---------------------------------------------------------------------------+

APP-FND-02704: Unable to alter user XXC to change password.

APP-FND-01564: ORACLE error 1403 in changepassword

Cause: changepassword failed due to ORA-01403: no data found.

The SQL statement being executed at the time of the error was: and was executed from the file &ERRFILE.

+---------------------------------------------------------------------------+

Concurrent request completed

Current system time is 09-JUL-2011 04:58:10

+---------------------------------------------------------------------------+

Troubleshooting and Solution

Initially looking at the error, we concentrated our efforts in the error ‘ORA-01403: no data found’ but could not find any significant lead.

Then we checked if the schema XXC was a oracle product schema or a database user only. The below query confirmed that it was a product schema and user had not expired nor was it disabled.

Select oracle_id,oracle_username,creation_date,last_update_date from apps.fnd_oracle_userid =’XXC’;

Select user_id,username, account_status,profile,created, ,password,expiry_date from dba_users where username = 'XXC';

As the user was present in the apps.fnd_oracle_userid table it was confirmed that we must use the FNDCPASS to change password and not alter user command, so we had a issue with FNDCPASS.

We then concentrated on the error ‘APP-FND-02704: Unable to alter user XXC to change password’.

We found the profile of the user XXC from the dba_users was DEFAULT.

When we checked the PASSWORD_VERIFY_FUNCTION of the value for profile the DEFAULT it was set to PASSWORD_DO_NOT_CHANGE.

We temporarily changed the PASSWORD_VERIFY_FUNCTION of the value for profile the DEFAULT to NULL and ran the FNDCPASS and it went through successfully. After that we changed back the PASSWORD_VERIFY_FUNCTION to the previous value

SQL> alter profile DEFAULT limit PASSWORD_VERIFY_FUNCTION NULL;

bash $> FNDCPASS apps/LPG 0 Y system/CNG ORACLE XXC K3r

Log filename : L7339702.log

Report filename : O7339702.out

bash $> cat L7339702.log

+---------------------------------------------------------------------------+

Application Object Library: Version : 12.0.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

module:

+---------------------------------------------------------------------------+

Current system time is 09-JUL-2011 06:01:35

+---------------------------------------------------------------------------+

+---------------------------------------------------------------------------+

Concurrent request completed successfully

Current system time is 09-JUL-2011 06:01:35

+---------------------------------------------------------------------------+

SQL> alter profile DEFAULT limit PASSWORD_VERIFY_FUNCTION PASSWORD_DO_NOT_CHANGE;

Background of FNDCPASS

FNDCPASS command is recommended to be used in e-Business Suite to change the password of the product schemas.

You can use the FNDCPASS utility to change password for below users

1. APPS and APPLSYS

FNDCPASS apps/ 0 Y system/ SYSTEM APPLSYS

Using above syntax APPLSYS password can be change. The change in APPLSYS password change changes the APPS password also.

When you issue the above command below things take place internally

i. Validations for SYSTEM and APPS password

ii. Re-registers password in Oracle Applications

iii. Changes the password for APPS and APPLSYS in FND_ORACLE_USERID table and stores it in encrypted format

iv. Runs the ALTER user command for APPS and APPLSYS to change the password in the DBA_USERS table

2. Product Schema like AP, PA, GL, etc .,

FNDCPASS apps/ 0 Y system/ ORACLE

Using above syntax we can change the password for Product Schema.

When you issue the above command below things take place internally

i. Validations of SYSTEM and APPS password

ii. Updates the new password for the product schema in the FND_ORACLE_USERID table and stores it in encrypted format

iii. Runs the ALTER user command for product schema to change password in the DBA_USERS table

3. Application User like SYSADMIN

FNDCPASS apps/ 0 Y system/ USER

Using above syntax Application User passwords can be changed.

When you issue the above command below things take place internally

i. Validations of APPS Password

ii. Updates password in the FND_USER table and stores it in encrypted format.

PS: Note that FNDCPASS when used for changing Application users does not validate SYSTEM password for changing the password.

You can give dummy password for SYSTEM and still use this command successfully.

-- Tanveer Madan