# tests for password history tools for user accounts

# Save the initial number of concurrent sessions
--source include/count_sessions.inc

--echo # 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;

--echo # 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';

--echo # Must return 0 rows
SELECT COUNT(*) FROM mysql.password_history WHERE
  User='no_pwd_history' AND Host='localhost';

SET PASSWORD FOR no_pwd_history@localhost = 'changeme';

--echo # SET PASSWORD FOR should not produce rows
SELECT COUNT(*) FROM mysql.password_history WHERE
  User='no_pwd_history' AND Host='localhost';

ALTER USER no_pwd_history@localhost IDENTIFIED BY 'changemeagain';

--echo # ALTER USER IDENTIFIED BY should not produce rows
SELECT COUNT(*) FROM mysql.password_history WHERE
User='no_pwd_history' AND Host='localhost';

# 4.1 hash of 'hihi'
ALTER USER no_pwd_history@localhost IDENTIFIED WITH "mysql_native_password"
  AS '*C40F578E1A9D8D1146AFD04AFCB0228EED9D45FB';

--echo # ALTER USER IDENTIFIED WITH .. AS .. should not produce rows
SELECT COUNT(*) FROM mysql.password_history WHERE
  User='no_pwd_history' AND Host='localhost';

connect(con1,localhost,no_pwd_history,hihi,test);

--echo # try 2 statements in a row in a quick succession
--query SET PASSWORD='pwd1'; SET PASSWORD='pwd2';

connection default;
disconnect con1;

--echo # 2 SET PASSWORD should not produce rows
SELECT COUNT(*) FROM mysql.password_history WHERE
  User='no_pwd_history' AND Host='localhost';


--echo # FR 1.1.1: must succeed
SET GLOBAL password_history= 1;

--echo # FR 1.1.2: must succeed. No history tracked so far
SET PASSWORD FOR no_pwd_history@localhost = 'tracked';

--echo # must be 0: unaffected by the global
SELECT COUNT(*) FROM mysql.password_history WHERE
  User='no_pwd_history' AND Host='localhost';

--echo # FR 1.1.2: must succeed again: no tracking
SET PASSWORD FOR no_pwd_history@localhost = 'tracked';

--echo # must be 0: unaffected by the global again
SELECT COUNT(*) FROM mysql.password_history WHERE
  User='no_pwd_history' AND Host='localhost';

SET GLOBAL password_history= default;
--echo # FR 2.1.1: must be settable at runtime
SET GLOBAL password_reuse_interval= 100;

--echo # FR 2.1.2: Must succeed: no history tracked
SET PASSWORD FOR no_pwd_history@localhost = 'tracked';

--echo # must be 0: unaffected by the global password reuse interval
SELECT COUNT(*) FROM mysql.password_history WHERE
  User='no_pwd_history' AND Host='localhost';

--echo # FR 2.1.2: password still not tracked: must succeed
SET PASSWORD FOR no_pwd_history@localhost = 'tracked';

--echo # 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';

--echo # FR6: Must have the password history clauses
--replace_regex /AS '(.*)'/AS '<non-deterministic-password-hash>'/g
SHOW CREATE USER no_pwd_history@localhost;

--echo # 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';

SET GLOBAL password_reuse_interval= DEFAULT;
DROP USER no_pwd_history@localhost;


--echo # Test the effect of reuse interval on regular users
CREATE USER default_def@localhost IDENTIFIED BY 'haha';

--echo # FR6: must have the history clauses
--replace_regex /AS '(.*)'/AS '<non-deterministic-password-hash>'/g
SHOW CREATE USER default_def@localhost;

--echo # 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';

--echo # 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';

--echo # FR1.1.2: set a global of 1 and see if it affects the default users
SET GLOBAL password_history=1;

--echo # Must work: first change
ALTER USER default_def@localhost IDENTIFIED BY 'haha';

--echo # 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';

--echo # Must fail: change to the same password and global is 1 for default users
--error ER_CREDENTIALS_CONTRADICT_TO_HISTORY
ALTER USER default_def@localhost IDENTIFIED BY 'haha';

SET GLOBAL password_history=default;

--echo # Must pass: change to the same password and global is 0
ALTER USER default_def@localhost IDENTIFIED BY 'haha';

--echo # 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';

--echo # FR3.4: set a global of 1 record history and then remove the user
SET GLOBAL password_history=1;

--echo # Must pass: change to the same password and global is 0
ALTER USER default_def@localhost IDENTIFIED BY 'haha';

--echo # 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';

DROP USER default_def@localhost;

--echo # 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';

SET GLOBAL password_history=default;

--echo # 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';

--echo # 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';

--echo # 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;

ALTER USER method_alter@localhost IDENTIFIED WITH 'sha256_password';

--echo # must be 0: changing the auth method prunes history
SELECT COUNT(*) FROM mysql.password_history WHERE
  User='method_alter' AND Host='localhost';

DROP USER method_alter@localhost;
SET GLOBAL password_history=default;

--echo # FR 3.7: RENAME USER renames the entries in history
CREATE USER to_be_renamed@localhost IDENTIFIED BY 'haha' PASSWORD HISTORY 1;

--echo # must be 1: user created with password history
SELECT COUNT(*) FROM mysql.password_history WHERE
  User='to_be_renamed' AND Host='localhost';

RENAME USER to_be_renamed@localhost TO now_renamed@localhost;

--echo # must be 0: user renamed
SELECT COUNT(*) FROM mysql.password_history WHERE
  User='to_be_renamed' AND Host='localhost';


--echo # must be 1: this is what the user is renamed to
SELECT COUNT(*) FROM mysql.password_history WHERE
  User='now_renamed' AND Host='localhost';

--echo # must fail: password is in history
--error ER_CREDENTIALS_CONTRADICT_TO_HISTORY
SET PASSWORD FOR now_renamed@localhost = 'haha';

--echo # FR3.8: changes to the history table take effect immediately
DELETE FROM mysql.password_history WHERE
  User='now_renamed' AND Host='localhost';

--echo # must pass: password history is empty
SET PASSWORD FOR now_renamed@localhost = 'haha';

--echo # must be 1: history added back again
SELECT COUNT(*) FROM mysql.password_history WHERE
  User='now_renamed' AND Host='localhost';

DROP USER now_renamed@localhost;

--echo # 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;

--echo # must fail: eventual write operation to history
--error ER_NO_SUCH_TABLE
CREATE USER no_pwd_history_err@localhost IDENTIFIED BY 'haha'
  PASSWORD HISTORY 0 PASSWORD REUSE INTERVAL 0 DAY;

--echo # Must fail: SET PASSWORD can write to history
--error ER_NO_SUCH_TABLE
SET PASSWORD FOR no_pwd_history@localhost = 'hehe';

--echo # Must fail: ALTER USER IDENTIFIED BY can write to history
--error ER_NO_SUCH_TABLE
ALTER USER no_pwd_history@localhost IDENTIFIED BY 'hihi';

--echo # Must fail: ALTER USER IDENTIFIED WITH can add to history
--error ER_NO_SUCH_TABLE
ALTER USER no_pwd_history@localhost IDENTIFIED WITH 'sha256_password';

--echo # Must fail: DROP USER can write to history
--error ER_NO_SUCH_TABLE
DROP USER no_pwd_history@localhost;

RENAME TABLE mysql.password_history_backup TO mysql.password_history;

DROP USER no_pwd_history@localhost;


--echo #FR4: new clauses can be specified more than once

--echo # With duplicate PASSWORD HISTORY the last one takes precedence
CREATE USER dup_history@localhost IDENTIFIED BY 'haha'
  PASSWORD HISTORY 1 PASSWORD HISTORY DEFAULT;

--echo # must be 0: global is 0 and last option takes precendence
SELECT COUNT(*) FROM mysql.password_history WHERE
  User='dup_history' AND Host='localhost';

DROP USER dup_history@localhost;

--echo # 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;

--echo # must be 0: global is 0 and last option takes precendence
SELECT COUNT(*) FROM mysql.password_history WHERE
  User='dup_interval' AND Host='localhost';

DROP USER dup_interval@localhost;

--echo # FR7: Empty passwords are not verified nor stored in password history.

CREATE USER empty_pwd@localhost IDENTIFIED BY ''
  PASSWORD HISTORY 1;

--echo # must be 0: empty passwords not recorded after CREATE USER
SELECT COUNT(*) FROM mysql.password_history WHERE
  User='empty_pwd' AND Host='localhost';

--echo # Must pass: it's ok to set again an empty password
SET PASSWORD FOR empty_pwd@localhost = '';

--echo # must be 0: empty passwords not recorded after SET PASSWORD
SELECT COUNT(*) FROM mysql.password_history WHERE
  User='empty_pwd' AND Host='localhost';

DROP USER empty_pwd@localhost;

--echo # FR2.1: A new global only system variable password_reuse_interval
--error ER_GLOBAL_VARIABLE
SET SESSION password_reuse_interval= 0;

SET GLOBAL password_history=0;
SET GLOBAL password_reuse_interval=1;
CREATE USER def_interval@localhost IDENTIFIED BY 'haha';

--echo # must be 1: password recorded due to interval in CREATE USER
SELECT COUNT(*) FROM mysql.password_history WHERE
  User='def_interval' AND Host='localhost';

--echo # Must fail: password reused too soon
--error ER_CREDENTIALS_CONTRADICT_TO_HISTORY
SET PASSWORD FOR def_interval@localhost = 'haha';

--echo # 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';

--echo # Must pass now: the password was used yesterday
SET PASSWORD FOR def_interval@localhost = 'haha';

--echo # 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());

SET PASSWORD FOR def_interval@localhost = 'hihi';

--echo # 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());

--echo # 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';

--echo # Must pass: password in SET PASSWORD not used for a month
SET PASSWORD FOR def_interval@localhost = 'hoho';

--echo # 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());

SET GLOBAL password_reuse_interval = default;
DROP USER def_interval@localhost;

--echo #
--echo # Bug #26364229: PASSWORD CHANGE FROM A USER SESSION
--echo #    DOES NOT PRESERVE THE PASSWORD_HISTORY VALUE
--echo #
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;

--echo # Must have PASSWORD HISTORY 1 and PASSWORD REUSE INTERVAL 2
--replace_regex /AS '(.*)'/AS '<non-deterministic-password-hash>'/g
SHOW CREATE USER mohit@localhost;

--echo # Must fail: password history is 1
--error ER_CREDENTIALS_CONTRADICT_TO_HISTORY
ALTER USER mohit@localhost IDENTIFIED BY 'mohit';

--echo # Must pass: new password
ALTER USER mohit@localhost IDENTIFIED BY 'mohit1';

--echo # Must have PASSWORD HISTORY 1 and PASSWORD REUSE INTERVAL 2
--replace_regex /AS '(.*)'/AS '<non-deterministic-password-hash>'/g
SHOW CREATE USER mohit@localhost;

DROP USER mohit@localhost;

SET GLOBAL password_reuse_interval= default;

CREATE USER mohit@localhost IDENTIFIED BY 'mohit' PASSWORD HISTORY 1;

--echo # Must have PASSWORD HISTORY 1
--replace_regex /AS '(.*)'/AS '<non-deterministic-password-hash>'/g
SHOW CREATE USER mohit@localhost;

--echo # must be 1: password recorded due to interval in CREATE USER
SELECT COUNT(*) FROM mysql.password_history WHERE
  User='mohit' AND Host='localhost';

--echo # Must fail: password history is 1
--error ER_CREDENTIALS_CONTRADICT_TO_HISTORY
ALTER USER mohit@localhost IDENTIFIED BY 'mohit';

--echo # Must pass: new password
ALTER USER mohit@localhost IDENTIFIED BY 'mohit1';

--echo # must be 1: new password bumped the old one
SELECT COUNT(*) FROM mysql.password_history WHERE
  User='mohit' AND Host='localhost';

--echo # 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;


--echo #
--echo # Bug #26410591: ASSERT FAIL IN COMPARE_NATIVE_PASSWORD_WITH_HASH
--echo #

CREATE USER mohit@localhost
  IDENTIFIED WITH sha256_password BY 'mohit' PASSWORD HISTORY 1;

--echo # must be 1: SHA256 password recorded due to history
SELECT COUNT(*) FROM mysql.password_history WHERE
  User='mohit' AND Host='localhost';

--echo # Save the SH256 password for later
CREATE TABLE pwd_history_backup AS
  SELECT * FROM mysql.password_history
    WHERE User='mohit' AND Host='localhost';

--echo # Must not crash
ALTER USER mohit@localhost IDENTIFIED WITH mysql_native_password BY 'mohit';

--echo # Must be 1: the native password
SELECT COUNT(*) FROM mysql.password_history WHERE
  User='mohit' AND Host='localhost';

--echo # 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';

--echo # Must not crash
ALTER USER mohit@localhost IDENTIFIED WITH mysql_native_password BY 'mohit';

DROP TABLE pwd_history_backup;
DROP USER mohit@localhost;


--echo #
--echo # Bug #26410846: PASSWORD ROTATION POLICY IS NOT WORKING FOR SHA256
--echo #   AUTHENTICATED USERS
--echo #

CREATE USER mohit_sha@localhost IDENTIFIED WITH sha256_password BY 'mohit_sha'
  PASSWORD HISTORY 1;

--echo # Must fail: same password
--error ER_CREDENTIALS_CONTRADICT_TO_HISTORY
ALTER USER mohit_sha@localhost IDENTIFIED BY 'mohit_sha';

--echo # Must pass: different password
ALTER USER mohit_sha@localhost IDENTIFIED BY 'mohit_sha2';

DROP USER mohit_sha@localhost;

--echo #
--echo # Altering a user with PASSWORD REUSE INTERVAL
--echo #

SET GLOBAL password_history=0;
SET GLOBAL password_reuse_interval= 1;

CREATE USER amar@localhost IDENTIFIED BY 'amar'
  PASSWORD REUSE INTERVAL 1 DAY;

--echo # Must be 1: due to INTERVAL 1 day
SELECT COUNT(*) FROM mysql.password_history WHERE
  User='amar' AND Host='localhost';

--echo # Must fail: duplicate password
--error ER_CREDENTIALS_CONTRADICT_TO_HISTORY
ALTER USER amar@localhost IDENTIFIED BY 'amar';

--echo # Must still be 1: due to INTERVAL 1 day again
SELECT COUNT(*) FROM mysql.password_history WHERE
  User='amar' AND Host='localhost';

DROP USER amar@localhost;
SET GLOBAL password_history=default;
SET GLOBAL password_reuse_interval=default;


--echo #
--echo # Tests for caching_sha2_password plugin
--echo #

CREATE USER mohit_sha@localhost IDENTIFIED WITH caching_sha2_password BY 'mohit_sha'
  PASSWORD HISTORY 1;

--echo # Must fail: same password
--error ER_CREDENTIALS_CONTRADICT_TO_HISTORY
ALTER USER mohit_sha@localhost IDENTIFIED BY 'mohit_sha';

--echo # Must pass: different password
ALTER USER mohit_sha@localhost IDENTIFIED BY 'mohit_sha2';

ALTER USER mohit_sha@localhost PASSWORD HISTORY 2;

--echo # Must pass: We do not record for the password
ALTER USER mohit_sha@localhost IDENTIFIED BY 'mohit_sha';

--echo # Must fail: passwords present in history table
--error ER_CREDENTIALS_CONTRADICT_TO_HISTORY
ALTER USER mohit_sha@localhost IDENTIFIED BY 'mohit_sha';
--error ER_CREDENTIALS_CONTRADICT_TO_HISTORY
ALTER USER mohit_sha@localhost IDENTIFIED BY 'mohit_sha2';

--echo # Must pass: different password
ALTER USER mohit_sha@localhost IDENTIFIED BY 'mohit_sha3';

DROP USER mohit_sha@localhost;

--echo # ------------------------------------------------------------------
--echo # -- End of 8.0 tests
--echo # ------------------------------------------------------------------


# Wait till all disconnects are completed
--source include/wait_until_count_sessions.inc