| 1 |
connection default; |
| 2 |
CALL mtr.add_suppression("Dictionary file not specified"); |
| 3 |
CALL mtr.add_suppression("Since the validate_password_policy is mentioned "); |
| 4 |
CALL mtr.add_suppression("Effective value of validate_password.length is changed."); |
| 5 |
# Setup |
| 6 |
CREATE USER wl15751_user_c PASSWORD REQUIRE CURRENT; |
| 7 |
CREATE USER wl15751_user_o PASSWORD REQUIRE CURRENT OPTIONAL; |
| 8 |
CREATE USER wl15751_user_d; |
| 9 |
CREATE USER wl15751_user_p PASSWORD REQUIRE CURRENT; |
| 10 |
GRANT CREATE USER ON *.* TO wl15751_user_p; |
| 11 |
INSTALL COMPONENT 'file://component_validate_password'; |
| 12 |
SET GLOBAL validate_password.changed_characters_percentage = 50; |
| 13 |
SET GLOBAL password_require_current = ON; |
| 14 |
# ---------------------------------------------------------------------- |
| 15 |
# 1. Empty current password |
| 16 |
# Must pass |
| 17 |
connect conn_wl15751_user_c, localhost, wl15751_user_c,,,,,; |
| 18 |
SET PASSWORD = 'Abcd1234@' REPLACE ''; |
| 19 |
# Must pass |
| 20 |
connect conn_wl15751_user_o, localhost, wl15751_user_o,,,,,; |
| 21 |
ALTER USER CURRENT_USER() IDENTIFIED BY 'Abcd1234@'; |
| 22 |
# Must pass |
| 23 |
connect conn_wl15751_user_d, localhost, wl15751_user_d,,,,,; |
| 24 |
ALTER USER CURRENT_USER() IDENTIFIED BY 'Abcd1234@' REPLACE ''; |
| 25 |
# Must pass |
| 26 |
connect conn_wl15751_user_p, localhost, wl15751_user_p,,,,,; |
| 27 |
SET PASSWORD = 'Abcd1234@'; |
| 28 |
# ---------------------------------------------------------------------- |
| 29 |
# 2. New password same as current password |
| 30 |
# Must fail |
| 31 |
connection conn_wl15751_user_c; |
| 32 |
SET PASSWORD = 'Abcd1234@' REPLACE 'Abcd1234@'; |
| 33 |
ERROR HY000: The new password must have at least '4' characters that are different from the old password. It has only '0' character(s) different. For this comparison, uppercase letters and lowercase letters are considered to be equal. |
| 34 |
# Must fail |
| 35 |
connection conn_wl15751_user_o; |
| 36 |
ALTER USER CURRENT_USER() IDENTIFIED BY 'Abcd1234@' REPLACE 'Abcd1234@'; |
| 37 |
ERROR HY000: The new password must have at least '4' characters that are different from the old password. It has only '0' character(s) different. For this comparison, uppercase letters and lowercase letters are considered to be equal. |
| 38 |
# Must pass: PASSWORD REQUIRE CURRENT OPTIONAL |
| 39 |
connection conn_wl15751_user_o; |
| 40 |
ALTER USER CURRENT_USER() IDENTIFIED BY 'Abcd1234@'; |
| 41 |
# Must fail |
| 42 |
connection conn_wl15751_user_d; |
| 43 |
ALTER USER CURRENT_USER() IDENTIFIED BY 'Abcd1234@' REPLACE 'Abcd1234@'; |
| 44 |
ERROR HY000: The new password must have at least '4' characters that are different from the old password. It has only '0' character(s) different. For this comparison, uppercase letters and lowercase letters are considered to be equal. |
| 45 |
# Must fail |
| 46 |
connection conn_wl15751_user_p; |
| 47 |
SET PASSWORD = 'Abcd1234@' REPLACE 'Abcd1234@'; |
| 48 |
ERROR HY000: The new password must have at least '4' characters that are different from the old password. It has only '0' character(s) different. For this comparison, uppercase letters and lowercase letters are considered to be equal. |
| 49 |
# Must pass: privileged user |
| 50 |
connection conn_wl15751_user_p; |
| 51 |
SET PASSWORD = 'Abcd1234@'; |
| 52 |
# ---------------------------------------------------------------------- |
| 53 |
# 3. New password has less than required number of changed characters |
| 54 |
# Change count must be 1. Rationale: |
| 55 |
# - One new digit is added |
| 56 |
# - Changing letter case is not counted as a change |
| 57 |
# - Repeating same character is not counted as a change even |
| 58 |
# if the character is not present in existing password |
| 59 |
# Must fail |
| 60 |
connection conn_wl15751_user_c; |
| 61 |
SET PASSWORD = 'AbcD01234@@' REPLACE 'Abcd1234@'; |
| 62 |
ERROR HY000: The new password must have at least '4' characters that are different from the old password. It has only '1' character(s) different. For this comparison, uppercase letters and lowercase letters are considered to be equal. |
| 63 |
SET PASSWORD = 'AbccD01234@@' REPLACE 'Abcd1234@'; |
| 64 |
ERROR HY000: The new password must have at least '4' characters that are different from the old password. It has only '1' character(s) different. For this comparison, uppercase letters and lowercase letters are considered to be equal. |
| 65 |
SET PASSWORD = 'AbccDD001234@@' REPLACE 'Abcd1234@'; |
| 66 |
ERROR HY000: The new password must have at least '4' characters that are different from the old password. It has only '1' character(s) different. For this comparison, uppercase letters and lowercase letters are considered to be equal. |
| 67 |
# Must fail |
| 68 |
connection conn_wl15751_user_o; |
| 69 |
ALTER USER CURRENT_USER() IDENTIFIED BY 'AbcD01234@@' REPLACE 'Abcd1234@'; |
| 70 |
ERROR HY000: The new password must have at least '4' characters that are different from the old password. It has only '1' character(s) different. For this comparison, uppercase letters and lowercase letters are considered to be equal. |
| 71 |
ALTER USER CURRENT_USER() IDENTIFIED BY 'AbccD01234@@' REPLACE 'Abcd1234@'; |
| 72 |
ERROR HY000: The new password must have at least '4' characters that are different from the old password. It has only '1' character(s) different. For this comparison, uppercase letters and lowercase letters are considered to be equal. |
| 73 |
ALTER USER CURRENT_USER() IDENTIFIED BY 'AbccDD001234@@' REPLACE 'Abcd1234@'; |
| 74 |
ERROR HY000: The new password must have at least '4' characters that are different from the old password. It has only '1' character(s) different. For this comparison, uppercase letters and lowercase letters are considered to be equal. |
| 75 |
# Must fail |
| 76 |
connection conn_wl15751_user_d; |
| 77 |
ALTER USER CURRENT_USER() IDENTIFIED BY 'AbcD01234@@' REPLACE 'Abcd1234@'; |
| 78 |
ERROR HY000: The new password must have at least '4' characters that are different from the old password. It has only '1' character(s) different. For this comparison, uppercase letters and lowercase letters are considered to be equal. |
| 79 |
ALTER USER CURRENT_USER() IDENTIFIED BY 'AbccD01234@@' REPLACE 'Abcd1234@'; |
| 80 |
ERROR HY000: The new password must have at least '4' characters that are different from the old password. It has only '1' character(s) different. For this comparison, uppercase letters and lowercase letters are considered to be equal. |
| 81 |
ALTER USER CURRENT_USER() IDENTIFIED BY 'AbccDD001234@@' REPLACE 'Abcd1234@'; |
| 82 |
ERROR HY000: The new password must have at least '4' characters that are different from the old password. It has only '1' character(s) different. For this comparison, uppercase letters and lowercase letters are considered to be equal. |
| 83 |
# Must fail |
| 84 |
connection conn_wl15751_user_p; |
| 85 |
SET PASSWORD = 'AbcD01234@@' REPLACE 'Abcd1234@'; |
| 86 |
ERROR HY000: The new password must have at least '4' characters that are different from the old password. It has only '1' character(s) different. For this comparison, uppercase letters and lowercase letters are considered to be equal. |
| 87 |
SET PASSWORD = 'AbccD01234@@' REPLACE 'Abcd1234@'; |
| 88 |
ERROR HY000: The new password must have at least '4' characters that are different from the old password. It has only '1' character(s) different. For this comparison, uppercase letters and lowercase letters are considered to be equal. |
| 89 |
SET PASSWORD = 'AbccDD001234@@' REPLACE 'Abcd1234@'; |
| 90 |
ERROR HY000: The new password must have at least '4' characters that are different from the old password. It has only '1' character(s) different. For this comparison, uppercase letters and lowercase letters are considered to be equal. |
| 91 |
# ---------------------------------------------------------------------- |
| 92 |
# 4. New password has required number of changed characters |
| 93 |
# Must pass |
| 94 |
connection conn_wl15751_user_c; |
| 95 |
SET PASSWORD = 'AbPq01234@%' REPLACE 'Abcd1234@'; |
| 96 |
# Must pass |
| 97 |
connection conn_wl15751_user_o; |
| 98 |
ALTER USER CURRENT_USER() IDENTIFIED BY 'AbPq01234@%' REPLACE 'Abcd1234@'; |
| 99 |
# Must pass |
| 100 |
connection conn_wl15751_user_d; |
| 101 |
ALTER USER CURRENT_USER() IDENTIFIED BY 'AbPq01234@%' REPLACE 'Abcd1234@'; |
| 102 |
# Must pass |
| 103 |
connection conn_wl15751_user_p; |
| 104 |
SET PASSWORD = 'AbPq01234@%' REPLACE 'Abcd1234@'; |
| 105 |
# ---------------------------------------------------------------------- |
| 106 |
# 4. New password has more than required number of changed characters |
| 107 |
# Must pass |
| 108 |
connection conn_wl15751_user_c; |
| 109 |
SET PASSWORD = 'FghIj56789#?' REPLACE 'AbPq01234@%'; |
| 110 |
# Must pass |
| 111 |
connection conn_wl15751_user_o; |
| 112 |
ALTER USER CURRENT_USER() IDENTIFIED BY 'FghIj56789#?' REPLACE 'AbPq01234@%'; |
| 113 |
# Must pass |
| 114 |
connection conn_wl15751_user_d; |
| 115 |
ALTER USER CURRENT_USER() IDENTIFIED BY 'FghIj56789#?' REPLACE 'AbPq01234@%'; |
| 116 |
# Must pass |
| 117 |
connection conn_wl15751_user_p; |
| 118 |
SET PASSWORD = 'FghIj56789#?' REPLACE 'AbPq01234@%'; |
| 119 |
# ---------------------------------------------------------------------- |
| 120 |
# 5. Number of characters to be changed depend on password length |
| 121 |
connection conn_wl15751_user_c; |
| 122 |
# Must pass: Minimum 6 characters must be different |
| 123 |
SET PASSWORD = 'Abcd123!' REPLACE 'FghIj56789#?'; |
| 124 |
# Must pass: Minimum 4 characters must be different |
| 125 |
SET PASSWORD = 'Efgh123!' REPLACE 'Abcd123!'; |
| 126 |
SET PASSWORD = 'Klmnopq01234!@$%' REPLACE 'Efgh123!'; |
| 127 |
# Must fail: Minimum of 8 characters must be different |
| 128 |
SET PASSWORD = 'Abcd1234!@' REPLACE 'Klmnopq01234!@$%'; |
| 129 |
ERROR HY000: The new password must have at least '8' characters that are different from the old password. It has only '4' character(s) different. For this comparison, uppercase letters and lowercase letters are considered to be equal. |
| 130 |
Must pass: Minimum of 8 characters must be different |
| 131 |
SET PASSWORD = 'FghIj56789#?' REPLACE 'Klmnopq01234!@$%'; |
| 132 |
# ---------------------------------------------------------------------- |
| 133 |
# 6. Other password policies must apply independent of |
| 134 |
# changed characters count |
| 135 |
SHOW VARIABLES LIKE '%validate_password%'; |
| 136 |
Variable_name Value |
| 137 |
validate_password.changed_characters_percentage 50 |
| 138 |
validate_password.check_user_name ON |
| 139 |
validate_password.dictionary_file |
| 140 |
validate_password.length 8 |
| 141 |
validate_password.mixed_case_count 1 |
| 142 |
validate_password.number_count 1 |
| 143 |
validate_password.policy MEDIUM |
| 144 |
validate_password.special_char_count 1 |
| 145 |
# Must fail |
| 146 |
connection conn_wl15751_user_c; |
| 147 |
SET PASSWORD = 'Ab12*&' REPLACE 'FghIj56789#?'; |
| 148 |
ERROR HY000: Your password does not satisfy the current policy requirements |
| 149 |
SET PASSWORD = 'abcd12*&' REPLACE 'FghIj56789#?'; |
| 150 |
ERROR HY000: Your password does not satisfy the current policy requirements |
| 151 |
SET PASSWORD = 'Abcdef*&' REPLACE 'FghIj56789#?'; |
| 152 |
ERROR HY000: Your password does not satisfy the current policy requirements |
| 153 |
SET PASSWORD = 'Abcd1234' REPLACE 'FghIj56789#?'; |
| 154 |
ERROR HY000: Your password does not satisfy the current policy requirements |
| 155 |
# Must fail |
| 156 |
connection conn_wl15751_user_o; |
| 157 |
ALTER USER CURRENT_USER() IDENTIFIED BY 'Ab12*&' REPLACE 'FghIj56789#?'; |
| 158 |
ERROR HY000: Your password does not satisfy the current policy requirements |
| 159 |
ALTER USER CURRENT_USER() IDENTIFIED BY 'Abcdef*&' REPLACE 'FghIj56789#?'; |
| 160 |
ERROR HY000: Your password does not satisfy the current policy requirements |
| 161 |
ALTER USER CURRENT_USER() IDENTIFIED BY 'Abcd1234' REPLACE 'FghIj56789#?'; |
| 162 |
ERROR HY000: Your password does not satisfy the current policy requirements |
| 163 |
# Must fail |
| 164 |
connection conn_wl15751_user_d; |
| 165 |
ALTER USER CURRENT_USER() IDENTIFIED BY 'Ab12*&' REPLACE 'FghIj56789#?'; |
| 166 |
ERROR HY000: Your password does not satisfy the current policy requirements |
| 167 |
ALTER USER CURRENT_USER() IDENTIFIED BY 'Abcdef*&' REPLACE 'FghIj56789#?'; |
| 168 |
ERROR HY000: Your password does not satisfy the current policy requirements |
| 169 |
ALTER USER CURRENT_USER() IDENTIFIED BY 'Abcd1234' REPLACE 'FghIj56789#?'; |
| 170 |
ERROR HY000: Your password does not satisfy the current policy requirements |
| 171 |
# Must fail |
| 172 |
connection conn_wl15751_user_p; |
| 173 |
SET PASSWORD = 'Ab12*&' REPLACE 'FghIj56789#?'; |
| 174 |
ERROR HY000: Your password does not satisfy the current policy requirements |
| 175 |
SET PASSWORD = 'Abcdef*&' REPLACE 'FghIj56789#?'; |
| 176 |
ERROR HY000: Your password does not satisfy the current policy requirements |
| 177 |
SET PASSWORD = 'Abcd1234' REPLACE 'FghIj56789#?'; |
| 178 |
ERROR HY000: Your password does not satisfy the current policy requirements |
| 179 |
# ---------------------------------------------------------------------- |
| 180 |
# 7. Setting global policy to off would allow users with |
| 181 |
# default setting to change password without supplying |
| 182 |
# the current one - effectively skipping the changed |
| 183 |
# character count check |
| 184 |
connection default; |
| 185 |
SET GLOBAL password_require_current=OFF; |
| 186 |
connection conn_wl15751_user_d; |
| 187 |
SET PASSWORD = 'FghIj56789#?'; |
| 188 |
ALTER USER CURRENT_USER() IDENTIFIED BY 'FghIj56789#?'; |
| 189 |
# ---------------------------------------------------------------------- |
| 190 |
# Teardown |
| 191 |
connection default; |
| 192 |
UNINSTALL COMPONENT 'file://component_validate_password'; |
| 193 |
DROP USER wl15751_user_c, wl15751_user_o, wl15751_user_d, wl15751_user_p; |