Oracle unexpire password: differenze tra le versioni
Jump to navigation
Jump to search
Riga 20: | Riga 20: | ||
begin |
begin |
||
execute immediate 'alter user ' || c1.username || ' profile nopasswdexpire_profile'; |
execute immediate 'alter user ' || c1.username || ' profile nopasswdexpire_profile'; |
||
exception |
|||
when others then |
|||
null; |
|||
end; |
|||
end loop; |
|||
end; |
|||
/ |
|||
</pre> |
|||
Per CDB: |
|||
<pre> |
|||
create profile c##nopasswdexpire_profile limit PASSWORD_LIFE_TIME unlimited FAILED_LOGIN_ATTEMPTS unlimited PASSWORD_VERIFY_FUNCTION null; |
|||
begin |
|||
for c1 in (select username from dba_users where profile='DEFAULT') |
|||
loop |
|||
begin |
|||
execute immediate 'alter user ' || c1.username || ' profile c##nopasswdexpire_profile'; |
|||
exception |
exception |
||
when others then |
when others then |
Versione delle 16:12, 25 mar 2016
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)');
Per account bloccati:
select username from dba_users where account_status='LOCKED';
Imposta per evitare la scadenza
create profile nopasswdexpire_profile limit PASSWORD_LIFE_TIME unlimited FAILED_LOGIN_ATTEMPTS unlimited PASSWORD_VERIFY_FUNCTION null; begin for c1 in (select username from dba_users where profile='DEFAULT') 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; begin for c1 in (select username from dba_users where profile='DEFAULT') 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; /