737 lines
29 KiB
Plaintext
737 lines
29 KiB
Plaintext
|
|
|
|
# Save the initial number of concurrent sessions
|
|
--source include/count_sessions.inc
|
|
|
|
--echo #
|
|
--echo # WL#6409: CREATE/ALTER USER
|
|
--echo #
|
|
|
|
--echo # CREATE USER
|
|
|
|
CREATE USER u1@localhost;
|
|
--replace_column 2 <default_authentication_plugin>
|
|
query_vertical SELECT User,plugin FROM mysql.user WHERE USER='u1';
|
|
|
|
CREATE USER u2@localhost IDENTIFIED BY 'auth_string';
|
|
--replace_column 2 <default_authentication_plugin>
|
|
query_vertical SELECT User,plugin FROM mysql.user WHERE USER='u2';
|
|
|
|
CREATE USER u3@localhost IDENTIFIED WITH 'sha256_password';
|
|
query_vertical SELECT User,plugin FROM mysql.user WHERE USER='u3';
|
|
|
|
CREATE USER u4@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string';
|
|
query_vertical SELECT User,plugin FROM mysql.user WHERE USER='u4';
|
|
|
|
CREATE USER u5@localhost REQUIRE SSL;
|
|
--replace_column 2 <default_authentication_plugin>
|
|
query_vertical SELECT User,plugin,ssl_type FROM mysql.user WHERE USER='u5';
|
|
|
|
CREATE USER u6@localhost IDENTIFIED BY 'auth_string' REQUIRE X509;
|
|
--replace_column 2 <default_authentication_plugin>
|
|
query_vertical SELECT User,plugin,ssl_type FROM mysql.user WHERE USER='u6';
|
|
|
|
CREATE USER u7@localhost IDENTIFIED WITH 'sha256_password'
|
|
REQUIRE CIPHER "DHE-RSA-AES256-SHA" PASSWORD EXPIRE NEVER;
|
|
query_vertical SELECT User,plugin,ssl_type,
|
|
ssl_cipher,x509_issuer,x509_subject,password_expired,password_lifetime FROM mysql.user WHERE USER='u7';
|
|
|
|
CREATE USER u8@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
REQUIRE ISSUER 'issuer';
|
|
query_vertical SELECT User,plugin,ssl_type,ssl_cipher,x509_issuer,x509_subject FROM mysql.user WHERE USER='u8';
|
|
|
|
CREATE USER u9@localhost REQUIRE SUBJECT 'sub';
|
|
--replace_column 2 <default_authentication_plugin>
|
|
query_vertical SELECT User,plugin,ssl_type,ssl_cipher,x509_issuer,x509_subject FROM mysql.user WHERE USER='u9';
|
|
|
|
CREATE USER u10@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND
|
|
SUBJECT "/C=SE/ST=Uppsala/O=MySQL AB"
|
|
ISSUER "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB";
|
|
query_vertical SELECT User,plugin,ssl_type,ssl_cipher,x509_issuer,x509_subject FROM mysql.user WHERE USER='u10';
|
|
|
|
CREATE USER u11@localhost WITH MAX_QUERIES_PER_HOUR 2;
|
|
--replace_column 2 <default_authentication_plugin>
|
|
query_vertical SELECT User,plugin,max_questions FROM mysql.user WHERE USER='u11';
|
|
|
|
CREATE USER u12@localhost IDENTIFIED BY 'auth_string' WITH MAX_QUERIES_PER_HOUR 2;
|
|
--replace_column 2 <default_authentication_plugin>
|
|
query_vertical SELECT User,plugin,max_questions FROM mysql.user WHERE USER='u12';
|
|
|
|
CREATE USER u13@localhost IDENTIFIED WITH 'sha256_password'
|
|
WITH MAX_CONNECTIONS_PER_HOUR 2;
|
|
query_vertical SELECT User,plugin,max_connections FROM mysql.user WHERE USER='u13';
|
|
|
|
CREATE USER u14@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
WITH MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE INTERVAL 6 DAY;
|
|
query_vertical SELECT User,plugin,max_user_connections,
|
|
password_expired,password_lifetime FROM mysql.user WHERE USER='u14';
|
|
|
|
CREATE USER u15@localhost,
|
|
u16@localhost IDENTIFIED BY 'auth_string',
|
|
u17@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string' PASSWORD EXPIRE;
|
|
--replace_column 2 <authentication_plugin>
|
|
query_vertical SELECT User,plugin,password_expired,password_lifetime FROM mysql.user WHERE USER BETWEEN 'u15' AND 'u17' ORDER BY User;
|
|
|
|
CREATE USER u18@localhost,
|
|
u19@localhost IDENTIFIED BY 'auth_string',
|
|
u20@localhost IDENTIFIED WITH 'sha256_password',
|
|
u21@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2
|
|
PASSWORD EXPIRE NEVER;
|
|
--replace_column 2 <authentication_plugin>
|
|
query_vertical SELECT User,plugin,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
max_questions,max_user_connections,password_expired,password_lifetime
|
|
FROM mysql.user WHERE USER BETWEEN 'u18' AND 'u21' ORDER BY User;
|
|
|
|
drop user u1@localhost, u2@localhost, u3@localhost, u4@localhost, u5@localhost,
|
|
u6@localhost, u7@localhost, u8@localhost, u9@localhost, u10@localhost,
|
|
u11@localhost, u12@localhost, u13@localhost, u14@localhost,
|
|
u15@localhost, u16@localhost, u17@localhost, u18@localhost,
|
|
u19@localhost, u20@localhost, u21@localhost;
|
|
|
|
|
|
--echo # ALTER USER
|
|
|
|
CREATE USER u1@localhost;
|
|
--replace_column 6 <authentication_plugin>
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime
|
|
FROM mysql.user WHERE USER='u1';
|
|
--echo its a no op
|
|
ALTER USER u1@localhost;
|
|
--replace_column 6 <authentication_plugin>
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime
|
|
FROM mysql.user WHERE USER='u1';
|
|
|
|
CREATE USER u2@localhost IDENTIFIED BY 'auth_string';
|
|
--replace_column 6 <authentication_plugin>
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime FROM mysql.user WHERE USER='u2';
|
|
ALTER USER u2@localhost IDENTIFIED BY 'new_auth_string';
|
|
# look for auth_string and password last changed field
|
|
--replace_column 6 <authentication_plugin>
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime FROM mysql.user WHERE USER='u2';
|
|
|
|
CREATE USER u3@localhost IDENTIFIED WITH 'sha256_password';
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime FROM mysql.user WHERE USER='u3';
|
|
ALTER USER u3@localhost IDENTIFIED WITH 'mysql_native_password';
|
|
# look for plugin,auth_string and password expired field
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime FROM mysql.user WHERE USER='u3';
|
|
|
|
CREATE USER u4@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string';
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime FROM mysql.user WHERE USER='u4';
|
|
ALTER USER u4@localhost IDENTIFIED WITH 'mysql_native_password'
|
|
BY 'auth_string';
|
|
# look for plugin,auth_string field
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime FROM mysql.user WHERE USER='u4';
|
|
|
|
CREATE USER u5@localhost REQUIRE SSL;
|
|
--replace_column 6 <authentication_plugin>
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime FROM mysql.user WHERE USER='u5';
|
|
ALTER USER u5@localhost IDENTIFIED WITH 'sha256_password';
|
|
# look for plugin,auth_string, password expired field
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime FROM mysql.user WHERE USER='u5';
|
|
|
|
CREATE USER u6@localhost IDENTIFIED BY 'auth_string' REQUIRE X509;
|
|
--replace_column 6 <authentication_plugin>
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime FROM mysql.user WHERE USER='u6';
|
|
ALTER USER u6@localhost IDENTIFIED BY 'new_auth_string' REQUIRE SSL;
|
|
--replace_column 6 <authentication_plugin>
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime FROM mysql.user WHERE USER='u6';
|
|
|
|
CREATE USER u7@localhost IDENTIFIED WITH 'sha256_password'
|
|
BY 'auth_string' REQUIRE CIPHER 'cipher';
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime FROM mysql.user WHERE USER='u7';
|
|
ALTER USER u7@localhost IDENTIFIED WITH 'mysql_native_password'
|
|
REQUIRE ISSUER 'issuer';
|
|
# look for plugin,auth_string, password expired, SSL type field
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime FROM mysql.user WHERE USER='u7';
|
|
|
|
CREATE USER u8@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
REQUIRE ISSUER 'issuer';
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime FROM mysql.user WHERE USER='u8';
|
|
ALTER USER u8@localhost IDENTIFIED WITH 'mysql_native_password'
|
|
REQUIRE CIPHER "DHE-RSA-AES256-SHA";
|
|
# look for plugin,auth_string, password expired, SSL fields
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime FROM mysql.user WHERE USER='u8';
|
|
|
|
CREATE USER u9@localhost REQUIRE SUBJECT 'sub';
|
|
--replace_column 6 <authentication_plugin>
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime FROM mysql.user WHERE USER='u9';
|
|
ALTER USER u9@localhost REQUIRE ISSUER "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB";
|
|
--replace_column 6 <authentication_plugin>
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime FROM mysql.user WHERE USER='u9';
|
|
|
|
CREATE USER u10@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND
|
|
SUBJECT "/C=SE/ST=Uppsala/O=MySQL AB"
|
|
ISSUER "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB";
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime FROM mysql.user WHERE USER='u10';
|
|
ALTER USER u10@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
REQUIRE SSL;
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime FROM mysql.user WHERE USER='u10';
|
|
|
|
CREATE USER u11@localhost WITH MAX_QUERIES_PER_HOUR 2;
|
|
query_vertical SELECT User,max_questions FROM mysql.user WHERE USER='u11';
|
|
ALTER USER u11@localhost WITH MAX_QUERIES_PER_HOUR 6;
|
|
query_vertical SELECT User,max_questions FROM mysql.user WHERE USER='u11';
|
|
|
|
CREATE USER u12@localhost IDENTIFIED BY 'auth_string' WITH MAX_QUERIES_PER_HOUR 2;
|
|
query_vertical SELECT User,max_questions FROM mysql.user WHERE USER='u12';
|
|
ALTER USER u12@localhost IDENTIFIED WITH 'sha256_password' WITH MAX_QUERIES_PER_HOUR 8;
|
|
query_vertical SELECT User,max_questions FROM mysql.user WHERE USER='u12';
|
|
|
|
CREATE USER u13@localhost IDENTIFIED WITH 'sha256_password'
|
|
WITH MAX_CONNECTIONS_PER_HOUR 2;
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime FROM mysql.user WHERE USER='u13';
|
|
ALTER USER u13@localhost PASSWORD EXPIRE;
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime FROM mysql.user WHERE USER='u13';
|
|
|
|
CREATE USER u14@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
WITH MAX_USER_CONNECTIONS 2;
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,max_user_connections,
|
|
password_lifetime FROM mysql.user WHERE USER='u14';
|
|
ALTER USER u14@localhost WITH MAX_USER_CONNECTIONS 12 PASSWORD EXPIRE INTERVAL 365 DAY;
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,max_user_connections,
|
|
password_lifetime FROM mysql.user WHERE USER='u14';
|
|
|
|
CREATE USER u15@localhost,
|
|
u16@localhost IDENTIFIED WITH 'sha256_password',
|
|
u17@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string';
|
|
--replace_column 6 <authentication_plugin>
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime FROM mysql.user
|
|
WHERE USER BETWEEN 'u15' AND 'u17' order by 1;
|
|
ALTER USER u15@localhost IDENTIFIED WITH 'sha256_password',
|
|
u16@localhost,
|
|
u17@localhost IDENTIFIED BY 'new_auth_string'
|
|
PASSWORD EXPIRE DEFAULT;
|
|
--replace_column 6 <authentication_plugin>
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,
|
|
password_lifetime FROM mysql.user
|
|
WHERE USER BETWEEN 'u15' AND 'u17' order by 1;
|
|
|
|
CREATE USER u18@localhost,
|
|
u19@localhost IDENTIFIED BY 'auth_string',
|
|
u20@localhost IDENTIFIED WITH 'sha256_password',
|
|
u21@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2;
|
|
--replace_column 6 <authentication_plugin>
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,max_user_connections,
|
|
max_questions,password_lifetime FROM mysql.user
|
|
WHERE USER BETWEEN 'u18' AND 'u21' order by 1;
|
|
ALTER USER u18@localhost, u19@localhost,
|
|
u20@localhost, u21@localhost
|
|
REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB'
|
|
WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2
|
|
PASSWORD EXPIRE NEVER;
|
|
--replace_column 6 <authentication_plugin>
|
|
query_vertical SELECT User,ssl_type,ssl_cipher,x509_issuer,x509_subject,
|
|
plugin,password_expired,max_user_connections,
|
|
max_questions,password_lifetime FROM mysql.user
|
|
WHERE USER BETWEEN 'u18' AND 'u21' order by 1;
|
|
|
|
drop user u1@localhost, u2@localhost, u3@localhost, u4@localhost, u5@localhost,
|
|
u6@localhost, u7@localhost, u8@localhost, u9@localhost, u10@localhost,
|
|
u11@localhost, u12@localhost, u13@localhost, u14@localhost,
|
|
u15@localhost, u16@localhost, u17@localhost, u18@localhost,
|
|
u19@localhost, u20@localhost, u21@localhost;
|
|
|
|
--echo # CREATE USER with password expire attributes
|
|
|
|
CREATE USER u1@localhost PASSWORD EXPIRE NEVER;
|
|
--echo # This should report 0
|
|
SELECT password_lifetime FROM mysql.user where user='u1';
|
|
DROP USER u1@localhost;
|
|
|
|
CREATE USER u1@localhost PASSWORD EXPIRE DEFAULT;
|
|
--echo # This should report NULL
|
|
SELECT password_expired,password_lifetime FROM mysql.user where user='u1';
|
|
--exec $MYSQL -uu1 -e "EXIT" 2>&1
|
|
DROP USER u1@localhost;
|
|
|
|
CREATE USER u1@localhost PASSWORD EXPIRE INTERVAL 4 DAY;
|
|
--echo # Should report 4
|
|
SELECT password_lifetime FROM mysql.user where user='u1';
|
|
--exec $MYSQL -uu1 -e "EXIT" 2>&1
|
|
DROP USER u1@localhost;
|
|
|
|
CREATE USER u1@localhost PASSWORD EXPIRE;
|
|
--echo # This should report Y
|
|
SELECT password_expired FROM mysql.user where user='u1';
|
|
--error 1
|
|
--exec $MYSQL -uu1 -e "EXIT" 2>&1
|
|
DROP USER u1@localhost;
|
|
|
|
--echo # CREATE USER with password expire attributes for anonymous user
|
|
--error ER_CANNOT_USER
|
|
CREATE USER '' PASSWORD EXPIRE;
|
|
--error ER_CANNOT_USER
|
|
CREATE USER '' PASSWORD EXPIRE NEVER;
|
|
--error ER_CANNOT_USER
|
|
CREATE USER '' PASSWORD EXPIRE INTERVAL 4 DAY;
|
|
|
|
--echo # ALTER USER with user()
|
|
|
|
CREATE USER u1@localhost IDENTIFIED BY 'abc';
|
|
--connect(con1, localhost, u1, abc)
|
|
SELECT USER();
|
|
connection default;
|
|
ALTER USER u1@localhost PASSWORD EXPIRE;
|
|
disconnect con1;
|
|
|
|
--connect(con1, localhost, u1, abc)
|
|
--error ER_MUST_CHANGE_PASSWORD
|
|
SELECT USER();
|
|
SET PASSWORD = 'def';
|
|
--disable_warnings
|
|
disconnect con1;
|
|
--enable_warnings
|
|
|
|
--connect(con1, localhost, u1, def)
|
|
SELECT USER();
|
|
connection default;
|
|
ALTER USER u1@localhost PASSWORD EXPIRE;
|
|
disconnect con1;
|
|
|
|
--connect(con1, localhost, u1, def)
|
|
--error ER_MUST_CHANGE_PASSWORD
|
|
SELECT USER();
|
|
# password set to user()
|
|
--disable_ps_protocol
|
|
ALTER USER user() IDENTIFIED BY 'abc';
|
|
--enable_ps_protocol
|
|
disconnect con1;
|
|
|
|
--connect(con1, localhost, u1, abc)
|
|
SELECT USER();
|
|
connection default;
|
|
ALTER USER u1@localhost PASSWORD EXPIRE;
|
|
disconnect con1;
|
|
|
|
--connect(con1, localhost, u1, abc)
|
|
--error ER_MUST_CHANGE_PASSWORD
|
|
SELECT USER();
|
|
connection default;
|
|
ALTER USER u1@localhost IDENTIFIED BY 'def';
|
|
disconnect con1;
|
|
|
|
--connect(con1, localhost, u1, def)
|
|
SELECT USER();
|
|
connection default;
|
|
DROP USER u1@localhost;
|
|
disconnect con1;
|
|
|
|
--echo # ALTER USER with current user is allowed to set only credential information
|
|
|
|
CREATE USER u1@localhost, u2@localhost IDENTIFIED BY 'abc';
|
|
GRANT ALL ON *.* TO u2@localhost;
|
|
|
|
--connect(con1, localhost, u2, abc)
|
|
--error ER_PARSE_ERROR
|
|
ALTER USER USER() IDENTIFIED WITH 'sha256_password';
|
|
--error ER_PARSE_ERROR
|
|
ALTER USER USER() IDENTIFIED BY 'def', u2@localhost PASSWORD EXPIRE;
|
|
--error ER_PARSE_ERROR
|
|
ALTER USER USER() IDENTIFIED BY 'def' PASSWORD EXPIRE;
|
|
--error ER_PARSE_ERROR
|
|
ALTER USER ;
|
|
|
|
connection default;
|
|
disconnect con1;
|
|
DROP USER u1@localhost, u2@localhost;
|
|
|
|
# Wait till all disconnects are completed
|
|
--source include/wait_until_count_sessions.inc
|
|
|
|
|
|
--echo # SHOW CREATE USER
|
|
|
|
CREATE USER u1@localhost;
|
|
--replace_regex /WITH '(.*)' REQUIRE/WITH '<default_authentication_plugin>' REQUIRE/
|
|
SHOW CREATE USER u1@localhost;
|
|
ALTER USER u1@localhost IDENTIFIED BY 'auth_string';
|
|
--replace_regex /WITH '(.*)' AS '(.*)' REQUIRE/WITH '<default_authentication_plugin>' AS '<non-deterministic-password-hash>' REQUIRE/
|
|
SHOW CREATE USER u1@localhost;
|
|
|
|
CREATE USER u2@localhost IDENTIFIED BY 'auth_string';
|
|
--replace_regex /WITH '(.*)' AS '(.*)' REQUIRE/WITH '<default_authentication_plugin>' AS '<non-deterministic-password-hash>' REQUIRE/
|
|
SHOW CREATE USER u2@localhost;
|
|
ALTER USER u2@localhost IDENTIFIED WITH 'sha256_password';
|
|
--replace_regex /AS '(.*)' REQUIRE/AS '<non-deterministic-password-hash>' REQUIRE/
|
|
SHOW CREATE USER u2@localhost;
|
|
|
|
CREATE USER u3@localhost IDENTIFIED WITH 'sha256_password';
|
|
SHOW CREATE USER u3@localhost;
|
|
ALTER USER u3@localhost PASSWORD EXPIRE NEVER;
|
|
SHOW CREATE USER u3@localhost;
|
|
|
|
CREATE USER u4@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string';
|
|
--replace_regex /AS '(.*)'/AS '<non-deterministic-password-hash>'/
|
|
SHOW CREATE USER u4@localhost;
|
|
ALTER USER u4@localhost PASSWORD EXPIRE INTERVAL 365 DAY;
|
|
--replace_regex /AS '(.*)' REQUIRE/AS '<non-deterministic-password-hash>' REQUIRE/
|
|
SHOW CREATE USER u4@localhost;
|
|
|
|
CREATE USER u5@localhost REQUIRE SSL;
|
|
--replace_regex /WITH '(.*)' REQUIRE/WITH '<default_authentication_plugin>' REQUIRE/
|
|
SHOW CREATE USER u5@localhost;
|
|
ALTER USER u5@localhost REQUIRE CIPHER "DHE-RSA-AES256-SHA";
|
|
--replace_regex /WITH '(.*)' REQUIRE/WITH '<default_authentication_plugin>' REQUIRE/
|
|
SHOW CREATE USER u5@localhost;
|
|
|
|
CREATE USER u6@localhost IDENTIFIED BY 'auth_string' REQUIRE X509;
|
|
--replace_regex /WITH '(.*)' AS '(.*)' REQUIRE/WITH '<default_authentication_plugin>' AS '<non-deterministic-password-hash>' REQUIRE/
|
|
SHOW CREATE USER u6@localhost;
|
|
ALTER USER u6@localhost REQUIRE CIPHER "DHE-RSA-AES256-SHA" WITH MAX_QUERIES_PER_HOUR 2;
|
|
--replace_regex /WITH '(.*)' AS '(.*)' REQUIRE/WITH '<default_authentication_plugin>' AS '<non-deterministic-password-hash>' REQUIRE/
|
|
SHOW CREATE USER u6@localhost;
|
|
|
|
CREATE USER u7@localhost IDENTIFIED WITH 'sha256_password'
|
|
REQUIRE CIPHER 'DHE-RSA-AES256-SHA';
|
|
SHOW CREATE USER u7@localhost;
|
|
ALTER USER u7@localhost REQUIRE NONE WITH MAX_USER_CONNECTIONS 12;
|
|
SHOW CREATE USER u7@localhost;
|
|
|
|
CREATE USER u8@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
REQUIRE ISSUER 'issuer';
|
|
--replace_regex /AS '(.*)'/AS '<non-deterministic-password-hash>'/
|
|
SHOW CREATE USER u8@localhost;
|
|
ALTER USER u8@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
|
|
SHOW CREATE USER u8@localhost;
|
|
|
|
CREATE USER u9@localhost REQUIRE SUBJECT 'sub';
|
|
--replace_regex /WITH '(.*)' REQUIRE/WITH '<default_authentication_plugin>' REQUIRE/
|
|
SHOW CREATE USER u9@localhost;
|
|
ALTER USER u9@localhost;
|
|
--replace_regex /WITH '(.*)' REQUIRE/WITH '<default_authentication_plugin>' REQUIRE/
|
|
SHOW CREATE USER u9@localhost;
|
|
|
|
CREATE USER u10@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND
|
|
SUBJECT "/C=SE/ST=Uppsala/O=MySQL AB"
|
|
ISSUER "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB";
|
|
--replace_regex /AS '(.*)'/AS '<non-deterministic-password-hash>'/
|
|
SHOW CREATE USER u10@localhost;
|
|
ALTER USER u10@localhost PASSWORD EXPIRE NEVER;
|
|
--replace_regex /AS '(.*)'/AS '<non-deterministic-password-hash>'/
|
|
SHOW CREATE USER u10@localhost;
|
|
|
|
CREATE USER u11@localhost WITH MAX_QUERIES_PER_HOUR 2;
|
|
--replace_regex /WITH '(.*)' REQUIRE/WITH '<default_authentication_plugin>' REQUIRE/
|
|
SHOW CREATE USER u11@localhost;
|
|
ALTER USER u11@localhost WITH MAX_QUERIES_PER_HOUR 10;
|
|
--replace_regex /WITH '(.*)' REQUIRE/WITH '<default_authentication_plugin>' REQUIRE/
|
|
SHOW CREATE USER u11@localhost;
|
|
|
|
CREATE USER u12@localhost IDENTIFIED BY 'auth_string' WITH MAX_QUERIES_PER_HOUR 2;
|
|
--replace_regex /WITH '(.*)' AS '(.*)' REQUIRE/WITH '<default_authentication_plugin>' AS '<non-deterministic-password-hash>' REQUIRE/
|
|
SHOW CREATE USER u12@localhost;
|
|
ALTER USER u12@localhost REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 10;
|
|
--replace_regex /WITH '(.*)' AS '(.*)' REQUIRE/WITH '<default_authentication_plugin>' AS '<non-deterministic-password-hash>' REQUIRE/
|
|
SHOW CREATE USER u12@localhost;
|
|
|
|
CREATE USER u13@localhost IDENTIFIED WITH 'sha256_password'
|
|
WITH MAX_CONNECTIONS_PER_HOUR 2;
|
|
SHOW CREATE USER u13@localhost;
|
|
ALTER USER u13@localhost REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 10;
|
|
SHOW CREATE USER u13@localhost;
|
|
|
|
CREATE USER u14@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
WITH MAX_USER_CONNECTIONS 2;
|
|
--replace_regex /AS '(.*)' REQUIRE/AS '<non-deterministic-password-hash>' REQUIRE/
|
|
SHOW CREATE USER u14@localhost;
|
|
ALTER USER u14@localhost REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' WITH MAX_QUERIES_PER_HOUR 10
|
|
PASSWORD EXPIRE;
|
|
--replace_regex /AS '(.*)' REQUIRE/AS '<non-deterministic-password-hash>' REQUIRE/
|
|
SHOW CREATE USER u14@localhost;
|
|
|
|
CREATE USER u15@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB' ISSUER "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB"
|
|
CIPHER 'DHE-RSA-AES256-SHA' WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2;
|
|
--replace_regex /AS '(.*)' REQUIRE/AS '<non-deterministic-password-hash>' REQUIRE/
|
|
SHOW CREATE USER u15@localhost;
|
|
ALTER USER u15@localhost REQUIRE X509 PASSWORD EXPIRE INTERVAL 365 DAY;
|
|
--replace_regex /AS '(.*)' REQUIRE/AS '<non-deterministic-password-hash>' REQUIRE/
|
|
SHOW CREATE USER u15@localhost;
|
|
|
|
CREATE USER u16@localhost IDENTIFIED BY 'auth_string' PASSWORD EXPIRE;
|
|
--replace_regex /WITH '(.*)' AS '(.*)' REQUIRE/WITH '<default_authentication_plugin>' AS '<non-deterministic-password-hash>' REQUIRE/
|
|
SHOW CREATE USER u16@localhost;
|
|
ALTER USER u16@localhost REQUIRE X509 PASSWORD EXPIRE INTERVAL 365 DAY;
|
|
--replace_regex /WITH '(.*)' AS '(.*)' REQUIRE/WITH '<default_authentication_plugin>' AS '<non-deterministic-password-hash>' REQUIRE/
|
|
SHOW CREATE USER u16@localhost;
|
|
|
|
CREATE USER u17@localhost WITH MAX_QUERIES_PER_HOUR 200
|
|
MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE NEVER;
|
|
--replace_regex /WITH '(.*)' REQUIRE/WITH '<default_authentication_plugin>' REQUIRE/
|
|
SHOW CREATE USER u17@localhost;
|
|
ALTER USER u17@localhost REQUIRE X509 PASSWORD EXPIRE INTERVAL 365 DAY;
|
|
--replace_regex /WITH '(.*)' REQUIRE/WITH '<default_authentication_plugin>' REQUIRE/
|
|
SHOW CREATE USER u17@localhost;
|
|
|
|
CREATE USER u18@localhost IDENTIFIED WITH 'sha256_password' PASSWORD EXPIRE INTERVAL 365 DAY;
|
|
SHOW CREATE USER u18@localhost;
|
|
ALTER USER u18@localhost PASSWORD EXPIRE NEVER;
|
|
SHOW CREATE USER u18@localhost;
|
|
|
|
CREATE USER u19@localhost REQUIRE SUBJECT '/C=SE/ST=Uppsala/O=MySQL AB'
|
|
ISSUER "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB"
|
|
PASSWORD EXPIRE DEFAULT;
|
|
--replace_regex /WITH '(.*)' REQUIRE/WITH '<default_authentication_plugin>' REQUIRE/
|
|
SHOW CREATE USER u19@localhost;
|
|
ALTER USER u19@localhost WITH MAX_QUERIES_PER_HOUR 200
|
|
MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE NEVER;
|
|
--replace_regex /WITH '(.*)' REQUIRE/WITH '<default_authentication_plugin>' REQUIRE/
|
|
SHOW CREATE USER u19@localhost;
|
|
|
|
drop user u1@localhost, u2@localhost, u3@localhost, u4@localhost, u5@localhost,
|
|
u6@localhost, u7@localhost, u8@localhost, u9@localhost, u10@localhost,
|
|
u11@localhost, u12@localhost, u13@localhost, u14@localhost,
|
|
u15@localhost, u16@localhost, u17@localhost, u18@localhost,
|
|
u19@localhost;
|
|
|
|
--echo #
|
|
--echo # Bug #20553132 USER WITH EXPIRED PASSWORD ABLE TO EXECUTE
|
|
--echo # ALTER USER .. PASSWORD EXPIRE COMMAND
|
|
--echo #
|
|
|
|
connection default;
|
|
|
|
CREATE USER 20553132_u1@localhost;
|
|
CREATE USER 20553132_u2@localhost;
|
|
CREATE USER '20553132_u3'@'%';
|
|
GRANT ALL ON *.* TO 20553132_u1@localhost;
|
|
ALTER USER 20553132_u1@localhost PASSWORD EXPIRE;
|
|
ALTER USER '20553132_u3'@'%' PASSWORD EXPIRE;
|
|
|
|
--connect(con_20553132_u1, localhost, 20553132_u1)
|
|
--error ER_MUST_CHANGE_PASSWORD
|
|
ALTER USER 20553132_u1@localhost PASSWORD EXPIRE NEVER;
|
|
--error ER_MUST_CHANGE_PASSWORD
|
|
ALTER USER 20553132_u1@localhost PASSWORD EXPIRE DEFAULT;
|
|
--error ER_MUST_CHANGE_PASSWORD
|
|
ALTER USER 20553132_u1@localhost, 20553132_u2@localhost IDENTIFIED BY 'abcd' PASSWORD EXPIRE NEVER;
|
|
|
|
# Must succeed
|
|
--disable_ps_protocol
|
|
ALTER USER 20553132_u2@localhost IDENTIFIED BY 'abcd', 20553132_u1@localhost IDENTIFIED BY 'defg' PASSWORD EXPIRE NEVER;
|
|
--enable_ps_protocol
|
|
|
|
disconnect con_20553132_u1;
|
|
|
|
connection default;
|
|
ALTER USER 20553132_u1@localhost PASSWORD EXPIRE;
|
|
|
|
--connect(con_20553132_u1, localhost, 20553132_u1, defg)
|
|
# Must succeed
|
|
--disable_ps_protocol
|
|
ALTER USER 20553132_u2@localhost IDENTIFIED BY 'abcd', 20553132_u1@localhost IDENTIFIED WITH 'mysql_native_password' BY 'hijk' PASSWORD EXPIRE DEFAULT;
|
|
--enable_ps_protocol
|
|
disconnect con_20553132_u1;
|
|
|
|
--connect(con_20553132_u1, localhost, 20553132_u1, hijk)
|
|
SELECT USER();
|
|
|
|
disconnect con_20553132_u1;
|
|
|
|
--connect(con_20553132_u3, localhost, 20553132_u3)
|
|
--disable_ps_protocol
|
|
ALTER USER CURRENT_USER() IDENTIFIED BY 'abcd';
|
|
--enable_ps_protocol
|
|
SELECT CURRENT_USER();
|
|
disconnect con_20553132_u3;
|
|
|
|
connection default;
|
|
ALTER USER '20553132_u3'@'%' PASSWORD EXPIRE;
|
|
|
|
--connect(con_20553132_u3, localhost, 20553132_u3, abcd)
|
|
--disable_ps_protocol
|
|
ALTER USER '20553132_u3'@'%' IDENTIFIED BY 'abcd';
|
|
--enable_ps_protocol
|
|
SELECT CURRENT_USER();
|
|
disconnect con_20553132_u3;
|
|
|
|
connection default;
|
|
DROP USER 20553132_u1@localhost;
|
|
DROP USER 20553132_u2@localhost;
|
|
DROP USER '20553132_u3'@'%';
|
|
|
|
--source include/wait_until_count_sessions.inc
|
|
--echo
|
|
--echo End of 5.7 tests!
|
|
--echo
|
|
|
|
--echo
|
|
--echo Bug #20600865: IDENTIFIED BY PASSWORD IS NOT DEPRECATED FOR
|
|
--echo ALTER USER BUT DOESN\'T WORK
|
|
--echo
|
|
|
|
CREATE USER u1;
|
|
|
|
--error ER_PARSE_ERROR
|
|
ALTER USER u1 IDENTIFIED BY PASSWORD '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF';
|
|
|
|
--error ER_PARSE_ERROR
|
|
ALTER USER u1 IDENTIFIED BY PASSWORD '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
PASSWORD EXPIRE;
|
|
|
|
--error ER_PARSE_ERROR
|
|
ALTER USER u1 IDENTIFIED BY PASSWORD '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2;
|
|
|
|
--error ER_PARSE_ERROR
|
|
ALTER USER u1 IDENTIFIED BY PASSWORD '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND
|
|
SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client";
|
|
|
|
--error ER_PARSE_ERROR
|
|
ALTER USER u1 IDENTIFIED BY PASSWORD '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
|
|
PASSWORD EXPIRE DEFAULT;
|
|
|
|
#Cleanup
|
|
DROP USER u1;
|
|
|
|
--echo
|
|
--echo Bug #20634154 GRANT/ALTER USER CLEARS PASSWORD EXPIRE.
|
|
--echo
|
|
|
|
CREATE USER bug20634154@localhost IDENTIFIED BY 'abc';
|
|
--connect(con1, localhost, bug20634154, abc)
|
|
SELECT CURRENT_USER();
|
|
disconnect con1;
|
|
connection default;
|
|
ALTER USER bug20634154@localhost PASSWORD EXPIRE;
|
|
--connect(con1, localhost, bug20634154, abc)
|
|
--error ER_MUST_CHANGE_PASSWORD
|
|
SELECT CURRENT_USER();
|
|
disconnect con1;
|
|
connection default;
|
|
GRANT USAGE ON *.* TO bug20634154@localhost;
|
|
--connect(con1, localhost, bug20634154, abc)
|
|
--error ER_MUST_CHANGE_PASSWORD
|
|
SELECT CURRENT_USER();
|
|
disconnect con1;
|
|
connection default;
|
|
ALTER USER bug20634154@localhost;
|
|
--connect(con1, localhost, bug20634154, abc)
|
|
--error ER_MUST_CHANGE_PASSWORD
|
|
SELECT CURRENT_USER();
|
|
disconnect con1;
|
|
connection default;
|
|
ALTER USER bug20634154@localhost IDENTIFIED BY 'def';
|
|
--connect(con1, localhost, bug20634154, def)
|
|
SELECT CURRENT_USER();
|
|
disconnect con1;
|
|
connection default;
|
|
ALTER USER bug20634154@localhost IDENTIFIED BY 'abc' PASSWORD EXPIRE;
|
|
--connect(con1, localhost, bug20634154, abc)
|
|
--error ER_MUST_CHANGE_PASSWORD
|
|
SELECT CURRENT_USER();
|
|
disconnect con1;
|
|
connection default;
|
|
ALTER USER bug20634154@localhost IDENTIFIED BY 'def' PASSWORD EXPIRE INTERVAL 10 DAY;
|
|
--connect(con1, localhost, bug20634154, def)
|
|
# this will work
|
|
SELECT CURRENT_USER();
|
|
disconnect con1;
|
|
connection default;
|
|
#cleanup
|
|
DROP USER bug20634154@localhost;
|
|
|
|
--echo
|
|
--echo Bug #22205360 ALTER USER/SET PASSWORD DO NOT WORK FOR --INIT-FILE EXECUTION
|
|
--echo
|
|
|
|
CREATE USER bug22205360@localhost;
|
|
|
|
--write_file $MYSQLTEST_VARDIR/tmp/set_password.sql
|
|
SET PASSWORD FOR bug22205360@localhost= 'abc';
|
|
EOF
|
|
|
|
--echo # shutdown the server
|
|
--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
|
|
--shutdown_server
|
|
--source include/wait_until_disconnected.inc
|
|
|
|
--echo # Restart server with init-file option
|
|
--exec echo "restart:--init-file=$MYSQLTEST_VARDIR/tmp/set_password.sql" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
|
|
--enable_reconnect
|
|
--source include/wait_until_connected_again.inc
|
|
|
|
--connect(con1, localhost, bug22205360, abc)
|
|
SELECT 1;
|
|
connection default;
|
|
|
|
--write_file $MYSQLTEST_VARDIR/tmp/alter_password.sql
|
|
ALTER USER bug22205360@localhost IDENTIFIED BY 'def';
|
|
EOF
|
|
|
|
--echo # shutdown the server
|
|
--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
|
|
--shutdown_server
|
|
--source include/wait_until_disconnected.inc
|
|
|
|
--echo # Restart server with init-file option
|
|
--exec echo "restart:--init-file=$MYSQLTEST_VARDIR/tmp/alter_password.sql" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
|
|
--enable_reconnect
|
|
--source include/wait_until_connected_again.inc
|
|
|
|
--connect(con2, localhost, bug22205360, def)
|
|
SELECT 1;
|
|
|
|
disconnect con1;
|
|
disconnect con2;
|
|
connection default;
|
|
DROP USER bug22205360@localhost;
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/alter_password.sql
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/set_password.sql
|