> 本文主要介绍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 Copyright (c) 1982, 2013, Oracle. All rights reserved. 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 Copyright (c) 1982, 2013, Oracle. All rights reserved. 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. ```