The Oracle (tm) Users' Co-Operative FAQ

Can I check that the old password before changing to a new password ?

Back to index of questions

The Quick Answer

The quick answer is to use SQL*Plus as the following examples show :

SQL> connect norman/********
Connected.

SQL> password
Changing password for NORMAN
Old password: ****
New password: ******
Retype new password: ******
ERROR:
ORA-28008: invalid old password
Password unchanged

The example above shows that the use of an incorrect old password has caused the changing of norman's new password to fail.

The next example shows the password being changed correctly when the correct old password is supplied :

SQL> password
Changing password for NORMAN
Old password: ********
New password: ******
Retype new password: ******
Password changed

So far, so good except if you run the password utility as a DBA user, you don't get prompted for the old password as follows :

SQL> connect system/************
Connected.

SQL> password norman
Changing password for norman
New password: ******
Retype new password: ******
Password changed

The Long-Winded Answer

Getting back to the original question, which was "In a change password procedure can I check that the old password has been entered correctly before changing to a new password ", one solution is the following procedure.

This needs to be created in the SYSTEM user (or similar) and the user needs to be granted select on sys.dba_users, and alter user privileges directly (not through a role) otherwise it doesn't work.

Here's the code :

create or replace procedure change_password(iUsername    in dba_users.username%type,
                                            iOldPassword in dba_users.password%type,
                                            iNewPassword in dba_users.password%type)
as
  -- Variables. 
  vOldEncryptedPassword dba_users.password%type := null; 
  vOldConfirmPassword   dba_users.password%type := null; 
  
  -- Constants. 
  cUsernameMissing      constant varchar2(100) := 'Username must be supplied.';
  cInvalidUsername      constant varchar2(100) := 'Username does not exist in this database.';
  
  cOldPasswordMissing   constant varchar2(100) := 'Old password must be supplied.';
  cOldPasswordMismatch  constant varchar2(100) := 'Old password mismatch - password not changed.';
  cNewPasswordMissing   constant varchar2(100) := 'New password must be supplied.';
  
  cPasswordChangeFailed constant varchar2(100) := 'ERROR: failed to change password.';
  
  -- Internal helper procedures and functions.  
  function GetPassword(iUsername in dba_users.username%type)
  return varchar2
  as
    vResult dba_users.password%type := null;
  begin
    select password
    into   vResult
    from   dba_users
    where  username = upper(iUsername);
    
   return vResult;
  exception
    when others then
      return null;
  end;
  
  procedure AlterPassword(iUsername in dba_users.username%type,
                          iPassword in dba_users.password%type,
                          iValues   in boolean default false)
  as
    vSQL varchar2(1000) := 'alter user ' || iUsername ||
                          ' identified by ';
  begin
    if (iValues) then
      vSQL := vSQL || 'values ''' || iPassword || '''';
    else
      vSQL := vSQL || iPassword;
    end if;   

    execute immediate vSQL;
                
  exception
    -- All that can go wrong is a malformed SQL statement 
    -- Famous last words .....
    when others then
      raise;
  end;
   
   
  
begin
  ---------------------------------------------------------------------------------------
  -- Parameter validation first is always a good idea :o)                              --
  ---------------------------------------------------------------------------------------
  
  -- Username must be supplied and be present in the database.
  -- We save the password for later on as we will need it.
  if (iUsername is null) then
    raise_application_error(-20001, cUsernameMissing);
    return;
  else
    vOldEncryptedPassword := GetPassword(iUsername);
  
    if (vOldEncryptedPassword is null) then
      raise_application_error(-20001, cInvalidUsername);
      return;
   end if;  
  end if;
  
  -- Old password must be supplied. We will be using this later to 
  -- validate the changing of the password.
  if (iOldPassword is null) then
    raise_application_error(-20001, cOldPasswordMissing);
    return;
  end if;
  
  -- New password must also be supplied - for obvious reasons.
  if (iNewPassword is null) then
    raise_application_error(-20001, cNewPasswordMissing);
    return;
  end if;
  
  ---------------------------------------------------------------------------------------
  -- We should now be in posession of a set of valid parameters, lets have some fun !  --
  ---------------------------------------------------------------------------------------
   
  -- We obtained the current encrypted password above, so now we can change the existing  
  -- password to the old one, and check to see if it is the same. If so, we are able to 
  -- continue by changing to the new password. If not, we reset the old password to the 
  -- encrypted on, and bale out.
  
  
  -- First of all, change the password to its current value - strange, but needed.   
  begin
    AlterPassword(iUsername, iOldPassword);
  exception
    when others then
      -- Cannot change the user's password to its existing value - bale out. 
      -- So far, we have changed nothing so no corrective actions required.
      raise_application_error(-20001, cPasswordChangeFailed);
  end;
  
  -- Next, fetch the 'new' old password and compare it with the 'old' old password.
  vOldConfirmPassword := GetPassword(iUsername);
  
  if (vOldEncryptedPassword <> vOldConfirmPassword) then
    begin
      -- Old password doesn't match, reset to the old one and bale out.
      AlterPassword(iUsername, vOldEncryptedPassword, true);
      raise_application_error(-20001, cOldPasswordMismatch);
      return;
    exception
      when others then
        raise;
    end;      
  end if;
    
  -- Old password matches, change password to the new one. 
  begin
    AlterPassword(iUsername, iNewPassword);
  exception
    when others then
      raise;
  end;
end;
/                                     

Examples of use

The following examples are running in user SYSTEM and are attempting to change the password for user FRED. Fred's current password is also fred. Just to prove it all :

SQL> connect fred/fred
connected

SQL>connect system/************
connected

First an attempt to use the wrong old password :

SQL> exec change_password('fred','trouble','wilma')
BEGIN change_password('fred','trouble','wilma'); END;
*
ERROR at line 1:
ORA-20001: Old password mismatch - password not changed.
ORA-06512: at "SYSTEM.CHANGE_PASSWORD", line 127
ORA-06512: at line 1

Next, not supplying a valid username :

SQL> exec change_password('nobody', 'something', 'new');
BEGIN change_password('nobody', 'something', 'new'); END;
*
ERROR at line 1:
ORA-20001: Username does not exist in this database.
ORA-06512: at "SYSTEM.CHANGE_PASSWORD", line 78
ORA-06512: at line 1

Finally, a working example

SQL> exec change_password('fred','fred','barney');
PL/SQL procedure successfully completed.

SQL>connect fred/barney
connected

I'm sure you can amend the above code to suit your system. Note that it uses execute immediate internall. If you are using an older version of Oracle, you will need to change this to use the dbms_sql package instead.

Further reading:

Back to top

Back to index of questions