1 2 Previous Next 23 Replies Latest reply on May 28, 2015 7:27 PM by thatJeffSmith-Oracle

    Reset password: username field disabled in SQL Developer 4.1 EA1

    jmarton

      The Reset password dialog box in SQL Developer 4.1 EA1 has the username field disabled, so it can't be entered/changed. This applies to an open and a closed connection as well.

       

      If there is a username saved in the connection properties, then it will appear in the username field of the reset password dialog, so password can indeed be changed for that user.

       

      The problem is that if I override the username during connect, still the username saved for the connection entry comes up leading to an attempt to change an other user's password, so Insufficient privileges error is generated, and the password can't be changed from the UI.

       

      What is more, if there's no username saved in the connection properties, the username field in the Reset password dialog is empty and is still disabled, so no password can be changed using the UI.

       

       

       

      Software versions used:

      SQL Developer version: 4.1.0.17.29

      Oracle JDK: 1.8.0_31, 64bit, Linux

      Instant client: x64-12.1.0.2.0

        • 1. Re: Reset password: username field disabled in SQL Developer 4.1 EA1
          thatJeffSmith-Oracle

          I'm confused - why would you want to change the username in the first place?

          • 2. Re: Reset password: username field disabled in SQL Developer 4.1 EA1
            Gary Graham-Oracle

            Apparently the code change to make username non-editable in 4.1 was intentional:

            make the username field in the password change dialog non-editable. The UI should only be used to change passwords for the current user; DBAs have a separate UI to change other users passwords

            is the relevant check-in comment.

            • 3. Re: Reset password: username field disabled in SQL Developer 4.1 EA1
              jmarton

              OK, I agree that it makes sense to allow changing password for the current user only. But the current user is not necessarily the one saved in the connection pereferences, and SQL Developer fills in the first field with the username from the connection preferences. This is problem, when:

              • we have username=SCOTT saved in the connection preferences, but when connecting, I change it to TOM, so the current user is TOM. Reset password dialog shows SCOTT in the first field, which is not editable.
              • we have no username saved in the connection preferences, but when connecting, I give username=TOM and its password. Now, reset password dialog shows empty first field, again, non-editable.

               

              Both situations lead to the fact that I can't change the password for the current user, which is TOM.

              • 4. Re: Reset password: username field disabled in SQL Developer 4.1 EA1
                thatJeffSmith-Oracle

                The current user is ALWAYS the user defined in the connection. Connections are defined as a user for a given database.

                 

                If you are disconnected from the database, and you want to change your password, right-click on the connection, and use the change password context menu. If you need to do it for a user other than the one defined for the connection, define a new connection, and use that.

                 

                If you're already connected, and you need to change your or someone else's password, you have two options:

                1. do it with SQL, ALTER USER X IDENTIFIED BY 123456; -- funny in the movies but not in real life
                2. do it with the GUI, View > DBA. Connect. Security > Users. Right-click. Change Password.
                • 5. Re: Reset password: username field disabled in SQL Developer 4.1 EA1
                  jmarton

                  Jeff wrote in note4: "The current user is ALWAYS the user defined in the connection. Connections are defined as a user for a given database."

                   

                  Definitely no, as the username field in the connection definition is optional and can be overridden upon connecting. See the problem illustrated on the following screenshots.

                   

                  This figure shows a connection called demo, which has scott as a username saved:

                   

                  01_connection_definition.png

                  On this 2nd figure, the demo connection is opened with an other username (h_marton) as shown on the script output pane, and the reset password dialog still shows scott in its first, non-editable field. I wanted to change the password for h_marton, the connected user.

                  02_current_user.png

                   

                  A connection can also be defined in SQLDeveloper with no username saved in, as illustrated in the demo_blank connection.

                   

                  03_connection_definition_blank.png

                   

                  When opening the demo_blank connection, I entered credentials for user h_marton. Now, the reset password dialog show empty username field, which is non-editable, so a validation fails upon clicking on OK.

                  04_current_user.png

                  • 6. Re: Reset password: username field disabled in SQL Developer 4.1 EA1
                    thatJeffSmith-Oracle

                    Hmm, so your last screenshot gets to the truth I think. We know who the user is. That should probably work.

                    • 7. Re: Reset password: username field disabled in SQL Developer 4.1 EA1
                      jmarton

                      Yes, it should work. If you fill in the first field with the result of "select user from dual" (or equivalent), then it could be left disabled, when reset password dialog is invoked from an opened connection.

                       

                      When the reset password dialog is invoked from a closed connection (this works only if there is az OCI client available to SQL Developer), the username field can't be disabled, as there is no database session, thus there is no current user the function should reliably default to. This is illustrated in the following figure, using the same demo_blank connection as in note5.

                       

                      05_reset_password_on_closed_connection.png

                      • 8. Re: Reset password: username field disabled in SQL Developer 4.1 EA1
                        jmarton

                        To summarize what my advice is...

                         

                        When I invoke the reset password dialog from the connections pane,

                        1. if the particular connection were opened, username field can be left diasbled, and it should default to "select user from dual" (or equivalent)
                        2. if the particular connection were not opened, username field need to be enabled, and it might default to the value saved in the SQL Developer connection definition.
                        • 9. Re: Reset password: username field disabled in SQL Developer 4.1 EA1
                          thatJeffSmith-Oracle

                          I logged a bug, if we know what the username is from the session vs the connection (where it's null), we'll populate that for you.

                          • 10. Re: Reset password: username field disabled in SQL Developer 4.1 EA1
                            jmarton

                            short note:

                            My example cases in note#4  might not be clear enough, that's why I have summarized an AFAIK working approach in note#8, repeated below. Please let me know if my approach is not correct, or leaves unhandled use-cases.

                             

                            A hopefully correct approach to the username field of the Reset Password dialog (repeated from note#8)

                             

                            When I invoke the reset password dialog from the connections pane,

                            1. if the particular connection were opened, username field can be left diasbled, and it should default to "select user from dual" (or equivalent)
                            2. if the particular connection were not opened, username field need to be enabled, and it might default to the value saved in the SQL Developer connection definition.

                             

                            long note (argument):

                            For an opened database session, if you only populate username from the session when it's null in the connection definition, it will be the case displayed on the 2dn Fig of note#4, leading to "ORA-01031: insufficient privileges". In this case, h_marton, the session's user, issued an "alter user scott identified by 123456", by clicking on OK of the reset password dialog.

                             

                            For a not opened database connection, username field can't be populated from the session, so it must be left editable for the user regardless whether it has default value from connection definition.

                            • 11. Re: Reset password: username field disabled in SQL Developer 4.1 EA1
                              jmarton

                              Please don't forget this 4.1 EA2 (4.1.0.18.37) still has this issue.

                              • 12. Re: Reset password: username field disabled in SQL Developer 4.1 EA1
                                thatJeffSmith-Oracle

                                I only logged a bug on scenario 1.

                                 

                                A connection isn't a connection w/o a user. If you want to reset a user's password, establish a connection or create a connection where the user property isn't blank.

                                • 13. Re: Re: Reset password: username field disabled in SQL Developer 4.1 EA1
                                  jmarton

                                  In both situations I have a connection established. So my previous explanation might not be clear. Sorry for that. Please forget all my previous posts, figures etc. in this topic.

                                   

                                  To make the situation clear, please try to reproduce the steps below in SQL Developer 4.1 EA2 (4.1.0.18.37).

                                   

                                  Terms used

                                  • connection defined: the "New/Select Database Connection" form (dialog) was filled in and saved under a Connection name, which appears in the connection browser.
                                    • note: SQL queries etc. can't be runned at this stage.
                                  • connection established: there is a connection defined, and a database session is opened.
                                    • note: SQL queries can indeed be runned at this stage.

                                   

                                  1st scenario: connection defined with blank username, then established using a valid username

                                  1. define a new connection with the following setup
                                    • connection name: demo_blank
                                    • username: (blank)
                                    • password: (blank)
                                    • save password: unchecked
                                    • Oracle connection type: Basic
                                    • Role: default
                                    • Hostname/Port/SID or Service name: some valid entry.
                                  2. save the connection and close the definition form. Now we have a connection defined.
                                  3. in the Connections browser, right click demo_blank, choose connect
                                    • fill in the login credentials: username: SCOTT, password: whatever. Click OK.
                                  4. At this point we have a connection established with username SCOTT, but the connection definition still has blank username.
                                  5. in the Connections browser, find demo_blank, which still represent an established connection. Right click, choose: Reset password.
                                    • Enter new password dialog appears:
                                      • username: (blank), disabled
                                      • fill in the 3 password fields
                                      • click OK.
                                      • Result is an error: Validation failed: The username must be specified.
                                        • It can't be specified on the UI, though.
                                      • Advice: please fill in the username field with the result of:
                                        select user from dual;

                                   

                                  Rationale behind this scenario: in a student lab environment, I create the connection predefined with blank username, and the students provide their actual username/password upon connecting to the database.

                                   

                                  2nd scenario: connection defined with username specified, then established with an other username

                                  1. define a new connection with the following setup
                                    • connection name: demo
                                    • username: TOM
                                    • password: (blank)
                                    • save password: unchecked
                                    • Oracle connection type: Basic
                                    • Role: default
                                    • Hostname/Port/SID or Service name: some valid entry.
                                  2. save the connection and close the definition form. Now we have a connection defined, which is valid.
                                  3. in the Connections browser, right click demo, choose connect
                                    • fill in the login credentials, overriding the username: username: SCOTT, password: whatever. Click OK.
                                  4. At this point we have a connection established with username SCOTT, but the connection is still defined with username TOM.
                                  5. in the Connections browser, find demo, which still represent an established connection. Right click, choose: Reset password.
                                    • Enter new password dialog appears:
                                      • username: TOM, disabled
                                      • fill in the 3 password fields,
                                      • click OK.
                                      • Result is an error: ORA-01031: insufficient privileges
                                      • Statements log shows the generated SQL:
                                        alter user tom identified by "new" replace "old"
                                        
                                      • So formally, SCOTT tried to change password for TOM, which he doesn't have privilege to do so. But this was not his intention. He wanted to change his own password, but it was the UI's fault that he was unable to do so.
                                      • Advice: please fill in the username field with the result of:
                                        select user from dual;

                                   

                                  Rationale behind this scenario: I usually work with the username TOM in the development environment, but for some reason, I temporarily need toestablish a connection as an other user, e.g. for debug some strange situation SCOTT was facing.

                                   

                                  Done.

                                  I hope that now the error is clear. The solution for both situation is the same, as seen above. Prefill the username field with the result of

                                  select user from dual;
                                  
                                  • 14. Re: Re: Re: Reset password: username field disabled in SQL Developer 4.1 EA1
                                    jmarton

                                    1st scenario mentioned in note#13 above (note#13 of Re: Re: Reset password: username field disabled in SQL Developer 4.1 EA1 ) seems to be resolved in SQL Developer 4.1 production (4.1.0.19.07): username field is blank and editable.

                                     

                                    2nd scenario in SQL Developer 4.1 production (4.1.0.19.07) still leads to an attempt to change some other user's password than the database session user, as the username field is disabled and prefilled with the username from the connection definition: ORA-01031:insufficient privileges

                                     

                                     

                                    I still ask you (and suggest) to prefill the username field of the reset password dialog with the result of

                                    select user from dual;  
                                    1 2 Previous Next