Skip to content

Commit 96a8c95

Browse files
gkodinovgkodinov
committed
WL#2392: Change Password at next login
1 parent 3b325d3 commit 96a8c95

106 files changed

Lines changed: 782 additions & 178 deletions

File tree

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

‎mysql-test/mysql-test-run.pl‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2484,7 +2484,10 @@ sub environment_setup {
24842484
my $file_mysql_fix_privilege_tables=
24852485
mtr_file_exists("$basedir/scripts/mysql_fix_privilege_tables.sql",
24862486
"$basedir/share/mysql_fix_privilege_tables.sql",
2487-
"$basedir/share/mysql/mysql_fix_privilege_tables.sql");
2487+
"$basedir/share/mysql/mysql_fix_privilege_tables.sql",
2488+
"$bindir/scripts/mysql_fix_privilege_tables.sql",
2489+
"$bindir/share/mysql_fix_privilege_tables.sql",
2490+
"$bindir/share/mysql/mysql_fix_privilege_tables.sql");
24882491
$ENV{'MYSQL_FIX_PRIVILEGE_TABLES'}= $file_mysql_fix_privilege_tables;
24892492

24902493
# ----------------------------------------------------

‎mysql-test/r/connect.result‎

Lines changed: 126 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -268,6 +268,132 @@ Warning 1287 'pre-4.1 password hash' is deprecated and will be removed in a futu
268268
FLUSH PRIVILEGES;
269269
DROP USER old_pwd@localhost, old_gpwd@localhost;
270270
SET old_passwords=default;
271+
#
272+
# WL#2392: Change Password at next login
273+
#
274+
CREATE USER must_change@localhost IDENTIFIED BY 'aha';
275+
SELECT password_expired FROM mysql.user
276+
WHERE user='must_change' and host = 'localhost';
277+
password_expired
278+
N
279+
SELECT USER();
280+
USER()
281+
must_change@localhost
282+
CREATE TABLE t1 (A INT);
283+
CREATE PROCEDURE TEST_t1(new_a INT) INSERT INTO t1 VALUES (new_a);
284+
CREATE FUNCTION last_t1() RETURNS INT RETURN (SELECT MAX(A) FROM t1);
285+
# Initialize the table
286+
CALL test_t1(1);
287+
UPDATE mysql.user SET password_expired='Y'
288+
WHERE user='must_change' and host = 'localhost';
289+
# without FLUSH the field has no effect
290+
# must not throw an error
291+
SELECT USER();
292+
USER()
293+
must_change@localhost
294+
# must not throw an error
295+
SELECT last_t1();
296+
last_t1()
297+
1
298+
# must not throw an error
299+
CALL test_t1(last_t1() + 1);
300+
FLUSH PRIVILEGES;
301+
# existing connections continue as before even after flush
302+
# must not throw an error
303+
SELECT USER();
304+
USER()
305+
must_change@localhost
306+
# must not throw an error
307+
SELECT last_t1();
308+
last_t1()
309+
2
310+
# must not throw an error
311+
CALL test_t1(last_t1() + 1);
312+
# new connections are blocked until a password reset
313+
SELECT USER();
314+
ERROR HY000: You must SET PASSWORD before executing this statement
315+
SELECT last_t1();
316+
ERROR HY000: You must SET PASSWORD before executing this statement
317+
CALL test_t1(last_t1() + 1);
318+
ERROR HY000: You must SET PASSWORD before executing this statement
319+
# setting a password unlocks it
320+
SET PASSWORD = PASSWORD('aha2');
321+
# must not throw an error
322+
SELECT USER();
323+
USER()
324+
must_change@localhost
325+
# must not throw an error
326+
SELECT last_t1();
327+
last_t1()
328+
3
329+
# must not throw an error
330+
CALL test_t1(last_t1() + 1);
331+
# check if SET PASSWORD resets the column
332+
SELECT password_expired FROM mysql.user
333+
WHERE user='must_change' and host = 'localhost';
334+
password_expired
335+
N
336+
UPDATE mysql.user SET password_expired='Y'
337+
WHERE user='must_change' and host = 'localhost';
338+
FLUSH PRIVILEGES;
339+
SELECT USER();
340+
ERROR HY000: You must SET PASSWORD before executing this statement
341+
SELECT last_t1();
342+
ERROR HY000: You must SET PASSWORD before executing this statement
343+
CALL test_t1(last_t1() + 1);
344+
ERROR HY000: You must SET PASSWORD before executing this statement
345+
# setting a password with a user name is no good
346+
SET PASSWORD FOR must_change@localhost = PASSWORD('aha3');
347+
ERROR HY000: You must SET PASSWORD before executing this statement
348+
SELECT USER();
349+
ERROR HY000: You must SET PASSWORD before executing this statement
350+
SELECT last_t1();
351+
ERROR HY000: You must SET PASSWORD before executing this statement
352+
CALL test_t1(last_t1() + 1);
353+
ERROR HY000: You must SET PASSWORD before executing this statement
354+
# setting a password for the current user works
355+
SET PASSWORD FOR CURRENT_USER() = PASSWORD('aha3');
356+
SELECT USER();
357+
USER()
358+
must_change@localhost
359+
SELECT last_t1();
360+
last_t1()
361+
4
362+
CALL test_t1(last_t1() + 1);
363+
# testing the ALTER USER command
364+
# try a single user
365+
ALTER USER must_change@localhost PASSWORD EXPIRE;
366+
SELECT password_expired FROM mysql.user
367+
WHERE user='must_change' and host = 'localhost';
368+
password_expired
369+
Y
370+
SELECT USER();
371+
ERROR HY000: You must SET PASSWORD before executing this statement
372+
SET PASSWORD = PASSWORD('aha4');
373+
# try a valid+invalid user combo
374+
ALTER USER
375+
invalid_user@localhost PASSWORD EXPIRE,
376+
must_change@localhost PASSWORD EXPIRE;
377+
ERROR HY000: Operation ALTER USER failed for 'invalid_user'@'localhost'
378+
SELECT password_expired FROM mysql.user
379+
WHERE user='must_change' and host = 'localhost';
380+
password_expired
381+
Y
382+
SELECT USER();
383+
ERROR HY000: You must SET PASSWORD before executing this statement
384+
SET PASSWORD = PASSWORD('aha5');
385+
SELECT USER();
386+
USER()
387+
must_change@localhost
388+
# Password change must be persistent when reconnecting
389+
SELECT USER();
390+
USER()
391+
must_change@localhost
392+
# cleanup
393+
DROP PROCEDURE test_t1;
394+
DROP FUNCTION last_t1;
395+
DROP TABLE t1;
396+
DROP USER must_change@localhost;
271397
# ------------------------------------------------------------------
272398
# -- End of 5.6 tests
273399
# ------------------------------------------------------------------

‎mysql-test/r/grant.result‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -55,6 +55,7 @@ max_connections 0
5555
max_user_connections 0
5656
plugin
5757
authentication_string NULL
58+
password_expired N
5859
show grants for mysqltest_1@localhost;
5960
Grants for mysqltest_1@localhost
6061
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA'
@@ -126,6 +127,7 @@ max_connections 0
126127
max_user_connections 0
127128
plugin
128129
authentication_string NULL
130+
password_expired N
129131
show grants for mysqltest_1@localhost;
130132
Grants for mysqltest_1@localhost
131133
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10
@@ -173,6 +175,7 @@ max_connections 30
173175
max_user_connections 0
174176
plugin
175177
authentication_string NULL
178+
password_expired N
176179
show grants for mysqltest_1@localhost;
177180
Grants for mysqltest_1@localhost
178181
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10 MAX_UPDATES_PER_HOUR 20 MAX_CONNECTIONS_PER_HOUR 30

‎mysql-test/r/information_schema.result‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -698,6 +698,7 @@ max_questions select,insert,update,references
698698
max_connections select,insert,update,references
699699
max_user_connections select,insert,update,references
700700
authentication_string select,insert,update,references
701+
password_expired select,insert,update,references
701702
use test;
702703
create function sub1(i int) returns int
703704
return i+1;

‎mysql-test/r/mysqld--help-notwin.result‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1110,7 +1110,7 @@ performance-schema-max-rwlock-instances 1000000
11101110
performance-schema-max-socket-classes 10
11111111
performance-schema-max-socket-instances 1000
11121112
performance-schema-max-stage-classes 150
1113-
performance-schema-max-statement-classes 168
1113+
performance-schema-max-statement-classes 169
11141114
performance-schema-max-table-handles 10000
11151115
performance-schema-max-table-instances 1000
11161116
performance-schema-max-thread-classes 50

‎mysql-test/r/mysqld--help-win.result‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1118,7 +1118,7 @@ performance-schema-max-rwlock-instances 1000000
11181118
performance-schema-max-socket-classes 10
11191119
performance-schema-max-socket-instances 1000
11201120
performance-schema-max-stage-classes 150
1121-
performance-schema-max-statement-classes 168
1121+
performance-schema-max-statement-classes 169
11221122
performance-schema-max-table-handles 10000
11231123
performance-schema-max-table-instances 1000
11241124
performance-schema-max-thread-classes 50

‎mysql-test/r/ps.result‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1195,13 +1195,13 @@ SET @aux= "SELECT COUNT(*)
11951195
prepare my_stmt from @aux;
11961196
execute my_stmt;
11971197
COUNT(*)
1198-
42
1198+
43
11991199
execute my_stmt;
12001200
COUNT(*)
1201-
42
1201+
43
12021202
execute my_stmt;
12031203
COUNT(*)
1204-
42
1204+
43
12051205
deallocate prepare my_stmt;
12061206
drop procedure if exists p1|
12071207
drop table if exists t1|

‎mysql-test/r/system_mysql_db.result‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -127,6 +127,7 @@ user CREATE TABLE `user` (
127127
`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
128128
`plugin` char(64) COLLATE utf8_bin DEFAULT '',
129129
`authentication_string` text COLLATE utf8_bin,
130+
`password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
130131
PRIMARY KEY (`Host`,`User`)
131132
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
132133
show create table func;

‎mysql-test/suite/funcs_1/r/is_columns_mysql.result‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -270,6 +270,7 @@ def mysql user max_questions 37 0 NO int NULL NULL 10 0 NULL NULL NULL int(11) u
270270
def mysql user max_updates 38 0 NO int NULL NULL 10 0 NULL NULL NULL int(11) unsigned select,insert,update,references
271271
def mysql user max_user_connections 40 0 NO int NULL NULL 10 0 NULL NULL NULL int(11) unsigned select,insert,update,references
272272
def mysql user Password 3 NO char 41 41 NULL NULL NULL latin1 latin1_bin char(41) select,insert,update,references
273+
def mysql user password_expired 43 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') select,insert,update,references
273274
def mysql user plugin 41 YES char 64 192 NULL NULL NULL utf8 utf8_bin char(64) select,insert,update,references
274275
def mysql user Process_priv 12 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') select,insert,update,references
275276
def mysql user References_priv 15 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') select,insert,update,references
@@ -646,3 +647,4 @@ NULL mysql user max_connections int NULL NULL NULL NULL int(11) unsigned
646647
NULL mysql user max_user_connections int NULL NULL NULL NULL int(11) unsigned
647648
3.0000 mysql user plugin char 64 192 utf8 utf8_bin char(64)
648649
1.0000 mysql user authentication_string text 65535 65535 utf8 utf8_bin text
650+
3.0000 mysql user password_expired enum 1 3 utf8 utf8_general_ci enum('N','Y')

0 commit comments

Comments
 (0)