首页 » ORACLE » 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密码生命周期无限制,密码仍然过期锁定的情形)

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 identified by 更新
在11r1及之前版本有时会因为PROFILE.PASSWORD_VERIFY_FUNCTION不为NULL,同样在ULIMITED情况遇ora-28002.

打赏

对不起,这篇文章暂时关闭评论。