ORA-28002的一个细节

有一个库，由于设置了PASSWORD_LIFE_TIME，且到期未重置密码，账户被锁了，手工解锁后，登录发现报错ORA-28002，明明解锁了，为何还会报错？

ORA-28002是一个很简单的错误号，

oerr ora 28002
28002, 00000, "the password will expire within %s days"
// *Cause:   The user's account is about to about to expire and the password needs to be changed
// *Action:  change the password or contact the DBA

可以分为两个场景说明，首先创建profile和用户t_pro_user，其中PASSWORD_LIFE_TIME设置为1，表示密码只有1天有效期，然后将其赋予t_pro_user用户，

SQL> create profile t_profile limit PASSWORD_LIFE_TIME 1;

Profile created.

SQL> create user t_pro_user identified by 123;

User created.

SQL> alter user t_pro_user profile t_profile;

User altered.

grant resource,connect to t_pro_user;

Grant succeeded.

场景一：超过PASSWORD_LIFE_TIME，未更改此值前就登录一次，会报ORA-28002

1天之后，登录就会提示，ORA-28002错误，警告密码7天内就会过期，

sqlplus t_pro_user/123

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 18 12:03:16 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:

ORA-28002: the password will expire within 7 days

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

注意：这里提示了7天，是由profile中的PASSWORD_GRACE_TIME参数控制的，默认值是7，表示密码过期之后还有多少天可以使用原密码。

此时将PASSWORD_LIFE_TIME设置为unlimited，

SQL> alter profile t_profile limit PASSWORD_LIFE_TIME unlimited;

Profile altered.

再次登录，提示相同的错误，

sqlplus t_pro_user/123

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 18 12:03:16 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:

ORA-28002: the password will expire within 7 days

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

手工修改一次新密码，

SQL> alter user t_pro_user identified by 123;

User altered.

再次登录，就不会报错了，

sqlplus t_pro_user/123

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 18 12:04:01 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

场景二：超过PASSWORD_LIFE_TIME，但不登录，直接改此参数，再次登录，不会报ORA-28002

1天之后，不登录直接修改PASSWORD_LIFE_TIME，

SQL> alter profile t_profile limit PASSWORD_LIFE_TIME unlimited;

Profile altered.

再次登录，此时未有提示ORA-28002，

sqlplus t_pro_user/123

SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 2 17:38:37 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

其实，这篇MOS文章《ORA-28002 Even If Default Profile Has Limits Set To 'UNLIMITED' (文档 ID 292093.1)》有相关介绍，

if we reset the default profile to have all the limits unlimited and set PASSWORD_VERIFY_FUNCTION to NULL, the restriction are still there because of the previous settings. Resetting the profile parameters is not enough.

如果PASSWORD_VERIFY_FUNCTION to NULL参数设置为NULL，不能仅仅依靠重新设置PASSWORD_LIFE_TIME参数。

If we change the password of the user(s) having default profile now (with PASSWORD_VERIFY_FUNCTION set to NULL), then the error ORA-28002 will not come. This is as expected because of the error ORA-28002. We can change the password of the user(s) to the same existing one.

PASSWORD_VERIFY_FUNCTION to NULL参数设置为NULL，修改了用户的密码，就不会报错ORA-28002，这也是ORA-28002错误期望的结果，当然允许设置重复的密码，重要的不是密码是什么，而是重新设置了一次。

For example:
ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION null;
alter user scott identified by tiger;
( alter user <username> identified by <same password>; )

总结：

1. 如果用户密码超过了PASSWORD_LIFE_TIME的值，未被提示ORA-28002之前对PASSWORD_LIFE_TIME进行了修改，再次登录，不会提示ORA-28002。

2. 如果用户密码超过了PASSWORD_LIFE_TIME的值，曾经登陆过并被提示ORA-28002，此时PASSWORD_LIFE_TIME进行了修改，再次登录，仍会提示ORA-28002，必须手工再改一次密码，才能消除此错。重新设置密码，这是ORA-28002错误的初衷。

对于ORA-28000错误，应该和上述两个结论一致，有兴趣可以试试。

如果您觉得此篇文章对您有帮助，欢迎关注微信公众号：bisal的个人杂货铺，您的支持是对我最大的鼓励！共同学习，共同进步:)