004.如何刷新DB用户密码？
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;
4.提取刷新用户密码
- 用于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');
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');
- 用于12c及之后的密码刷新
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;
5.执行刷新用户密码
类似于以下的语句
alter user SYSTEM identified by values 'S:7816B9AE7C7B3024EB97F75879C8F568240ACFE74DA1259A730BB702CDFA';
6.还原密码策略
alter profile DEFAULT limit PASSWORD_REUSE_MAX 5;
alter profile DEFAULT limit PASSWORD_REUSE_TIME 1800;