Oracle unexpire password

Interroga

Per vedere se ci sono utenti con password scaduta o in scadenza.

select username from dba_users where account_status in ('EXPIRED','EXPIRED(GRACE)');
select username,expiry_date,account_status from dba_users where expiry_date is not null and account_status IN ('OPEN','EXPIRED(GRACE)');

Per vedere se ci sono account bloccati:

select username from dba_users where account_status='LOCKED';

Normalmente gli account di sistema sono in stato 'EXPIRED & LOCKED':

da SQLPLUS bisogna disattivare l'opzione define per consentire valori con &

set define off
select username from dba_users where account_status='EXPIRED & LOCKED';

Unlock

alter user <username> account unlock;

Visualizza stato utenti

col username for a25
col profile for a30
set linesize 200
set pagesize 200
select USERNAME,ACCOUNT_STATUS,PROFILE from dba_users;

Imposta per evitare la scadenza

Per database singoli e PDB:

create profile nopasswdexpire_profile limit PASSWORD_LIFE_TIME unlimited FAILED_LOGIN_ATTEMPTS unlimited PASSWORD_VERIFY_FUNCTION null;

set define off

begin
for c1 in (select username from dba_users where profile='DEFAULT' and account_status!='EXPIRED & LOCKED')
loop
  begin
   execute immediate 'alter user ' || c1.username || ' profile nopasswdexpire_profile';
   exception
    when others then
	  null;
  end;
end loop;
end;
/

Per CDB:

create profile c##nopasswdexpire_profile limit PASSWORD_LIFE_TIME unlimited FAILED_LOGIN_ATTEMPTS unlimited PASSWORD_VERIFY_FUNCTION null;

set define off

begin
for c1 in (select username from dba_users where profile='DEFAULT' and account_status!='EXPIRED & LOCKED')
loop
  begin
   execute immediate 'alter user ' || c1.username || ' profile c##nopasswdexpire_profile';
   exception
    when others then
	  null;
  end;
end loop;
end;
/

Password Unexpire

(Bisogna scrivere un unica procedura che gestisca tutti e tre i casi)

Versione 11G

begin
 for c1 in (select t1.username u,t2.password p,t2.spare4 s 
              from dba_users t1, sys.user$ t2 
             where t2.name=t1.username 
               and t1.account_status in ('EXPIRED','EXPIRED(GRACE)') 
               and t1.password_versions='10G 11G ')
 loop
  execute immediate 'alter user ' || c1.u || ' identified by values ''' || c1.p || ';' || c1.s || ''' ';
 end loop;
end;
/

begin
 for c1 in (select t1.username u,t2.password p,t2.spare4 s 
              from dba_users t1, sys.user$ t2 
             where t2.name=t1.username
               and t1.account_status in ('EXPIRED','EXPIRED(GRACE)')
               and t1.password_versions='11G ')
 loop
  execute immediate 'alter user ' || c1.u || ' identified by values ''' || c1.s || ''' ';
 end loop;
end;
/

begin
 for c1 in (select t1.username u,t2.password p,t2.spare4 s 
              from dba_users t1, sys.user$ t2 
             where t2.name=t1.username 
               and t1.account_status in ('EXPIRED','EXPIRED(GRACE)') 
               and t1.password_versions='10G ')
 loop
  execute immediate 'alter user ' || c1.u || ' identified by values ''' || c1.p || ''' ';
 end loop;
end;
/

Versione 12C

begin
 for c1 in (select t1.username u,t2.password p,t2.spare4 s 
              from dba_users t1, sys.user$ t2 
             where t2.name=t1.username 
               and t1.account_status in ('EXPIRED','EXPIRED(GRACE)') 
               and t1.password_versions='10G 11G 12C ')
 loop
  execute immediate 'alter user ' || c1.u || ' identified by values ''' || c1.p || ';' || c1.s || ''' ';
 end loop;
end;
/