Skip to content

Commit 0ff896b

Browse files
gkodinovgkodinov
committed
Bug#14588145: NEED ABILITY TO FLUSH PASSWORD VALIDATION DICTIONARY FILE
PROBLEM: Once validate_password plugin is loaded any change made to dictionary file was not accepted. File content is cached and it cannot be flushed without re-loading the plugin. SOLUTION: Dictionary file can be flushed. Changing the validate_password_dictionary_file variable from a readonly to a dynamic variable. We can specify the new dictionary file name by setting validate_password_dictionary_file. It will flush the previous dictionary file and load the new one. Also added 2 new status variables: * validate_password_dictionary_file_last_parsed : a YYYY-MM-DD HH:MM:SS OS local time string on when the last password file update took place. * validate_password_dictionary_file_words_count an integer containing the exact number of words in the dictionary. Both status variables are updated when a password file is parsed. Test cases added. Also updated the existing test to not restart the server to change the dictionary file.
1 parent 621e38d commit 0ff896b

3 files changed

Lines changed: 300 additions & 60 deletions

File tree

‎mysql-test/r/validate_password_plugin.result‎

Lines changed: 67 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -96,10 +96,7 @@ SET @@global.validate_password_special_char_count= 1;
9696
SET @@global.validate_password_policy=STRONG;
9797
SET PASSWORD FOR 'base_user'@'localhost'= PASSWORD('password1A#');
9898
UPDATE mysql.user SET PASSWORD= PASSWORD('password1A#') WHERE user='base_user';
99-
UNINSTALL PLUGIN validate_password;
100-
# restarting the server with dictionary file.
101-
# Restart server.
102-
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
99+
SET @@global.validate_password_dictionary_file="MYSQL_ERRMSG_BASEDIR/dictionary.txt";
103100
# password policy strong
104101
# default_file : dictionary.txt
105102
SET @@global.validate_password_policy=STRONG;
@@ -161,4 +158,70 @@ DROP USER 'user2'@'localhost';
161158
DROP USER 'base_user'@'localhost';
162159
DROP USER 'user1'@'localhost';
163160
DROP USER 'user'@'localhost';
161+
SET @@global.validate_password_length=default;
162+
SET @@global.validate_password_number_count=default;
163+
SET @@global.validate_password_mixed_case_count=default;
164+
SET @@global.validate_password_special_char_count=default;
165+
SET @@global.validate_password_policy=default;
166+
SET @@global.validate_password_dictionary_file=default;
167+
SELECT @@validate_password_length,
168+
@@validate_password_number_count,
169+
@@validate_password_mixed_case_count,
170+
@@validate_password_special_char_count,
171+
@@validate_password_policy,
172+
@@validate_password_dictionary_file;
173+
@@validate_password_length @@validate_password_number_count @@validate_password_mixed_case_count @@validate_password_special_char_count @@validate_password_policy @@validate_password_dictionary_file
174+
8 1 1 1 MEDIUM NULL
175+
#
176+
# Bug#14588145 - NEED ABILITY TO FLUSH PASSWORD VALIDATION DICTIONARY FILE
177+
#
178+
SET @@global.validate_password_policy=STRONG;
179+
# No dictionary file, password is accepted
180+
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'passWORD123#';
181+
SET @@global.validate_password_dictionary_file="MYSQLTEST_VARDIR/tmp/dictionary2.txt";
182+
# must return 3
183+
SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS
184+
WHERE VARIABLE_NAME = 'validate_password_dictionary_file_words_count';
185+
VARIABLE_VALUE
186+
3
187+
SELECT VARIABLE_VALUE into @ts1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS
188+
WHERE VARIABLE_NAME = "validate_password_dictionary_file_last_parsed";
189+
# check format of the TS
190+
SELECT @ts1;
191+
@ts1
192+
0000-00-00 00:00:00
193+
# must return 19
194+
SELECT LENGTH(@ts1);
195+
LENGTH(@ts1)
196+
19
197+
# must sleep for at least 1 sec so that the timestamps differ
198+
SET @@global.validate_password_dictionary_file="MYSQLTEST_VARDIR/tmp/dictionary.txt";
199+
# must return 2
200+
SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS
201+
WHERE VARIABLE_NAME = 'validate_password_dictionary_file_words_count';
202+
VARIABLE_VALUE
203+
2
204+
SELECT VARIABLE_VALUE into @ts2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS
205+
WHERE VARIABLE_NAME = "validate_password_dictionary_file_last_parsed";
206+
# must return 1
207+
SELECT @ts1 <> @ts2;
208+
@ts1 <> @ts2
209+
1
210+
CREATE USER 'user2'@'localhost' IDENTIFIED BY 'passWORD123#';
211+
ERROR HY000: Your password does not satisfy the current policy requirements
212+
SET @@global.validate_password_dictionary_file=NULL;
213+
# Cache flushed and no dictionary file is loaded
214+
CREATE USER 'user2'@'localhost' IDENTIFIED BY 'passWORD123#';
215+
# Select commands to show that the validate_password lock is instrumented
216+
SELECT NAME FROM performance_schema.setup_instruments WHERE NAME LIKE '%validate%';
217+
NAME
218+
wait/synch/rwlock/validate/LOCK_dict_file
219+
SELECT NAME FROM performance_schema.rwlock_instances WHERE NAME LIKE '%validate%';
220+
NAME
221+
wait/synch/rwlock/validate/LOCK_dict_file
222+
# cleanup
223+
DROP USER 'user1'@'localhost', 'user2'@'localhost';
224+
SET @@global.validate_password_policy=DEFAULT;
225+
# clean up after the test
164226
UNINSTALL PLUGIN validate_password;
227+
End of tests

‎mysql-test/t/validate_password_plugin.test‎

Lines changed: 93 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -109,34 +109,8 @@ SET @@global.validate_password_policy=STRONG;
109109
SET PASSWORD FOR 'base_user'@'localhost'= PASSWORD('password1A#');
110110
UPDATE mysql.user SET PASSWORD= PASSWORD('password1A#') WHERE user='base_user';
111111

112-
UNINSTALL PLUGIN validate_password;
113-
114-
--echo # restarting the server with dictionary file.
115-
116-
# Write file to make mysql-test-run.pl wait for the server to stop
117-
--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
118-
119-
# Request shutdown
120-
-- send_shutdown
121-
122-
# Call script that will poll the server waiting for it to disapear
123-
-- source include/wait_until_disconnected.inc
124-
125-
--echo # Restart server.
126-
127-
--exec echo "restart:--loose-validate_password_dictionary_file=$MYSQL_ERRMSG_BASEDIR/dictionary.txt" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
128-
129-
# Turn on reconnect
130-
--enable_reconnect
131-
132-
# Call script that will poll the server waiting for it to be back online again
133-
--source include/wait_until_connected_again.inc
134-
135-
# Turn off reconnect again
136-
--disable_reconnect
137-
138-
--replace_regex /\.dll/.so/
139-
eval INSTALL PLUGIN validate_password SONAME '$VALIDATE_PASSWORD';
112+
--replace_result $MYSQL_ERRMSG_BASEDIR MYSQL_ERRMSG_BASEDIR
113+
eval SET @@global.validate_password_dictionary_file="$MYSQL_ERRMSG_BASEDIR/dictionary.txt";
140114

141115
--echo # password policy strong
142116
--echo # default_file : dictionary.txt
@@ -189,7 +163,7 @@ SET @@global.validate_password_special_char_count= 0;
189163
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
190164
SET @@global.validate_password_mixed_case_count= 0;
191165
# user has the update/create privilege but needs to satisfy password policy
192-
# to update/create new account
166+
# to update/create new account
193167
--error ER_NOT_VALID_PASSWORD
194168
CREATE USER 'user2'@'localhost' IDENTIFIED BY 'password';
195169
CREATE USER 'user2'@'localhost' IDENTIFIED BY 'PA00wrd!#';
@@ -204,4 +178,94 @@ connection default;
204178
DROP USER 'base_user'@'localhost';
205179
DROP USER 'user1'@'localhost';
206180
DROP USER 'user'@'localhost';
181+
SET @@global.validate_password_length=default;
182+
SET @@global.validate_password_number_count=default;
183+
SET @@global.validate_password_mixed_case_count=default;
184+
SET @@global.validate_password_special_char_count=default;
185+
SET @@global.validate_password_policy=default;
186+
SET @@global.validate_password_dictionary_file=default;
187+
188+
SELECT @@validate_password_length,
189+
@@validate_password_number_count,
190+
@@validate_password_mixed_case_count,
191+
@@validate_password_special_char_count,
192+
@@validate_password_policy,
193+
@@validate_password_dictionary_file;
194+
195+
196+
--echo #
197+
--echo # Bug#14588145 - NEED ABILITY TO FLUSH PASSWORD VALIDATION DICTIONARY FILE
198+
--echo #
199+
200+
SET @@global.validate_password_policy=STRONG;
201+
202+
--write_file $MYSQLTEST_VARDIR/tmp/dictionary.txt
203+
password
204+
validate
205+
EOF
206+
207+
--write_file $MYSQLTEST_VARDIR/tmp/dictionary2.txt
208+
password
209+
validate
210+
monkey
211+
EOF
212+
213+
--echo # No dictionary file, password is accepted
214+
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'passWORD123#';
215+
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
216+
eval SET @@global.validate_password_dictionary_file="$MYSQLTEST_VARDIR/tmp/dictionary2.txt";
217+
# Dictionary file loaded
218+
219+
--echo # must return 3
220+
SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS
221+
WHERE VARIABLE_NAME = 'validate_password_dictionary_file_words_count';
222+
223+
SELECT VARIABLE_VALUE into @ts1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS
224+
WHERE VARIABLE_NAME = "validate_password_dictionary_file_last_parsed";
225+
226+
--echo # check format of the TS
227+
--replace_regex /[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]/0000-00-00 00:00:00/
228+
SELECT @ts1;
229+
230+
--echo # must return 19
231+
SELECT LENGTH(@ts1);
232+
233+
--echo # must sleep for at least 1 sec so that the timestamps differ
234+
--sleep 1
235+
236+
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
237+
eval SET @@global.validate_password_dictionary_file="$MYSQLTEST_VARDIR/tmp/dictionary.txt";
238+
239+
--echo # must return 2
240+
SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS
241+
WHERE VARIABLE_NAME = 'validate_password_dictionary_file_words_count';
242+
243+
SELECT VARIABLE_VALUE into @ts2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS
244+
WHERE VARIABLE_NAME = "validate_password_dictionary_file_last_parsed";
245+
246+
--echo # must return 1
247+
SELECT @ts1 <> @ts2;
248+
249+
--error ER_NOT_VALID_PASSWORD
250+
CREATE USER 'user2'@'localhost' IDENTIFIED BY 'passWORD123#';
251+
252+
SET @@global.validate_password_dictionary_file=NULL;
253+
254+
--echo # Cache flushed and no dictionary file is loaded
255+
CREATE USER 'user2'@'localhost' IDENTIFIED BY 'passWORD123#';
256+
257+
--echo # Select commands to show that the validate_password lock is instrumented
258+
SELECT NAME FROM performance_schema.setup_instruments WHERE NAME LIKE '%validate%';
259+
SELECT NAME FROM performance_schema.rwlock_instances WHERE NAME LIKE '%validate%';
260+
261+
--echo # cleanup
262+
DROP USER 'user1'@'localhost', 'user2'@'localhost';
263+
SET @@global.validate_password_policy=DEFAULT;
264+
remove_file $MYSQLTEST_VARDIR/tmp/dictionary.txt;
265+
remove_file $MYSQLTEST_VARDIR/tmp/dictionary2.txt;
266+
267+
--echo # clean up after the test
207268
UNINSTALL PLUGIN validate_password;
269+
270+
271+
--echo End of tests

0 commit comments

Comments
 (0)