Oracle unexpire password: differenze tra le versioni
Jump to navigation
Jump to search
Nessun oggetto della modifica |
|||
(24 versioni intermedie di uno stesso utente non sono mostrate) | |||
Riga 3: | Riga 3: | ||
<pre> |
<pre> |
||
set linesize 200 pagesize 200 |
|||
select username,profile,created from dba_users where account_status in ('EXPIRED','EXPIRED(GRACE)'); |
|||
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; |
|||
</pre> |
</pre> |
||
Riga 10: | Riga 13: | ||
<pre> |
<pre> |
||
set linesize 200 pagesize 200 |
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)'); |
select username,profile,expiry_date,account_status from dba_users where expiry_date is not null and account_status IN ('OPEN','EXPIRED(GRACE)'); |
||
</pre> |
</pre> |
||
Riga 16: | Riga 21: | ||
<pre> |
<pre> |
||
set linesize 200 pagesize 200 |
|||
select username,profile,created from dba_users where account_status='LOCKED'; |
|||
col username for a30 |
|||
col profile for a40 |
|||
select username,profile,created,lock_date from dba_users where account_status in ('LOCKED(TIMED)','LOCKED'); |
|||
</pre> |
</pre> |
||
A volte gli account non di sistema vanno in expired & locked: |
|||
<pre> |
|||
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; |
|||
</pre> |
|||
Normalmente gli account di sistema sono in stato <code>'EXPIRED & LOCKED'</code>: |
Normalmente gli account di sistema sono in stato <code>'EXPIRED & LOCKED'</code>: |
||
Riga 24: | Riga 39: | ||
<pre> |
<pre> |
||
set define off |
set define off |
||
select username,profile from dba_users where account_status='EXPIRED & LOCKED'; |
select username,profile,created,lock_date from dba_users where account_status='EXPIRED & LOCKED'; |
||
</pre> |
</pre> |
||
Riga 85: | Riga 100: | ||
==Versione 10G== |
==Versione 10G== |
||
Non usare su versione 11 o successive. |
|||
<pre> |
<pre> |
||
begin |
begin |
||
Riga 100: | Riga 116: | ||
==Versione 11G== |
==Versione 11G== |
||
<pre> |
|||
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; |
|||
</pre> |
|||
===PL/SQL=== |
|||
(Bisogna scrivere un unica procedura che gestisca tutti e tre i casi) |
(Bisogna scrivere un unica procedura che gestisca tutti e tre i casi) |
||
<pre> |
<pre> |
||
Riga 140: | Riga 166: | ||
/ |
/ |
||
</pre> |
</pre> |
||
==Versione 12C== |
==Versione 12C== |
||
<pre> |
|||
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; |
|||
/ |
|||
</pre> |
|||
<pre> |
|||
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; |
|||
/ |
|||
</pre> |
|||
<pre> |
|||
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; |
|||
/ |
|||
</pre> |
|||
===versione singola=== |
|||
<pre> |
<pre> |
||
begin |
begin |
||
Riga 147: | Riga 216: | ||
where t2.name=t1.username |
where t2.name=t1.username |
||
and t1.account_status in ('EXPIRED','EXPIRED(GRACE)') |
and t1.account_status in ('EXPIRED','EXPIRED(GRACE)') |
||
and t1.password_versions |
and t1.password_versions in ('11G 12C ','10G 11G 12C ') ) |
||
loop |
loop |
||
execute immediate 'alter user ' || c1.u || ' identified by values ''' || c1.p || ';' || c1.s || ''' '; |
execute immediate 'alter user ' || c1.u || ' identified by values ''' || c1.p || ';' || c1.s || ''' '; |
||
end loop; |
|||
end; |
|||
/ |
|||
</pre> |
|||
===Versione con cambio di profilo=== |
|||
<pre> |
|||
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 loop; |
||
end; |
end; |
||
Riga 161: | Riga 248: | ||
from dba_users t1, sys.user$ t2 |
from dba_users t1, sys.user$ t2 |
||
where t2.name=t1.username |
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##%'); |
|||
and username in ('USER1','USER2'); |
|||
</pre> |
</pre> |
||
Riga 168: | Riga 255: | ||
select dbms_metadata.get_ddl( object_type=>'USER', name=>'USER1') from dual; |
select dbms_metadata.get_ddl( object_type=>'USER', name=>'USER1') from dual; |
||
</pre> |
</pre> |
||
==Ripristina password== |
|||
Nel caso che il profilo utente non consenta il riutilizzo di password o l'utilizzo di password fuori regola: |
|||
<pre> |
|||
-- |
|||
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'; |
|||
</pre> |
|||
[[Categoria:Database]] |
[[Categoria:Database]] |
Versione attuale delle 12:34, 14 nov 2023
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; /
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';