# FR 1.2, FR 2.2 Test a user with password history checks off
CREATE USER no_pwd_history@localhost
PASSWORD HISTORY 0 PASSWORD REUSE INTERVAL 0 DAY;
# Check the mysql.user columns. Must be zeroes.
SELECT Password_reuse_history, Password_reuse_time
FROM mysql.user
WHERE Host='localhost' AND User='no_pwd_history';
Password_reuse_history	Password_reuse_time
0	0
# Must return 0 rows
SELECT COUNT(*) FROM mysql.password_history WHERE
User='no_pwd_history' AND Host='localhost';
COUNT(*)
0
SET PASSWORD FOR no_pwd_history@localhost = 'changeme';
# SET PASSWORD FOR should not produce rows
SELECT COUNT(*) FROM mysql.password_history WHERE
User='no_pwd_history' AND Host='localhost';
COUNT(*)
0
ALTER USER no_pwd_history@localhost IDENTIFIED BY 'changemeagain';
# ALTER USER IDENTIFIED BY should not produce rows
SELECT COUNT(*) FROM mysql.password_history WHERE
User='no_pwd_history' AND Host='localhost';
COUNT(*)
0
ALTER USER no_pwd_history@localhost IDENTIFIED WITH "mysql_native_password"
  AS '*C40F578E1A9D8D1146AFD04AFCB0228EED9D45FB';
# ALTER USER IDENTIFIED WITH .. AS .. should not produce rows
SELECT COUNT(*) FROM mysql.password_history WHERE
User='no_pwd_history' AND Host='localhost';
COUNT(*)
0
# try 2 statements in a row in a quick succession
SET PASSWORD='pwd1'; SET PASSWORD='pwd2';;
# 2 SET PASSWORD should not produce rows
SELECT COUNT(*) FROM mysql.password_history WHERE
User='no_pwd_history' AND Host='localhost';
COUNT(*)
0
# FR 1.1.1: must succeed
SET GLOBAL password_history= 1;
# FR 1.1.2: must succeed. No history tracked so far
SET PASSWORD FOR no_pwd_history@localhost = 'tracked';
# must be 0: unaffected by the global
SELECT COUNT(*) FROM mysql.password_history WHERE
User='no_pwd_history' AND Host='localhost';
COUNT(*)
0
# FR 1.1.2: must succeed again: no tracking
SET PASSWORD FOR no_pwd_history@localhost = 'tracked';
# must be 0: unaffected by the global again
SELECT COUNT(*) FROM mysql.password_history WHERE
User='no_pwd_history' AND Host='localhost';
COUNT(*)
0
SET GLOBAL password_history= default;
# FR 2.1.1: must be settable at runtime
SET GLOBAL password_reuse_interval= 100;
# FR 2.1.2: Must succeed: no history tracked
SET PASSWORD FOR no_pwd_history@localhost = 'tracked';
# must be 0: unaffected by the global password reuse interval
SELECT COUNT(*) FROM mysql.password_history WHERE
User='no_pwd_history' AND Host='localhost';
COUNT(*)
0
# FR 2.1.2: password still not tracked: must succeed
SET PASSWORD FOR no_pwd_history@localhost = 'tracked';
# must be 0: still unaffected by the global password reuse interval
SELECT COUNT(*) FROM mysql.password_history WHERE
User='no_pwd_history' AND Host='localhost';
COUNT(*)
0
# FR6: Must have the password history clauses
SHOW CREATE USER no_pwd_history@localhost;
CREATE USER for no_pwd_history@localhost
CREATE USER 'no_pwd_history'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '<non-deterministic-password-hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY 0 PASSWORD REUSE INTERVAL 0 DAY PASSWORD REQUIRE CURRENT DEFAULT
# NF 1.3 NF 2.3: Must have 0 and 0 for the password history columns
SELECT Password_reuse_history, Password_reuse_time FROM mysql.user
WHERE User='no_pwd_history' AND Host='localhost';
Password_reuse_history	Password_reuse_time
0	0
SET GLOBAL password_reuse_interval= DEFAULT;
DROP USER no_pwd_history@localhost;
# Test the effect of reuse interval on regular users
CREATE USER default_def@localhost IDENTIFIED BY 'haha';
# FR6: must have the history clauses
SHOW CREATE USER default_def@localhost;
CREATE USER for default_def@localhost
CREATE USER 'default_def'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
# NF 1.3 NF 2.3 Must have NULL and NULL for the password history columns
SELECT Password_reuse_history, Password_reuse_time FROM mysql.user
WHERE User='default_def' AND Host='localhost';
Password_reuse_history	Password_reuse_time
NULL	NULL
# FR1.1.2: must be 0: default is 0 and it's effective for default users
SELECT COUNT(*) FROM mysql.password_history WHERE
User='default_def' AND Host='localhost';
COUNT(*)
0
# FR1.1.2: set a global of 1 and see if it affects the default users
SET GLOBAL password_history=1;
# Must work: first change
ALTER USER default_def@localhost IDENTIFIED BY 'haha';
# FR1.1.2: must be 1: global is 1 and it's effective for default users
SELECT COUNT(*) FROM mysql.password_history WHERE
User='default_def' AND Host='localhost';
COUNT(*)
1
# Must fail: change to the same password and global is 1 for default users
ALTER USER default_def@localhost IDENTIFIED BY 'haha';
ERROR HY000: Cannot use these credentials for 'default_def@localhost' because they contradict the password history policy
SET GLOBAL password_history=default;
# Must pass: change to the same password and global is 0
ALTER USER default_def@localhost IDENTIFIED BY 'haha';
# FR3.3: must be 0: global is 0 and it's effective for default users
SELECT COUNT(*) FROM mysql.password_history WHERE
User='default_def' AND Host='localhost';
COUNT(*)
0
# FR3.4: set a global of 1 record history and then remove the user
SET GLOBAL password_history=1;
# Must pass: change to the same password and global is 0
ALTER USER default_def@localhost IDENTIFIED BY 'haha';
# must be 1: global is 1 and it's effective for default users
SELECT COUNT(*) FROM mysql.password_history WHERE
User='default_def' AND Host='localhost';
COUNT(*)
1
DROP USER default_def@localhost;
# FR3.4: must be 0: global is 0 and the user is dropped
SELECT COUNT(*) FROM mysql.password_history WHERE
User='default_def' AND Host='localhost';
COUNT(*)
0
SET GLOBAL password_history=default;
# FR3.5: set a global of 1, record history and then change auth
SET GLOBAL password_history=1;
CREATE USER method_alter@localhost IDENTIFIED BY 'haha';
# must be 1: global is 1 and create user should have logged the password
SELECT COUNT(*) FROM mysql.password_history WHERE
User='method_alter' AND Host='localhost';
COUNT(*)
1
# FR3.6: must be 1: at least 1 hash is equal in history and in mysql.user
SELECT COUNT(*) FROM mysql.password_history, mysql.user WHERE
mysql.user.User='method_alter' AND mysql.user.host='localhost' AND
mysql.user.User=mysql.password_history.user AND
mysql.user.host=mysql.password_history.host;
COUNT(*)
1
ALTER USER method_alter@localhost IDENTIFIED WITH 'sha256_password';
# must be 0: changing the auth method prunes history
SELECT COUNT(*) FROM mysql.password_history WHERE
User='method_alter' AND Host='localhost';
COUNT(*)
0
DROP USER method_alter@localhost;
SET GLOBAL password_history=default;
# FR 3.7: RENAME USER renames the entries in history
CREATE USER to_be_renamed@localhost IDENTIFIED BY 'haha' PASSWORD HISTORY 1;
# must be 1: user created with password history
SELECT COUNT(*) FROM mysql.password_history WHERE
User='to_be_renamed' AND Host='localhost';
COUNT(*)
1
RENAME USER to_be_renamed@localhost TO now_renamed@localhost;
# must be 0: user renamed
SELECT COUNT(*) FROM mysql.password_history WHERE
User='to_be_renamed' AND Host='localhost';
COUNT(*)
0
# must be 1: this is what the user is renamed to
SELECT COUNT(*) FROM mysql.password_history WHERE
User='now_renamed' AND Host='localhost';
COUNT(*)
1
# must fail: password is in history
SET PASSWORD FOR now_renamed@localhost = 'haha';
ERROR HY000: Cannot use these credentials for 'now_renamed@localhost' because they contradict the password history policy
# FR3.8: changes to the history table take effect immediately
DELETE FROM mysql.password_history WHERE
User='now_renamed' AND Host='localhost';
# must pass: password history is empty
SET PASSWORD FOR now_renamed@localhost = 'haha';
# must be 1: history added back again
SELECT COUNT(*) FROM mysql.password_history WHERE
User='now_renamed' AND Host='localhost';
COUNT(*)
1
DROP USER now_renamed@localhost;
# FR3.9: when history table absent updates pass, reads read empty
CREATE USER no_pwd_history@localhost IDENTIFIED BY 'haha';
RENAME TABLE mysql.password_history TO mysql.password_history_backup;
# must fail: eventual write operation to history
CREATE USER no_pwd_history_err@localhost IDENTIFIED BY 'haha'
  PASSWORD HISTORY 0 PASSWORD REUSE INTERVAL 0 DAY;
ERROR 42S02: Table 'mysql.password_history' doesn't exist
# Must fail: SET PASSWORD can write to history
SET PASSWORD FOR no_pwd_history@localhost = 'hehe';
ERROR 42S02: Table 'mysql.password_history' doesn't exist
# Must fail: ALTER USER IDENTIFIED BY can write to history
ALTER USER no_pwd_history@localhost IDENTIFIED BY 'hihi';
ERROR 42S02: Table 'mysql.password_history' doesn't exist
# Must fail: ALTER USER IDENTIFIED WITH can add to history
ALTER USER no_pwd_history@localhost IDENTIFIED WITH 'sha256_password';
ERROR 42S02: Table 'mysql.password_history' doesn't exist
# Must fail: DROP USER can write to history
DROP USER no_pwd_history@localhost;
ERROR 42S02: Table 'mysql.password_history' doesn't exist
RENAME TABLE mysql.password_history_backup TO mysql.password_history;
DROP USER no_pwd_history@localhost;
#FR4: new clauses can be specified more than once
# With duplicate PASSWORD HISTORY the last one takes precedence
CREATE USER dup_history@localhost IDENTIFIED BY 'haha'
  PASSWORD HISTORY 1 PASSWORD HISTORY DEFAULT;
# must be 0: global is 0 and last option takes precendence
SELECT COUNT(*) FROM mysql.password_history WHERE
User='dup_history' AND Host='localhost';
COUNT(*)
0
DROP USER dup_history@localhost;
# With duplicate PASSWORD REUSE INTERVAL the last one takes precedence
CREATE USER dup_interval@localhost IDENTIFIED BY 'haha'
  PASSWORD REUSE INTERVAL 10 DAY PASSWORD REUSE INTERVAL DEFAULT;
# must be 0: global is 0 and last option takes precendence
SELECT COUNT(*) FROM mysql.password_history WHERE
User='dup_interval' AND Host='localhost';
COUNT(*)
0
DROP USER dup_interval@localhost;
# FR7: Empty passwords are not verified nor stored in password history.
CREATE USER empty_pwd@localhost IDENTIFIED BY ''
  PASSWORD HISTORY 1;
# must be 0: empty passwords not recorded after CREATE USER
SELECT COUNT(*) FROM mysql.password_history WHERE
User='empty_pwd' AND Host='localhost';
COUNT(*)
0
# Must pass: it's ok to set again an empty password
SET PASSWORD FOR empty_pwd@localhost = '';
# must be 0: empty passwords not recorded after SET PASSWORD
SELECT COUNT(*) FROM mysql.password_history WHERE
User='empty_pwd' AND Host='localhost';
COUNT(*)
0
DROP USER empty_pwd@localhost;
# FR2.1: A new global only system variable password_reuse_interval
SET SESSION password_reuse_interval= 0;
ERROR HY000: Variable 'password_reuse_interval' is a GLOBAL variable and should be set with SET GLOBAL
SET GLOBAL password_history=0;
SET GLOBAL password_reuse_interval=1;
CREATE USER def_interval@localhost IDENTIFIED BY 'haha';
# must be 1: password recorded due to interval in CREATE USER
SELECT COUNT(*) FROM mysql.password_history WHERE
User='def_interval' AND Host='localhost';
COUNT(*)
1
# Must fail: password reused too soon
SET PASSWORD FOR def_interval@localhost = 'haha';
ERROR HY000: Cannot use these credentials for 'def_interval@localhost' because they contradict the password history policy
# Rewind back the history record with 1 day
UPDATE mysql.password_history
SET Password_timestamp = TIMESTAMPADD(DAY, -1, Password_timestamp)
WHERE User='def_interval' AND Host='localhost';
# Must pass now: the password was used yesterday
SET PASSWORD FOR def_interval@localhost = 'haha';
# must be 1: password reused in SET PASSWORD today
SELECT COUNT(*) FROM mysql.password_history WHERE
User='def_interval' AND Host='localhost' AND
TO_DAYS(Password_timestamp)=TO_DAYS(NOW());
COUNT(*)
1
SET PASSWORD FOR def_interval@localhost = 'hihi';
# must be 2: a new password added in SET PASSWORD today
SELECT COUNT(*) FROM mysql.password_history WHERE
User='def_interval' AND Host='localhost' AND
TO_DAYS(Password_timestamp)=TO_DAYS(NOW());
COUNT(*)
2
# Rewind back the history records with 1 month
UPDATE mysql.password_history
SET Password_timestamp = TIMESTAMPADD(MONTH, -1, Password_timestamp)
WHERE User='def_interval' AND Host='localhost';
# Must pass: password in SET PASSWORD not used for a month
SET PASSWORD FOR def_interval@localhost = 'hoho';
# must be 1: old history records purged from history
SELECT COUNT(*) FROM mysql.password_history WHERE
User='def_interval' AND Host='localhost' AND
TO_DAYS(Password_timestamp)=TO_DAYS(NOW());
COUNT(*)
1
SET GLOBAL password_reuse_interval = default;
DROP USER def_interval@localhost;
#
# Bug #26364229: PASSWORD CHANGE FROM A USER SESSION
#    DOES NOT PRESERVE THE PASSWORD_HISTORY VALUE
#
SET GLOBAL password_history=5;
SET GLOBAL password_reuse_interval=5;
CREATE USER mohit@localhost IDENTIFIED BY 'mohit'
  PASSWORD HISTORY 1 PASSWORD REUSE INTERVAL 2 DAY;
# Must have PASSWORD HISTORY 1 and PASSWORD REUSE INTERVAL 2
SHOW CREATE USER mohit@localhost;
CREATE USER for mohit@localhost
CREATE USER 'mohit'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY 1 PASSWORD REUSE INTERVAL 2 DAY PASSWORD REQUIRE CURRENT DEFAULT
# Must fail: password history is 1
ALTER USER mohit@localhost IDENTIFIED BY 'mohit';
ERROR HY000: Cannot use these credentials for 'mohit@localhost' because they contradict the password history policy
# Must pass: new password
ALTER USER mohit@localhost IDENTIFIED BY 'mohit1';
# Must have PASSWORD HISTORY 1 and PASSWORD REUSE INTERVAL 2
SHOW CREATE USER mohit@localhost;
CREATE USER for mohit@localhost
CREATE USER 'mohit'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY 1 PASSWORD REUSE INTERVAL 2 DAY PASSWORD REQUIRE CURRENT DEFAULT
DROP USER mohit@localhost;
SET GLOBAL password_reuse_interval= default;
CREATE USER mohit@localhost IDENTIFIED BY 'mohit' PASSWORD HISTORY 1;
# Must have PASSWORD HISTORY 1
SHOW CREATE USER mohit@localhost;
CREATE USER for mohit@localhost
CREATE USER 'mohit'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY 1 PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
# must be 1: password recorded due to interval in CREATE USER
SELECT COUNT(*) FROM mysql.password_history WHERE
User='mohit' AND Host='localhost';
COUNT(*)
1
# Must fail: password history is 1
ALTER USER mohit@localhost IDENTIFIED BY 'mohit';
ERROR HY000: Cannot use these credentials for 'mohit@localhost' because they contradict the password history policy
# Must pass: new password
ALTER USER mohit@localhost IDENTIFIED BY 'mohit1';
# must be 1: new password bumped the old one
SELECT COUNT(*) FROM mysql.password_history WHERE
User='mohit' AND Host='localhost';
COUNT(*)
1
# Must pass: password history set to 1
ALTER USER mohit@localhost IDENTIFIED BY 'mohit';
DROP USER mohit@localhost;
SET GLOBAL password_history= default;
SET GLOBAL password_reuse_interval= default;
#
# Bug #26410591: ASSERT FAIL IN COMPARE_NATIVE_PASSWORD_WITH_HASH
#
CREATE USER mohit@localhost
IDENTIFIED WITH sha256_password BY 'mohit' PASSWORD HISTORY 1;
# must be 1: SHA256 password recorded due to history
SELECT COUNT(*) FROM mysql.password_history WHERE
User='mohit' AND Host='localhost';
COUNT(*)
1
# Save the SH256 password for later
CREATE TABLE pwd_history_backup AS
SELECT * FROM mysql.password_history
WHERE User='mohit' AND Host='localhost';
# Must not crash
ALTER USER mohit@localhost IDENTIFIED WITH mysql_native_password BY 'mohit';
# Must be 1: the native password
SELECT COUNT(*) FROM mysql.password_history WHERE
User='mohit' AND Host='localhost';
COUNT(*)
1
# Restore the SHA256 password (invalid hash) into the history table
UPDATE mysql.password_history
SET Password=(
SELECT Password FROM pwd_history_backup
WHERE User='mohit' AND Host='localhost')
WHERE User='mohit' AND Host='localhost';
# Must not crash
ALTER USER mohit@localhost IDENTIFIED WITH mysql_native_password BY 'mohit';
DROP TABLE pwd_history_backup;
DROP USER mohit@localhost;
#
# Bug #26410846: PASSWORD ROTATION POLICY IS NOT WORKING FOR SHA256
#   AUTHENTICATED USERS
#
CREATE USER mohit_sha@localhost IDENTIFIED WITH sha256_password BY 'mohit_sha'
  PASSWORD HISTORY 1;
# Must fail: same password
ALTER USER mohit_sha@localhost IDENTIFIED BY 'mohit_sha';
ERROR HY000: Cannot use these credentials for 'mohit_sha@localhost' because they contradict the password history policy
# Must pass: different password
ALTER USER mohit_sha@localhost IDENTIFIED BY 'mohit_sha2';
DROP USER mohit_sha@localhost;
#
# Altering a user with PASSWORD REUSE INTERVAL
#
SET GLOBAL password_history=0;
SET GLOBAL password_reuse_interval= 1;
CREATE USER amar@localhost IDENTIFIED BY 'amar'
  PASSWORD REUSE INTERVAL 1 DAY;
# Must be 1: due to INTERVAL 1 day
SELECT COUNT(*) FROM mysql.password_history WHERE
User='amar' AND Host='localhost';
COUNT(*)
1
# Must fail: duplicate password
ALTER USER amar@localhost IDENTIFIED BY 'amar';
ERROR HY000: Cannot use these credentials for 'amar@localhost' because they contradict the password history policy
# Must still be 1: due to INTERVAL 1 day again
SELECT COUNT(*) FROM mysql.password_history WHERE
User='amar' AND Host='localhost';
COUNT(*)
1
DROP USER amar@localhost;
SET GLOBAL password_history=default;
SET GLOBAL password_reuse_interval=default;
#
# Tests for caching_sha2_password plugin
#
CREATE USER mohit_sha@localhost IDENTIFIED WITH caching_sha2_password BY 'mohit_sha'
  PASSWORD HISTORY 1;
# Must fail: same password
ALTER USER mohit_sha@localhost IDENTIFIED BY 'mohit_sha';
ERROR HY000: Cannot use these credentials for 'mohit_sha@localhost' because they contradict the password history policy
# Must pass: different password
ALTER USER mohit_sha@localhost IDENTIFIED BY 'mohit_sha2';
ALTER USER mohit_sha@localhost PASSWORD HISTORY 2;
# Must pass: We do not record for the password
ALTER USER mohit_sha@localhost IDENTIFIED BY 'mohit_sha';
# Must fail: passwords present in history table
ALTER USER mohit_sha@localhost IDENTIFIED BY 'mohit_sha';
ERROR HY000: Cannot use these credentials for 'mohit_sha@localhost' because they contradict the password history policy
ALTER USER mohit_sha@localhost IDENTIFIED BY 'mohit_sha2';
ERROR HY000: Cannot use these credentials for 'mohit_sha@localhost' because they contradict the password history policy
# Must pass: different password
ALTER USER mohit_sha@localhost IDENTIFIED BY 'mohit_sha3';
DROP USER mohit_sha@localhost;
# ------------------------------------------------------------------
# -- End of 8.0 tests
# ------------------------------------------------------------------