今天在登入測試系統時，發現下面的報錯。

oracle@ora10g ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 21 14:35:56 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn scott/tiger
ERROR:
ORA-28001: the password has expired
Changing password for scott
New password:
Retype new password:
ERROR:
ORA-00988: missing or invalid password(s)
Password unchanged
Warning: You are no longer connected to ORACLE.

SQL> alter user scott identified by tiger; ----修改密碼，報錯，這是上次測試profile後沒有恢復預設導致的。
alter user scott identified by tiger
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20003: Password should contain at least one digit, one character and one punctuation ---密碼複雜度要求必須包含一個數字，字元，特殊字元

SQL> conn / as sysdba
Connected.

SQL> select * from dba_profiles;
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 3
DEFAULT PASSWORD_LIFE_TIME PASSWORD 60
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT PASSWORD_REUSE_TIME PASSWORD 1800
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION -----使用密碼複雜度校驗函式verify_function對密碼做校驗
DEFAULT PASSWORD_LOCK_TIME PASSWORD .0006
DEFAULT PASSWORD_GRACE_TIME PASSWORD 10
MONITORING_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
MONITORING_PROFILE SESSIONS_PER_USER KERNEL DEFAULT
MONITORING_PROFILE CPU_PER_SESSION KERNEL DEFAULT
MONITORING_PROFILE CPU_PER_CALL KERNEL DEFAULT
MONITORING_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
MONITORING_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
MONITORING_PROFILE IDLE_TIME KERNEL DEFAULT
MONITORING_PROFILE CONNECT_TIME KERNEL DEFAULT
MONITORING_PROFILE PRIVATE_SGA KERNEL DEFAULT
MONITORING_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
MONITORING_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT
32 rows selected.

SQL> select username,ACCOUNT_STATUS,PROFILE from dba_users;

USERNAME ACCOUNT_STATUS PROFILE
------------------------------ -------------------------------- ------------------------------
MGMT_VIEW OPEN DEFAULT
SYS OPEN DEFAULT
SYSTEM OPEN DEFAULT
DBSNMP OPEN MONITORING_PROFILE
SYSMAN OPEN DEFAULT
LUHJ OPEN DEFAULT
STORE OPEN DEFAULT
SCOTT EXPIRED DEFAULT -------------scott使用者使用的是預設的profile
OUTLN EXPIRED & LOCKED DEFAULT
MDSYS EXPIRED & LOCKED DEFAULT
ORDSYS EXPIRED & LOCKED DEFAULT
USERNAME ACCOUNT_STATUS PROFILE
------------------------------ -------------------------------- ------------------------------
EXFSYS EXPIRED & LOCKED DEFAULT
DMSYS EXPIRED & LOCKED DEFAULT
WMSYS EXPIRED & LOCKED DEFAULT
CTXSYS EXPIRED & LOCKED DEFAULT
ANONYMOUS EXPIRED & LOCKED DEFAULT
XDB EXPIRED & LOCKED DEFAULT
ORDPLUGINS EXPIRED & LOCKED DEFAULT
SI_INFORMTN_SCHEMA EXPIRED & LOCKED DEFAULT
OLAPSYS EXPIRED & LOCKED DEFAULT
TSMSYS EXPIRED & LOCKED DEFAULT
BI EXPIRED & LOCKED DEFAULT
USERNAME ACCOUNT_STATUS PROFILE
------------------------------ -------------------------------- ------------------------------
PM EXPIRED & LOCKED DEFAULT
MDDATA EXPIRED & LOCKED DEFAULT
IX EXPIRED & LOCKED DEFAULT
SH EXPIRED & LOCKED DEFAULT
DIP EXPIRED & LOCKED DEFAULT
OE EXPIRED & LOCKED DEFAULT
HR EXPIRED & LOCKED DEFAULT
29 rows selected.

停止對密碼函式較驗：

SQL> alter profile DEFAULT limit password_verify_function null;

Profile altered.

SQL> select * from dba_profiles;
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 3
DEFAULT PASSWORD_LIFE_TIME PASSWORD 60
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT PASSWORD_REUSE_TIME PASSWORD 1800
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL -----此時密碼校驗為空，也就是沒有限制
DEFAULT PASSWORD_LOCK_TIME PASSWORD .0006
DEFAULT PASSWORD_GRACE_TIME PASSWORD 10
MONITORING_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
MONITORING_PROFILE SESSIONS_PER_USER KERNEL DEFAULT
MONITORING_PROFILE CPU_PER_SESSION KERNEL DEFAULT
MONITORING_PROFILE CPU_PER_CALL KERNEL DEFAULT
MONITORING_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
MONITORING_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
MONITORING_PROFILE IDLE_TIME KERNEL DEFAULT
MONITORING_PROFILE CONNECT_TIME KERNEL DEFAULT
MONITORING_PROFILE PRIVATE_SGA KERNEL DEFAULT
MONITORING_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
MONITORING_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT
32 rows selected.

再次修改密碼
SQL> alter user scott identified by tiger;

User altered.

SQL> conn scott/tiger
Connected.

同樣，可以修改密碼重用的限制。

SQL> alter profile DEFAULT limit password_reuse_time unlimited;
Profile altered.

SQL> select * from dba_profiles;
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 3
DEFAULT PASSWORD_LIFE_TIME PASSWORD 60
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD .0006
DEFAULT PASSWORD_GRACE_TIME PASSWORD 10
MONITORING_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
MONITORING_PROFILE SESSIONS_PER_USER KERNEL DEFAULT
MONITORING_PROFILE CPU_PER_SESSION KERNEL DEFAULT
MONITORING_PROFILE CPU_PER_CALL KERNEL DEFAULT
MONITORING_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
MONITORING_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
MONITORING_PROFILE IDLE_TIME KERNEL DEFAULT
MONITORING_PROFILE CONNECT_TIME KERNEL DEFAULT
MONITORING_PROFILE PRIVATE_SGA KERNEL DEFAULT
MONITORING_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
MONITORING_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT
32 rows selected.

profile對密碼的限制

相關文章