ORA-28002 during change password in Powerbuilder 12.5

[Former Member]

I'm using Powerbuilder v 12.5 with an Oracle database.  I discovered a problem after upgrading Oracle database from 11.2.0.3 to 11.2.0.4.  I wrote a small Powerbuilder application to demonstrate this.  The application logs in as a user whose expiry_date is in the past, but whose account status is "OPEN" based on a query against the data dictionary view DBA_USERS.  After logging in the application performs an ALTER USER <user> IDENTIFIED BY <new password> REPLACE <old password>.  It does this via EXECUTE IMMEDIATE <stmt> USING SQLCA.  After this statement completes, the user's password is successfully changed in Oracle but the SQLCA object returns a -1 in its SQLCODE member.  The SQLCA.SQLDBCODE is set to 28002 and the SQLCA.SQLERRTEXT has the message "ORA-28002: the password will expire within 10 days".

When I run the same application using Oracle 11.2.0.3 and the same user in the same state, the SQLCA.SQLCODE is 0.  This behavior is obviously different from 11.2.0.4.

The same issue occurs with Powerbuilder 11.5.

We are using the SQLCA.DBMS="010 Oracle10g (10.1.0)".

I also tested it with the newer SQLCA.DBMS="ORA Oracle11g" for Powerbuilder 12.5.  This string doesn't work in Powerbuilder 11.5.

Oracle claims that there are no differences between 11.2.0.3 and 11.2.0.4. 

I'm wondering if Powerbuilder does an internal Oracle version check during EXECUTE IMMEDIATE ... USING SQLCA.

Sure we can code around it but that means rebuilding and testing our applications and redelivering them.  But, we would like to avoid that if possible.

If there is something simple we can do to mitigate this behavior such as using a different DBMS string or changing the Oracle DLL that would help us out tremendously.

Thanks and best regards,

Will

[Former Member]

Will,

Thank you SOOOOOOO much for identifying this and documenting it.  Our system just converted to enforcing password changes and the change in profile forced hundreds of them to have expiry dates in the past.  I was going crazy trying to figure out what was happening.

I believe this to be BOTH and Oracle and PowerBuilder bug.  Oracle should not be generating these 28002 messages.  But, SQLCA should not be generating a -1 either.  I will let you know if I discover anything more on our side - you've probably closed the issue on your side anyway.  But, for what it's worth, thank you!

Brian

[Former Member]

Abdallah here's the test document for this copied from Word 2010.  PWDialog=1 had no visible effect.

Test of Powerbuilder anomaly after Oracle database upgrade.

Introduction

This is a test of change password with Powerbuilder 12.5 using Oracle v 11.2.0.4.  The problem is that when one performs an execute immediate of “ALTER USER IDENTIFIED BY … REPLACE” it sometimes returns an ORA-28002 even though it has successfully changed the password.  For this to happen the conditions are that the user has expired and no attempts have been made to log in.  This problem occurs in Oracle 11.2.0.4 but does not occur in Oracle 11.2.0.3.

The backup process

The database instance was backed up by shutting it down, copying the files to a separate directory, then performing tar and gzip to compress them.  All data files, temp files, control files, and redo log files are included.  This snapshot is stored along with a shell script for reconstituting the database.

The upgrade process

I used an Oracle instance in 11.2.0.3 and upgraded it to 11.2.0.4 using a manual upgrade process. I took backups of the database instance before and after the upgrade.

The test flow

I restored a database using 11.2.0.3.  I then ran the test.  Then I restored the database with the 11.2.0.4 snapshot.  I ran the test a second time.

A screenshot of the Powerbuilder 12.5 application

The code behind the “Change Password” button

// Validate

IF trim(sle_user.text) = “” THEN

        st_sqlcode.text = “Validation”

        st_sqlerr.text = “User name required”

END IF

IF trim(sle_old.text) = “” THEN

        st_sqlcode.text = “Validation”

        st_sqlerr.text = “Old password required”

END IF

IF trim(sle_new.text) = “” THEN

        st_sqlcode.text = “Validation”

        st_sqlerr.text = “New password required”

END IF

1. sqlca.dbms = “O10 Oracle10g (10.1.0)”
2. sqlca.database=”b52.ingrfed.net”
3. sqlca.servername=”contest3.ingrfed.net”
4. sqlca.dbparm=”PWDialog=1”

// These don’t do anything for the login

1. sqlca.userid = sle_user.text
2. sqlca.dbpass=sle_old.text

// These are actually what is used to log in

1. sqlca.logid = sle_user.text
2. sqlca.logpass=sle_old.text

connect using sqlca;

String ls_sql

ls_sql = “ALTER USER “+sle_user.text+” IDENTIFIED BY ~””+sle_new.text+”~” REPLACE ~””+sle_old.text+”~””

EXECUTE IMMEDIATE :ls_sql USING sqlca;

st_sqlcode.text = string(sqlca.sqldbcode)

st_sqlerr.text = sqlca.sqlerrtext

DISCONNECT USING SQLCA;

The user

The Oracle user account has to have a password_expiry date in the past and an account_status of “OPEN”.

The test

Step	Action	Result
1	Insure the database instance “CONTEST3” is set up by cold restore from a set of files.  Start the instance.	CONTEST3 is running in Oracle 11.2.0.4.
2	Check the status of the user to test with. SELECT    User_name,    Expiry_date,    Account_status FROM dba_users;	REPNET 02-MAR-2014 OPEN
3	Turn on network sniffing using HPUX nettl utility to capture and log SQL*NET packets.	Nettl is recording network traffic
4	Launch pbchangepassword.exe and perform change password: User: REPNET Old Password: abc.134 New password: abc.135 Click on “Change Password”	ORA-28002 ORA-28002: the password will expire within 10 days.
5	Turn off network sniffing and save the log file.	The trace.output.11.2.0.4 is produced.
6	Check the status of the user SELECT    User_name,    Expiry_date,    Account_status FROM dba_users;	REPNET 30-SEP-2015 OPEN
7	Restore CONTEST3 using the Oracle 11.2.0.3 version	CONTEST3 is running in Oracle 11.2.0.3.
8	Check the status of the user to test with. SELECT    User_name,    Expiry_date,    Account_status FROM dba_users;	REPNET 02-MAR-2014 OPEN
9	Turn on network sniffing as above.	The nettl utility is logging network traffic.
10	Launch the pbchangepassword.exe and perform change password: User: REPNET Old Password: abc.134 New password: abc.135 Click on “Change Password”	0
11	Turn off network sniffing and save the file.	The trace.output.11.2.0.3 is produced.
12	Check the status of the user to test with. SELECT    User_name,    Expiry_date,    Account_status FROM dba_users;	REPNET 30-SEP-2015 OPEN

[Former Member]

I'll have to come back next week with how to share the files mentioned above.  This forum apparently does not allow me to attach files.  I have two .txt files and a .docx file.

[Former Member]

I set PWDialog=1 but it had no effect that I could see.

I then ran another set of tests.  I have attached some files that should give a lot more detail about my test setup.

[Former Member]

Hi,

Can you test with the dbparm PWDialog=1 to see if it behaves differently

as changing the password by a SQL statement.

Regards.

Abdallah.