思庄oracle技术分享-ORA-28001: the password has expired

duanweifang

已于 2022-10-28 09:44:16 修改

阅读量750

点赞数

分类专栏： oracle数据库文章标签： oracle 数据库

于 2022-10-28 09:43:55 首次发布

原文链接：http://bbs.cqsztech.com/forum.php?mod=viewthread&tid=30261&extra=page%3D1

版权

oracle数据库专栏收录该内容

215 篇文章 2 订阅

订阅专栏

问题描述：trace文件中发现存在ora-28001告警，如下所示：

数据库：oracle11.2.0.4 64位

Mon Oct17 04:26:02 2022

Errors infile d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_1228.trc (incident=169673):

ORA-00600:internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], []

ORA-00600:internal error code, arguments: [17059], [0x19BFF78CB0], [0x19BFF79548],[0x1ABFF6CB90], [], [], [], [], [], [], [], []

ORA-28001:the password has expired

原因分析：经确认PASSWORD_LIFE_TIME参数已经为UNLIMITED，那为什么还会出现用户密码过期的异常呢？

oracle11g默认default概要文件中PASSWORD_LIFE_TIME为180天，若将其修改为unlimited，那么还没有被提示ora-28002or ora-28001告警的用户不会再遇到该问题，但修改之前就遇到ora-28002or ora-28001告警的用户就需要更改一次密码，此后该用户才能正常登陆且PASSWORD_LIFE_TIME变为unlimited.

1、模拟场景

查用户Profile，此次以scott用户作为测试.

>select username,profile from dba_users;

USERNAME PROFILE

--------------------------------------------------

SYS DEFAULT

SYSTEM DEFAULT

OUTLN DEFAULT

MGMT_VIEW DEFAULT

FLOWS_FILES DEFAULT

MDSYS DEFAULT

ORDSYS DEFAULT

EXFSYS DEFAULT

DBSNMP MONITORING_PROFILE

WMSYS DEFAULT

APPQOSSYS DEFAULT

USERNAME PROFILE

--------------------------------------------------

APEX_030200 DEFAULT

OWBSYS_AUDIT DEFAULT

ORDDATA DEFAULT

CTXSYS DEFAULT

ANONYMOUS DEFAULT

SYSMAN DEFAULT

XDB DEFAULT

ORDPLUGINS DEFAULT

OWBSYS DEFAULT

SI_INFORMTN_SCHEMA DEFAULT

OLAPSYS DEFAULT

USERNAME PROFILE

--------------------------------------------------

SCOTT DEFAULT

ORACLE_OCM DEFAULT

XS$NULL DEFAULT

MDDATA DEFAULT

DIP DEFAULT

APEX_PUBLIC_USER DEFAULT

SPATIAL_CSW_ADMIN_USR DEFAULT

SPATIAL_WFS_ADMIN_USR DEFAULT

30 rowsselected.

查密码有效期.

>select * from dba_profiles s where s.profile='DEFAULT' andresource_name='PASSWORD_LIFE_TIME'

PROFILE RESOURCE_NAME RESOURCE LIMIT

----------------------------------------------- -------- ---------------

DEFAULT PASSWORD_LIFE_TIME PASSWORD 180

>select username,account_status,to_char(lock_date,'yyyy-mm-ddhh24:mi:ss'),to_char(expiry_date,'yyyy-mm-ddhh24:mi:ss'),to_char(created,'yyyy-mm-dd hh24:mi:ss') from dba_users whereusername=’SCOTT’;

USERNAME ACCOUNT_STATUS TO_CHAR(LOCK_DATE,'TO_CHAR(EXPIRY_DATE TO_CHAR(CREATED,'YY

-------------------------------------------------------------- ------------------- --------------------------------------

SCOTT OPEN 2023-04-18 10:19:18 2009-08-15 00:50:14

1rows selected.

修改password_life_time为1分钟过期.

SQL>alter profile default limit password_life_time 1/24/60;

Profilealtered.

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

PROFILE RESOURCE_NAME RESOURCE LIMIT

-------------------------------------------------------------- ------------------------------------------------

DEFAULT PASSWORD_LIFE_TIME PASSWORD .0006

SQL> !date

Sat Oct22 20:51:05 CST 2022

SQL>conn scott/tiger

ERROR:

ORA-28002:the password will expire within 7 days

Connected.

SQL>select username,account_status,to_char(lock_date,'yyyy-mm-dd hh24:mi:ss'),to_char(expiry_date,'yyyy-mm-ddhh24:mi:ss'),to_char(created,'yyyy-mm-dd hh24:mi:ss') from dba_users whereusername=’SCOTT’;

USERNAME ACCOUNT_STATUS TO_CHAR(LOCK_DATE,'TO_CHAR(EXPIRY_DATE TO_CHAR(CREATED,'YY

-------------------------------------------------------------- ------------------- --------------------------------------

SCOTT EXPIRED(GRACE) 2022-10-29 20:56:10 2009-08-15 00:50:14

1 rowsselected.

现将密码有效期设置为永久.

SQL>alter profile default limit password_life_time unlimited;

Profilealtered.

SQL>conn scott/tiger

ERROR:

ORA-28002:the password will expire within 7 days

Connected.

SQL>select * from dba_profiles s where s.profile='DEFAULT' andresource_name='PASSWORD_LIFE_TIME';

PROFILE RESOURCE_NAME RESOURCE LIMIT

-------------------------------------------------------------- ------------------------------------------------

DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED

说明：成功模拟出异常场景，虽然scott用户的PASSWORD_LIFE_TIME为UNLIMITED，但登陆依然报警ora-28002.

2、解决方案

由于此处存在7天的宽限期，所以通过关键字“TRACE”查出用户信息，对于状态为EXPIRED& LOCKED的用户，可以通过相应关键字查询出来.

SQL>select * from dba_profiles where resource_name='PASSWORD_GRACE_TIME';

PROFILE RESOURCE_NAME RESOURCE LIMIT

-------------------------------------------------------------- ------------------------------------------------

DEFAULT PASSWORD_GRACE_TIME PASSWORD 7

MONITORING_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT

SQL>select username,account_status,to_char(lock_date,'yyyy-mm-ddhh24:mi:ss'),to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss'),to_char(created,'yyyy-mm-ddhh24:mi:ss') from dba_users where account_status like '%GRACE%';

USERNAME ACCOUNT_STATUS TO_CHAR(LOCK_DATE,'TO_CHAR(EXPIRY_DATE TO_CHAR(CREATED,'YY

-------------------------------------------------------------- ------------------- --------------------------------------

SCOTT EXPIRED(GRACE) 2022-10-29 20:56:10 2009-08-15 00:50:14
重置密码

SQL>alter user scott identified by tiger account unlock;

Useraltered.

SQL>conn scott/tiger

Connected.

结论：scott用户恢复正常，成功处理ora-28001ora28002的异常.