This is the 15th day of my participation in the August More Text Challenge

background

For security, Oracle 11g adds password complexity verification. By default, when you need to change the password, a message is displayed indicating that the password is invalid. This function is controlled by the PASSWORD_VERIFY_FUNCTION parameter.

Enable password complexity verification

SQL> @? /rdbms/admin/utlpwdmg.sql Function created. Grant succeeded. Profile altered. Function created. Grant succeeded. 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 10 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_11G DEFAULT PASSWORD_LOCK_TIME PASSWORD 1 DEFAULT PASSWORD_GRACE_TIME PASSWORD 7 16 rows selected.Copy the code

After this function is enabled, the length of the new password must exceed 8 characters and contain uppercase and lowercase letters. Otherwise, an error message is displayed:

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

SQL> alter user syd identified by Sydoracle123;
User altered.
Copy the code

Disable password complexity verification

SQL> alter profile default limit PASSWORD_VERIFY_FUNCTION null; Profile altered. SQL> select * from dba_profiles where profile='DEFAULT'; 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 10 DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 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.Copy the code

After this function is turned off, the password can be set at will without restriction:

SQL> alter user syd identified by syd;
User altered.

SQL> alter user syd identified by oracle;
User altered.
Copy the code

Reset the password validity period

The utlpwdmg. SQL script contains the following content:

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;
Copy the code

After the script is executed, the password validity period is changed back to the default 180 days when password verification is enabled.

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

SQL> select * from dba_profiles where profile='DEFAULT'; 

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 10
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD UNLIMITED
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.
Copy the code