Oracle unexpire password

Interroga

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

set linesize 200 pagesize 200
col username for a30
col profile for a40
select username,profile,created,account_status from dba_users where account_status in ('EXPIRED','EXPIRED(GRACE)') order by username;

Elenca gli utenti soggetti a scadenza password ma con password non ancora scaduta

set linesize 200 pagesize 200
col username for a30
col profile for a40
select username,profile,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:

set linesize 200 pagesize 200
col username for a30
col profile for a40
select username,profile,created,lock_date from dba_users where account_status in ('LOCKED(TIMED)','LOCKED');

A volte gli account non di sistema vanno in expired & locked:

set linesize 200 pagesize 200
col username for a30
col profile for a40
select username,profile,account_status,created,lock_date from dba_users where account_status !='OPEN' order by created;

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,profile,created,lock_date 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;
/

modifica del profilo default

Verifica profilo di default:

set linesize 200 pagesize 200
col profile for a30
col limit for a20
select * from dba_profiles where profile='DEFAULT';

Modifica profilo di default:

alter profile DEFAULT limit PASSWORD_LIFE_TIME unlimited PASSWORD_GRACE_TIME unlimited FAILED_LOGIN_ATTEMPTS unlimited PASSWORD_VERIFY_FUNCTION null;
alter profile DEFAULT limit PASSWORD_LIFE_TIME unlimited PASSWORD_GRACE_TIME unlimited;

Password Unexpire

Versione 10G

Non usare su versione 11 o successive.

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

Versione 11G

set linesize 400
select 'alter user ' || t1.username || ' identified by values ''' || t2.password || ';' || t2.spare4 || ''';'
 from dba_users t1, sys.user$ t2 where t2.name=t1.username and t1.account_status in ('EXPIRED','EXPIRED(GRACE)') and t2.password is not null and t2.spare4 is not null
 union
select 'alter user ' || t1.username || ' identified by values ''' || t2.spare4 || ''';'
 from dba_users t1, sys.user$ t2 where t2.name=t1.username and t1.account_status in ('EXPIRED','EXPIRED(GRACE)') and t2.password is null and t2.spare4 is not null;

PL/SQL

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

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 ')
  loop
   execute immediate 'alter user ' || c1.u || ' identified by values ''' || c1.p || ''' ';
  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 in ('12C ','11G ','11G 12C ')
  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 in '10G 11G 12C ')
  loop
   execute immediate 'alter user ' || c1.u || ' identified by values ''' || c1.p || ';' || c1.s || ''' ';
  end loop;
 end;
 /

versione singola

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 in ('11G 12C ','10G 11G 12C ') )
 loop
  execute immediate 'alter user ' || c1.u || ' identified by values ''' || c1.p || ';' || c1.s || ''' ';
 end loop;
end;
/

Versione con cambio di profilo

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.profile='DOMAIN_USERS'
               and t1.account_status in ('EXPIRED','EXPIRED(GRACE)') 
               and t1.password_versions in ('11G 12C ','10G 11G 12C '))
 loop
  execute immediate 'alter user ' || c1.u || ' profile default';
  execute immediate 'alter user ' || c1.u || ' identified by values ''' || c1.p || ';' || c1.s || ''' ';
  execute immediate 'alter user ' || c1.u || ' profile DOMAIN_USERS';

 end loop;
end;
/

Reverse users

select 'alter user ' || t1.username || ' identified by values ''' || t2.password || ';' || t2.spare4 || ''' ;'
  from dba_users t1, sys.user$ t2
 where t2.name=t1.username
   and username in (select username from dba_users where oracle_maintained='N' and authentication_type='PASSWORD' and username not like 'C##%');

select dbms_metadata.get_ddl( object_type=>'USER', name=>'USER1') from dual;

Ripristina password

Nel caso che il profilo utente non consenta il riutilizzo di password o l'utilizzo di password fuori regola:

--

set define off

/*
create profile PASSWORD_UNLIMITED limit
 PASSWORD_LIFE_TIME UNLIMITED
 PASSWORD_REUSE_TIME UNLIMITED
 PASSWORD_REUSE_MAX UNLIMITED
 PASSWORD_VERIFY_FUNCTION NULL
 INACTIVE_ACCOUNT_TIME UNLIMITED;
*/

variable PP varchar2(120);
begin
 select profile into :PP from dba_users where username='&&1';
end;
/


select :PP from dual;
select profile from dba_users where username='&&1';

alter user &&1 profile password_unlimited;
alter user &&1 identified by "&&2" account unlock;

begin
 execute immediate 'alter user &&1 profile ' || :PP;
end;
/

select profile from dba_users where username='&&1';