Skip to Content
author's profile photo Former Member
Former Member

ORA-28002 during change password in Powerbuilder 12.5

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jul 01, 2015 at 02:06 PM

    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.


    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 02, 2015 at 10:32 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 02, 2015 at 10:46 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 06, 2015 at 07:06 PM

    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:

    1. User: REPNET
    2. Old Password: abc.134
    3. New password: abc.135
    4. 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:

    1. User: REPNET
    2. Old Password: abc.134
    3. 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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 17, 2015 at 03:01 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.