> 本文主要介紹Oracle密碼複雜度設定情況說明， -- by Firsouler

隨著安全越來越重要，對於網路、資料等相關安全的保護等級也提高了不少， 很多客戶都要求使用者密碼複雜度，那麼oracle設定複雜度需要注意什麼

本文以Oracle11.2.0.4 為例

資料庫預設除了密碼區分大小寫之前（只談論關於密碼複雜度問題），沒有其他限制。 也就是密碼隨便設定，當想啟用密碼限制時，可執行下列指令碼，啟用。
```
[oracle@myasm verify]$ sqlplus / as sysdba @?/rdbms/admin/utlpwdmg.sql

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 15 15:15:30 2021

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

Function created.

Grant succeeded.

Profile altered.

Function created.

Grant succeeded.
```

注意，執行後，密碼預設有效期、登入次數會有相應限制，請注意，避免因上述問題造成使用者被鎖或過期。

```
SQL> select * from dba_profiles where resource_name like '%PASSWORD%';

PROFILE 		       RESOURCE_NAME			RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT 		       PASSWORD_LIFE_TIME		PASSWORD 180
DEFAULT 		       PASSWORD_REUSE_TIME		PASSWORD UNLIMITED
DEFAULT 		       PASSWORD_REUSE_MAX		PASSWORD UNLIMITED
DEFAULT 		       PASSWORD_VERIFY_FUNCTION 	PASSWORD VERIFY_FUNCTION
DEFAULT 		       PASSWORD_LOCK_TIME		PASSWORD 1
DEFAULT 		       PASSWORD_GRACE_TIME		PASSWORD 7
```

不影響現有密碼，再次設定密碼時，會限制密碼長度,對複雜度並沒有要求

```
SQL> alter user tt identified by oracle;
alter user tt identified by oracle
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20001: Password length less than 8

SQL> alter user tt identified by oracle123456;

User altered.
```

檢查profile相關引數，發現，啟用後 PASSWORD_VERIFY_FUNCTION值為VERIFY_FUNCTION_11G。 我們將該值改為VERIFY_FUNCTION，再次測試。
```
SQL> /

PROFILE 		       RESOURCE_NAME			RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT 		       PASSWORD_VERIFY_FUNCTION 	PASSWORD VERIFY_FUNCTION_11G
MONITORING_PROFILE	       PASSWORD_VERIFY_FUNCTION 	PASSWORD DEFAULT

SQL>  alter profile default limit password_verify_function verify_function;

Profile altered.

SQL> alter user tt identified by oracle123456;
alter user tt identified by oracle123456
*
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> alter profile default limit password_verify_function null;

Profile altered.

SQL> select * from dba_profiles where resource_NAME='PASSWORD_VERIFY_FUNCTION';

PROFILE 		       RESOURCE_NAME			RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT 		       PASSWORD_VERIFY_FUNCTION 	PASSWORD NULL
MONITORING_PROFILE	       PASSWORD_VERIFY_FUNCTION 	PASSWORD DEFAULT
```

上述函式具體說明可參考 $ORACLE_HOME/rdbms/admin/utlpwdmg.sql.

也可以透過一個指令碼檢測密碼情況，具體可參考下列網址

- https://www.oradba.ch/publications/scripts/

舉例如下（裡邊只是簡單列舉幾個密碼，可在指令碼里新增其他簡單密碼用於校驗，不在列表裡的會顯示OK，在列表裡會顯示該使用者的密碼）：
```
[oracle@myasm verify]$ sqlplus / as sysdba @verify_alluser_passwords.sql

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 15 15:34:22 2021

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

User			      Status	Password
----------------------------- -----------------------------
SYS			      0 	ORACLE
SYSTEM			      0 	ORACLE
OUTLN			      9 	OUTLN
DIP			      9 	DIP
ORACLE_OCM		      9 	OK
DBSNMP			      9 	DBSNMP
APPQOSSYS		      9 	APPQOSSYS
WMSYS			      9 	WMSYS
EXFSYS			      9 	OK
CTXSYS			      9 	CHANGE_ON_INSTALL
XDB			      9 	CHANGE_ON_INSTALL
ANONYMOUS		      9 	OK
ORDSYS			      9 	ORDSYS
ORDDATA 		      9 	ORDDATA
ORDPLUGINS		      9 	ORDPLUGINS
SI_INFORMTN_SCHEMA	      9 	SI_INFORMTN_SCHEMA
MDSYS			      9 	MDSYS
OLAPSYS 		      9 	OK
MDDATA			      9 	MDDATA
SPATIAL_WFS_ADMIN_USR	      9 	SPATIAL_WFS_ADMIN_US
SPATIAL_CSW_ADMIN_USR	      9 	SPATIAL_CSW_ADMIN_US
SYSMAN			      9 	SYSMAN
MGMT_VIEW		      9 	OK
FLOWS_FILES		      9 	OK
APEX_PUBLIC_USER	      9 	OK
APEX_030200		      9 	OK
OWBSYS			      9 	OK
OWBSYS_AUDIT		      9 	OK
SCOTT			      0 	TIGER
HR			      9 	CHANGE_ON_INSTALL
OE			      9 	CHANGE_ON_INSTALL
IX			      9 	CHANGE_ON_INSTALL
SH			      9 	CHANGE_ON_INSTALL
PM			      9 	CHANGE_ON_INSTALL
BI			      9 	CHANGE_ON_INSTALL
SYPMDATA		      0 	OK
MYWORK			      0 	OK
MYTEST			      0 	ORACLE
LS			      0 	OK
MY2021			      0 	ORACLE
T2021			      0 	ORACLE
TT			      0 	OK
XS$NULL 		      9 	OK

PL/SQL procedure successfully completed.

```

【PROFILE】Oracle11g密碼複雜度說明

相關文章