[Oracle] Disable password expiration & unlock expired account

5 thoughts on “[Oracle] Disable password expiration & unlock expired account”

• Olivier says:

  03/01/2018 at 08:55

  Linked error :
  ORA-28001: the password has expired

  Reply
• Olivier says:

  03/01/2018 at 09:05

  Unlock users :

  DECLARE
  
  USER_PASSWORD VARCHAR2(50) := 'password';
  NB_UNLOCKED PLS_INTEGER := 0;
  
  CURSOR C_LOCKED_USERS IS
      select username, account_status, 'alter user '||username||' identified by '||USER_PASSWORD||' account unlock' as sqlStmt
      from dba_users 
      where ACCOUNT_STATUS LIKE '%EXPIRED%'
      and (
          username like 'SCOTT%'
      );
  
  BEGIN
  
      FOR lockedUser IN C_LOCKED_USERS LOOP
      
          dbms_output.put_line('Unlocking user '||lockedUser.username );
          EXECUTE IMMEDIATE lockedUser.sqlStmt;
          NB_UNLOCKED := NB_UNLOCKED+1;
      
      END LOOP;
      
      dbms_output.put_line('Nb user(s) unlocked : '||NB_UNLOCKED );
  
  END;
  

  Reply
• Olivier says:

  17/05/2018 at 13:50

  select 'alter user '||username||' identified by password account unlock; ' 
  from dba_users where ACCOUNT_STATUS LIKE '%EXPIRED%' order by username;
  

  Reply
• OLIVIER COMBE says:

  15/11/2021 at 14:55

  Lock an account :

  alter user username account lock;
  

  Reply
• OLIVIER COMBE says:

  28/05/2024 at 10:18

  Unlock all user (inside try/catch) and update default profile:

  DECLARE
   
  USER_PASSWORD VARCHAR2(50) := 'password';
  NB_UNLOCKED PLS_INTEGER := 0;
   
  CURSOR C_LOCKED_USERS IS
      select username, account_status, 'alter user '||username||' identified by '||USER_PASSWORD||' account unlock' as sqlStmt
      from dba_users 
      where ACCOUNT_STATUS LIKE '%EXPIRED%';
   
  BEGIN
  
      dbms_output.put_line('Update DEFAULT profile');
      EXECUTE IMMEDIATE 'alter profile DEFAULT limit password_life_time UNLIMITED';
   
      FOR lockedUser IN C_LOCKED_USERS LOOP
       
          BEGIN
              dbms_output.put_line('Unlock user '||lockedUser.username );
              EXECUTE IMMEDIATE lockedUser.sqlStmt;
              NB_UNLOCKED := NB_UNLOCKED+1;
          EXCEPTION 
              WHEN OTHERS 
              THEN dbms_output.put_line(lockedUser.username || ' - ERROR: ' ||SQLERRM(SQLCODE));
          END;
  
       
      END LOOP;
       
      dbms_output.put_line('Nb user(s) unlocked : '||NB_UNLOCKED );
   
  END;
  

  Reply