开发者社区> bisal> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

ORA-28002的一个细节

简介: 有一个库,由于设置了PASSWORD_LIFE_TIME,且到期未重置密码,账户被锁了,手工解锁后,登录发现报错ORA-28002,明明解锁了,为何还会报错? ORA-28002是一个很简单的错误号, oerr ora 2800228002, 00...
+关注继续查看

有一个库,由于设置了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的个人杂货铺,您的支持是对我最大的鼓励!共同学习,共同进步:)

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
c++代码模板之 CRTP
c++代码模板之 CRTP
96 0
MySQL锁系列(四)之 undo log
什么是undo 1) redo 记录的是对页的重做日志,undo 记录的是对事务的逆向操作 2) undo 会产生redo,undo的产生也会伴随这redo的产生,因为重启恢复的时候,可以通过redo还原这些undo的操作,以达到回滚的目的 undo有什么用 1) 用于对事务的回滚 2)用于MVCC undo的存储结构 rollback segment * 在MySQL5.
2949 0
ORA-14402的处理
SQL>  SQL> create table test_part(id number(10),name varchar2(100),cdate date)           2  partition by range(cdate...
676 0
+关注
bisal
Oracle ACE,腾讯云TVP,墨天轮MVP,Oracle 10g/11g OCP,11g OCM,Exin DevOps Master,OCMU成员,Oracle YEP成员,微信公众号:bisal的个人杂货铺
337
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载