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