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.