219 lines
7.8 KiB
Text
219 lines
7.8 KiB
Text
# WL#8540
|
|
# Test behavior of IF [NOT] EXISTS clause
|
|
# in CREATE / ALTER / DROP USER commands.
|
|
#
|
|
CALL mtr.add_suppression('Following users were specified in CREATE USER IF NOT EXISTS but they already exist');
|
|
CALL mtr.add_suppression('Following users were specified in ALTER USER IF EXISTS but they do not exist');
|
|
|
|
# cleanup
|
|
DELETE FROM mysql.user WHERE user = 'wl8540';
|
|
FLUSH PRIVILEGES;
|
|
|
|
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
|
|
CREATE USER wl8540@host1;
|
|
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
|
|
--error ER_CANNOT_USER
|
|
CREATE USER wl8540@host1;
|
|
CREATE USER IF NOT EXISTS wl8540@host1;
|
|
SHOW WARNINGS;
|
|
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
|
|
CREATE USER IF NOT EXISTS wl8540@host1, wl8540@host2;
|
|
SHOW WARNINGS;
|
|
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
|
|
CREATE USER IF NOT EXISTS wl8540@host1, wl8540@host2;
|
|
SHOW WARNINGS;
|
|
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
|
|
|
|
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540' AND account_locked = 'Y';
|
|
--error ER_CANNOT_USER
|
|
ALTER USER wl8540@host3 ACCOUNT LOCK;
|
|
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540' AND account_locked = 'Y';
|
|
ALTER USER IF EXISTS wl8540@host3, wl8540@host4, wl8540@host1 ACCOUNT LOCK;
|
|
SHOW WARNINGS;
|
|
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540' AND account_locked = 'Y';
|
|
ALTER USER IF EXISTS wl8540@host2 ACCOUNT LOCK;
|
|
SHOW WARNINGS;
|
|
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540' AND account_locked = 'Y';
|
|
|
|
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
|
|
--error ER_CANNOT_USER
|
|
DROP USER wl8540@host3;
|
|
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
|
|
DROP USER IF EXISTS wl8540@host3;
|
|
SHOW WARNINGS;
|
|
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
|
|
DROP USER IF EXISTS wl8540@host3,wl8540@host2,wl8540@host4;
|
|
SHOW WARNINGS;
|
|
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
|
|
|
|
# No users exist:
|
|
|
|
DROP USER IF EXISTS wl8540@nohost1, wl8540@nohost2;
|
|
SHOW WARNINGS;
|
|
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
|
|
ALTER USER IF EXISTS wl8540@nohost1, wl8540@nohost2 ACCOUNT LOCK;
|
|
SHOW WARNINGS;
|
|
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
|
|
CREATE USER IF NOT EXISTS wl8540@nohost1, wl8540@nohost2;
|
|
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
|
|
|
|
# All users exist:
|
|
|
|
ALTER USER IF EXISTS wl8540@nohost1, wl8540@nohost2 ACCOUNT LOCK;
|
|
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
|
|
CREATE USER IF NOT EXISTS wl8540@nohost1, wl8540@nohost2;
|
|
SHOW WARNINGS;
|
|
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
|
|
DROP USER IF EXISTS wl8540@nohost1, wl8540@nohost2;
|
|
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
|
|
|
|
CREATE USER wl8540@nohost1;
|
|
|
|
# One of two users exist:
|
|
|
|
DROP USER IF EXISTS wl8540@nohost1, wl8540@nohost2;
|
|
SHOW WARNINGS;
|
|
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
|
|
ALTER USER IF EXISTS wl8540@nohost1, wl8540@nohost2 ACCOUNT LOCK;
|
|
SHOW WARNINGS;
|
|
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
|
|
CREATE USER IF NOT EXISTS wl8540@nohost1, wl8540@nohost2;
|
|
SHOW WARNINGS;
|
|
SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
|
|
|
|
# cleanup
|
|
DELETE FROM mysql.user WHERE user = 'wl8540';
|
|
FLUSH PRIVILEGES;
|
|
|
|
--echo
|
|
# QA test cases:
|
|
|
|
# CRAETE USER IF NOT EXISTS , ALTER USER IF EXISTS testing
|
|
# with --log-row=off (By default it's OFF).
|
|
|
|
#
|
|
--echo
|
|
CREATE USER user1@localhost
|
|
IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y';
|
|
|
|
CREATE USER IF NOT EXISTS user2@localhost
|
|
IDENTIFIED WITH 'mysql_native_password'
|
|
AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF';
|
|
|
|
query_vertical SELECT User,plugin,authentication_string,ssl_type,
|
|
password_expired,password_lifetime FROM mysql.user WHERE USER='user2';
|
|
|
|
CREATE USER IF NOT EXISTS user2@localhost
|
|
IDENTIFIED WITH 'sha256_password';
|
|
query_vertical SELECT User,plugin,authentication_string,ssl_type,
|
|
password_expired,password_lifetime FROM mysql.user WHERE USER='user2';
|
|
|
|
ALTER USER IF EXISTS user2@localhost
|
|
IDENTIFIED WITH 'mysql_native_password'
|
|
AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF';
|
|
|
|
query_vertical SELECT User,plugin,authentication_string,ssl_type,
|
|
password_expired,password_lifetime FROM mysql.user WHERE USER='user2';
|
|
|
|
# CREATE USER IF NOT EXISTS statements with plain-text passwords will work
|
|
CREATE USER IF NOT EXISTS user1@localhost
|
|
IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y';
|
|
|
|
# Operation CREATE USER works but triggers a warning
|
|
CREATE USER IF NOT EXISTS ne_user1@localhost,user1@localhost
|
|
IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
|
|
|
|
# Operation ALTER USER works with a password too
|
|
ALTER USER IF EXISTS ne_user2@localhost
|
|
IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y';
|
|
# Operation ALTER USER works with a password too
|
|
ALTER USER IF EXISTS user1@localhost,ne_user3@localhost
|
|
IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y';
|
|
|
|
# Cleanup
|
|
DROP USER IF EXISTS user1@localhost,user2@localhost,ne_user1@localhost,
|
|
ne_user2@localhost,ne_user3@localhost;
|
|
|
|
--echo
|
|
# CRAETE USER IF NOT EXISTS , ALTER USER IF EXISTS testing
|
|
# with --log-row=ON
|
|
--echo # Restarting the server with --log-row ON
|
|
|
|
# Write file to make mysql-test-run.pl wait for the server to stop.
|
|
let $expect_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect;
|
|
--exec echo "wait" > $expect_file
|
|
|
|
# Request shutdown
|
|
--send_shutdown
|
|
#--sleep 1
|
|
# Call script that will poll the server waiting for it to disapear
|
|
--source include/wait_until_disconnected.inc
|
|
|
|
--echo # Restart server.
|
|
--exec echo "restart:--log-raw=ON " > $expect_file
|
|
|
|
# Call script that will poll the server waiting for it to be back online again
|
|
--source include/wait_until_connected_again.inc
|
|
|
|
# CREATE USER IF NOT EXISTS statements with plain-text passwords should not
|
|
# trigger an error if the user does exist and --log_raw=ON.
|
|
CREATE USER user1@localhost
|
|
IDENTIFIED WITH 'mysql_native_password';
|
|
|
|
CREATE USER IF NOT EXISTS user2@localhost
|
|
IDENTIFIED WITH 'mysql_native_password'
|
|
AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF';
|
|
|
|
CREATE USER IF NOT EXISTS user1@localhost
|
|
IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y';
|
|
|
|
CREATE USER IF NOT EXISTS ne_user1@localhost,user1@localhost
|
|
IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
|
|
|
|
# ALTER USER IF EXISTS statements with plain-text passwords should not trigger
|
|
# an error if the user does not exist and --log_raw=ON
|
|
ALTER USER IF EXISTS ne_user2@localhost
|
|
IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y';
|
|
ALTER USER IF EXISTS user1@localhost,ne_user3@localhost
|
|
IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y';
|
|
|
|
# Cleanup
|
|
DROP USER IF EXISTS user1@localhost,user2@localhost,ne_user1@localhost,
|
|
ne_user2@localhost,ne_user3@localhost;
|
|
|
|
# restore --log-raw option
|
|
let $restart_parameters = restart:;
|
|
--source include/restart_mysqld.inc
|
|
|
|
|
|
--echo #
|
|
--echo # Bug #21807286: "CREATE USER IF NOT EXISTS" REPORTS AN ERROR
|
|
--echo #
|
|
|
|
CREATE USER IF NOT EXISTS b21807286@localhost IDENTIFIED BY 'xyz';
|
|
--echo # Must not fail but return a warning
|
|
CREATE USER IF NOT EXISTS b21807286@localhost IDENTIFIED BY 'xyz';
|
|
DROP USER b21807286@localhost;
|
|
--echo # Must not fail but return a warning
|
|
ALTER USER IF EXISTS b21807286@localhost IDENTIFIED BY 'xyz';
|
|
|
|
--echo # End of 5.7 tests
|
|
|
|
--echo #
|
|
--echo # Bug #34906592: CREATE USER IF NOT EXISTS adds a password history entry
|
|
--echo #
|
|
|
|
SET GLOBAL password_history = 5;
|
|
CREATE USER IF NOT EXISTS b34906592@localhost IDENTIFIED BY 'password';
|
|
--echo # Test: should return 0 rows.
|
|
SELECT User,Host FROM mysql.password_history WHERE User = 'b34906592';
|
|
--echo # Test: shouls succeed with a warning that user exists
|
|
CREATE USER IF NOT EXISTS b34906592@localhost IDENTIFIED BY 'password';
|
|
--echo # Test: should return 0 rows.
|
|
SELECT User,Host FROM mysql.password_history WHERE User = 'b34906592';
|
|
|
|
#cleanup
|
|
DROP USER b34906592@localhost;
|
|
SET GLOBAL password_history = default;
|
|
|
|
--echo # End of 8.0 tests
|