mirror of
https://github.com/MariaDB/server.git
synced 2025-10-15 20:22:13 +02:00
eeb00ceffd
Follow-up patch with adjustments of test files and updates of result files for tests. Some of tests were rewritten slighlty. Everywhere where common pattern used: ----- CREATE USER userA; --connect con1 ... userA ... <sql statements...> --disconnect con1 DROP USER userA; ----- the DROP USER statement has been eclosed into the directive --disable_warnings --enable_warnings This change is caused by the race conddition between --disconnect and DROP USER since a number of currently running sessions established on behalf the user being dropped is counted by holding the rw_lock THD_list_iterator::lock that is not acquired on execution the DROP USER statement but the lock is taken as the last step on handling disconnection (when the client is already sending the next statement). Therefore, for the cases where the command --disconnect precedes the DROP USER statement we hide the possible warnings about presence of active sessions for the user being deleted to make tests deterministic.
306 lines
13 KiB
Text
306 lines
13 KiB
Text
# The numbers represent test cases of the test plan.
|
|
|
|
--source include/have_plugin_auth.inc
|
|
--source include/not_embedded.inc
|
|
#enable view protocol after fix MDEV-29542
|
|
--source include/no_view_protocol.inc
|
|
|
|
CREATE DATABASE test_user_db;
|
|
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
|
|
--echo ========== test 1.1.3.2 ====================================
|
|
|
|
# CREATE...WITH/CREATE...BY/GRANT
|
|
CREATE USER plug_user IDENTIFIED WITH test_plugin_server AS 'plug_dest';
|
|
CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd';
|
|
GRANT PROXY ON `plug%dest` TO plug_user;
|
|
GRANT ALL ON test_user_db.* TO plug_user;
|
|
--error 1
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1
|
|
REVOKE PROXY ON `plug%dest` FROM plug_user;
|
|
GRANT PROXY ON plug_dest TO plug_user;
|
|
GRANT ALL ON test_user_db.* TO 'plug_dest'@'%';
|
|
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1
|
|
REVOKE PROXY ON plug_dest FROM plug_user;
|
|
--error 1
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1
|
|
--disable_warnings
|
|
DROP USER plug_user,plug_dest;
|
|
--enable_warnings
|
|
#
|
|
# GRANT...WITH
|
|
GRANT ALL PRIVILEGES ON test_user_db.* TO plug_user
|
|
IDENTIFIED WITH test_plugin_server AS 'plug_dest';
|
|
GRANT ALL PRIVILEGES ON test_user_db.* TO plug_dest IDENTIFIED BY 'plug_dest_passwd';
|
|
GRANT PROXY ON plug_dest TO plug_user;
|
|
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
--echo 1)
|
|
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1
|
|
REVOKE ALL PRIVILEGES ON test_user_db.* FROM 'plug_user';
|
|
--echo 2)
|
|
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1
|
|
REVOKE PROXY ON plug_dest FROM plug_user;
|
|
--echo 3)
|
|
--error 1
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1
|
|
DROP USER plug_user,plug_dest;
|
|
#
|
|
# GRANT...WITH/CREATE...BY
|
|
GRANT ALL PRIVILEGES ON test_user_db.* TO plug_user
|
|
IDENTIFIED WITH test_plugin_server AS 'plug_dest';
|
|
CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd';
|
|
--echo 1)
|
|
--error 1
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1
|
|
GRANT PROXY ON plug_dest TO plug_user;
|
|
GRANT ALL ON test_user_db.* TO 'plug_dest'@'%';
|
|
--echo 2)
|
|
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1
|
|
REVOKE ALL PRIVILEGES ON test_user_db.* FROM 'plug_user';
|
|
#REVOKE ALL PRIVILEGES ON test_user_db.* FROM 'plug_dest'';
|
|
DROP USER plug_user,plug_dest;
|
|
|
|
--echo ========== test 1.2 ========================================
|
|
|
|
# GRANT...WITH/CREATE...BY
|
|
GRANT ALL PRIVILEGES ON test_user_db.* TO plug_user
|
|
IDENTIFIED WITH test_plugin_server AS 'plug_dest';
|
|
CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd';
|
|
GRANT PROXY ON plug_dest TO plug_user;
|
|
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();" 2>&1
|
|
RENAME USER plug_dest TO new_dest;
|
|
--error 1
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();" 2>&1
|
|
GRANT PROXY ON new_dest TO plug_user;
|
|
--error 1
|
|
--exec $MYSQL -u plug_user --password=new_dest -e "SELECT current_user();SELECT user();" 2>&1
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
DROP USER plug_user,new_dest;
|
|
|
|
# CREATE...WITH/CREATE...BY
|
|
CREATE USER plug_user
|
|
IDENTIFIED WITH test_plugin_server AS 'plug_dest';
|
|
CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd';
|
|
--error 1
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();" 2>&1
|
|
GRANT PROXY ON plug_dest TO plug_user;
|
|
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();" 2>&1
|
|
RENAME USER plug_dest TO new_dest;
|
|
--error 1
|
|
--exec $MYSQL -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();" 2>&1
|
|
GRANT PROXY ON new_dest TO plug_user;
|
|
--error 1
|
|
--exec $MYSQL -u plug_user --password=new_dest -e "SELECT current_user();SELECT user();" 2>&1
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
DROP USER plug_user,new_dest;
|
|
# CREATE...WITH
|
|
CREATE USER plug_user
|
|
IDENTIFIED WITH test_plugin_server AS 'plug_dest';
|
|
CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd';
|
|
GRANT PROXY ON plug_dest TO plug_user;
|
|
connect(plug_user,localhost,plug_user,plug_dest,"*NO-ONE*");
|
|
select USER(),CURRENT_USER();
|
|
connection default;
|
|
disconnect plug_user;
|
|
RENAME USER plug_user TO new_user;
|
|
connect(plug_user,localhost,new_user,plug_dest,"*NO-ONE*");
|
|
select USER(),CURRENT_USER();
|
|
connection default;
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
disconnect plug_user;
|
|
UPDATE mysql.global_priv SET user='plug_user' WHERE user='new_user';
|
|
FLUSH PRIVILEGES;
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
--disable_warnings
|
|
DROP USER plug_dest,plug_user;
|
|
--enable_warnings
|
|
--echo ========== test 1.3 ========================================
|
|
|
|
#
|
|
CREATE USER plug_user
|
|
IDENTIFIED WITH test_plugin_server AS 'plug_dest';
|
|
CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd';
|
|
GRANT PROXY ON plug_dest TO plug_user;
|
|
connect(plug_user,localhost,plug_user,plug_dest,"*NO-ONE*");
|
|
select USER(),CURRENT_USER();
|
|
connection default;
|
|
disconnect plug_user;
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
UPDATE mysql.global_priv SET user='new_user' WHERE user='plug_user';
|
|
FLUSH PRIVILEGES;
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
UPDATE mysql.global_priv SET priv=JSON_SET(priv, '$.authentication_string', 'new_dest') WHERE user='new_user';
|
|
FLUSH PRIVILEGES;
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
UPDATE mysql.global_priv SET priv=JSON_SET(priv, '$.plugin', 'new_plugin_server') WHERE user='new_user';
|
|
FLUSH PRIVILEGES;
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
--disable_query_log
|
|
--error ER_PLUGIN_IS_NOT_LOADED
|
|
connect(plug_user,localhost,new_user,new_dest);
|
|
--enable_query_log
|
|
UPDATE mysql.global_priv SET priv=JSON_SET(priv, '$.plugin', 'test_plugin_server') WHERE user='new_user';
|
|
UPDATE mysql.global_priv SET user='new_dest' WHERE user='plug_dest';
|
|
FLUSH PRIVILEGES;
|
|
GRANT PROXY ON new_dest TO new_user;
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
connect(plug_user,localhost,new_user,new_dest,"*NO-ONE*");
|
|
select USER(),CURRENT_USER();
|
|
connection default;
|
|
disconnect plug_user;
|
|
UPDATE mysql.global_priv SET user='plug_dest' WHERE user='new_dest';
|
|
FLUSH PRIVILEGES;
|
|
CREATE USER new_dest IDENTIFIED BY 'new_dest_passwd';
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
GRANT ALL PRIVILEGES ON test.* TO new_user;
|
|
connect(plug_user,localhost,new_dest,new_dest_passwd,"*NO-ONE*");
|
|
select USER(),CURRENT_USER();
|
|
connection default;
|
|
disconnect plug_user;
|
|
--disable_warnings
|
|
DROP USER new_user,new_dest,plug_dest;
|
|
--enable_warnings
|
|
|
|
--echo ========== test 2, 2.1, 2.2 ================================
|
|
|
|
CREATE USER ''@'%%' IDENTIFIED WITH test_plugin_server AS 'proxied_user';
|
|
CREATE USER proxied_user IDENTIFIED BY 'proxied_user_passwd';
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
connect(proxy_con,localhost,proxied_user,proxied_user_passwd,"*NO-ONE*");
|
|
SELECT USER(),CURRENT_USER();
|
|
--echo ========== test 2.2.1 ======================================
|
|
SELECT @@proxy_user;
|
|
connection default;
|
|
disconnect proxy_con;
|
|
--disable_query_log
|
|
--error ER_ACCESS_DENIED_ERROR : this should fail : no grant
|
|
connect(proxy_con,localhost,proxy_user,proxied_user);
|
|
--enable_query_log
|
|
GRANT PROXY ON proxied_user TO ''@'%%';
|
|
connect(proxy_con,localhost,proxied_user,proxied_user_passwd,"*NO-ONE*");
|
|
SELECT USER(),CURRENT_USER();
|
|
connection default;
|
|
disconnect proxy_con;
|
|
connect(proxy_con,localhost,proxy_user,proxied_user,"*NO-ONE*");
|
|
SELECT USER(),CURRENT_USER();
|
|
--echo ========== test 2.2.1 ======================================
|
|
SELECT @@proxy_user;
|
|
connection default;
|
|
disconnect proxy_con;
|
|
--disable_warnings
|
|
DROP USER ''@'%%',proxied_user;
|
|
--enable_warnings
|
|
#
|
|
GRANT ALL PRIVILEGES ON test_user_db.* TO ''@'%%'
|
|
IDENTIFIED WITH test_plugin_server AS 'proxied_user';
|
|
CREATE USER proxied_user IDENTIFIED BY 'proxied_user_passwd';
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
connect(proxy_con,localhost,proxied_user,proxied_user_passwd,"*NO-ONE*");
|
|
SELECT USER(),CURRENT_USER();
|
|
SELECT @@proxy_user;
|
|
connection default;
|
|
disconnect proxy_con;
|
|
--disable_query_log
|
|
--error ER_ACCESS_DENIED_ERROR : this should fail : no grant
|
|
connect(proxy_con,localhost,proxy_user,proxied_user);
|
|
--enable_query_log
|
|
GRANT PROXY ON proxied_user TO ''@'%%';
|
|
connect(proxy_con,localhost,proxied_user,proxied_user_passwd,"*NO-ONE*");
|
|
SELECT USER(),CURRENT_USER();
|
|
connection default;
|
|
disconnect proxy_con;
|
|
connect(proxy_con,localhost,proxy_user,proxied_user,"*NO-ONE*");
|
|
SELECT USER(),CURRENT_USER();
|
|
SELECT @@proxy_user;
|
|
connection default;
|
|
disconnect proxy_con;
|
|
--disable_warnings
|
|
DROP USER ''@'%%',proxied_user;
|
|
--enable_warnings
|
|
#
|
|
CREATE USER ''@'%%' IDENTIFIED WITH test_plugin_server AS 'proxied_user';
|
|
CREATE USER proxied_user_1 IDENTIFIED BY 'proxied_user_1_pwd';
|
|
CREATE USER proxied_user_2 IDENTIFIED BY 'proxied_user_2_pwd';
|
|
CREATE USER proxied_user_3 IDENTIFIED BY 'proxied_user_3_pwd';
|
|
CREATE USER proxied_user_4 IDENTIFIED BY 'proxied_user_4_pwd';
|
|
CREATE USER proxied_user_5 IDENTIFIED BY 'proxied_user_5_pwd';
|
|
GRANT PROXY ON proxied_user_1 TO ''@'%%';
|
|
GRANT PROXY ON proxied_user_2 TO ''@'%%';
|
|
GRANT PROXY ON proxied_user_3 TO ''@'%%';
|
|
GRANT PROXY ON proxied_user_4 TO ''@'%%';
|
|
GRANT PROXY ON proxied_user_5 TO ''@'%%';
|
|
--sorted_result
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
connect(proxy_con_1,localhost,proxied_user_1,'proxied_user_1_pwd',"*NO-ONE*");
|
|
connect(proxy_con_2,localhost,proxied_user_2,proxied_user_2_pwd,"*NO-ONE*");
|
|
connect(proxy_con_3,localhost,proxied_user_3,proxied_user_3_pwd,"*NO-ONE*");
|
|
connect(proxy_con_4,localhost,proxied_user_4,proxied_user_4_pwd,"*NO-ONE*");
|
|
connect(proxy_con_5,localhost,proxied_user_5,proxied_user_5_pwd,"*NO-ONE*");
|
|
connection proxy_con_1;
|
|
SELECT USER(),CURRENT_USER();
|
|
SELECT @@proxy_user;
|
|
connection proxy_con_2;
|
|
SELECT USER(),CURRENT_USER();
|
|
SELECT @@proxy_user;
|
|
connection proxy_con_3;
|
|
SELECT USER(),CURRENT_USER();
|
|
SELECT @@proxy_user;
|
|
connection proxy_con_4;
|
|
SELECT USER(),CURRENT_USER();
|
|
SELECT @@proxy_user;
|
|
connection proxy_con_5;
|
|
SELECT USER(),CURRENT_USER();
|
|
SELECT @@proxy_user;
|
|
connection default;
|
|
disconnect proxy_con_1;
|
|
disconnect proxy_con_2;
|
|
disconnect proxy_con_3;
|
|
disconnect proxy_con_4;
|
|
disconnect proxy_con_5;
|
|
--disable_warnings
|
|
DROP USER ''@'%%',proxied_user_1,proxied_user_2,proxied_user_3,proxied_user_4,proxied_user_5;
|
|
--enable_warnings
|
|
|
|
--echo ========== test 3 ==========================================
|
|
|
|
GRANT ALL PRIVILEGES ON *.* TO plug_user
|
|
IDENTIFIED WITH test_plugin_server AS 'plug_dest';
|
|
CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd';
|
|
GRANT PROXY ON plug_dest TO plug_user;
|
|
FLUSH PRIVILEGES;
|
|
|
|
# Not working with the patch.
|
|
|
|
#--replace_result $MYSQLADMIN MYSQLADMIN $MASTER_MYPORT MYPORT $MASTER_MYSOCK MYSOCK
|
|
#--exec $MYSQLADMIN -h localhost -u plug_user --password=plug_dest ping 2>&1
|
|
#--replace_result $MYSQL_CHECK MYSQL_CHECK $MASTER_MYPORT MYPORT
|
|
#--exec $MYSQL_CHECK -h localhost -u plug_user --password=plug_dest test
|
|
#--replace_result $MYSQL_DUMP MYSQL_DUMP $MASTER_MYPORT MYPORT
|
|
#--exec $MYSQL_DUMP -h localhost -u plug_user --password=plug_dest test
|
|
#--replace_result $MYSQL_SHOW MYSQL_SHOW $MASTER_MYPORT MYPORT
|
|
#--exec $MYSQL_SHOW -h localhost --plugin_dir=../plugin/auth -u plug_user --password=plug_dest 2>&1
|
|
DROP USER plug_user, plug_dest;
|
|
DROP DATABASE test_user_db;
|
|
--exit
|