ORA-28002 Even If Default Prpfile PASSWORD_LIFE_TIME Has UNLIMITED Limit(用户profile密码生命周期无限制,密码仍然过期锁定的情形)
ORA-28002 Even If Default Prpfile PASSWORD_LIFE_TIME Has UNLIMITED Limit(用户profile密码生命周期无限制,密码仍然过期锁定的情形)
上周也就是2013年的最后一天，突然客户反应有些WEB应用页面报错只提到了ora-28002, 我立即用sqlplus 登录提示是ora-28001,显然密码已经过期, 我清清楚记的才因为一起类似问题, 前不久把所有的oracle 数据库应用用户profile PASSWORD_LIFE_TIME改为unlimited，怎么还会这样呢?
看一下当时的情况
[oracle@dbserver40 ~]$ sqlplus xmsb_bjqx/ .. Enter password: ERROR: ORA-28001: ?Ч sys@PORA40>select * from dba_profiles; PROFILE RESOURCE_NAME RESOURCE LIMIT ------------------------------ -------------------------------- -------- ---------------------------------------- ... have truncated DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL DEFAULT PASSWORD_LOCK_TIME PASSWORD 1 DEFAULT PASSWORD_GRACE_TIME PASSWORD 7 ... sys@PORA40>select username,account_status,profile from dba_users; USERNAME ACCOUNT_STATUS PROFILE ------------------------------ -------------------------------- ------------------------------ ... XMSB_SH EXPIRED DEFAULT XMSB_BJQX EXPIRED DEFAULT ... 情况紧急对密码做了重置处理解决了expired的问题 sys@PORA40>alter user xmsb_bjqx identified by "xxxxxxx";
MOS 有记载一种情况
ORA-28002 Even If Default Prpfile Has UNLIMITED Limit (Doc ID 292093.1)
Applies to
Release: 9.2 to 11.1
Changes
The script for password management utlpwdmg.sql has been run.
After that the the default profile has been modified to have unlimited limits and no verify_function is imposed now.
Cause
all the users having profile as default (dba_users.profile) will have the password restrictions mentioned in the verify_function. Once done that, 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.
Solution
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.
我的当前版本是11.2.0.3.6,而且上面正如你看到的PASSWORD_VERIFY_FUNCTION 值就是NULL,所以不属于这样情况,后来怀疑是10G升级11G时是由于impdp 把用户过期的信息导入的，结果也排除掉了。
看一下用户信息基表信息
11G以前是$ORACLE_HOME/rdbms/admin/sql.bsq
11G起是$ORACLE_HOME/rdbms/admin/dcore.bsq
create table user$ /* user table */
( user# number not null, /* user identifier number */
name varchar2("M_IDEN") not null, /* name of user */
type# number not null, /* 0 = role, 1 = user */
password varchar2("M_IDEN"), /* encrypted password */
datats# number not null, /* default tablespace for permanent objects */
tempts# number not null, /* default tablespace for temporary tables */
ctime date not null, /* user account creation time */
ptime date, /* password change time */
exptime date, /* actual password expiration time */
ltime date, /* time when account is locked */
resource$ number not null, /* resource profile# */
audit$ varchar2("S_OPFL"), /* user audit options */
defrole number not null, /* default role indicator: */
/* 0 = no roles, 1 = all roles granted, 2 = roles in defrole$ */
defgrp# number, /* default undo group */
defgrp_seq# number, /* global sequence number for the grp *
spare varchar2("M_IDEN"), /* reserved for future */
astatus number default 0 not null, /* status of the account */
/* 1 = Locked, 2 = Expired, 3 = Locked and Expired, 0 - open */
...
sys@PORA40>select user#,name,ctime,exptime from user$ ;
USER# NAME CTIME EXPTIME
-------------------- ------------------------------ ------------------- -------------------
...
86 XMSB_BJQX 2013-06-26 15:35:19 2013-12-30 15:42:23
87 XMSB_SH 2013-06-26 15:35:57 2013-12-30 16:16:18
Tip:
exptime columns value was actual password expiration time ,XMSB_BJQX password actual exptime was 2013-12-30 15:42:23.
再看修改profile 对用户过期信息的影响是不是immediate的?
情况一
sys@ORCL>select * from dba_profiles where profile='DEFAULT' and RESOURCE_NAME='PASSWORD_LIFE_TIME'; PROFILE RESOURCE_NAME RESOURCE LIMIT ------------------------------ -------------------------------- -------- ---------------------------------------- DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 sys@ORCL>select username,EXPIRY_DATE,profile,account_status from dba_users order by user_id; ... USERNAME EXPIRY_DAT PROFILE ACCOUNT_STATUS ------------------------------ ---------- ------------------------------ -------------------------------- KEYAN 2014-01-11 DEFAULT OPEN ... sys@ORCL>alter profile default limit PASSWORD_LIFE_TIME 365; sys@ORCL>select username,EXPIRY_DATE,profile,account_status from dba_users order by user_id; USERNAME EXPIRY_DAT PROFILE ACCOUNT_STATUS ------------------------------ ---------- ------------------------------ -------------------------------- KEYAN 2014-07-15 DEFAULT OPEN sys@ORCL>alter profile default limit PASSWORD_LIFE_TIME unlimited; sys@ORCL>select username,EXPIRY_DATE,profile,account_status from dba_users order by user_id; USERNAME EXPIRY_DAT PROFILE ACCOUNT_STATUS ------------------------------ ---------- ------------------------------ -------------------------------- KEYAN DEFAULT OPEN
情况二
sys@ANBOB>select * from dba_profiles;
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7
sys@ANBOB>alter profile default limit PASSWORD_LIFE_TIME 30;
sys@ANBOB>select user#,name,ctime,ptime,exptime from user$ ;
USER# NAME CTIME PTIME EXPTIME
-------------------- ------------------------------ ---------- ---------- ----------
95 ICME6 2013-12-02 2013-12-02
TIP:
2014-01-09(EXPIRY_DATE)=2013-12-02(PTIME)+30(PASSWORD_LIFE_TIME)+(7)PASSWORD_GRACE_TIME
sys@ANBOB>conn icme6
Enter password:
ERROR:
ORA-28002: the password will expire within 7 days
sys@ANBOB>conn / as sysdba
sys@ANBOB>select user#,name,ctime,ptime,exptime from user$ ;
USER# NAME CTIME PTIME EXPTIME
-------------------- ------------------------------ ---------- ---------- ----------
95 ICME6 2013-12-02 2013-12-02 2014-01-09
sys@ANBOB>select username,profile,account_status from dba_users where username='ICME6';
USERNAME PROFILE ACCOUNT_STATUS
------------------------------ ------------------------------ --------------------------------
ICME6 DEFAULT EXPIRED(GRACE)
sys@ANBOB>alter profile default limit PASSWORD_LIFE_TIME 100;
Profile altered.
sys@ANBOB>select user#,name,ctime,ptime,exptime from user$ ;
USER# NAME CTIME PTIME EXPTIME
-------------------- ------------------------------ ---------- ---------- ----------
95 ICME6 2013-12-02 2013-12-02 2014-01-09
sys@ANBOB>alter profile default limit PASSWORD_LIFE_TIME unlimited;
sys@ANBOB>select user#,name,ctime,ptime,exptime from user$ ;
USER# NAME CTIME PTIME EXPTIME
-------------------- ------------------------------ ---------- ---------- ----------
95 ICME6 2013-12-02 2013-12-02 2014-01-09
sys@ANBOB>select username,profile,EXPIRY_DATE,account_status from dba_users where username='ICME6';
USERNAME PROFILE EXPIRY_DATE ACCOUNT_STATUS
-------------------- ------------------------------ ------------------- --------------------------------
ICME6 DEFAULT 2014-01-09 11:48:16 EXPIRED(GRACE)
NOTE:
可以看到如果用户已经进入EXPIRED(GRACE)期, 再修改profile 此用户的 EXPIRY_DATE 不再影响(延长). 我遇到的案件应该就是这种。
Summary:
修改profile 前需要验证当前profile所有用户是否有EXPIRED(GRACE),EXPIRED状态，这些状态下修改的profile不会对当前已过期用户做延期更新，这种情况下修改profile后需要再做
alter user
在11r1及之前版本有时会因为PROFILE.PASSWORD_VERIFY_FUNCTION不为NULL，同样在ULIMITED情况遇ora-28002.
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。