oracle用户密码刷新

joson / 2023-07-31 / 原文

1、查询用户信息

col username for a25 
col account_status for a18 
col profile for a20 
select username,account_status,to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss') as expiry_d,to_char(lock_date,'yyyy-mm-dd hh24:mi:ss') as lock_d,profile from dba_users order by 2,3;

2、查询密码策略

select * from dba_profiles where RESOURCE_NAME in ('PASSWORD_REUSE_TIME','PASSWORD_REUSE_MAX'); 
select * from dba_profiles;

3、去除密码策略限制：

alter profile DEFAULT limit PASSWORD_REUSE_MAX unlimited;
alter profile DEFAULT limit PASSWORD_REUSE_TIME unlimited;

5、提取刷新用户密码

用于12c之前的密码刷新

--用户刷新将要过期的用户
select 'alter user ' || name || ' identified by values '''|| password || ''';' from sys.user$ where name in( select username from dba_users where account_status not like '%LOCK%');
--用户刷新已经过期的用户  
select 'alter user ' || name || ' identified by values '''|| password || ''';' from sys.user$ where name in( select username from dba_users where account_status= 'LOCKED');

用于12c及之后的密码刷新

--用户刷新将要过期的用户
select 'alter user ' || name || ' identified by values '''|| SPARE4 || ''';' from sys.user$ where name in( select username from dba_users where account_status not like '%LOCK%');
--用户刷新已经过期的用户  
select 'alter user ' || name || ' identified by values '''|| SPARE4 || ''';' from sys.user$ where name in( select username from dba_users where account_status= 'LOCKED');
SELECT ' alter user ' || NAME || ' identified by values ''' ||B.SPARE4 || ''';' AS reset_password FROM SYS.USER$ B INNER JOIN DBA_USERS A ON B.NAME = A.USERNAME WHERE ACCOUNT_STATUS = 'OPEN' and a.username<>'SYS' and b.spare4 is not null;

6、执行刷新用户密码，类似于以下的语句

 alter user SYSTEM identified by values 'S:7816B9AE7C7B3024EB97F75879C8F568240ACFE74DA1259A730BB702CDFA';

7、还原密码策略

alter profile DEFAULT limit PASSWORD_REUSE_MAX 5;
alter profile DEFAULT limit PASSWORD_REUSE_TIME 1800;

8、查询用户信息

col username for a25 
col account_status for a18 
col profile for a20 
select username,account_status,to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss') as expiry_d,to_char(lock_date,'yyyy-mm-dd hh24:mi:ss') as lock_d,profile from dba_users order by 2,3;