【Oracle SQL】用户密码设置不过期

---

1. 查看用户使用的profile文件是哪个，默认是DEFAULT

select username, account_status, profile from dba_users where USERNAME = 'USER1';

2. 查看指定profile文件的密码有效期设置

select * from dba_profiles s where s.profile = 'DEFAULT' and resource_name = 'PASSWORD_LIFE_TIME';

3. 将密码有效期由默认的180天修改成无限制

注意：如果之前还没有提醒到期，则当前密码都变成永久了。如果当前已经提醒过到期了，则需要修改一次密码，才可以正常使用。

alter profile default limit password_life_time unlimited;

4. 检查已过期或已锁定的所有用户

select username, account_status from dba_users where account_status like '%EXPIRED%' or account_status like '%LOCKED%';

5. 其他：利用脚本修改用户密码，命令窗口

5.1 所有过期用户设置为脚本中给出的新密码

spool on;
set echo off; --显示sqlplus中的每个sql命令本身
set heading off; --输出域标题
set feedback off; --回显本次sql命令处理的记录条数
set serveroutput off; --显示输出 
spool D:\Shell\SQL\unlock.sql; --本机路径，默认路径为C:\Windows\system32\（无权限访问），建议D盘路径，并且不带空格
select 'alter user '|| username || ' account unlock;' from dba_users where account_status = 'EXPIRED';
spool off;
@D:\Shell\SQL\unlock.sql; --执行脚本，@为执行
spool on;
set echo off;
set heading off;
set feedback off;
set serveroutput off;
spool D:\Shell\SQL\pwchangen.sql;
select 'alter user '|| username || ' identified by "123456";' from dba_users where account_status = 'EXPIRED';
spool off;
@D:\Shell\SQL\pwchangen.sql;

5.2 所有过期用户重置为旧密码，不是设置新密码

spool on;
set echo off;
set heading off;
set feedback off;
set serveroutput off;
spool D:\Shell\SQL\unlock.sql;
select 'alter user '|| username || ' account unlock;' from dba_users where account_status like '%LOCKED%';
spool off;
@D:\Shell\SQL\unlock.sql;
set lines 300;
set echo off;
set heading off;
set feedback off;
set serveroutput off;
spool D:\Shell\SQL\pwchangeo.sql;
select 'alter user '|| username || ' identified by values ''' || spare4 || ''';' from dba_users, user$ where account_status = 'EXPIRED' and username = name;
spool off;
@D:\Shell\SQL\pwchangeo.sql;