for nomal lifecycle
open --> expired(graced) -->expired
for failed attempts
open --> lock (timed) -->open or immediatly go to expired (graced)
note:password_lock_time has nothing to do with normal-lifecycle
--my testing log
oracle@ocp600 oracle]$ cat account_status.log
SQL> select * from dba_users where username='USER1';
USERNAME USER_ID PASSWORD
------------------------------ ---------- ------------------------------
ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
-------------------------------- --------- ---------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED
------------------------------ ------------------------------ ---------
PROFILE INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
EXTERNAL_NAME
--------------------------------------------------------------------------------
USER1 60 BBE7786A584F9103
OPEN 26-OCT-06
USERS TEMP 25-OCT-06
WWW DEFAULT_CONSUMER_GROUP
SQL> select * from dba_profiles where profile='WWW';
PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
WWW COMPOSITE_LIMIT KERNEL
DEFAULT
WWW SESSIONS_PER_USER KERNEL
DEFAULT
WWW CPU_PER_SESSION KERNEL
DEFAULT
WWW CPU_PER_CALL KERNEL
DEFAULT
WWW LOGICAL_READS_PER_SESSION KERNEL
DEFAULT
WWW LOGICAL_READS_PER_CALL KERNEL
DEFAULT
WWW IDLE_TIME KERNEL
DEFAULT
WWW CONNECT_TIME KERNEL
DEFAULT
WWW PRIVATE_SGA KERNEL
DEFAULT
WWW FAILED_LOGIN_ATTEMPTS PASSWORD
DEFAULT
WWW PASSWORD_LIFE_TIME PASSWORD
1
WWW PASSWORD_REUSE_TIME PASSWORD
DEFAULT
WWW PASSWORD_REUSE_MAX PASSWORD
DEFAULT
WWW PASSWORD_VERIFY_FUNCTION PASSWORD
DEFAULT
WWW PASSWORD_LOCK_TIME PASSWORD
1
WWW PASSWORD_GRACE_TIME PASSWORD
1
16 rows selected.
SQL> set lines 132
SQL> select * from dba_profiles where profile='WWW';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
WWW COMPOSITE_LIMIT KERNEL DEFAULT
WWW SESSIONS_PER_USER KERNEL DEFAULT
WWW CPU_PER_SESSION KERNEL DEFAULT
WWW CPU_PER_CALL KERNEL DEFAULT
WWW LOGICAL_READS_PER_SESSION KERNEL DEFAULT
WWW LOGICAL_READS_PER_CALL KERNEL DEFAULT
WWW IDLE_TIME KERNEL DEFAULT
WWW CONNECT_TIME KERNEL DEFAULT
WWW PRIVATE_SGA KERNEL DEFAULT
WWW FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
WWW PASSWORD_LIFE_TIME PASSWORD 1
WWW PASSWORD_REUSE_TIME PASSWORD DEFAULT
WWW PASSWORD_REUSE_MAX PASSWORD DEFAULT
WWW PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
WWW PASSWORD_LOCK_TIME PASSWORD 1
WWW PASSWORD_GRACE_TIME PASSWORD 1
16 rows selected.
SQL> select * from dba_users where username='USER1';
USERNAME USER_ID PASSWORD ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
------------------------------ ---------- ------------------------------ -------------------------------- --------- ---------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE
------------------------------ ------------------------------ --------- ------------------------------
INITIAL_RSRC_CONSUMER_GROUP
------------------------------
EXTERNAL_NAME
------------------------------------------------------------------------------------------------------------------------------------
USER1 60 BBE7786A584F9103 OPEN 26-OCT-06
USERS TEMP 25-OCT-06 WWW
DEFAULT_CONSUMER_GROUP
SQL> select to_char(sysdate,'dd-mm-yyyy HH24:mi') from dual;
TO_CHAR(SYSDATE,
----------------
25-10-2006 10:50
SQL> select to_char(sysdate,'dd-MON-yyyy HH24:mi') from dual;
TO_CHAR(SYSDATE,'
-----------------
25-OCT-2006 10:51
SQL> REM To test what happend when profile has password_lock_time limited value
SQL> select to_char(sysdate,'dd-MON-yyyy HH24:mi') from dual;
TO_CHAR(SYSDATE,'
-----------------
26-OCT-2006 10:51
SQL> select * from dba_users where username='USER1';
USERNAME USER_ID PASSWORD ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
------------------------------ ---------- ------------------------------ -------------------------------- --------- ---------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE
------------------------------ ------------------------------ --------- ------------------------------
INITIAL_RSRC_CONSUMER_GROUP
------------------------------
EXTERNAL_NAME
------------------------------------------------------------------------------------------------------------------------------------
USER1 60 BBE7786A584F9103 OPEN 26-OCT-06
USERS TEMP 25-OCT-06 WWW
DEFAULT_CONSUMER_GROUP
SQL> connect user1/user1
ERROR:
ORA-01045: user USER1 lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect /
Connected.
SQL> grant create session to user1;
Grant succeeded.
SQL> connect user1/user1
ERROR:
ORA-28002: the password will expire within 1 days
Connected.
SQL> show user;
USER is "USER1"
SQL> select * from user_users;
USERNAME USER_ID ACCOUNT_STATUS LOCK_DATE EXPIRY_DA DEFAULT_TABLESPACE
------------------------------ ---------- -------------------------------- --------- --------- ------------------------------
TEMPORARY_TABLESPACE CREATED INITIAL_RSRC_CONSUMER_GROUP
------------------------------ --------- ------------------------------
EXTERNAL_NAME
------------------------------------------------------------------------------------------------------------------------------------
USER1 60 EXPIRED(GRACE) 27-OCT-06 USERS
TEMP 25-OCT-06 DEFAULT_CONSUMER_GROUP
SQL> select to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') from user_users;
TO_CHAR(EXPIRY_DA
-----------------
27-Oct-2006 10:51
SQL> connect /
Connected.
SQL> select to_char(sysdate,'dd-MON-yyyy HH24:mi') from dual;
TO_CHAR(SYSDATE,'
-----------------
27-OCT-2006 10:52
SQL> select * from dba_users where username='USER1';
USERNAME USER_ID PASSWORD ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
------------------------------ ---------- ------------------------------ -------------------------------- --------- ---------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE
------------------------------ ------------------------------ --------- ------------------------------
INITIAL_RSRC_CONSUMER_GROUP
------------------------------
EXTERNAL_NAME
------------------------------------------------------------------------------------------------------------------------------------
USER1 60 BBE7786A584F9103 EXPIRED(GRACE) 27-OCT-06
USERS TEMP 25-OCT-06 WWW
DEFAULT_CONSUMER_GROUP
SQL> connect user1/user1
ERROR:
ORA-28001: the password has expired
Changing password for user1
Password unchanged
Warning: You are no longer connected to ORACLE.
SQL> connect /
Connected.
SQL> select * from dba_users where username='USER1';
USERNAME USER_ID PASSWORD ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
------------------------------ ---------- ------------------------------ -------------------------------- --------- ---------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE
------------------------------ ------------------------------ --------- ------------------------------
INITIAL_RSRC_CONSUMER_GROUP
------------------------------
EXTERNAL_NAME
------------------------------------------------------------------------------------------------------------------------------------
USER1 60 BBE7786A584F9103 EXPIRED 27-OCT-06
USERS TEMP 25-OCT-06 WWW
DEFAULT_CONSUMER_GROUP
SQL> select to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
TO_CHAR(EXPIRY_DA
-----------------
27-Oct-2006 10:51
SQL> REM now the status is expired and is locked for one day
SQL> REM +5 HOURS
SQL> select to_char(sysdate,'dd-MON-yyyy HH24:mi') from dual;
TO_CHAR(SYSDATE,'
-----------------
27-OCT-2006 15:52
SQL> connect user1/user1
ERROR:
ORA-28001: the password has expired
Changing password for user1
Password unchanged
Warning: You are no longer connected to ORACLE.
SQL> connect /
Connected.
SQL> select to_char(sysdate,'dd-MON-yyyy HH24:mi') from dual;
TO_CHAR(SYSDATE,'
-----------------
28-OCT-2006 11:52
SQL> select to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
TO_CHAR(EXPIRY_DA
-----------------
27-Oct-2006 10:51
SQL> select * from dba_users where username='USER1';
USERNAME USER_ID PASSWORD ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
------------------------------ ---------- ------------------------------ -------------------------------- --------- ---------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE
------------------------------ ------------------------------ --------- ------------------------------
INITIAL_RSRC_CONSUMER_GROUP
------------------------------
EXTERNAL_NAME
------------------------------------------------------------------------------------------------------------------------------------
USER1 60 BBE7786A584F9103 EXPIRED 27-OCT-06
USERS TEMP 25-OCT-06 WWW
DEFAULT_CONSUMER_GROUP
SQL> connect user1/user1
ERROR:
ORA-28001: the password has expired
Changing password for user1
Password unchanged
Warning: You are no longer connected to ORACLE.
SQL> select * from dba_users where username='USER1';
SP2-0640: Not connected
SQL> connect /
Connected.
SQL> select * from dba_users where username='USER1';
USERNAME USER_ID PASSWORD ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
------------------------------ ---------- ------------------------------ -------------------------------- --------- ---------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE
------------------------------ ------------------------------ --------- ------------------------------
INITIAL_RSRC_CONSUMER_GROUP
------------------------------
EXTERNAL_NAME
------------------------------------------------------------------------------------------------------------------------------------
USER1 60 BBE7786A584F9103 EXPIRED 27-OCT-06
USERS TEMP 25-OCT-06 WWW
DEFAULT_CONSUMER_GROUP
SQL> connect user1/user1
ERROR:
ORA-28001: the password has expired
Changing password for user1
Password unchanged
Warning: You are no longer connected to ORACLE.
SQL> select to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
SP2-0640: Not connected
SQL> connect /
Connected.
SQL> select to_char(sysdate,'dd-MON-yyyy HH24:mi') from dual;
TO_CHAR(SYSDATE,'
-----------------
29-OCT-2006 11:52
SQL> connect user1/user1
ERROR:
ORA-28001: the password has expired
Changing password for user1
Password changed
Connected.
SQL> connect /
Connected.
SQL> select to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
TO_CHAR(EXPIRY_DA
-----------------
30-Oct-2006 11:52
SQL> select * from dba_users where username='USER1';
USERNAME USER_ID PASSWORD ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
------------------------------ ---------- ------------------------------ -------------------------------- --------- ---------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE
------------------------------ ------------------------------ --------- ------------------------------
INITIAL_RSRC_CONSUMER_GROUP
------------------------------
EXTERNAL_NAME
------------------------------------------------------------------------------------------------------------------------------------
USER1 60 FFD1BA7052E17D17 OPEN 30-OCT-06
USERS TEMP 25-OCT-06 WWW
DEFAULT_CONSUMER_GROUP
SQL> select * from dba_profiles where profile='WWW';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
WWW COMPOSITE_LIMIT KERNEL DEFAULT
WWW SESSIONS_PER_USER KERNEL DEFAULT
WWW CPU_PER_SESSION KERNEL DEFAULT
WWW CPU_PER_CALL KERNEL DEFAULT
WWW LOGICAL_READS_PER_SESSION KERNEL DEFAULT
WWW LOGICAL_READS_PER_CALL KERNEL DEFAULT
WWW IDLE_TIME KERNEL DEFAULT
WWW CONNECT_TIME KERNEL DEFAULT
WWW PRIVATE_SGA KERNEL DEFAULT
WWW FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
WWW PASSWORD_LIFE_TIME PASSWORD 1
WWW PASSWORD_REUSE_TIME PASSWORD DEFAULT
WWW PASSWORD_REUSE_MAX PASSWORD DEFAULT
WWW PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
WWW PASSWORD_LOCK_TIME PASSWORD 1
WWW PASSWORD_GRACE_TIME PASSWORD 1
16 rows selected.
SQL> alter profile www limit password_lock_time unlimited;
Profile altered.
SQL> select * from dba_profiles where profile='WWW';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
WWW COMPOSITE_LIMIT KERNEL DEFAULT
WWW SESSIONS_PER_USER KERNEL DEFAULT
WWW CPU_PER_SESSION KERNEL DEFAULT
WWW CPU_PER_CALL KERNEL DEFAULT
WWW LOGICAL_READS_PER_SESSION KERNEL DEFAULT
WWW LOGICAL_READS_PER_CALL KERNEL DEFAULT
WWW IDLE_TIME KERNEL DEFAULT
WWW CONNECT_TIME KERNEL DEFAULT
WWW PRIVATE_SGA KERNEL DEFAULT
WWW FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
WWW PASSWORD_LIFE_TIME PASSWORD 1
WWW PASSWORD_REUSE_TIME PASSWORD DEFAULT
WWW PASSWORD_REUSE_MAX PASSWORD DEFAULT
WWW PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
WWW PASSWORD_LOCK_TIME PASSWORD UNLIMITED
WWW PASSWORD_GRACE_TIME PASSWORD 1
16 rows selected.
SQL> alter profile www limit failed_login_attempts 5;
Profile altered.
SQL> select * from dba_profiles where profile='WWW';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
WWW COMPOSITE_LIMIT KERNEL DEFAULT
WWW SESSIONS_PER_USER KERNEL DEFAULT
WWW CPU_PER_SESSION KERNEL DEFAULT
WWW CPU_PER_CALL KERNEL DEFAULT
WWW LOGICAL_READS_PER_SESSION KERNEL DEFAULT
WWW LOGICAL_READS_PER_CALL KERNEL DEFAULT
WWW IDLE_TIME KERNEL DEFAULT
WWW CONNECT_TIME KERNEL DEFAULT
WWW PRIVATE_SGA KERNEL DEFAULT
WWW FAILED_LOGIN_ATTEMPTS PASSWORD 5
WWW PASSWORD_LIFE_TIME PASSWORD 1
WWW PASSWORD_REUSE_TIME PASSWORD DEFAULT
WWW PASSWORD_REUSE_MAX PASSWORD DEFAULT
WWW PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
WWW PASSWORD_LOCK_TIME PASSWORD UNLIMITED
WWW PASSWORD_GRACE_TIME PASSWORD 1
16 rows selected.
SQL> REM do the same test with password_lock_time
SQL> select * from dba_users where username='USER1';
USERNAME USER_ID PASSWORD ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
------------------------------ ---------- ------------------------------ -------------------------------- --------- ---------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE
------------------------------ ------------------------------ --------- ------------------------------
INITIAL_RSRC_CONSUMER_GROUP
------------------------------
EXTERNAL_NAME
------------------------------------------------------------------------------------------------------------------------------------
USER1 60 FFD1BA7052E17D17 OPEN 30-OCT-06
USERS TEMP 25-OCT-06 WWW
DEFAULT_CONSUMER_GROUP
SQL> select to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
TO_CHAR(EXPIRY_DA
-----------------
30-Oct-2006 11:52
SQL> select to_char(sysdate,'dd-MON-yyyy HH24:mi') from dual;
TO_CHAR(SYSDATE,'
-----------------
29-OCT-2006 11:57
SQL> select to_char(sysdate,'dd-MON-yyyy HH24:mi') from dual;
TO_CHAR(SYSDATE,'
-----------------
30-OCT-2006 11:52
SQL> connect user1/user123
Connected.
SQL> connect /
Connected.
SQL> select to_char(sysdate,'dd-MON-yyyy HH24:mi') from dual;
TO_CHAR(SYSDATE,'
-----------------
30-OCT-2006 11:58
SQL> connect user1/user123
ERROR:
ORA-28002: the password will expire within 1 days
Connected.
SQL> connect /
Connected.
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA
-------------------------------- -----------------
EXPIRED(GRACE) 31-Oct-2006 11:58
SQL> select to_char(sysdate,'dd-MON-yyyy HH24:mi') from dual;
TO_CHAR(SYSDATE,'
-----------------
31-OCT-2006 11:58
SQL> connect user1/user123
ERROR:
ORA-28001: the password has expired
Changing password for user1
Password unchanged
Warning: You are no longer connected to ORACLE.
SQL> connect /
Connected.
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA
-------------------------------- -----------------
EXPIRED 31-Oct-2006 11:58
SQL> select to_char(sysdate,'dd-MON-yyyy HH24:mi') from dual;
TO_CHAR(SYSDATE,'
-----------------
01-NOV-2006 11:58
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA
-------------------------------- -----------------
EXPIRED 31-Oct-2006 11:58
SQL> connect user1/user123
ERROR:
ORA-28001: the password has expired
Changing password for user1
Password unchanged
Warning: You are no longer connected to ORACLE.
SQL> connect /
Connected.
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA
-------------------------------- -----------------
EXPIRED 31-Oct-2006 11:58
SQL> select to_char(sysdate,'dd-MON-yyyy HH24:mi') from dual;
TO_CHAR(SYSDATE,'
-----------------
02-NOV-2006 11:58
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA
-------------------------------- -----------------
EXPIRED 31-Oct-2006 11:58
SQL> connect user1/user123
ERROR:
ORA-28001: the password has expired
Changing password for user1
Password unchanged
Warning: You are no longer connected to ORACLE.
SQL> connect /
Connected.
SQL> REM colusion: from here we can see password_lock_time has nothing to do with open-expired(grace)-expired lifecycle
SQL> REM now continue to test function of password_lock_time
SQL> select to_char(sysdate,'dd-MON-yyyy HH24:mi') from dual;
TO_CHAR(SYSDATE,'
-----------------
02-NOV-2006 12:05
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA
-------------------------------- -----------------
EXPIRED 31-Oct-2006 11:58
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') ,to_char(lock_date,'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA TO_CHAR(LOCK_DATE
-------------------------------- ----------------- -----------------
EXPIRED 31-Oct-2006 11:58
SQL> connect user1/user123
ERROR:
ORA-28001: the password has expired
Changing password for user1
Password changed
Connected.
SQL> connect /
Connected.
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA
-------------------------------- -----------------
OPEN 03-Nov-2006 12:06
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') ,to_char(lock_date,'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA TO_CHAR(LOCK_DATE
-------------------------------- ----------------- -----------------
OPEN 03-Nov-2006 12:06
SQL> connect user1/user123
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect user1/user123
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect user1/user123
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect /
Connected.
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') ,to_char(lock_date,'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA TO_CHAR(LOCK_DATE
-------------------------------- ----------------- -----------------
OPEN 03-Nov-2006 12:06
SQL> connect user1/user123
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') ,to_char(lock_date,'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
SP2-0640: Not connected
SQL> connect /
Connected.
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') ,to_char(lock_date,'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA TO_CHAR(LOCK_DATE
-------------------------------- ----------------- -----------------
OPEN 03-Nov-2006 12:06
SQL> connect user1/user123
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect user1/user123
ERROR:
ORA-28000: the account is locked
SQL> connect /
Connected.
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') ,to_char(lock_date,'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA TO_CHAR(LOCK_DATE
-------------------------------- ----------------- -----------------
LOCKED(TIMED) 03-Nov-2006 12:06 02-Nov-2006 12:08
SQL> select * from dba_profiles where profile='WWW';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
WWW COMPOSITE_LIMIT KERNEL DEFAULT
WWW SESSIONS_PER_USER KERNEL DEFAULT
WWW CPU_PER_SESSION KERNEL DEFAULT
WWW CPU_PER_CALL KERNEL DEFAULT
WWW LOGICAL_READS_PER_SESSION KERNEL DEFAULT
WWW LOGICAL_READS_PER_CALL KERNEL DEFAULT
WWW IDLE_TIME KERNEL DEFAULT
WWW CONNECT_TIME KERNEL DEFAULT
WWW PRIVATE_SGA KERNEL DEFAULT
WWW FAILED_LOGIN_ATTEMPTS PASSWORD 5
WWW PASSWORD_LIFE_TIME PASSWORD 1
WWW PASSWORD_REUSE_TIME PASSWORD DEFAULT
WWW PASSWORD_REUSE_MAX PASSWORD DEFAULT
WWW PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
WWW PASSWORD_LOCK_TIME PASSWORD UNLIMITED
WWW PASSWORD_GRACE_TIME PASSWORD 1
16 rows selected.
SQL> select to_char(sysdate,'dd-MON-yyyy HH24:mi') from dual;
TO_CHAR(SYSDATE,'
-----------------
05-NOV-2006 11:58
SQL> connect user1/user123
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
SQL> CONNECT /
Connected.
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') ,to_char(lock_date,'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA TO_CHAR(LOCK_DATE
-------------------------------- ----------------- -----------------
LOCKED(TIMED) 03-Nov-2006 12:06 02-Nov-2006 12:08
SQL> rem now try to connect with correct password
SQL> connect user1/user1
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
SQL> connect /
Connected.
SQL> alter user user1 account unlock;
User altered.
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') ,to_char(lock_date,'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA TO_CHAR(LOCK_DATE
-------------------------------- ----------------- -----------------
OPEN 03-Nov-2006 12:06
SQL> connect user1/user1
ERROR:
ORA-28002: the password will expire within 1 days
Connected.
SQL> connect /
Connected.
SQL> select to_char(sysdate,'dd-MON-yyyy HH24:mi') from dual;
TO_CHAR(SYSDATE,'
-----------------
05-NOV-2006 12:00
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') ,to_char(lock_date,'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA TO_CHAR(LOCK_DATE
-------------------------------- ----------------- -----------------
EXPIRED(GRACE) 06-Nov-2006 12:00
SQL> connect user1/user123
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect user1/user123
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect user1/user123
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect user1/user123
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect user1/user123
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect user1/user123
ERROR:
ORA-28000: the account is locked
SQL> connect user1/user123
ERROR:
ORA-28000: the account is locked
SQL> connect /
Connected.
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') ,to_char(lock_date,'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA TO_CHAR(LOCK_DATE
-------------------------------- ----------------- -----------------
EXPIRED(GRACE) & LOCKED(TIMED) 06-Nov-2006 12:00 05-Nov-2006 12:01
SQL> alter user user1 account unlock;
User altered.
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') ,to_char(lock_date,'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA TO_CHAR(LOCK_DATE
-------------------------------- ----------------- -----------------
EXPIRED(GRACE) 06-Nov-2006 12:00
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') ,to_char(lock_date,'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA TO_CHAR(LOCK_DATE
-------------------------------- ----------------- -----------------
EXPIRED(GRACE) 06-Nov-2006 12:00
SQL> select to_char(sysdate,'dd-MON-yyyy HH24:mi') from dual;
TO_CHAR(SYSDATE,'
-----------------
05-NOV-2006 12:03
SQL> REM note that the expiry_date is the datetime after grace period
SQL> select to_char(sysdate,'dd-MON-yyyy HH24:mi') from dual;
TO_CHAR(SYSDATE,'
-----------------
06-NOV-2006 12:05
SQL> connect user1/user1
ERROR:
ORA-28001: the password has expired
Changing password for user1
Password changed
Connected.
SQL> connect /
Connected.
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') ,to_char(lock_date,'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA TO_CHAR(LOCK_DATE
-------------------------------- ----------------- -----------------
OPEN 07-Nov-2006 12:05
SQL> REM now testing with limted password_lock_time
SQL> select * from dba_profiles where profile='WWW';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
WWW COMPOSITE_LIMIT KERNEL DEFAULT
WWW SESSIONS_PER_USER KERNEL DEFAULT
WWW CPU_PER_SESSION KERNEL DEFAULT
WWW CPU_PER_CALL KERNEL DEFAULT
WWW LOGICAL_READS_PER_SESSION KERNEL DEFAULT
WWW LOGICAL_READS_PER_CALL KERNEL DEFAULT
WWW IDLE_TIME KERNEL DEFAULT
WWW CONNECT_TIME KERNEL DEFAULT
WWW PRIVATE_SGA KERNEL DEFAULT
WWW FAILED_LOGIN_ATTEMPTS PASSWORD 5
WWW PASSWORD_LIFE_TIME PASSWORD 1
WWW PASSWORD_REUSE_TIME PASSWORD DEFAULT
WWW PASSWORD_REUSE_MAX PASSWORD DEFAULT
WWW PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
WWW PASSWORD_LOCK_TIME PASSWORD UNLIMITED
WWW PASSWORD_GRACE_TIME PASSWORD 1
16 rows selected.
SQL> alter profile WWW limit password_lock_time 1;
Profile altered.
SQL> select * from dba_profiles where profile='WWW';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
WWW COMPOSITE_LIMIT KERNEL DEFAULT
WWW SESSIONS_PER_USER KERNEL DEFAULT
WWW CPU_PER_SESSION KERNEL DEFAULT
WWW CPU_PER_CALL KERNEL DEFAULT
WWW LOGICAL_READS_PER_SESSION KERNEL DEFAULT
WWW LOGICAL_READS_PER_CALL KERNEL DEFAULT
WWW IDLE_TIME KERNEL DEFAULT
WWW CONNECT_TIME KERNEL DEFAULT
WWW PRIVATE_SGA KERNEL DEFAULT
WWW FAILED_LOGIN_ATTEMPTS PASSWORD 5
WWW PASSWORD_LIFE_TIME PASSWORD 1
WWW PASSWORD_REUSE_TIME PASSWORD DEFAULT
WWW PASSWORD_REUSE_MAX PASSWORD DEFAULT
WWW PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
WWW PASSWORD_LOCK_TIME PASSWORD 1
WWW PASSWORD_GRACE_TIME PASSWORD 1
16 rows selected.
SQL> select to_char(sysdate,'dd-MON-yyyy HH24:mi') from dual;
TO_CHAR(SYSDATE,'
-----------------
06-NOV-2006 12:07
SQL> connect user1/user2
Connected.
SQL> connect /
Connected.
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') ,to_char(lock_date,'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA TO_CHAR(LOCK_DATE
-------------------------------- ----------------- -----------------
OPEN 07-Nov-2006 12:05
SQL> connect user1/user1
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect user1/user1
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect user1/user1
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect user1/user1
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect user1/user1
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect user1/user1
ERROR:
ORA-28000: the account is locked
SQL> connect /
Connected.
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') ,to_char(lock_date,'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA TO_CHAR(LOCK_DATE
-------------------------------- ----------------- -----------------
LOCKED(TIMED) 07-Nov-2006 12:05 06-Nov-2006 12:07
SQL> select to_char(sysdate,'dd-MON-yyyy HH24:mi') from dual;
TO_CHAR(SYSDATE,'
-----------------
06-NOV-2006 12:08
SQL> select to_char(sysdate,'dd-MON-yyyy HH24:mi') from dual;
TO_CHAR(SYSDATE,'
-----------------
06-NOV-2006 15:05
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') ,to_char(lock_date,'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA TO_CHAR(LOCK_DATE
-------------------------------- ----------------- -----------------
LOCKED(TIMED) 07-Nov-2006 12:05 06-Nov-2006 12:07
SQL> connect user1/user1
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
SQL> connect /
Connected.
SQL> select to_char(sysdate,'dd-MON-yyyy HH24:mi') from dual;
TO_CHAR(SYSDATE,'
-----------------
07-NOV-2006 15:05
SQL> REM now after lock 1 day
SQL> connect /
Connected.
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') ,to_char(lock_date,'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA TO_CHAR(LOCK_DATE
-------------------------------- ----------------- -----------------
LOCKED(TIMED) 07-Nov-2006 12:05 06-Nov-2006 12:07
SQL> connect user1/user1
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect /
Connected.
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') ,to_char(lock_date,'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA TO_CHAR(LOCK_DATE
-------------------------------- ----------------- -----------------
OPEN 07-Nov-2006 12:05
SQL> REM now automated unlocked, however expiry_date hasn't changed as so far no sucessful login made
SQL> connect user1/user2
ERROR:
ORA-28002: the password will expire within 1 days
Connected.
SQL> connect /
Connected.
SQL> select account_status,to_char(expiry_date, 'dd-Mon-yyyy hh24:mi') ,to_char(lock_date,'dd-Mon-yyyy hh24:mi') from dba_users where username='USER1';
ACCOUNT_STATUS TO_CHAR(EXPIRY_DA TO_CHAR(LOCK_DATE
-------------------------------- ----------------- -----------------
EXPIRED(GRACE) 08-Nov-2006 15:06
SQL> rem conclusion: from here we now that password_lock_time only affects lock.
SQL> spool off
--other's testing log
More details show below
Oracle的用户帐户管理里面大有学问了, 控制非常灵活, 设计非常精巧, 如果我们能够在我们自己的数据库应用程序中应用这种技术, 相信用户也会非常开心的^^.
用户的Accountstatus一共有一下几种情况.
SQL> select * from user_astatus_map;
STATUS# STATUS
---------- --------------------------------
0 OPEN 当前帐户是开放的用户可以自由登录
1 EXPIRED 当前帐户已经过期, 用户必须在修改密码以后才可以登录系统, 在登录的时候, 系统会提示修改密码
2 EXPIRED(GRACE) 这是有password_grace_time定义的一个时间段, 在用户密码过期以后的第一次登录, 系统会提示用户,密码在指定的时间段以后会过期, 需要及时修改系统密码.
4 LOCKED(TIMED) 这是一个有条件的帐户锁定日期, 由password_lock_time进行控制, 在lock_date加上password_lock_time的日期以后,帐户会自动解锁 upon sucessful logon.
8 LOCKED 帐户是锁定的,用户不可以登录, 必须由安全管理员将帐户打开用户才可以登录.
5 EXPIRED & LOCKED(TIMED)
6 EXPIRED(GRACE) & LOCKED(TIMED)
9 EXPIRED & LOCKED
10 EXPIRED(GRACE) & LOCKED
9 rows selected.
下面大概解释一下open.locked,expired,EXPIRED(GRACE) ,LOCKED(TIMED) ,
其余几个概念就是这几个状态的组合:-), 我就不多言了.
1. open 当前用户是开放的可以使用的.
SQL> select username,account_status from dba_users where username = 'TONGJW';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
TONGJW OPEN
SQL> conn tongjw
Enter password: *******
Connected.
2. expired 表示用户已经过期, 登录的时候, 如果密码输入正确,系统会提示重新输入密码的.
SQL> conn jfdata/jf
Connected.
SQL> alter user tongjw account unlock;
User altered.
SQL> alter user tongjw password expire;
User altered.
SQL> conn tongjw
Enter password: *******
ERROR:
ORA-28001: the password has expired
Changing password for tongjw
New password: *******
Retype new password: *******
Password changed
Connected.
3. locked 表明当前帐户被锁了, 不可以登录的.
SQL> conn jfdata/jf
Connected.
SQL> alter user tongjw account lock;
User altered.
SQL> conn tongjw
Enter password: *******
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
expired (grace) 与 locked (timed) 是有系统的profile来进行控制的.
4. expired (grace) , 通过profile的password_grace_time进行控制.
password_grace_time 指的是在你的密码已经过期以后, 第一次登录时间开始往后统计, 使系统可以使用的日期限度.
SQL> select username,account_status,lock_date,expiry_date,profile
2 from dba_users where username = 'TONGJW'
3 /
USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- --------- ------------------------------
TONGJW OPEN DEFAULT
SQL> create profile test limit password_life_time 10 password_grace_time 3;
Profile created.
SQL> alter user tongjw profile test;
User altered.
SQL> select username,account_status,lock_date,expiry_date,profile
2 from dba_users where username = 'TONGJW'
3 /
USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- --------- ------------------------------
TONGJW OPEN 15-APR-05 TEST
为什么这个地方是 05-4-15日到期, 我还需要进一步学习^^.
SQL> host
[oracle@TzDbTzcenter2 ]$ su
Password: ********
我们修改对应的系统时间, 使其在密码过期的日期以后.
[root@TzDbTzcenter2 ]# date '04161528'
Sat Apr 16 15:28:00 CST 2005
[root@TzDbTzcenter2 ]# exit
[oracle@TzDbTzcenter2 ]$ exit
SQL> select username,account_status,lock_date,expiry_date,profile
2 from dba_users where username = 'TONGJW'
3 /
USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- --------- ------------------------------
TONGJW OPEN 15-APR-05 TEST
SQL> conn tongjw/tongjw
ERROR:
ORA-28002: the password will expire within 3 days
Connected.
SQL> conn jfdata/jf
Connected.
SQL> select username,account_status,lock_date,expiry_date,profile
2 from dba_users where username = 'TONGJW'
3 /
USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- --------- ------------------------------
TONGJW EXPIRED(GRACE) 19-APR-05 TEST
SQL> host
[oracle@TzDbTzcenter2 ]$ su
Password: ********
[root@TzDbTzcenter2 ]# date '04201529'
Wed Apr 20 15:29:00 CST 2005
[root@TzDbTzcenter2 ]# exit
[oracle@TzDbTzcenter2 ]$ exit
SQL> select username,account_status,lock_date,expiry_date,profile
2 from dba_users where username = 'TONGJW'
3 /
USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- --------- ------------------------------
TONGJW EXPIRED(GRACE) 19-APR-05 TEST
SQL> conn tongjw/tongjw
ERROR:
ORA-28001: the password has expired
Changing password for tongjw
New password:
Retype new password:
ERROR:
ORA-00988: missing or invalid password(s)
Password unchanged
Warning: You are no longer connected to ORACLE.
SQL> conn jfdata/jf
Connected.
SQL> select username,account_status,lock_date,expiry_date,profile
2 from dba_users where username = 'TONGJW'
3 /
USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- --------- ------------------------------
TONGJW EXPIRED 19-APR-05 TEST
SQL> conn tongjw/tongjw
ERROR:
ORA-28001: the password has expired
Changing password for tongjw
New password: ******
Retype new password: ******
Password changed
Connected.
SQL> conn jfdata/
Enter password: **
Connected.
SQL> select username,account_status,lock_date,expiry_date,profile
2 from dba_users where username = 'TONGJW'
3 /
USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- --------- ------------------------------
TONGJW OPEN 30-APR-05 TEST
SQL>
如果该用户, 没有登录, 对应的expiry_date是不会发生改变的,
只要铜壶登录, 对应的expiry_date就会进行更新, 当然也会提示用户修改对应的密码,
也就是说, 这个日期是在用户登录的时候进行更新的,
更进一步, 我们可由此断定, Oracle是在用户登录的时候, 取出用户对应的这些信息,
if expiry_date > sysdate then
enter into
else if expiry_date < sysdate then
if grace_time is set then
expiry_date = sysdate + expiry_date;
else
prompt your passwors has expired.
end if
end if;
用户的密码过期路径可以大略的表示如下.
t1 设置profile的时间/或者用户重新修改密码的时间05-4-5
t2 用户在密码过期以后,第一次登录数据库的时间, 05-4-16 password_change_time + life_time
t3 用户密码真正过期的时间, 05-04-19 05-04-16 + grace_time
t4 修改密码的时间, 05-04-19...
t1 t2 t3 t4
|<-----------密码正常时期------------------>|<------grace expird----->|<-------expired------------>|
|---------------------------------------------------------------------------------------------------------------------
5. lock (timed) 可以这么理解,a. 当前系统是锁定的, b. 这个锁定有一个时间限制,是timed. 这个时间跨度有profile的password_lock_time来进行控制.
SQL> select username,account_status,lock_date,expiry_date,profile
2 from dba_users where username = 'TONGJW'
3 /
USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- --------- ------------------------------
TONGJW OPEN DEFAULT
SQL> create profile test limit
2 failed_login_attempts 3
3 password_lock_time 3
4 /
Profile created.
SQL> alter user tongjw profile test;
User altered.
SQL> select username,account_status,lock_date,expiry_date,profile
2 from dba_users where username = 'TONGJW'
3 /
USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- --------- ------------------------------
TONGJW OPEN TEST
SQL> alter user tongjw identified by tongjw
2 /
User altered.
SQL> conn tongjw/tong
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn tongjw/tongj
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn tongjw/tong1
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn jfdata/jf
Connected.
SQL> select username,account_status,lock_date,expiry_date,profile
2 from dba_users where username = 'TONGJW'
3 /
USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- --------- ------------------------------
TONGJW LOCKED(TIMED) 26-MAR-05 TEST
SQL> host
[oracle@TzDbTzcenter2 ]$ su
Password: ********
[root@TzDbTzcenter2 ]# date '04301603'
Sat Apr 30 16:03:00 CST 2005
[root@TzDbTzcenter2 ]# exit
[oracle@TzDbTzcenter2 ]$ exit
SQL> select username,account_status,lock_date,expiry_date,profile
2 from dba_users where username = 'TONGJW'
3 /
USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- --------- ------------------------------
TONGJW LOCKED(TIMED) 26-MAR-05 TEST
SQL> conn tongjw/tongjw
Connected.
SQL>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment