5 Replies Latest reply on May 8, 2019 6:29 PM by EdStevens

    changing sys password

    SQL_Warrior

      our security auditor has asked us to reset the default passwords reported in the following view DBA_USERS_WITH_DEFPWD

       

      when attempting to change the SYS password using alter user command we faced the following error

      ERROR at line 1:

       

       

      ORA-01994: Password file missing or disabled

       

      your feedback is much appreciated....please note that its an 18c database instance.

        • 1. Re: changing sys password
          EdStevens

          SQL_Warrior wrote:

           

          our security auditor has asked us to reset the default passwords reported in the following view DBA_USERS_WITH_DEFPWD

           

          when attempting to change the SYS password using alter user command we faced the following error

          ERROR at line 1:

           

           

          ORA-01994: Password file missing or disabled

           

          your feedback is much appreciated....please note that its an 18c database instance.

          In  general, an error message with no context is almost worthless.

          Show us the exact command you issued that resulted in that error.  Better yet, replicate the error in sqlplus and copy and paste the entire command and response.  And show who issued the command.

           

          "18c" is a marketing term.  The actual version is a 4-decimal number, like 18.1.0.1.

           

          All that said, have you googled "ORA-01994"?  If not, why?

          • 2. Re: changing sys password
            Emad Al-Mousa

            it seems the auditor is looking to update the SYS password hash, as you might know the oracle software owner will authenticate as sysdba so changing SYS password will not add a real security protection.

             

            starting from 12cR2 and beyond you can't change the sys password using "alter user" command....you need configure password file:

             

             

            set the following parameter, a database restart is required:

             

            remote_login_passwordfile=exclusive

             

            go to $ORACLE_HOME/dbs , then exeucte the following command to generate a password file:

             

            orapwd file=orapwSID password=XXXXXX entries=3

             

            execute the following query:

             

            SQL> select username,sysdba,sysoper from v$pwfile_users;

             

            you can now execute the alter user command:

             

            SQL> alter user sys identified by XXXXXXXXXXXXXX ;

            • 3. Re: changing sys password
              EdStevens

              Emad Al-Mousa wrote:

               

              it seems the auditor is looking to update the SYS password hash,

              I seriously doubt the auditor knows anything about hashes.  And as you might know, the password is stored as a hash, so the way you change the hash is to change the password.

               

               

               

              as you might know the oracle software owner will authenticate as sysdba so changing SYS password will not add a real security protection.

              This is true only for local, os-authenticated connections.  Connections via tns very much rely on the password. 

               

              starting from 12cR2 and beyond you can't change the sys password using "alter user" command....you need configure password file:

              This is simply not true. 

              Here are my password files (I have two databases on this system). Note the timestamps.

               

              oracle:fs92upg$ ll orapw*

              -rw-r----- 1 oracle oinstall 8192 Dec  4  2017 orapwfs92upg

              -rw-r----- 1 oracle oinstall 8192 Jan 30  2018 orapwhr92upg

              And here I log in (remotely) as sysdba and the current password, then change the password.

              oracle:fs92upg$ sqlplus sys/halftrack@fs92upg as sysdba

               

              SQL*Plus: Release 12.1.0.2.0 Production on Tue May 7 07:47:54 2019

               

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

               

               

              Connected to:

              Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

               

              SQL> alter user sys identified by trackhalf;

               

              User altered.

               

              SQL> exit

              Disconnected from Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

               

              Now, notice that the password file timestamp has changed, indicating that my 'alter user' did indeed modify the password.

              oracle:fs92upg$ ll orapw*

              -rw-r----- 1 oracle oinstall 8192 May  7 07:48 orapwfs92upg

              -rw-r----- 1 oracle oinstall 8192 Jan 30  2018 orapwhr92upg

               

              And now we log on with the new password:

              oracle:fs92upg$ sqlplus sys/trackhalf@fs92upg as sysdba

               

              SQL*Plus: Release 12.1.0.2.0 Production on Tue May 7 07:48:54 2019

               

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

               

               

              Connected to:

              Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

               

              SQL>

               

              And just to prove that it really is dependent on that new password, let's try again with the original:

              oracle:fs92upg$ sqlplus sys/halftrack@fs92upg as sysdba

               

              SQL*Plus: Release 12.1.0.2.0 Production on Tue May 7 08:01:26 2019

               

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

               

              ERROR:

              ORA-01017: invalid username/password; logon denied

               

               

               

               

               

              set the following parameter, a database restart is required:

               

              remote_login_passwordfile=exclusive

               

              go to $ORACLE_HOME/dbs , then exeucte the following command to generate a password file:

               

              orapwd file=orapwSID password=XXXXXX entries=3

               

              execute the following query:

               

              SQL> select username,sysdba,sysoper from v$pwfile_users;

               

              you can now execute the alter user command:

               

              SQL> alter user sys identified by XXXXXXXXXXXXXX ;

               

              IF the connection is a local connection AND the os user is a member of the os DBA group, then all credentials are ignored and the connections is OS authenticated.

              IF the connection is via TNS, then the password file is required and is used for authentication.

              The ALTER USER command always works, and there is nothing new about it with 12c.  IF there is no password file, it is rather pointless, because of OS authentication, but the ALTER USER, even for SYS will succeed whether there is a password file or not. If there is a password file, then the ALTER USER will change the password (well, its hash) both in the database and in the password file.

               

               

              • 4. Re: changing sys password
                Emad Al-Mousa

                Ed

                 

                you are performing your simulation based on 12cR1 (12.1.0.2) which is not what we are talking about here we are talking about 12cR2,18c,...etc

                 

                check the below references:

                 

                ORA-01994 "password file missing or disabled" when changing SYS password (Doc ID 2535089.1)

                 

                 

                https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/ALTER-USER.html#GUID-9FCD038D-8193-4241-85CD-2F4723B27D44

                • 5. Re: changing sys password
                  EdStevens

                  Emad Al-Mousa wrote:

                   

                  Ed

                   

                  you are performing your simulation based on 12cR1 (12.1.0.2) which is not what we are talking about here we are talking about 12cR2,18c,...etc

                   

                  check the below references:

                   

                  ORA-01994 "password file missing or disabled" when changing SYS password (Doc ID 2535089.1)

                   

                   

                  https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/ALTER-USER.html#GUID-9FCD038D-8193-4241-85CD-2F4723B27D44

                  Ah, so it is.  I am still at 12.1.0.2 (SE2) for all of my installations.  Thanks for pointing this out.