7 Replies Latest reply on May 4, 2016 3:23 AM by user1151048

    Changing passwords in SQL Developer

    user1151048

      I hate to revive what looks like a much-discussed issue, but we are trying to improve security by implementing password expiration for our users, who connect to 11.1.0.7 and 11.2.0.4 databases using SQL Developer and other tools.  We need a method of allowing them to change their passwords themselves and SQL Developer seems the most logical choice for a standard method.  We are having the same issue others have reported (Enforcing users to change their password on SQL Developer is the most recent post I found) and I get the same results testing myself.  I create a user with a password set to expire on the first login:

       

      create user &username identified by &password default tablespace USERS temporary tablespace TEMP password expire profile &profile_name;

       

      In SQL Plus, I can login to this account and get the password expired message and the prompts to change it, which I can do successfully.  In SQL Developer 4.1.3.20, using an 11.2.0.4 instant client against an 11.1.0.7 database, I can successfully test my login and see the message that the password has expired.  I then right-click on the Reset Password option and fill in the same prompts I got in SQL Plus (I skipped the change in SQL Plus, so I could try it here).  I then get the ORA-01017 error that the username and/or password is invalid:

      ora-01017.PNG

      To confirm that I am using the correct expired password, I go back to the connection properties and click test.  I get the message that the password has expired, not that it is incorrect. 

      ora-28001.PNG

      Just for grins, I change the password to something I know is incorrect and click test again.  I get the message I would expect:

      ora-01017-test.PNG

      It appears that the Reset Password box is not passing in the same string that I type.  Is there a known issue around this?  I setup Instant Client according to Jeff's post as well as a more recent one at Oracle Database Admin: RESET EXPIRED PASSWORD USING SQL DEVELOPER.  My config works, according to the Test button in Preferences.  It looks like this, although I tried this first without the OCI/Thick box checked.

      oci.PNG

      Is there anything else I am missing in configuring this?  If we are likely to have mixed results with SQL Developer, has anyone found an easier method users can take to change their own passwords?  I am looking for the easiest solution so that we don't create a lot of help desk tickets for individual desktops having issues

       

      Thanks,

       

      John

        • 1. Re: Changing passwords in SQL Developer
          thatJeffSmith-Oracle

          Not a known issue. I'd be very surprised if we're not sending the string that's supplied by the user in the change password dialog.

           

          I'll see if I can reproduce the issue.

          • 2. Re: Changing passwords in SQL Developer
            Gary Graham-Oracle

            Are you using two different Oracle clients in the cases you mention?

            1. In SQL Plus, I can login to this account and get the password expired message and the prompts to change it, which I can do successfully.

            2. using an 11.2.0.4 instant client against an 11.1.0.7 database...

            That is, perhaps you are using an 11.1.0.7 client for the SQL*Plus case, not the 11.2.0.4 instant client?  I believe the OCI/Thick case is pretty finicky about the client version.  For example, if I use a 12.1.0.2 instant client and try to reset an expired password on an 11.2 XE local database I get the following...

            C:\Tools>sqlplus ASCOT/xxxxx@xe

             

            SQL*Plus: Release 12.1.0.2.0 Production on Tue May 3 10:22:39 2016

             

            Copyright (c) 1982, 2014, Oracle.  All rights reserved.

             

            ERROR:

            ORA-28001: the password has expired

             

             

            Changing password for ASCOT

            New password:

            Retype new password:

            ERROR:

            ORA-01017: invalid username/password; logon denied

             

             

            Password unchanged

            Enter user-name:

            But if I switch the command line environment so that PATH and  ORACLE_HOME point to the 11.2 XE install the reset password works fine...

            C:\Tools>sqlplus ASCOT/xxxxx@xe

             

            SQL*Plus: Release 11.2.0.2.0 Production on Tue May 3 10:11:43 2016

             

            Copyright (c) 1982, 2014, Oracle.  All rights reserved.

             

            ERROR:

            ORA-28001: the password has expired

             

             

            Changing password for ASCOT

            New password:

            Retype new password:

            Password changed

             

            Connected to:

            Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

             

            SQL>

            So this seems more likely a client mismatch issue with the 11.1.0.7 database instead of a SQL Developer issue.  Since SQL Developer 4.1.3 requires an 11.2.0.3 or higher client JDBC driver, it may not be possible for you to provide the reset password support you desire for 11.1.0.7 databases.

            • 3. Re: Changing passwords in SQL Developer
              user1151048

              Thanks Gary.  I am in fact using SQL Plus on the database server, so that's 11.1.0.7.  I'm confused, however; I thought from Jeff's post (http://www.thatjeffsmith.com/archive/2012/11/resetting-your-oracle-user-password-with-sql-developer/) that OCI was used in place of JDBC.  Has that changed with this version?

               

              Any other suggestions for supporting many users with a 90-day password change policy? 

              • 4. Re: Changing passwords in SQL Developer
                Gary Graham-Oracle

                Actually there are different flavors of JDBC driver: https://en.wikipedia.org/wiki/JDBC_driver

                 

                I guess Jeff meant to say a pure Java (type 4) Thin JDBC driver does not support password reset prior to connection, whereas a OCI/Thick JDBC driver using native DB client API libraries (type 2) will.  He comments a bit more about thin vs thick in http://www.thatjeffsmith.com/archive/2014/01/oracle-sql-developer-4-and-the-oracle-client/

                 

                Also, our FAQ talks about type 2 and 4 drivers: Oracle JDBC Frequently Asked Questions

                 

                It would be great if a future release of the type 4 JDBC driver added support for reset password when the password is expired or, in general, prior to connecting -- then, in theory, we should not have to worry about any restrictions on matching client versions with server versions.  For now, however, we'll just have to hope that happens some day. 

                 

                Edit:

                In the case of a user receiving a warning dialog upon connection about an upcoming password expiration, the Reset Password... context menu item is always available while connected, so the user could take advantage of that to reset the password prior to expiration.

                1 person found this helpful
                • 5. Re: Changing passwords in SQL Developer
                  thatJeffSmith-Oracle

                  >>it would be great if a future release of the type 4 JDBC driver added support for reset password

                  It WILL be great Stay tuned for updates on this area.

                  • 6. Re: Changing passwords in SQL Developer

                    Any other suggestions for supporting many users with a 90-day password change policy?

                    Yes - generate a weekly report for your users that tells them their pasword will be expiring in XX days and then remind them again to change their password before it expires.

                     

                    Uses can EASILY change their passwords BEFORE they expire and totally prevent your problem.

                    • 7. Re: Changing passwords in SQL Developer
                      user1151048

                      I think I found a workable solution for 11.1.0.7 databases. 

                       

                      I downloaded the 11.1.0.7 Basic and SQL Plus downloads from the Instant Client page.  After unzipping these to the same directory, I opened a SQL Plus session and got prompted to change my expired password.  I was able to do this successfully.

                       

                      C:\

                      C:\ cd \sqlplus\instantclient_11_1

                      C:\sqlplus\instantclient_11_1>sqlplus.exe testuser2/changenow10@testdev1:1521/devdb1

                       

                      SQL*Plus: Release 11.1.0.7.0 - Production on Tue May 3 21:11:58 2016

                      Copyright (c) 1982, 2008, Oracle.  All rights reserved.

                       

                      ERROR:

                      ORA-28001: the password has expired

                       

                      Changing password for testuser2

                      New password:

                      Retype new password:

                      Password changed

                       

                      Connected to:

                      Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

                       

                      SQL>

                       

                      This should work for users and should be easy enough for them to manage on their own.  I have more testing to do, but so far this has worked after a couple of attempts.  Another plus with this method is that I didn't have to add the path to environment variables, so just unzipping the files and logging in should do it.