oralce 11G开启密码复杂性校验

环境:

OS:Centos 7

db:11.2.0.4

1.开启密码复杂性校验

su - oracle
SQL> connect / as sysdba
Connected.
SQL> @?/rdbms/admin/utlpwdmg.sql

Function created.


Grant succeeded.


Profile altered.


Function created.


Grant succeeded.

SQL>set linesize 1000;
SQL> column profile  format a10;
SQL> column resource_name  format a32;
SQL> column resource_type  format a16;
SQL> column limit  format a32;
SQL> SELECT t.profile,t.resource_name,t.resource_type,t.limit FROM Dba_Profiles t WHERE t.profile='DEFAULT';

PROFILE    RESOURCE_NAME                    RESOURCE_TYPE    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         10
DEFAULT    PASSWORD_LIFE_TIME               PASSWORD         180

PROFILE    RESOURCE_NAME                    RESOURCE_TYPE    LIMIT
---------- -------------------------------- ---------------- --------------------------------
DEFAULT    PASSWORD_REUSE_TIME              PASSWORD         UNLIMITED
DEFAULT    PASSWORD_REUSE_MAX               PASSWORD         UNLIMITED
DEFAULT    PASSWORD_VERIFY_FUNCTION         PASSWORD         VERIFY_FUNCTION_11G
DEFAULT    PASSWORD_LOCK_TIME               PASSWORD         1
DEFAULT    PASSWORD_GRACE_TIME              PASSWORD         7

16 rows selected.

 

2.尝试修改密码

SQL> alter user hxl identified by oracle;
alter user hxl 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 hxl identified by Oracle123;
alter user hxl identified by Oracle123
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20006: Password too simple

SQL> alter user hxl identified by Oracle#123;

User altered.

 

3.关闭密码复杂性校验

SQL> alter profile default limit PASSWORD_VERIFY_FUNCTION null;
Profile altered.

SQL> SELECT t.profile,t.resource_name,t.resource_type,t.limit FROM Dba_Profiles t WHERE t.profile='DEFAULT';

PROFILE    RESOURCE_NAME                    RESOURCE_TYPE    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         10
DEFAULT    PASSWORD_LIFE_TIME               PASSWORD         180

PROFILE    RESOURCE_NAME                    RESOURCE_TYPE    LIMIT
---------- -------------------------------- ---------------- --------------------------------
DEFAULT    PASSWORD_REUSE_TIME              PASSWORD         UNLIMITED
DEFAULT    PASSWORD_REUSE_MAX               PASSWORD         UNLIMITED
DEFAULT    PASSWORD_VERIFY_FUNCTION         PASSWORD         NULL
DEFAULT    PASSWORD_LOCK_TIME               PASSWORD         1
DEFAULT    PASSWORD_GRACE_TIME              PASSWORD         7

16 rows selected.


SQL> alter user hxl identified by oracle;

User altered.

 

utlpwdmg.sql脚本中有如下一段内容:
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION verify_function_11G;
运行了以上脚本后，在开启了密码校验性的同时，oracle密码期限也被改回了默认的180天，若需要密码不过期别忘更改

SQL> alter profile default limit PASSWORD_LIFE_TIME unlimited;
Profile altered.

 

 

4.再次开启和关闭

alter profile default limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_11G;
alter profile default limit PASSWORD_VERIFY_FUNCTION null;