11 Replies Latest reply: Feb 28, 2013 4:07 PM by EdStevens RSS

    Problem to change user password

    993786
      Hi,

      I'm with some problems when trying to alter a oracle user password. After using the alter command, it gives the message "User altered", but when i try to connect with it throws the ORA-01017 error...
      The strange part is... it works just fine on my XE 11g !

      Someone have an idea on this issue? :)

      Thanks.

      ================

      oracle@arc02:~> sqlplus mybase/mypass@myinstance

      SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jan 15 11:52:48 2013

      Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


      Connected to:
      Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
      With the Real Application Clusters option

      SQL> alter user z48829 identified by Telemar$0n;

      User altered.

      SQL> quit
      Disconnected from Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
      With the Real Application Clusters option
      oracle@dtcdbarc02:~> sqlplus z48829/Telemar$0n@myinstance

      SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jan 15 11:53:14 2013

      Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

      ERROR:
      ORA-01017: invalid username/password; logon denied


      Enter user-name:
      ERROR:
      ORA-01017: invalid username/password; logon denied
        • 1. Re: Problem to change user password
          sb92075
          990783 wrote:
          Hi,

          I'm with some problems when trying to alter a oracle user password. After using the alter command, it gives the message "User altered", but when i try to connect with it throws the ORA-01017 error...
          The strange part is... it works just fine on my XE 11g !

          Someone have an idea on this issue? :)

          Thanks.

          ================

          oracle@arc02:~> sqlplus mybase/mypass@myinstance

          SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jan 15 11:52:48 2013

          Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


          Connected to:
          Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
          With the Real Application Clusters option

          SQL> alter user z48829 identified by Telemar$0n;

          User altered.

          SQL> quit
          Disconnected from Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
          With the Real Application Clusters option
          oracle@dtcdbarc02:~> sqlplus z48829/Telemar$0n@myinstance

          SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jan 15 11:53:14 2013

          Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

          ERROR:
          ORA-01017: invalid username/password; logon denied


          Enter user-name:
          ERROR:
          ORA-01017: invalid username/password; logon denied
          OS/command line interpreter is operating against "$0n" as environmental variable.

          You should NOT be including dollar sign character in the password.
          • 2. Re: Problem to change user password
            TSharma-Oracle
            Is your 10.2.0.4 is *nix or Windows Palatform?                                                                                                                                                                                                                   
            • 3. Re: Problem to change user password
              EdStevens
              In addition to SB's catch of the "$", you should also not use the "@". Guess what happens with
              sqlplus scott/myp@ssword@orcl
              I use the password complexity function to dis-allow these and other 'problematic' special characters.
              • 4. Re: Problem to change user password
                vlethakula
                ora@TST1> alter user demo1 identified by "Telemar$0n";


                User altered.

                bash-3.2$ sqlplus demo1/Telemar\$0n

                SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 27 15:08:37 2013

                Copyright (c) 1982, 2010, Oracle. All Rights Reserved.


                Connected to:
                Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
                With the Partitioning, OLAP, Data Mining and Real Application Testing options

                ora@TST1>
                • 5. Re: Problem to change user password
                  jgarry
                  Then don't you have conflict with places that require special characters? Any shell work is going to have to deal with special characters anyhoo.
                  • 6. Re: Problem to change user password
                    moreajays
                    Hi,

                    Use sqlplus z48829/'Telemar$0n'@myinstance
                    [ora10g@pos-uat-2:/home/ora10g>]sqlplus ajay/Telemar$0n@EBPSUATN
                    SQL*Plus: Release 10.2.0.3.0 - Production on Thu Feb 28 13:29:17 2013
                    Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
                    ERROR:
                    ORA-01017: invalid username/password; logon denied
                    Enter user-name: ^C
                    [ora10g@pos-uat-2:/home/ora10g>]sqlplus ajay/'Telemar$0n'@EBPSUATN
                    SQL*Plus: Release 10.2.0.3.0 - Production on Thu Feb 28 13:29:22 2013
                    Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
                    Connected to:
                    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
                    With the Partitioning and Data Mining options
                    13:29:22 SQL> exit
                    Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
                    Thanks,
                    Ajay More
                    http://www.moreajays.com
                    • 7. Re: Problem to change user password
                      EdStevens
                      vlethakula wrote:
                      ora@TST1> alter user demo1 identified by "Telemar$0n";


                      User altered.

                      bash-3.2$ sqlplus demo1/Telemar\$0n

                      SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 27 15:08:37 2013

                      Copyright (c) 1982, 2010, Oracle. All Rights Reserved.


                      Connected to:
                      Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
                      With the Partitioning, OLAP, Data Mining and Real Application Testing options

                      ora@TST1>
                      Do you really expect users to 'escape' special characters in their passwords?
                      • 8. Re: Problem to change user password
                        EdStevens
                        jgarry wrote:
                        Then don't you have conflict with places that require special characters? Any shell work is going to have to deal with special characters anyhoo.
                        But in this case, the password complexity routine is for my organization's database, and I can write it to meet my organization's requirements. I don't dis-allow all special characters, just those found to be problematic. At my last job, the requirements for passwords were
                        - minimum 15 characters total, with a minimum of
                        -- 2 upper-case
                        -- 2 lower-case
                        -- 2 numeric
                        -- 2 special
                        -- 60 day lifetime
                        -- 10 passwords before reuse.

                        We were a multi-site organization. We each had our own home-grown complexity function ... well, there was a lot of sharing, but no 'organizational standard'. I was using a function based on the standard one supplied by oracle, but enhanced for our organizational requirement. As we discovered characters that were problematic in passwords, I would tweak the function to dis-allow those characters. I even added code to throw up a message indicating what was wrong if a password was being disallowed. At some point, my counterpart at a different site took the rules in my version and re-wrote it in a much more elegant fashion, and that pretty much got adopted as the organizational standard.



                        True, if the password is for an account that doesn't belong to a human, and so is only ever used in a script or some other 'behind the scenes' scenario, one could do whatever is needed to make it work. If fact, off the top of my head it seems that might actually be an additional layer of security for those types of accounts.

                        BTW, in addition to the "$" and "@", for reasons already mentioned, at my last shop we found that a "#" gave problems for users who were connecting through and Oracle Forms app. Seems that Forms itself uses that as a meta-character for something. I don't know, but it seems possible that "\" could also present problems if it is seen as an escape indicator.
                        • 9. Re: Problem to change user password
                          jgarry
                          EdStevens wrote:
                          vlethakula wrote:
                          ora@TST1> alter user demo1 identified by "Telemar$0n";


                          User altered.

                          bash-3.2$ sqlplus demo1/Telemar\$0n

                          SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 27 15:08:37 2013

                          Copyright (c) 1982, 2010, Oracle. All Rights Reserved.


                          Connected to:
                          Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
                          With the Partitioning, OLAP, Data Mining and Real Application Testing options

                          ora@TST1>
                          Do you really expect users to 'escape' special characters in their passwords?
                          Obviously the OP didn't know it, but he must by now. I think it isn't too unreasonable to expect people to understand each environment they work in, especially when their work crosses environments. This also means I'm kind of against making one environment work like another, or lowest common denominators, even though the differences are often arbitrary and confusing. I'm probably in the minority thinking that way, at least for the workalike part.
                          • 10. Re: Problem to change user password
                            993786
                            That works !!
                            The tip with "\" vlethakula posted also work.

                            Just a FYI that i forgot to write in the issue description:

                            - The 10.2.0.4.0 database where this error happened was on Solaris 10.
                            - The XE 11g where I tested the same alter command, and it worked, was on Windows 7.

                            Thanks everybody for the help.
                            • 11. Re: Problem to change user password
                              EdStevens
                              jgarry wrote:
                              EdStevens wrote:
                              vlethakula wrote:
                              ora@TST1> alter user demo1 identified by "Telemar$0n";


                              User altered.

                              bash-3.2$ sqlplus demo1/Telemar\$0n

                              SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 27 15:08:37 2013

                              Copyright (c) 1982, 2010, Oracle. All Rights Reserved.


                              Connected to:
                              Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
                              With the Partitioning, OLAP, Data Mining and Real Application Testing options

                              ora@TST1>
                              Do you really expect users to 'escape' special characters in their passwords?
                              Obviously the OP didn't know it, but he must by now. I think it isn't too unreasonable to expect people to understand each environment they work in, especially when their work crosses environments. This also means I'm kind of against making one environment work like another, or lowest common denominators, even though the differences are often arbitrary and confusing. I'm probably in the minority thinking that way, at least for the workalike part.
                              Looks like I had another "senior moment", aka a "DSA". I was thinking of the payroll clerk sitting at a keyboard, not the gear-head writing shell scripts. Obviously the "$" in the password only becomes an issue when it is in a shell script, where the person writing/maintaining the script should know how to deal with it. Just like a query on a dynamic view like v$instance. It would not be an issue for a human sitting at a keyboard and logging on.

                              However, the "@" very much is an issue for the human at the keyboard, and so really should be disallowed by the complexity function.

                              Edited by: EdStevens on Feb 28, 2013 4:06 PM