ora-28001 ora-28002 密码过期

原创

Liujun_Deng 2022-10-22 22:26:09 博主文章分类：Oracle ©著作权

文章标签 ora-28001 ora-28002 password_life_time unlimited PASSWORD_GRACE_TIME 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者Liujun_Deng的原创作品，请联系作者获取转载授权，否则将追究法律责任

问题描述：trace文件中发现存在ora-28001告警，如下所示：
数据库：oracle 11.2.0.4 64位
Mon Oct 17 04:26:02 2022
Errors in file 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，那为什么还会出现用户密码过期的异常呢？
oracle 11g默认default概要文件中PASSWORD_LIFE_TIME为180天，若将其修改为unlimited，那么还没有被提示ora-28002 or ora-28001告警的用户不会再遇到该问题，但修改之前就遇到ora-28002 or 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 rows selected.
查密码有效期.
> 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 180
> select username,account_status,to_char(lock_date,'yyyy-mm-dd hh24:mi:ss'),to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss'),to_char(created,'yyyy-mm-dd hh24:mi:ss') from dba_users where username=’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
1 rows selected.
修改password_life_time为1分钟过期.
SQL> alter profile default limit password_life_time 1/24/60;

Profile altered.
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 Oct 22 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-dd hh24:mi:ss'),to_char(created,'yyyy-mm-dd hh24:mi:ss') from dba_users where username=’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 rows selected.
现将密码有效期设置为永久.
SQL> alter profile default limit password_life_time unlimited;

Profile altered.
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' and resource_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-dd hh24:mi:ss'),to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss'),to_char(created,'yyyy-mm-dd hh24: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;

User altered.

SQL> conn scott/tiger
Connected.
结论：scott用户恢复正常，成功处理ora-28001 ora28002的异常.

参考网址：https://blog.csdn.net/qq_43307934/article/details/84828285