Oracle unexpire password
Jump to navigation
Jump to search
Interroga
Per vedere se ci sono utenti con password scaduta o in scadenza.
select username,profile,created from dba_users where account_status in ('EXPIRED','EXPIRED(GRACE)');
Elenca gli utenti soggetti a scadenza password ma con password non ancora scaduta
set linesize 200 pagesize 200 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:
select username,profile,created from dba_users where account_status='LOCKED';
A volte gli account non di sistema vanno in expired & locked:
select username,account_status,profile from dba_users 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 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
Versione 10G
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
(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 11G 12C ') 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 12C ') loop execute immediate 'alter user ' || c1.u || ' identified by values ''' || c1.p || ';' || c1.s || ''' '; 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 ('USER1','USER2');
select dbms_metadata.get_ddl( object_type=>'USER', name=>'USER1') from dual;