428 lines
23 KiB
Plaintext
428 lines
23 KiB
Plaintext
# TestPasswordExpiration
|
|
drop user if EXISTS testuser, testuser1, testuser2, testuser3, testuser4;
|
|
drop role if EXISTS role1;
|
|
CREATE USER testuser;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser';
|
|
CREATE USER testuser1 PASSWORD EXPIRE;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser1';
|
|
CREATE USER testuser2 PASSWORD EXPIRE DEFAULT;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser2';
|
|
CREATE USER testuser3 PASSWORD EXPIRE NEVER;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser3';
|
|
CREATE USER testuser4 PASSWORD EXPIRE INTERVAL 3 DAY;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser4';
|
|
CREATE ROLE role1;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'role1';
|
|
ALTER USER testuser PASSWORD EXPIRE NEVER;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser';
|
|
ALTER USER testuser PASSWORD EXPIRE DEFAULT;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser';
|
|
ALTER USER testuser PASSWORD EXPIRE INTERVAL 3 DAY;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser';
|
|
ALTER USER testuser PASSWORD EXPIRE;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser';
|
|
ALTER USER testuser IDENTIFIED BY '' PASSWORD EXPIRE;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser';
|
|
ALTER USER testuser IDENTIFIED WITH 'mysql_native_password' AS '';
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser';
|
|
ALTER USER testuser IDENTIFIED BY '';
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser';
|
|
ALTER USER testuser1 PASSWORD EXPIRE NEVER;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser1';
|
|
ALTER USER testuser1 PASSWORD EXPIRE DEFAULT;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser1';
|
|
ALTER USER testuser1 PASSWORD EXPIRE INTERVAL 3 DAY;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser1';
|
|
ALTER USER testuser1 PASSWORD EXPIRE;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser1';
|
|
ALTER USER testuser1 IDENTIFIED BY '' PASSWORD EXPIRE;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser1';
|
|
ALTER USER testuser1 IDENTIFIED WITH 'mysql_native_password' AS '';
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser1';
|
|
ALTER USER testuser1 IDENTIFIED BY '';
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser1';
|
|
ALTER USER testuser2 PASSWORD EXPIRE NEVER;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser2';
|
|
ALTER USER testuser2 PASSWORD EXPIRE DEFAULT;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser2';
|
|
ALTER USER testuser2 PASSWORD EXPIRE INTERVAL 3 DAY;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser2';
|
|
ALTER USER testuser2 PASSWORD EXPIRE;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser2';
|
|
ALTER USER testuser2 IDENTIFIED BY '' PASSWORD EXPIRE;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser2';
|
|
ALTER USER testuser2 IDENTIFIED WITH 'mysql_native_password' AS '';
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser2';
|
|
ALTER USER testuser2 IDENTIFIED BY '';
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser2';
|
|
ALTER USER testuser3 PASSWORD EXPIRE NEVER;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser3';
|
|
ALTER USER testuser3 PASSWORD EXPIRE DEFAULT;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser3';
|
|
ALTER USER testuser3 PASSWORD EXPIRE INTERVAL 3 DAY;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser3';
|
|
ALTER USER testuser3 PASSWORD EXPIRE;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser3';
|
|
ALTER USER testuser3 IDENTIFIED BY '' PASSWORD EXPIRE;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser3';
|
|
ALTER USER testuser3 IDENTIFIED WITH 'mysql_native_password' AS '';
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser3';
|
|
ALTER USER testuser3 IDENTIFIED BY '';
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser3';
|
|
ALTER USER testuser4 PASSWORD EXPIRE NEVER;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser4';
|
|
ALTER USER testuser4 PASSWORD EXPIRE DEFAULT;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser4';
|
|
ALTER USER testuser4 PASSWORD EXPIRE INTERVAL 3 DAY;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser4';
|
|
ALTER USER testuser4 PASSWORD EXPIRE;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser4';
|
|
ALTER USER testuser4 IDENTIFIED BY '' PASSWORD EXPIRE;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser4';
|
|
ALTER USER testuser4 IDENTIFIED WITH 'mysql_native_password' AS '';
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser4';
|
|
ALTER USER testuser4 IDENTIFIED BY '';
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser4';
|
|
ALTER USER role1 PASSWORD EXPIRE NEVER;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'role1';
|
|
ALTER USER role1 PASSWORD EXPIRE DEFAULT;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'role1';
|
|
ALTER USER role1 PASSWORD EXPIRE INTERVAL 3 DAY;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'role1';
|
|
ALTER USER role1 PASSWORD EXPIRE;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'role1';
|
|
ALTER USER role1 IDENTIFIED BY '' PASSWORD EXPIRE;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'role1';
|
|
ALTER USER role1 IDENTIFIED WITH 'mysql_native_password' AS '';
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'role1';
|
|
ALTER USER role1 IDENTIFIED BY '';
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'role1';
|
|
ALTER USER testuser PASSWORD EXPIRE;
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser';
|
|
SET PASSWORD FOR testuser = '1234';
|
|
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'testuser';
|
|
drop user if EXISTS ''@localhost;
|
|
-- error 3016
|
|
CREATE USER ''@localhost IDENTIFIED BY 'pass' PASSWORD EXPIRE;
|
|
CREATE USER ''@localhost IDENTIFIED BY 'pass';
|
|
-- error 3016
|
|
ALTER USER ''@localhost PASSWORD EXPIRE;
|
|
DROP USER IF EXISTS 'u1'@'localhost';
|
|
CREATE USER 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password';
|
|
ALTER USER 'u1'@'localhost' IDENTIFIED BY 'pass';
|
|
ALTER USER 'u1'@'localhost' PASSWORD EXPIRE;
|
|
SELECT password_expired FROM mysql.user WHERE user = 'u1' and host = 'localhost';
|
|
DROP USER IF EXISTS 'u1'@'localhost';
|
|
CREATE USER 'u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password';
|
|
ALTER USER 'u1'@'localhost' IDENTIFIED BY 'pass';
|
|
ALTER USER 'u1'@'localhost' PASSWORD EXPIRE;
|
|
SELECT password_expired FROM mysql.user WHERE user = 'u1' and host = 'localhost';
|
|
DROP USER IF EXISTS 'u1'@'localhost';
|
|
CREATE USER 'u1'@'localhost' IDENTIFIED WITH 'tidb_sm3_password';
|
|
ALTER USER 'u1'@'localhost' IDENTIFIED BY 'pass';
|
|
ALTER USER 'u1'@'localhost' PASSWORD EXPIRE;
|
|
SELECT password_expired FROM mysql.user WHERE user = 'u1' and host = 'localhost';
|
|
drop user 'u1'@'localhost';
|
|
|
|
# TestUserReuseControl
|
|
show variables like "password_history";
|
|
show variables like "password_reuse_interval";
|
|
set global password_history = -1;
|
|
set global password_reuse_interval = -1;
|
|
show variables like "password_history";
|
|
show variables like "password_reuse_interval";
|
|
set global password_history = 4294967295;
|
|
set global password_reuse_interval = 4294967295;
|
|
show variables like "password_history";
|
|
show variables like "password_reuse_interval";
|
|
set global password_history = 4294967296;
|
|
set global password_reuse_interval = 4294967296;
|
|
show variables like "password_history";
|
|
show variables like "password_reuse_interval";
|
|
-- error 1229
|
|
set session password_history = 42949;
|
|
-- error 1229
|
|
set session password_reuse_interval = 42949;
|
|
|
|
set global password_history = DEFAULT;
|
|
set global password_reuse_interval = DEFAULT;
|
|
|
|
# TestUserReuseInfo
|
|
drop user if EXISTS testReuse;
|
|
CREATE USER testReuse;
|
|
SELECT Password_reuse_history,Password_reuse_time FROM mysql.user WHERE user = 'testReuse';
|
|
ALTER USER testReuse PASSWORD HISTORY 5;
|
|
SELECT Password_reuse_history,Password_reuse_time FROM mysql.user WHERE user = 'testReuse';
|
|
ALTER USER testReuse PASSWORD HISTORY 0;
|
|
SELECT Password_reuse_history,Password_reuse_time FROM mysql.user WHERE user = 'testReuse';
|
|
ALTER USER testReuse PASSWORD HISTORY DEFAULT;
|
|
SELECT Password_reuse_history,Password_reuse_time FROM mysql.user WHERE user = 'testReuse';
|
|
ALTER USER testReuse PASSWORD HISTORY 65536;
|
|
SELECT Password_reuse_history,Password_reuse_time FROM mysql.user WHERE user = 'testReuse';
|
|
ALTER USER testReuse PASSWORD REUSE INTERVAL 5 DAY;
|
|
SELECT Password_reuse_history,Password_reuse_time FROM mysql.user WHERE user = 'testReuse';
|
|
ALTER USER testReuse PASSWORD REUSE INTERVAL 0 DAY;
|
|
SELECT Password_reuse_history,Password_reuse_time FROM mysql.user WHERE user = 'testReuse';
|
|
ALTER USER testReuse PASSWORD REUSE INTERVAL DEFAULT;
|
|
SELECT Password_reuse_history,Password_reuse_time FROM mysql.user WHERE user = 'testReuse';
|
|
ALTER USER testReuse PASSWORD REUSE INTERVAL 65536 DAY;
|
|
SELECT Password_reuse_history,Password_reuse_time FROM mysql.user WHERE user = 'testReuse';
|
|
ALTER USER testReuse PASSWORD HISTORY 6 PASSWORD REUSE INTERVAL 6 DAY;
|
|
SELECT Password_reuse_history,Password_reuse_time FROM mysql.user WHERE user = 'testReuse';
|
|
ALTER USER testReuse PASSWORD HISTORY 6 PASSWORD HISTORY 7 ;
|
|
SELECT Password_reuse_history,Password_reuse_time FROM mysql.user WHERE user = 'testReuse';
|
|
drop USER testReuse;
|
|
CREATE USER testReuse PASSWORD HISTORY 5;
|
|
SELECT Password_reuse_history,Password_reuse_time FROM mysql.user WHERE user = 'testReuse';
|
|
drop USER testReuse;
|
|
CREATE USER testReuse PASSWORD REUSE INTERVAL 5 DAY;
|
|
SELECT Password_reuse_history,Password_reuse_time FROM mysql.user WHERE user = 'testReuse';
|
|
drop USER testReuse;
|
|
CREATE USER testReuse PASSWORD REUSE INTERVAL 5 DAY PASSWORD REUSE INTERVAL 6 DAY;
|
|
SELECT Password_reuse_history,Password_reuse_time FROM mysql.user WHERE user = 'testReuse';
|
|
drop USER testReuse;
|
|
CREATE USER testReuse PASSWORD HISTORY 5 PASSWORD REUSE INTERVAL 6 DAY;
|
|
SELECT Password_reuse_history,Password_reuse_time FROM mysql.user WHERE user = 'testReuse';
|
|
drop USER testReuse;
|
|
CREATE USER testReuse PASSWORD REUSE INTERVAL 6 DAY PASSWORD HISTORY 5;
|
|
SELECT Password_reuse_history,Password_reuse_time FROM mysql.user WHERE user = 'testReuse';
|
|
drop USER testReuse;
|
|
-- error 1064
|
|
CREATE USER testReuse PASSWORD HISTORY -5;
|
|
-- error 1064
|
|
CREATE USER testReuse PASSWORD REUSE INTERVAL -6 DAY;
|
|
CREATE USER testReuse PASSWORD HISTORY 65535 PASSWORD REUSE INTERVAL 65535 DAY;
|
|
SELECT Password_reuse_history,Password_reuse_time FROM mysql.user WHERE user = 'testReuse';
|
|
drop USER testReuse;
|
|
CREATE USER testReuse PASSWORD HISTORY 65536 PASSWORD REUSE INTERVAL 65536 DAY;
|
|
SELECT Password_reuse_history,Password_reuse_time FROM mysql.user WHERE user = 'testReuse';
|
|
drop USER testReuse;
|
|
CREATE USER testReuse PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT;
|
|
SELECT Password_reuse_history,Password_reuse_time FROM mysql.user WHERE user = 'testReuse';
|
|
drop USER testReuse;
|
|
CREATE USER testReuse PASSWORD HISTORY 0 PASSWORD REUSE INTERVAL 0 DAY;
|
|
SELECT Password_reuse_history,Password_reuse_time FROM mysql.user WHERE user = 'testReuse';
|
|
|
|
# TestUserReuseFunction
|
|
drop user if EXISTS testReuse;
|
|
CREATE USER testReuse identified by 'test';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
set global password_history = 1;
|
|
alter USER testReuse identified by 'test';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
-- error 3638
|
|
alter USER testReuse identified by 'test';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
alter USER testReuse identified by 'test1';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
DROP USER testReuse;
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
set global password_history = 0;
|
|
set global password_reuse_interval = 1;
|
|
CREATE USER testReuse identified by 'test';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
-- error 3638
|
|
alter USER testReuse identified by 'test';
|
|
alter USER testReuse identified by 'test1';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
alter USER testReuse identified by 'test2';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
alter USER testReuse identified by 'test3';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
update mysql.password_history set Password_timestamp = date_sub(Password_timestamp,interval '1 0:0:1' DAY_SECOND);
|
|
alter USER testReuse identified by 'test';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
drop USER testReuse ;
|
|
set global password_reuse_interval = 0;
|
|
CREATE USER testReuse PASSWORD HISTORY 5 PASSWORD REUSE INTERVAL 6 DAY;
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
drop USER testReuse ;
|
|
CREATE USER testReuse identified by 'test' PASSWORD HISTORY 5;
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
alter USER testReuse identified by 'test1';
|
|
alter USER testReuse identified by 'test2';
|
|
alter USER testReuse identified by 'test3';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
-- error 3638
|
|
alter USER testReuse identified by 'test';
|
|
alter USER testReuse identified by 'test4';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
alter USER testReuse identified by 'test5';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
-- error 3638
|
|
alter USER testReuse identified by 'test1';
|
|
alter USER testReuse identified by 'test';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
drop USER testReuse;
|
|
CREATE USER testReuse identified by 'test' PASSWORD HISTORY 5 PASSWORD REUSE INTERVAL 3 DAY;
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
alter USER testReuse identified by 'test1';
|
|
alter USER testReuse identified by 'test2';
|
|
alter USER testReuse identified by 'test3';
|
|
alter USER testReuse identified by 'test4';
|
|
alter USER testReuse identified by 'test5';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
-- error 3638
|
|
alter USER testReuse identified by 'test';
|
|
update mysql.password_history set Password_timestamp = date_sub(Password_timestamp,interval '3 0:0:1' DAY_SECOND) where user = 'testReuse' order by Password_timestamp asc limit 1;
|
|
alter USER testReuse identified by 'test';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
drop USER testReuse;
|
|
CREATE USER testReuse identified by 'test' PASSWORD HISTORY 5 PASSWORD REUSE INTERVAL 3 DAY;
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
alter USER testReuse identified by 'test1';
|
|
alter USER testReuse identified by 'test2';
|
|
alter USER testReuse identified by 'test3';
|
|
update mysql.password_history set Password_timestamp = date_sub(Password_timestamp,interval '3 0:0:1' DAY_SECOND) where user = 'testReuse' order by Password_timestamp asc limit 1;
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
-- error 3638
|
|
alter USER testReuse identified by 'test';
|
|
ALTER USER testReuse PASSWORD HISTORY 3;
|
|
alter USER testReuse identified by 'test';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
drop USER testReuse;
|
|
set global password_history = 1;
|
|
set global password_reuse_interval = 1;
|
|
CREATE USER testReuse identified by 'test' PASSWORD HISTORY 0 PASSWORD REUSE INTERVAL 0 DAY;
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
alter USER testReuse identified by 'test';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
drop USER testReuse;
|
|
set global password_history = 0;
|
|
set global password_reuse_interval = 360000000;
|
|
CREATE USER testReuse identified by 'test';
|
|
alter USER testReuse identified by 'test1';
|
|
-- error 3638
|
|
alter USER testReuse identified by 'test';
|
|
-- error 3638
|
|
set PASSWORD FOR testReuse = 'test';
|
|
alter USER testReuse identified by '';
|
|
alter USER testReuse identified by '';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
alter USER testReuse identified by 'test2';
|
|
set global password_reuse_interval = 4294967295;
|
|
alter USER testReuse identified by 'test3';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
set PASSWORD FOR testReuse = 'test4';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
drop USER testReuse;
|
|
set global password_reuse_interval = 0;
|
|
CREATE USER testReuse identified by 'test' PASSWORD HISTORY 5;
|
|
alter USER testReuse identified by 'test1';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
alter USER testReuse identified by 'test1' PASSWORD HISTORY 0;
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
alter USER testReuse identified by 'test1' PASSWORD HISTORY 2 PASSWORD REUSE INTERVAL 1 DAY;
|
|
alter USER testReuse identified by 'test2';
|
|
alter USER testReuse identified by 'test3';
|
|
alter USER testReuse identified by 'test1' PASSWORD HISTORY 2 PASSWORD REUSE INTERVAL 0 DAY;
|
|
drop USER testReuse;
|
|
set global password_history = 1;
|
|
CREATE USER testReuse identified by 'test' PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT;
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
-- error 3638
|
|
ALTER USER testReuse identified by 'test' PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT;
|
|
ALTER USER testReuse identified by 'test1' PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT;
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
|
|
set global password_history = DEFAULT;
|
|
set global password_reuse_interval = DEFAULT;
|
|
|
|
# TestUserReuseDifferentAuth
|
|
drop user if EXISTS testReuse;
|
|
CREATE USER testReuse identified with 'caching_sha2_password' by 'test' PASSWORD HISTORY 1 ;
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
-- error 3638
|
|
alter USER testReuse identified by 'test';
|
|
-- error 3638
|
|
set password for testReuse = 'test';
|
|
alter USER testReuse identified by 'test1';
|
|
alter USER testReuse identified with 'tidb_sm3_password';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
drop USER testReuse;
|
|
CREATE USER testReuse identified with 'tidb_sm3_password' by 'test' PASSWORD HISTORY 1 ;
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
-- error 3638
|
|
alter USER testReuse identified by 'test';
|
|
-- error 3638
|
|
set password for testReuse = 'test';
|
|
alter USER testReuse identified by 'test1';
|
|
alter USER testReuse identified with 'caching_sha2_password';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
drop USER testReuse;
|
|
CREATE USER testReuse identified with 'caching_sha2_password' by 'test' PASSWORD REUSE INTERVAL 1 DAY;
|
|
-- error 3638
|
|
alter USER testReuse identified by 'test';
|
|
-- error 3638
|
|
set password for testReuse = 'test';
|
|
alter USER testReuse identified by 'test1';
|
|
alter USER testReuse identified by 'test2';
|
|
alter USER testReuse identified by 'test3';
|
|
-- error 3638
|
|
alter USER testReuse identified by 'test';
|
|
update mysql.password_history set Password_timestamp = date_sub(Password_timestamp,interval '1 0:0:1' DAY_SECOND) where user = 'testReuse' order by Password_timestamp asc limit 1;
|
|
alter USER testReuse identified by 'test';
|
|
drop USER testReuse;
|
|
-- error 1524
|
|
CREATE USER testReuse identified with 'mysql_clear_password' by 'test' PASSWORD REUSE INTERVAL 1 DAY;
|
|
-- error 1524
|
|
CREATE USER testReuse identified with 'tidb_session_token' by 'test' PASSWORD REUSE INTERVAL 1 DAY;
|
|
CREATE USER testReuse identified with 'auth_socket' by 'test' PASSWORD REUSE INTERVAL 1 DAY;
|
|
ALTER USER testReuse identified by 'test' ;
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
SELECT authentication_string FROM mysql.user WHERE user = 'testReuse';
|
|
ALTER USER testReuse identified with 'caching_sha2_password' by 'test' ;
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
drop USER testReuse;
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
CREATE USER testReuse identified with 'tidb_auth_token' by 'test' PASSWORD REUSE INTERVAL 1 DAY;
|
|
ALTER USER testReuse identified by 'test' ;
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
set password for testReuse = 'test';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
ALTER USER testReuse identified with 'caching_sha2_password' by 'test' ;
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
-- error 3638
|
|
alter USER testReuse identified by 'test';
|
|
-- error 3638
|
|
set password for testReuse = 'test';
|
|
drop USER testReuse;
|
|
|
|
# TestUserReuseMultiuser
|
|
drop user if EXISTS testReuse, testReuse1, testReuse2, testReuse3;
|
|
CREATE USER testReuse identified by 'test', testReuse1 identified by 'test', testReuse2 identified by 'test' PASSWORD HISTORY 65535 PASSWORD REUSE INTERVAL 65535 DAY;
|
|
SELECT Password_reuse_history,Password_reuse_time FROM mysql.user WHERE user like 'testReuse%';
|
|
ALTER USER testReuse identified by 'test1', testReuse1 identified by 'test1', testReuse2 identified by 'test1' PASSWORD HISTORY 3 PASSWORD REUSE INTERVAL 3 DAY;
|
|
SELECT Password_reuse_history,Password_reuse_time FROM mysql.user WHERE user like 'testReuse%';
|
|
SELECT count(*) FROM mysql.password_history WHERE user like 'testReuse%' group by user;
|
|
CREATE USER testReuse3 identified by 'test';
|
|
SELECT Password_reuse_history,Password_reuse_time FROM mysql.user WHERE user like 'testReuse%';
|
|
SELECT count(*) FROM mysql.password_history WHERE user like 'testReuse%' group by user;
|
|
-- error 3638
|
|
ALTER USER testReuse identified by 'test1', testReuse3 identified by 'test1';
|
|
drop User testReuse, testReuse1, testReuse2, testReuse3;
|
|
SELECT count(*) FROM mysql.password_history WHERE user like 'testReuse%' ;
|
|
|
|
# TestUserReuseRename
|
|
drop user if EXISTS testReuse, testReuse1;
|
|
CREATE USER testReuse identified by 'test' PASSWORD HISTORY 5;
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
alter USER testReuse identified by 'test1';
|
|
alter USER testReuse identified by 'test2';
|
|
alter USER testReuse identified by 'test3';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
rename USER testReuse to testReuse1;
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse';
|
|
SELECT count(*) FROM mysql.password_history WHERE user = 'testReuse1';
|
|
|
|
# TestUserAlterUser
|
|
drop user if EXISTS test1;
|
|
CREATE USER test1 IDENTIFIED WITH 'mysql_native_password' BY '1234';
|
|
-- error 1396
|
|
ALTER USER 'test1' IDENTIFIED BY '222', 'test_not_exist'@'localhost' IDENTIFIED BY '111';
|
|
SELECT authentication_string FROM mysql.User WHERE User="test1" and Host="%";
|
|
ALTER USER IF EXISTS 'test1' IDENTIFIED BY '222', 'test_not_exist'@'localhost' IDENTIFIED BY '111';
|
|
show warnings;
|
|
SELECT authentication_string FROM mysql.User WHERE User="test1" and Host="%";
|
|
|