SQL> alter profile default limit PASSWORD_LIFE_TIME 10;
Profile altered.
SQL> select * from dba_profiles where profile='DEFAULT';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LIFE_TIME PASSWORD 10
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 (密码过期后的宽限期)
SQL> !date
2013年 04月 18日 星期四 16:46:26 CST
SQL> create user u1 identified by u1 ;
User created.
SQL> grant connect to u1;
Grant succeeded.
SQL> select username,account_status,expiry_date,lock_date from dba_users where username='U1';
USERNAME ACCOUNT_STATUS EXPIRY_DA LOCK_DATE
------------------------------ -------------------------------- --------- ---------
U1 OPEN 28-APR-13
[oracle@ora11gR2 ~]$ date
2013年 04月 29日 星期一 15:08:00 CST
[oracle@ora11gR2 ~]$
[oracle@ora11gR2 ~]$
[oracle@ora11gR2 ~]$
[oracle@ora11gR2 ~]$
[oracle@ora11gR2 ~]$ sqlplus u1/u1
SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 29 15:08:05 2013
Copyright (c) 1982, 2009, 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.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL>
[oracle@ora11gR2 ~]$ date
2013年 05月 06日 星期一 15:07:57 CST
[oracle@ora11gR2 ~]$ sqlplus u1/u1
SQL*Plus: Release 11.2.0.1.0 Production on Tue May 7 15:08:00 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-28001: the password has expired
Changing password for u1
New password:
============================================================================================
在帐户状态为 OPEN ,并且还没到过期时间之前修改 PROFILE 可以避免 密码过期
SQL> select username,account_status,expiry_date,lock_date from dba_users where username='U1';
USERNAME ACCOUNT_STATUS EXPIRY_DA LOCK_DATE
------------------------------ -------------------------------- --------- ---------
U1 OPEN 17-MAY-13
SQL> alter profile default limit password_life_time unlimited;
Profile altered.
SQL> select username,account_status,expiry_date,lock_date from dba_users where username='U1';
USERNAME ACCOUNT_STATUS EXPIRY_DA LOCK_DATE
------------------------------ -------------------------------- --------- ---------
U1 OPEN
SQL>
===========================================================================================
dba_users.EXPIRY_DATE 指的是密码过期时间
SQL> select username,account_status,expiry_date,lock_date from dba_users where username='U1';
USERNAME ACCOUNT_STATUS EXPIRY_DA LOCK_DATE
------------------------------ -------------------------------- --------- ---------
U1 OPEN 17-MAY-13
[root@ora11gR2 ~]# date 060715072013.55
2013年 06月 07日 星期五 15:07:55 CST
修改系统时间后用户密码已经过期,此时仍可登陆,但是会抛出错误(ORA-28002: the password will expire within 7 days)
并从过期日之后第一次登陆的时间开始算起给出7天的“宽限期”(PASSWORD_GRACE_TIME)
SQL> select username,account_status,expiry_date,lock_date from dba_users where username='U1';
USERNAME ACCOUNT_STATUS EXPIRY_DA LOCK_DATE
------------------------------ -------------------------------- --------- ---------
U1 EXPIRED(GRACE) 14-JUN-13
(14-JUN-13) - (2013年 06月 07日) = 7 ,当 ACCOUNT_STATUS 为 EXPIRED(GRACE) 时,EXPIRY_DADE 给出的是强制过期时间
哪怕此时再修改 Profile 的 PASSWORD_LIFE_TIME 也没用
SQL> alter profile default limit password_life_time unlimited;
Profile altered.
SQL> select username,account_status,expiry_date,lock_date from dba_users where username='U1';
USERNAME ACCOUNT_STATUS EXPIRY_DA LOCK_DATE
------------------------------ -------------------------------- --------- ---------
U1 EXPIRED(GRACE) 18-AUG-13
SQL>
[oracle@ora11gR2 ~]$ sqlplus u1/u1
SQL*Plus: Release 11.2.0.1.0 Production on Tue May 7 15:08:00 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-28001: the password has expired
Changing password for u1
New password:
阅读(6472) | 评论(0) | 转发(0) |