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;
标签:username,status,密码,user,刷新,oracle,where,alter,select
From: https://www.cnblogs.com/shunqian/p/17593965.html