trunc(date)/trunc(number)
column io_date format a12
e.g
select to_char(snap_time,'yyyy-mm-dd') io_date,
...
group by to_char(snap_time,'yyyy-mm-dd')
also learned functions round and ceil .
^-^
Saturday, November 04, 2006
Tuesday, October 31, 2006
ORA-00059, MAXDATAFILES and DB_FILES
check v$controlfile_record_section to see the db_files usage
/home/liqy> kmtune |grep max |grep files
maxfiles 4096 - 4096
maxfiles_lim 32768 Y 32768
Error: ORA 59
Text: maximum number of DB_FILES exceeded
-------------------------------------------------------------------------------
Cause: An unavailable resource was requested.
The maximum number of datafiles is specified by the DB_FILES parameter
in the initialization parameter file.
When this maximum is reached, no more requests are processed.
Action: Try again when the resource is freed.
If this message occurs often, Oracle must be shut down and restarted
after increasing the DB_FILES parameter in the initialization
parameter file.
If the DB_FILES parameter cannot be changed because it is already set
to the MAXDATAFILES parameter value, set at database creation, you
must create a new control file.
- Why would one set MAXDATAFILES to anything less than the port-specific
maximum?
Increasing the value of MAXDATAFILES increases the size of the
CONTROL FILE. ( I understand this, when set it to 20000, the control file size is 75Mb)
- Why would one set DB_FILES to anything less than MAXDATAFILES?
Increasing the value of DB_FILES increases the size of the PGA, or
Program Global Area, which is allocated for every user process
connected to ORACLE.
(can't understand the underlying relationship)
For my case, MAXDATAFILES in control file is 20000, however using default value of db_files, i.e. 200, caused the problem . After specify db_files=400 and restart db, I can create more datafiles now.
/home/liqy> kmtune |grep max |grep files
maxfiles 4096 - 4096
maxfiles_lim 32768 Y 32768
Error: ORA 59
Text: maximum number of DB_FILES exceeded
-------------------------------------------------------------------------------
Cause: An unavailable resource was requested.
The maximum number of datafiles is specified by the DB_FILES parameter
in the initialization parameter file.
When this maximum is reached, no more requests are processed.
Action: Try again when the resource is freed.
If this message occurs often, Oracle must be shut down and restarted
after increasing the DB_FILES parameter in the initialization
parameter file.
If the DB_FILES parameter cannot be changed because it is already set
to the MAXDATAFILES parameter value, set at database creation, you
must create a new control file.
- Why would one set MAXDATAFILES to anything less than the port-specific
maximum?
Increasing the value of MAXDATAFILES increases the size of the
CONTROL FILE. ( I understand this, when set it to 20000, the control file size is 75Mb)
- Why would one set DB_FILES to anything less than MAXDATAFILES?
Increasing the value of DB_FILES increases the size of the PGA, or
Program Global Area, which is allocated for every user process
connected to ORACLE.
(can't understand the underlying relationship)
For my case, MAXDATAFILES in control file is 20000, however using default value of db_files, i.e. 200, caused the problem . After specify db_files=400 and restart db, I can create more datafiles now.
Monday, October 30, 2006
log file sync due to batch job has too many COMMITs
9:30am, my exention ring before I drink my coffee, developer reported that his trial run job keeps running since 5:30am.
investigating ...
no long-runing query captured. the developer told maybe because of the update is too fast and can't. However, check wait event. I saw one session started at 05:30am with wait event "log file sync". That it is.
When a user session COMMITs (or rolls back), the sessions redo information needs to be flushed to the redo logfile. The user session will post the LGWR to write all redo required from the log buffer to the redo log file. When the LGWR has finished it will post the user session. The user session waits on this wait event while waiting for LGWR to post it back to confirm all redo changes are safely on disk. [QY: this implies that commit is not immediate done as we can feel it using sql*plus]
("log file sync" applies to ROLLBACK in that once the rollback is complete the end of the rollback operation requires all changes to complete the rollback to be flushed to the redo log)
"If there are lots of short duration transactions see if it is possible to BATCH
transactions together so there are fewer distinct COMMIT operations. Each
commit has to have it confirmed that the relevant REDO is on disk. Although
commits can be "piggybacked" by Oracle reducing the overall number of
commits by batching transactions can have a very beneficial effect."
runt the report and see below
redo blocks written 2,760,350 153.4 2.8
...
redo writes 968,848 53.8 1.0
size/write=(2,760,350/968,848)*1k , consider as small
**1k here is internal redo block,related to mount point mounting option. it is not block size (i just learned that)
Also observed that transaction per second is 55, higher than usual 8.
Solution: advise developer to reduce COMMITs.
investigating ...
no long-runing query captured. the developer told maybe because of the update is too fast and can't. However, check wait event. I saw one session started at 05:30am with wait event "log file sync". That it is.
When a user session COMMITs (or rolls back), the sessions redo information needs to be flushed to the redo logfile. The user session will post the LGWR to write all redo required from the log buffer to the redo log file. When the LGWR has finished it will post the user session. The user session waits on this wait event while waiting for LGWR to post it back to confirm all redo changes are safely on disk. [QY: this implies that commit is not immediate done as we can feel it using sql*plus]
("log file sync" applies to ROLLBACK in that once the rollback is complete the end of the rollback operation requires all changes to complete the rollback to be flushed to the redo log)
"If there are lots of short duration transactions see if it is possible to BATCH
transactions together so there are fewer distinct COMMIT operations. Each
commit has to have it confirmed that the relevant REDO is on disk. Although
commits can be "piggybacked" by Oracle reducing the overall number of
commits by batching transactions can have a very beneficial effect."
runt the report and see below
redo blocks written 2,760,350 153.4 2.8
...
redo writes 968,848 53.8 1.0
size/write=(2,760,350/968,848)*1k , consider as small
**1k here is internal redo block,related to mount point mounting option. it is not block size (i just learned that)
Also observed that transaction per second is 55, higher than usual 8.
Solution: advise developer to reduce COMMITs.
Friday, October 20, 2006
Wednesday, October 18, 2006
Account Status
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>
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>
Saturday, September 16, 2006
Subscribe to:
Posts (Atom)
